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.

Posted

20 January 2015

Investment, Property

MECTILE

Tags:

calculate, comparison, Excel, finance, interest, loan, period, rate

Comments

3 responses to “How to calculate comparison interest rate in Excel”

patrick

5 October 2015

you have explained this really badly and havent followed your spreadsheet, it starts off good and you quickly get off track the further you go, its confusing until you look at your spreadsheet and understand that your outgoings include your principal which your doing in a very long winded way, would be much easier to explain that you add the monthly cost of additional fees to the monthly payment. this would then require using three steps to calculate a comparison rather than working out interest etc which is pointless.

Reply
Bane

5 January 2017

Agree with Patrick in regards to explanation, troublesome to understand the explanation until you view the spreadsheet which makes a lot more sense. It is a lot of steps for comparison rate alone but useful to get other information too and customise to suite for individual use e.g. add additional fees if applicable. Great work on the spreadsheet definitely helped me. Thanks

Reply
Bane

5 January 2017

Also I hope you didn’t write the GetFormula() macro yourself unless you like to challenge yourself a bit as FORMULATEXT() function does the same thing, just an F.Y.I. otherwise great work!

Reply