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

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

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