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.