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.
Creating an Amortization Table
You may already be familiar with amortization tables and not realize it. Most of us who have a mortgage or have borrowed money from the bank will have received an amortization schedule from our lender so we can see the breakdown of our monthly payments. It is useful to know how to create an amortization table in Excel particularly if you work in the financial sector. Even if you don't, the process of creating an amortization table introduces us to some new techniques and formulas in Excel.
We can download an amortization table template in
Excel or we can start from scratch and create our own.
To download a template
- Go to the File tab.
- Click on the New page.
- Type "amortization" into the search bar.
In this example, we are going to start from a blank template that does not contain any formulas.
Understanding the Amortization Schedule
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.
Calculating the Monthly Payment
First, let's calculate the monthly payment.
Period Rate
The Period Rate calculation works out the monthly interest rate. Currently, in the table above the interest rate is per annum. We want to know what our monthly payments will be so all of our calculations must be monthly.
This is a simple SUM calculation. We divide the Interest Rate (Annual) by the Number of periods per year.
We are paying a monthly interest rate of 0.31250%.
Total Number of Periods
Next, we need to work out the total number of periods the loan will be paid over. A month is classified as a period so effectively, how many months are we paying the loan over.
If you have the number of months already in the table then you do not need to do this calculation. In our example, we have the Loan Period showing as 30 years so we need to convert this to months.
This is a simple SUM calculation. We need to multiply the Loan Periods (Years) by the number of periods per year.
The loan will be paid over a total of 360 months.
Monthly Payment
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.
Completing 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.
Payment Number
We are paying the loan over 360 months so we need to have 0 - 360 listed in the Payment Number column. Typing out these numbers manually would be a very tedious task. Fortunately, there is a magic trick in Excel that can help us with this.
- Type 0 into the first cell.
- Hold down the right mouse button, drag the fill handle down one cell and then back up one cell to reveal a secret menu.
- Select Series from the menu.
- In the Series in area, select Columns.
- Add a Stop value of 360.
This will quickly fill down 1 - 360 in the Payment Number column.
Payment Date
Next, we need to enter the Payment Date for each payment. We have the first payment date listed in the table. In our example, we are going to make a payment on the last day of the month.
- Manually type the date of the first payment in the cell next to payment 1.
- Double-click the fill handle to fill the dates down.
Notice that the dates are not correct. By default, Excel will assume we want consecutive dates. We can easily change this.
- Click the Autofill Options button.
- Select Fill Months from the menu.
The dates will now be the last day of every month.
PMT
Next, we need to bring our PMT calculation to the table. We can simply link to the cell.
- Click in the cell and type =
- Select the cell that contains the PMT calculation.
The amount we pay each month stays the same so remember to press
F4 to make the cell absolute.
- Double-click the fill handle to copy the formula down.
Interest Paid
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.
Principal Reduction
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.
Ending Balance
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.
Completing the Amortization Table
Now we have all the basic calculations in place, we can simply copy them down to complete the table.
- Select all cells in the second row that contain calculations.
- Double-click on the fill handle to copy down.
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.
Rounding
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.
- Press F2 to edit the PMT formula.
- Round the result to 2 digits.
- Press F2 to edit the Interest Paid formula.
- Round the result to 2 digits.
- Copy the formula down using the fill handle.
For additional information on this topic in the MMC course library, check out all our Excel courses.