Fill in the column titled "Period" with your payment dates. Input the month and year for the first payment loan within cell A8.Calculate Total Payment Amount (PMT Formula). Loan Amortization Formula ; 1. 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. This Microsoft Excel template illustrates how to create a loan amortization schedule, as well as dealing with additional payments and variable interest rates. Open an Excel spreadsheet to help with your calculations. Create a table like the one below. Now that you've set up your basic sheet, let's calculate the monthly payment using Excel's PMT function.