Mortage Calculations

[John in Surrey]

Hi Team

I wish to work out the formula used by banks to calculate mortgage
payments...

Here is the data from my bank statement:

Interest Mortgage Monthly Payment Interest Paid
7.50% $59,462.00 $483.00 $379.00

20 year term ( I think)

I will then use excel to work out what benifit wil lbe gained in
making a lump sum payment on the fixed loan ver the penilty payment
for doing so on a fixed loan...
- dont do this for me, thats my project! I'm just after info on how
payments are calclated

chers
john
Images of home (NZ)
http://www.titahi-bay.co.nz/home
What we are up to in the UK
http://www.titahi-bay.co.nz

 

[Niek Otten]

Hi John,

<20 year term ( I think)>

That's the problem!

None of my calculations gets exactly your result. If I use the PMT function and try to make it return 483, I can't get any closer
than 485.5

First try to make sure what the term is exactly.

In the meantime you could experiment with PMT yourself: try both the yearly rate/12, the EFFECT(7.5%,12)/12 rate, a yearly
payment/12, payment in advance or arrears, etc.

have fun!

 

[John in Surrey]

First try to make sure what the term is exactly.

True, same here, my calcs came within $10 of the banks.





Interest Rate 7.50%
Period for interest 1
Years of loan 20
Value of Loan $58,655.28
Lump Sum Payment $5,000.00
Current Interest Payment -$332
=IPMT(E49/12, E50*3, E51, E52)
After Lump Sum Payment# -$304
=IPMT(E49/12,E50*3, E51,(E52-E53))
Savings over 6 mnth of making payment -$170
After Penalty Payment $30

Has been fun, thanks

Images of home (NZ)
http://www.titahi-bay.co.nz/home
What we are up to in the UK
http://www.titahi-bay.co.nz