# 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)`

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%.