This finance video tutorial explains how to create an amortization table in excel. Step 1: Set up your amortization table.Open a new Excel sheet and create input cells for the known components of your loan. It is actually quite simple. You just need to make a table in excel showing Opening Balance, adding interest and reducing through payments. Here is the link to my spreadsheet where you can calculate a loan over x years to be paid back monthly. It will give you the amortized payment amount. 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. 1. Download the amortization schedule from Microsoft. 2.