I had always assumed that the Excel PMT formula was the correct formula to use to calculate mortgage payments, but it isn't.
On a £200,000 loan, over 10 years at 6%, the difference in the monthly payment is £44.
Just how do banks do the calculation?
Neil Robinson
Replies (6)
Please login or register to join the discussion.
re PMT
I have tried PMT and it produces the same answer as CUMPRINC
only difference in the formula is to remove the 1,1 and the + interest only amount at the end
i.e. CUMPRINC returns just the repayment amount and PMT returns the total repayment amount
Perhaps you are missing out any arrangements fees added to the mortgage amount?
CUMPRINC
I use CUMPRINC and it works to the pound:
=-CUMPRINC(C9/12,K9*12,G9,1,1,0)+H9
where C9 = annual interest rate
K9 = term, say 25
G9 = loan amount
1 = start period (month)
1 = end period (month)
0 = timing of payment, 1 for beginning of month, 0 for end of month
H9 = monthly interest only amount which is simply loan amount (G9) x interest rate (C9)/12
prudence
It appears that the online calculators are being prudent and in your example of the bbc calculator, there appears to be a 2% allowance built in for arrangement fees added which is probably worst case scenario.
I have compared my Excel calculations to KFI's/mortgage offers from lenders and it works to the penny in most cases and to the pound in others.
As you may know the FSA require mortgage advisors to furnish clients with KFI's before they do anything else i.e. penny perfect illustrations directly from the lender.
I am suprised that online calculators would do this. There is a small disclaimer on the website but I feel this should be flagged a lot better so that no one is mislead as you have been! £44/month is significant to a lot of people.