NEW: Excel Home Loan Formula

[Doosra]

Hi Guys!

New to the forum.

I am currently completing a maths assignment which requires us to investigate the best way to pay off a home loan.

Basically, the guy has 22 years remaining on his home loan and is required to make monthly payments @5.37% Interest P.A

I am able to calculate (using graphics calculator), that he is required to pay $1872 per month

The assignment requires me to prove this using excel, yet i can only get my formula to take into account the interest compounding monthly which comes to $1893 (which is incorrect)

Is there a way in which i can calculate the required monthly payments if it only compounds annually?


Any help would be much appreciated

 

[StephenCrump]

Welcome to the Forum!

Usually, Home Loan providers calculate interest based on daily balances, and compound monthly.

Calculators (and many Excel functions) approximate interest calculations by assuming that each month is exactly 1/12 of year, and compound at nominal interest rate/12 per month. I suspect this is what your calculator is doing.

There are functions in Excel that will calculate home loan repayments. However, my guess is that the question is asking you to produce a monthly loan schedule showing month by month: interest, repayment and outstanding balance. This balance should reduce to exactly NIL after 22x12 months, proving that the calculator's answer is correct.

Perhaps you can tell us the loan balance you're working with, and the formula(e) you're using to get $1,893?

 

[Doosra]

Welcome to the Forum!

Usually, Home Loan providers calculate interest based on daily balances, and compound monthly.

Calculators (and many Excel functions) approximate interest calculations by assuming that each month is exactly 1/12 of year, and compound at nominal interest rate/12 per month. I suspect this is what your calculator is doing.

There are functions in Excel that will calculate home loan repayments. However, my guess is that the question is asking you to produce a monthly loan schedule showing month by month: interest, repayment and outstanding balance. This balance should reduce to exactly NIL after 22x12 months, proving that the calculator's answer is correct.

Perhaps you can tell us the loan balance you're working with, and the formula(e) you're using to get $1,893?

Hi Stephen.

Thanks very much for responding. Apologies for not including the Loan Balance!

• The Loan Balance is $293,000
• Payments are made monthly
• Interest Rate of 5.37% P.A
• Remaining time is 22 Years (264 Months)

The question on the task sheet is asking:

"Calculate the minimum monthly repayment that will enable Carl to pay off the home loan in the remaining time"

So it's looking for one constant payment over the 264 months
Using the TVM Solver on a graphics calculator it comes to:

N= 264 (12*22)
I= 5.37
PV= 293,000
PMT= -1872.38
FV= 0
P/Y= 12
C/Y= 1

*If i change C/Y on the calculator i get the same as the excel formula (-1893.83)*


The formula i am using on Excel is =PMT(0.0537/12, 264, 293000) = -$1893.83

Thanks!

 

[joeu2004]

• The Loan Balance is $293,000
• Payments are made monthly
• Interest Rate of 5.37% P.A
• Remaining time is 22 Years (264 Months)

[....]
The formula i am using on Excel is =PMT(0.0537/12, 264, 293000) = -$1893.83

There are two common conventions (even more!) for stating annual interest rate for home loans: nominal rate, and effective rate.

For a nominal rate (USA, e.g), the monthly rate is 5.37%/12, as you wrote.

For an effective rate (EU, e.g), the monthly rate is (1+5.37%)^(1/12) - 1.

I am not familiar with the TVM Solver. I suspect the TVM Solver can be configured for either method. But it appears the assignment wants you to treat 5.37% as an effective rate. One way:

=PMT((1+5.37%)^(1/12)-1, 22*12, -293000)

yields about 1872.38 (actually 1872.37504956153).

Note that I entered -293000 instead of 239000 so that PMT yields a positive value. That is a personal choice. The important point is: inflows (293000) and outflows (PMT) should have opposite signs.

 

[StephenCrump]

Well done! You've replicated the result of the nominal rate approach using Excel.

And as Joeu2004 has shown, you can also replicate the result of the effective rate approach.

But effectively, you've used two "calculators". Do you think the requirement "to prove this using Excel" is asking you to demonstrate by way of a loan schedule?

 

[Doosra]

Well done! You've replicated the result of the nominal rate approach using Excel.

And as Joeu2004 has shown, you can also replicate the result of the effective rate approach.

But effectively, you've used two "calculators". Do you think the requirement "to prove this using Excel" is asking you to demonstrate by way of a loan schedule?

There are two common conventions (even more!) for stating annual interest rate for home loans: nominal rate, and effective rate.

For a nominal rate (USA, e.g), the monthly rate is 5.37%/12, as you wrote.

For an effective rate (EU, e.g), the monthly rate is (1+5.37%)^(1/12) - 1.

I am not familiar with the TVM Solver. I suspect the TVM Solver can be configured for either method. But it appears the assignment wants you to treat 5.37% as an effective rate. One way:

=PMT((1+5.37%)^(1/12)-1, 22*12, -293000)

yields about 1872.38 (actually 1872.37504956153).

Note that I entered -293000 instead of 239000 so that PMT yields a positive value. That is a personal choice. The important point is: inflows (293000) and outflows (PMT) should have opposite signs.

Thank you very much for your awesome help Stephen & Joe!!

This help significantly and i was able to successfully complete this section of the assignment. Much appreciated

@Stephen yes that was correct, i clarified with the teacher and the loan schedule was exactly what he was looking for


FYI: most home loans in Australia are presented in a annual form, yet paid monthly


Thanks Again!!

 

[StephenCrump]

Thank you very much for your awesome help Stephen & Joe!!

This help significantly and i was able to successfully complete this section of the assignment.

Great, and well done!

I am glad we could point you in the right direction.