This finance video tutorial explains how to create an amortization table in excel. Loan Amortization Formula​​ In order to create a loan amortization schedule in Excel, we can utilize the following built-in functions: Excel PMT Function.Step 1: Set up your amortization table. Open a new Excel sheet and create input cells for the known components of your loan. This example teaches you how to create a loan amortization schedule in Excel. 1. We use the PMT function to calculate the monthly payment on a loan.