I'm looking to take out a mortgage and I want to draw up a spreadsheet to
compare different mortgages and test different scenarios. In particular, I

want to be able to calculate an 'effective' interest rate that includes
repayment of any initial or redemption fees averaged over the life of the
mortgage, which looks like being only a few years before I switch. Can
anyone please explain or point me to an exact definition of the interest
rate that is quoted in a mortgage ad? Is it the total proportion of the loan
value that would be paid in a year, if one were to continuously repay only
the interest on the loan so as to keep the debt exactly constant? If
repayments were made only once a month, the interest paid would therefore be
slightly higher. Or is it something else? Also, I'd be interested to learn
about how the quoted APR figures are calculated, and whether it saves me
doing some maths.
Thanks,
Joe

Yes and No
There are two methods in wide use. Both assume that you actually pay
a fixed amount per month.
One method assumes (incorrectly) that if, say, the amount outstanding at
the beginning of the year is £100k and the annual interest rate is 6%,
that you are borrowing the whole £100k for the whole year, and need
therefore to be charged £6k of interest for that year. Then also
calculate how much extra you should pay to reduce the balance in
order to pay it off over whatever term is agreed, and they divide this
amount by 12 and you pay that each month. In other words, the monthly
payments are a twelfth of £100k*0.06 / (1 - 1.06^-20) if the loan is
to be paid off over 20 years.
Another method assumes (correctly) that you are borrowing the whole
£100k only for a month, and are then maing an interest payment of £500
plus an amount to reduce the balance, and are then borrowing slightly
less than £100k for month 2, etc. The monthly interest rate is taken
to be a twelfth of the quoted annual rate. In other words, the monthly
payments are £100k*0.005 / (1 - 1.005^-240) if the loan is to be paid
off over the same period, i.e. 240 months.
I would say this second method is more popular these days. It means
you pay slightly less each month than with the first.

Only? It's extremely unusual to make repayments more often than monthly.

