Calculating mortgage payments

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
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Joe Kelleher wrote:

Sure

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. :-(
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Ronald Raygun wrote:

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
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Joe Kelleher wrote:

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).
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
"Joe Kelleher"
message

loan

be

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.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Matt Robertson wrote:

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".
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

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.
--
John Boyle

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

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.