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.

Calculate Stamp Duty with Excel

A quick post of how to calculate the Stamp Duty of a property with Excel, in my example I am using the Western Australia Residential Rate Dutiable value.

Stamp Duty
Cutoff [$]  Rate [%]  Duty [$]  Formula
0           1.9       0    0
120000      2.85      2280      =C3+(A4-A3)*B3/100
150000      3.8       3135      =C4+(A5-A4)*B4/100
360000      4.75      11115     =C5+(A6-A5)*B5/100
725000      5.15      28452.5   =C6+(A7-A6)*B6/100

Property Value        500000    
Duty                  17765     =VLOOKUP(C9,A:C,3,TRUE) + (C9-VLOOKUP(C9,A:C,1,TRUE))*VLOOKUP(C9,A:C,2,TRUE)/100

Download the Excel WorkBook.

Update, next day
This also works for Individual income tax rates;

Tax Rates            
Cutoff [$]  Rate [%] Tax [$]  Formula
0           0        0        0
6000        15       0        =C3+(A4-A3)*B3/100
37000       30       4650     =C4+(A5-A4)*B4/100
80000       37       17550    =C5+(A6-A5)*B5/100
180000      45       54550    =C6+(A7-A6)*B6/100
            
Income               100000    
Tax                  24950    =VLOOKUP(C9,A:C,3,TRUE) + (C9-VLOOKUP(C9,A:C,1,TRUE))*VLOOKUP(C9,A:C,2,TRUE)/100