Building an Amortization Table

Amortization is the process of spreading a loan into payments that consist of both principal and interest over a set timeline, called an amortization schedule.

Amortization tables show a detailed, chronological breakdown of every payment allocated towards a commercial real estate loan, helping you visualize how a loan is paid off in a transparent and understandable way.

Here’s how you can build an amortization table in 7 steps:

1. Date: This will be the payment date of the loan. Use the ‘EOMONTH’ formula in Excel to calculate the future payment dates in descending order.

2. Month & Year: Calculate the numeric month and year in descending order.

3. Monthly Payment: This will be the total payment paid to the lender every month. You will use the ‘PMT’ formula in Excel to calculate this.

4. Principle Payment: An easy way to calculate the principal portion of the payment is to subtract the interest amount from the total monthly payment.

5. Interest Payment: To calculate interest on a loan, multiply your annual interest rate by the beginning loan balance for that month, and divide by 12 (to get monthly payment).

6. Beginning Balance: This is your current outstanding loan balance in the beginning of the month.

7. Ending Balance: This is your outstanding loan balance for the month, less any principle paid. If your loan is interest only (no principle paid), the ending loan balance will not change.


Check out our free amortization table!