Calculating Monthly Mortgage Payments

My understanding of how to do this in Excel is as follows...

-PMT(Interest,Number of Payments,Amount of Loan)

So that for a mortgage of 180k over 30 years at 6% the formula would read

PMT(6%/12,30*12,180000) giving monthly payments of 1,079.19

However, if I look at the BBC site - http://www.bbc.co.uk/homes/property/...lculator.shtml - it indicates the payment is 1,089.73

Am I doing something wrong or is there a reason for the discrepancy?

The PMT function you are using calculates interest as charged on a daily basis (better for the borrower) whereas the BBC calculator you reference calculates interest as charged on an annual basis (best for the lender). Standard U.S. residential mortgages use the daily basis. Your use of the PMT function for a daily basis mortgage is giving you the correct daily basis monthly payment amount of $1,079.19. Hope this helps. John

Many thanks for your response John. I guess you guys have it better out there! The BBC is very likely reflective of the way most UK banks charge.

Timmy Mac,

In your scenario, the additonal interest paid over the term of the mortgage is $3,794 charging on an annual versus daily basis. The Excel calculation of the monthly payment based upon charging on an annual basis would be:

=-PMT(6%,30,180000)/12 = $1,089.73

Similar formula adjustments, dividing the result by 12 versus dividing the interest rate by 12 and converting the term to months, would be required for the IPMT, PPMT, CUMIPMT and CUMPPMT Excel functions. However, all will return the correct value for annual and daily basis loans.

Have a great day,
John

Brilliant John!

Thank you very much indeed