Amortization tables are commonly used in the financial sector. They list out the periodic payments on a loan or mortgage over a period of time and break down each payment into principal and interest. When we create an amortization table in Excel we can define, the amount we are borrowing, the interest rate, the number of payments, if we are paying at the beginning or end of the month, the start date, and if we have a balloon payment to clear the balance.
In this example, we are going to start from a blank template that does not contain any formulas.
Before we start adding calculations and formulas, let's first understand what we are looking at.
At the top, we have the details we need to calculate the monthly payments on a loan.
In this example, we are borrowing $350,000 to buy a house. We have $50,000 in savings which we will use at the end of the loan to clear the balance.
To calculate our monthly payments over the term of the loan, we need to first use formulas to work out the Period Rate, the Total Number of Periods, and the Monthly Payment. Once we have these calculations, we can use them to complete the amortization table.
We are paying a monthly interest rate of 0.31250%.
The loan will be paid over a total of 360 months.
We can now use the PMT function in Excel to calculate the monthly payment. PMT stands for Payment and it is one of Excel's Financial functions.
This is how our PMT formula looks. Note the highlighted cells used in the calculation.
The PMT formula will produce a negative result by default. This is because Excel sees this money as being taken from your account.
To make this a positive value, press F2 to edit the PMT formula and add a minus sign before the pv.
The total monthly payment for this loan is $1,545.60.
We can now use the monthly payment to complete the amortization table.
An amortization table allows us to see how much interest we are paying and how much the principal amount decreases by over the period of the loan. We need to complete each column in the table.
The dates will now be the last day of every month.
Next, we need to calculate the amount of interest paid each month. This will change as the loan reduces. To make this easier, we are going to add the Ending Balance in the first row of the table.
The Interest Paid is the Balance * Monthly Interest Rate. We are going to copy this formula down so remember to lock cell D13. This calculation tells us how much interest we will pay each month.
Next, we need to calculate the Principal Reduction. This is the amount we are paying on the balance of the loan that is not interest. This is a simple calculation of PMT - Interest Paid.
Finally, we need to calculate the Ending Balance. This is the amount outstanding on the loan each month. This is a simple calculation of Ending Balance - Principal Reduction.
If the amortization table has been completed correctly, we should see that payment 360 has an ending balance of $50,000. It is at this point we would make our balloon payment to clear the loan.
Notice that as the loan reduces the amount of interest we are paying also reduces and the amount we are paying off the principal increases.
When working with financial spreadsheets and money, we need to be as accurate as possible. If we apply rounding to our amortization table, the ending balance will be a few pennies off but the calculations will be more accurate.
To round all calculations in the amortization table, we only need to round the PMT formula and the Interest Paid formula.
For additional information on this topic in the MMC course library, check out all our Excel courses.