How To Create An Amortisation Schedule For Your Home Loan?

By iCompareLoan Editorial Team

If you are a serious borrower keen to learn how the interest and principle payments of your home loan change each month, this article attempts to teach you to create an amortisation schedule for your home loan.

An amortisation schedule presents in a table the total payment, interest, principle and outstanding balance in each period. It can be created with a

• Financial calculator

or

• Spreadsheet, like Excel

Unless you are in a financial industry, typically people will not own a financial calculator; but almost everybody has Excel or some other spreadsheet installed on their computer. So here, we will utilise Excel. Nevertheless most of the techniques taught here can also be applied to other spreadsheet programs.

Excel functions

First of all, we will look at the three functions we need to use (Do note that most other spreadsheet programs use the exact same functions).

1. PMT (Rate, NPer,PV, FV, Type): Total payment (principle and interest) payable for that compounding period
2. PPMT (Rate, Per, NPer, PV, FV, Type): Principle payable for that compounding period
3. IPMT (Rate, Per, NPer, PV, FV, Type): Interest payable for that compounding period

So what is each of the variables in the parenthesis? Note that the ones in italics are optional.

• Rate (Required): Nominal interest rate for that compounding period

• NPer (Required): Total number of compounding periods

• PV (Required): Present value of the loan

• FV: Loan amount outstanding after all payments have been made. If this variable is omitted, Excel will assume the default value of 0.

• Type: The timing of the payment. It can be either 0 or1. If this variable is omitted, Excel will assume the default value of 0.

 

For PPMT and IPMT, there is the additional variable ‘Per’, which is

• Per: The particular compounding period for which you want to find the interest or principle payable.

Creating the amortisation schedule

Rest, or the compounding period, is the frequency in which the outstanding loan amount is calculated. For the purpose of this exercise, we will assume the most common case of a monthly-rest loan.

Example:
Rest = Monthly
Loan Amount = $ 1,000,000
Loan Duration = 30 years
Interest Rate = 3% per annum for the first year
5% per annum thereafter

We enter these information into the Excel sheet, as below

As the frequency in which the outstanding loan is calculated once a month, the number of compounding period each year is 12. The loan lasts for 30 years; hence the ‘Total Periods’ is 12 x 30 = 360

Since compounding is done 12 times a year, the ‘Nominal Monthly Interest Rate’ becomes 3% / 12 = 0.0025.

Next, we proceed to set up the amortisation table as below

We will need to make use of the functions to obtain the ‘Payment’, ‘Principle’, ‘Interest’ and ‘Balance’. Table 1 illustrates the syntax to enter for each of the cells. Note that we include $ for some variables, this is to freeze the cell references so that they remain the same as we drag the formulas down.

Table 1

Cell	Enter
G12	=B2
D13	=PMT(B$6,B$4,-B$2)
E13	=PPMT(B$6,C13,B$4,-B$2)
F13	=IPMT(B$6,C13,B$4,-B$2)
G13	=G12-E13

 

So after finding the values for Period 1, which in our case is the amorisation schedule for the first month, how do we find the values for the next 11 months of the year? It will be too tedious to re-type the syntax with variation for each of the months.

For those readers who are proficient in Excel, the answer is obvious! Select D13: G13 and drag the selection down until Period 12. And you will obtain the below.

So after finding the amorisation schedule for the first year, what about the second year? We set up a new amortization schedule with different values, as below.

The loan amount becomes the outstanding balance at the end of the first year (i.e. Period 12), which is $ 979,122.

The remaining loan duration is 29 years; while the annual interest rate becomes the 2nd year rate.

To find the amortisation schedule for Period 0 and 1 we enter the exact same syntax as in Table 1. Since there is no change in interest rate from the 2nd year onwards, we can drag the formulas down until the end of the loan, which is the 348th period.

At the 348th period, the loan is completely paid off, so the balance (outstanding loan) is $0.

The amortisation schedule worksheet, with the formulas, can be DOWNLOADED HERE. To calculate the amortization schedule for your loan, all you have to do is key the relevant information in the ‘Loan Details’. And presto! You can see the amortisation schedule for Period 0 and 1. Pull and drag the formulas down to find the values for the rest of the periods.

For advice on a new home loan.

For refinancing advice.

Download this article here.