From: Ronald Raygun 
Subject: Re: BBC Mortage Calculator error?
Newsgroups: uk.finance
Date: Sun, 04 Apr 2004 20:25:23 GMT

The Wallport wrote:

> Just been trying to make excel calculate mortgage repayments and tried
> to check them against the BBC mortgage calculator on their website.
> Just can't get the numbers to agree. Now I'd normally just give in and
> say they must know something I don't about the calculation method
> but...
> 
> I got some quotes from mortgage provider websites that matched my
> numbers (and some others which matched their numbers - never at the
> same % rate though).
> 
> The formula used is (pinched this from another post):
> 
> =-PMT(%/12,*12,,0,0)
> 
> e.g.
> 
> 50,000 mortgage over 10 years at 5.2%
> 
> BBC   = £544.85
> Excel = £535.23
> 
> What is going on - anyone understand?

Yes.  One of them is calculating payments based on 120 months at 0.43333%
per month, the other is calculating payments based on 10 years at 5.2% per
year, and dividing the answer by 12 (actually I make it £544.86).

I don't speak spreadsheets, they're horrible nasty things, so I
don't understand this PMT stuff.

But I think, based on what you've written above, you'll find you get
the same answer if you omit the "*12*, and move the "/12" to the outside.

What they "know" that you don't is that some lenders do in fact work
it out by the BBC method, but the more reputable ones do it right.