How to calculate comparison interest rate in Excel

Searching for how comparison interest rates are calculated, I found an article on Tomorrow Finance, but unfortunately a lot of the numbers provided no insight into how they were calculated. I hope to break it down further and explain how to calculate comparison interest rates with Excel. The Excel formula can be quite tricky and the parameters vague, so I will go through each of the formula as well.

  • rate – the interest rate of the loan annually equal to 4.63% (the current rate most banks are offering as of today)
  • principle – the loan amount always equal to $150,000 when calculating a comparison rate.
  • period – the period in years always equal to 25 years when calculating a comparison rate.

Payments for a loan

Firstly, calculate the monthly payments for a loan based on constant principle payments and a constant interest rate. The key here is to convert the interest rate to a monthly interest rate and the number of periods also in months.

payment = PMT(rate/12, period*12, principle)

In this case payments equals -$844.86 per month and includes principle and interest. The figure is negative since it is an outgoing.

Total interest payment

total payment = payment * 12 * period

Since the monthly payments are all the same, the total payments is simply the monthly payment multiplied by 12 months by 25 years, this equals -$253,456.66.

total interest = total payment + principle

The total interest is imply the total payment (which is negative) plus the principle, this results in a total interest only of -$103,456.66.

A quicker way to calculate the cumulative interest over the lifetime of a loan is;

total interest = CUMIPMT(rate/12, period*12, principle, 1, period*12, 0)

Additional fees

Next the additional fees over the lifetime of the loan are added, usually these are annual service fees, with nab there is a -$395 annual service fees. The total fee over the life of the loan would be 25 years multiplied by -$395 which equals -$9,875.

total outgoings = total interest + total fees

The total outgoings over the life of the loan would be the total interest and total fees, this would equal -$113,331.66.

outgoings = total outgoings / 25 /12

To get the outgoings per month divide by 300 to get -$877.77 per month.

Comparison rate

Finally, the comparison rate can be calculated;

comparison rate = RATE(period * 12, outgoings, principle)*12

The rate is per month which is multiplied by 12 to get an annual comparison rate, in this case equal to 0.050101482 or 5.01%.

Download the WorkBook how-to-calculate-comparison-interest-rate-in-excel.xlsm.

Home loans – Fixed vs. Variable interest rates

I saw an interesting chart on Canstar which plotted the history of RBA Cash Rate, variable and 3-year fixed interest rates. This data is all available on the Reserve Bank of Australia website, look for A2 and F2 statistics.

History of cash rate, variable and fixed

I thought I would take it a step further and determine from past history if the 3-year fixed or variable rate would have been a better choice at a given time. I always believe that choosing a 3-year fixed interest rate was like gambling, it could either work in your favour or not, I am curious to see if I am right.

Given monthly data for the variable interest rate, to calculate which would have been the better choice I average thirty-six months of future variable rates. Because it is constantly looking three-years into the future, it can be observed that the data finishes in February 2010.

3-year average future variable vs. 3-year fixed

What can be observed is that the fixed rate is sometimes a better option (when under the three-years future average) and sometimes it is worse. Pre 2000, the fixed was mostly a poor choice, post 2000 the fixed rate is lower most of the time.

Finally, a difference of the two and it can be seen that it really jumps back and forth between good and bad. If we take the average of the difference we get 0.41%, which means the 3-year fixed rate is generally an interest rate of 0.41% worse.

3-year average future variable vs. 3-year fixed difference

Out of 233 months, 121 months (or 52% of the time) the fixed had a worse off interest rate and 112 months (or 48% of the time) the fixed had a better interest rate.

However, if we look at the 121 months from 2000 onwards, the variable interest rate is better off by an average interest rate of 0.23% which occurs about two-thirds of the time.

In conclusion, 3-year fixed rates these days do seem to have a good chance of having a lower interest rate for the locked-in period. That combined with the advantage of having a non fluctuating interest rate makes it a good choice.
I still would avoid them though, fixed rates make it difficult to switch lenders due to the extra fees payable.