Millennial Money

08th May 2021


Welcome back to another Millennial Money article where we demystify key concepts in personal finance affecting university students. Particularly with the RBA raising the cash rate by 25 basis points (0.25%) this week, mortgages have become a hot topic. There is a lot we could talk about here, and we will only scratch the surface, but hopefully you can come out of this as interested about housing and credit as we are.


How are mortgages repaid?

A mortgage refers to a loan which is taken out to purchase a property. Not to sound ominous, but in old French it literally translates to ‘dead pledge’.  Like most other forms of credit, the borrower is liable to pay back the principal of the mortgage, often over a period of 20 to 30 years as well as interest. Ignoring the time value of money for a second, with a per annum interest rate of 3% and a mortgage over 30 years, an individual will pay about 1.5 times the amount of the mortgage in total.

The way this works is a reducing balance loan, where the payments are kept constant, but, over time, the amount of principal paid off as a proportion of the total payment increases. This is known as loan amortisation. We believe it is important how to construct a loan amortisation schedule yourself because financial calculators provided by banks often don’t disclose the assumptions that they make, which means you could be paying more or less than what it is suggesting. Therefore, we have compiled a basic amortisation schedule to demonstrate this. It is actually not too difficult, and if you are interested in the details, check out the appendix at the bottom of the article.

How are mortgages approved?

The process of being approved for a mortgage is a little bit complex and opaque, but we will do our best to explain some of it here. The first step is pre-approval, which is basically an acknowledgement by the bank that they believe the borrower can repay the amount they are willing to lend, assuming their circumstances do not change. According to our research, there are four main things which are needed for pre-approval: assets, income, a good credit history and proof of employment. The one worth explaining here is a good credit history: this simply means that you have a strong record of repaying debts (for example credit card debts). Individuals with better credit histories are charged lower interest rates, because they have a lower risk of default, and this can save a lot of money. Therefore, the decisions you make about borrowing and spending now can have an impact on the future. To be able to borrow the house, the borrower will also be required to put up a deposit, which, for the average house buyer, is 20% of income. Given the median house price is $1.1 million in Melbourne, this is an eye-watering amount.


Conclusion and More Information

This motivates the discussion for next week’s article, where we will be continuing our discussion of mortgages, including the consideration of the Great Australian Dream and the impacts of the RBA’s increase in interest rates on the two sides of the housing market.

If you are looking for more information about the topics discussed today check out the following:

Appendix: Loan Amortisation Tutorial (Basic Excel Knowledge Required)

The first step is to figure out the yearly repayment, based on the assumptions. The Excel Function is . Note we have to enter a negative number for PV so the PMT comes out positive. Based on the assumptions the required entry is


The second step is to construct the table to figure out the loan schedule. To do this you’ll want 5 headings (Year, PMT, Interest, Principal, Balance) and 31 rows, numbered from 0 to 30.

First, in the balance column enter the initial amount ($500,000) for year 0.

In the PMT column you are going to enter the PMT that we calculated in the first step. The easiest way to do this is to reference the cell where you have stored the PMT. A couple of tips with this one. First, use absolute referencing (shortcut FN F4) so that when you drag down it continues to reference the same cell. This is the only time you will need to use absolute referencing. Second, to automatically fill the columns, double click the black dot in the right-hand corner of the cell. Your table should now look like this:

Next, in the interest column calculate the interest payment by multiplying the interest rate by the previous years balance. So for the first year this would be 0.03*500,000 = $15000. You may wish to store the interest rate in a cell to make the table more dynamic. Again, double click the black dot in the right-hand corner of the cell. After year 1, the interest payment will be 0 at the moment, but that is because the balance column has not been adjusted.

In the principal column, the amount paid is the total payment less the interest payment. Similarly in the balance column the amount is the previous years balance less the principal paid. One you drag down these two formulas, the interest payments will adjust, and you should have a completed table that looks like this:

The Finance Student's Association is not a financial adviser, the views expressed within this article are those of the authors and do not represent the views of the Finance Student's Association. All images and references in this article are for fair and educational purposes only. The content in this article is not intended as legal, financial or investment advice and should not be construed or relied on as such. 

That is all there is to it. You now have the tools to complete a loan amortisation schedule.