minimum monthly principal repayment on home loan?

Hi Folks
Does anyone know an excel formula for working out the principal component of a basic term home loan. The interest component starting piont is easy but because the loan amount reduces with every principal repayment I am having to guess the starting piont so I can match the term on the spread sheet. :confused:
I have seen mathmatical formulas for doing this but they are too complex for my poor brain to absorb :confused:
An Excel formula would solve my frustration.
Kind regards
Simon
 
simonjulie said:
Hi Folks
Does anyone know an excel formula for working out the principal component of a basic term home loan. The interest component starting piont is easy but because the loan amount reduces with every principal repayment I am having to guess the starting piont so I can match the term on the spread sheet. :confused:
I have seen mathmatical formulas for doing this but they are too complex for my poor brain to absorb :confused:
An Excel formula would solve my frustration.
Kind regards
Simon

The principal and interest components of the "payment" change on each payment of course.. but here's the basic methodology for working it out:

Assume a 7.5% loan over 30 years, starting at $100,000

The P&I payment is:

=PMT(7.5/100/12,30*12,100000,0)

For each month you'll need to work out the interest component (you know how to do this) and subtract it from the P&I payment above to derive the interest component.

Here's a sample spreadsheet for that scenario:
 

Attachments

  • Amort.xls
    62 KB · Views: 100
Hi Duncan
Thanks for your help.
I must still be a bit thick or something. :confused:
I put your formula into my spreadsheet and came up with the same outcome as I had with my own calcs :confused: The problem is that when banks set a monthly set payment they manage to somehow make that payment go the full term of the loan. That is the formula I need. In my calcs because of the effect of compounding loan reduction My spread sheet pays out the loan in around 190mths instead of the full term :confused:
Here is a copy of my spread sheet :)
Kind regards
Simon
 

Attachments

  • loan calculator.xls
    71.5 KB · Views: 81
Simon,

Not sure I understand correctly. However, I believe the PPMT function is appropriate. See attached.

HTH
 

Attachments

  • loan calculator.xls
    83 KB · Views: 78
Simon,

Removing some unnecessary data and using some names so that it might (?) be simpler, revised file attached.

regards,
 

Attachments

  • loan calculator.xls
    59.5 KB · Views: 79
Hi Pete
Thanks for your help too.
The reason I am confused is that I have an existing P&I loan with St George
Loan 252K Interest rate 7.07 over 30 years. However the monthly fixed payment is only $1699 instead of 2k+ on my formula(this works out perfectly over the 30 year life of the loan) So they must have a formula that does this :confused:
Any ideas?
Simon
 
Simon,

Excel function for loan repayment is PMT - it returns monthly repayment value of $1,688.43.

This is a very standard calculation.

I answered the question you originally posted - formula for payment calculation. And now the monthly repayment calculation.

I have not looked at all at "your formula", though it seems you really want to know why your formula is wrong? If so, please post again.

If I have misunderstood, please clarify exactly what you want as I do not understand.

regards,
 
simonjulie said:
Hi Pete
Thanks for your help too.
The reason I am confused is that I have an existing P&I loan with St George
Loan 252K Interest rate 7.07 over 30 years. However the monthly fixed payment is only $1699 instead of 2k+ on my formula(this works out perfectly over the 30 year life of the loan) So they must have a formula that does this :confused:
Any ideas?
Simon

Hi Simon,

Your Interest Rate Cell is ALREADY a percentage.. and later you've divided it by 100 again.. making your effective interest rate .7% Would love to get me some of that! :)

Sing out if you want me to fix your spreadsheet and re-upload.
 
duncan_m said:
Hi Simon,

Your Interest Rate Cell is ALREADY a percentage.. and later you've divided it by 100 again.. making your effective interest rate .7% Would love to get me some of that! :)

Sing out if you want me to fix your spreadsheet and re-upload.


Sorry, one other problem.. Your Total Column (D) is the sum of Cols B and C.. but the payment in Col B includes Interest ALREADY.. so your paying double the interest you need to..
 
Thanks Duncan and Pete
I thought it must have been my calculation mistake :)
After all "I'm only a musician" :) I can only count to four :D
I would really appreciate your help fixing my spread sheet Duncan.
I am sorry Pete for not being clearer and thanks for all your help.
Kind regards
Simon
 
duncan_m said:
Sorry, one other problem.. Your Total Column (D) is the sum of Cols B and C.. but the payment in Col B includes Interest ALREADY.. so your paying double the interest you need to..

Here's the fixed one showing the loan being paid out in Month 360.. (ie 30*12)
 

Attachments

  • Fixed.xls
    72 KB · Views: 86
simonjulie said:
After all "I'm only a musician" :) I can only count to four :D

I'm only a software developer.. I can only count to 1, and I have to start at zero :)

(see previous message, fixed spreadsheet attached)
 
That file I posted earlier probably does what you needed.

However, I'm posting again to correct the latest file from Duncan - it didn't actually wipe the loan out after 30 years. I didn't investigate why, just fixed it.

I can count but not well enough to be a musician.

HTH
 

Attachments

  • loan.xls
    80 KB · Views: 95
Thanks Guys
This all started out from my step daughter slaving over a year twelve asignment with a calculator. It has had me beat for a few days and sleepless nights.
I really appreciate both your imputs :)
Kind regards
Simon
 
Great stuff!
Now that I've got the excel formula I can work the spreadsheet into my other calculators.
Thanks guys it has now given me a quantum leap forward with my property portfolio forecasting
Simon
 
Back
Top