The APR is as a rule a completely useless figure for comparison,
especially in the case of deals which start with a discount period,
because they all tend to assume a 25-year term, which in the case of
people switching lenders part way through an existing loan term
is inappropriate.
What they do is come up with a schedule of payments for the whole
300 months, add up all the payments, including any fees which apply
at the start and at the end or at any other time, having first scaled
them back, taking into account the time at which each payment is made,
using the *same* "discount rate" (a technical term for applying the
interest rate backwards in time to see what the amount would have been
worth at month 0, i.e. the day the loan was advanced) for each payment.
They use a guessed value for this discount rate, and re-run the
computation until this sum matches the amount lent. When this has been
achieved, the discount rate is the APR (or rather its inverse).
I trust this is now clear as mud. :-)
In the simplified case where no fees apply at all ever, and all monthly
payments are strictly equal, and the interest rate never changes, the APR
can be calculated by compounding the monthly rate. In other words, if
the monthly interest rate is 0.5%, the APR will be 1.005^12-1 or 6.168%.
In practice, life is never that simple. :-(

Thanks for providing such a detailed explanation, including the examples.
This method appears to be the one used by the mortgage calculators on the
Nationwide and Guardian.co.uk web sites, so I'm happy with it. The
derivation of the formula isn't immediately obvious to me, but it agrees
exactly with a quick spreadsheet I knocked up based on your description, on
which I used the Goal Seek tool to find the monthly repayment which
completely repayed the debt in the final month.

In fact here is where my confusion lay. I was wrongly assuming the quoted
interest rate should be continuously compounded throughout the course of
each month, e.g. as described at
http://en.wikipedia.org/wiki/Interest#Mathematics_of_interest_rates ,
thereby making the monthly repayments slightly higher than they would
otherwise be. However, it appears not, and even if it were, the difference
is only on the order of 1 / month for the examples I considered.
Joe

If you're interested in the derivation, I think it's been a few years
since I last disgorged it, so here it is:
If the amount owed at time t is A(t) and the amount borrowed is A,
then A(0)=A.
During the first period, interest accrues on the debt at the rate r
per period, and if we use f to be the interest factor (f = 1+r),
then the amount owed at the end of period 1 is A*f just before
making the periodic payment, and just afterwards it is A(1) = A*f-P.
Basically the amount owing in any period is f times what was owing
last time, minus the fixed periodic payment P.
A(2) = A(1)*f - P = A*f^2 - P*f - P
A(3) = A(2)*f - P = A*f^3 - P*f*2 - P*f - P
in general at time k we have
A(k) = A*f^k - P*(Sum of all f^i for i between 0 and k-1 inclusive)
Specifically, if n is the planned end-time, we want A(n) = 0 and so
A*f^n = P*(Sum of f^i for i = 0 to n-1)
We can look up the sum of powers formula in a handy reference book
(or I can bore everyone to tears by deriving it here[*]) but it is
(f^n-1)/(f-1).
Hence P = A * f^n * (f-1) / (f^n - 1)
which simplifies, by dividing top & bottom by f^n, to A*(f-1) / (1 - f^-n).
If you prefer, you can express it all back in terms of r and it becomes
A * r / (1 - (1+r)^-n). You just need to be mindful that n is the number
of periods and r is the rate per period, no matter whether the period is
a year, a month, or anything else; what matters is that it is the period
which corresponds to the frequency of when payments are made (or, more
accurately, when the payments are *applied* to the loan account so that
interest then accrues on a smaller balance -- the thing about some
annual schemes is that although the payments are *made* monthly, the
devious lender continues to charge interest on the sum outstanding
at the beginning of the year, he just stores the monthly payments
in a holding account and doesn't apply them to the loan account
until 12 payments have been accumulated).
[*] Boring derivation of sum of powers formula:
If S(n) = f^0 + f^1 + ... + f^(n-1),
then it follows, by multiplying both sides by f, that:
f*S(n) = f^1 + f^2 + ... + f^n.
By subtracting the first equation from the second we get
f*S(n) - S(n) = f^n - f^0
because all the intermediate terms f^1 to f^(n-1) cancel out.
Hence S(n)*(f-1) = f^n - 1, in other words S(n) = (f^n-1)/(f-1).

I won't do the math, Ron is the chap there, but I will point out a big
bugbear of mine, which is deceitful behaviour on the part of lenders when
calculating the APR.
Several factors are taken into account when calculating APR, including legal
costs - you'll see a lot of providers quoting an (estimated) figure sub 250
legal costs for a house purchase, most of us are going to have to pay in
excess of 750.............
As Ron pointed out, APR isn't a true indicator, look at true cost to you,
including the oustanding loan balance at the end of any 'special' rate.

There is no deceit. The lenders are forced to calculate the APR by
rules specified by law.

No. You don't pay £750 legal costs to record the mortgage, most of your
legal costs cover the conveyancing, and you would incur those even if you
bought the house for cash, i.e. without a loan. Therefore you cannot
consider the full £750 as being a "payment" connected with the loan and
which would therefore need to be taken into account in the APR calculation.
Unfortunately, lenders are unable to publish an accurate APR because the
factors they must take into account before *quoting* an APR vary from
case to case. Therefore the best they can do is quote a "typical" APR
based on the size and term of loan taken out by "Mr & Mrs Average".

Thats because the legal costs of buying a house are NOT part of the APR,
in the same way that a specialist electricians survey is not. The legal
costs referred to in the APR are those incurred which relate to the
mortgage only, and £250 sounds about right for that part of things.

BeanSmart.com is a site by and for consumers of financial services and advice. We are not affiliated with any of the banks, financial services or software manufacturers discussed here.
All logos and trade names are the property of their respective owners.

Tax and financial advice you come across on this site is freely given by your peers and professionals on their own time and out of the kindness of their hearts. We can guarantee
neither accuracy of such advice nor its applicability for your situation. Simply put, you are fully responsible for the results of using information from this site in real life situations.