Calculation of mortgage payments

Calculation of mortgage payments

Didn't find your answer?

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.

avatar
By neilrobinson5
12th Oct 2007 15:32

Mortgage payments
Thanks for the answer regarding the CUMPRINC formula. Do you know why the PMT formula does not work?

Thanks (0)
avatar
By AnonymousUser
14th Oct 2007 13:20

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?

Thanks (0)
avatar
By nick farrow
14th Oct 2007 18:36

thanks for this clear explanation Samuel - I'm sure this will come in useful

Thanks (0)
avatar
By neilrobinson5
15th Oct 2007 12:38

Example
Samuel, thanks for your comments.

Take the following example - loan £200,000 over 10 years at 6%.

PMT returns a monthly payment of £2,220.

Find any mortgage calculator on the internet (eg. www.bbc.co.uk/homes/property/mortgagecalculator.shtml) - this returns a monthly payment of £2,264.

Why the difference?

Regards

Neil Robinson

Thanks (0)
avatar
By AnonymousUser
10th Oct 2007 23:51

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

Thanks (0)
avatar
By AnonymousUser
15th Oct 2007 17:00

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.

Thanks (0)