UK loans: how is monthly pmt determined?
Errata....
I wrote:
4a. UK weekly rate: 0.1173% Solver[*]
[....]
[*] RATE(1560,PMT(...),-1000000,0.1173%) fails!
Typo! RATE(1560,PMT(...),-1000000,0,0,0.11%) works fine. Even a guess of
1%.
----- original message -----
"JoeU2004" wrote in message
...
"Fred Smith" wrote:
OK. Let's take a $1,000,000 at 6% and calculate the
future value using different periodic rates
[...]
US, monthly, rate = 6%/12, FV = 6,022,575.21
US, weekly, rate = 6%/52, FV = 6,043,373.22
First, you are confusing the mechanics for a fixed-rate investment (e.g.
savings) with the mechanics for a mortgage loan.
Second, your examples incorrectly compute the UK periodic rate, basing it
on an APR instead of interest rate, if we are to believe the UK online
mortgage calculators.
Perhaps your intention was to show the following for a 30-year loan of
1,000,000 at 6%. Actual amounts are rounded. UK #a is based on the
aforementioned UK online mortgage calculators; UK #b is based on the
aforementioned MS KB article.
1. US monthly rate: 0.5000% 6%/12
monthly payment: 5,995.51
total interest: 1,158,381.89
2. US weekly rate: 0.1154% 6%/52
weekly payment: 1,382.63
total interest: 1,156,903.98
3a. UK monthly rate: 0.5076% RATE(360,PMT(...),-1000000)
monthly payment: 6,054.08 PMT(6%,30,-1000000)/12
total interest: 1,179,467.34
b. UK monthly rate: 0.4868% (1+6%)^(1/12)-1
monthly payment: 5,893.70
total interest: 1,121,733.50
4a. UK weekly rate: 0.1173% Solver[*]
monthly payment: 1,397.09 PMT(6%,30,-1000000)/52
total interest: 1,179,467.34
[*] RATE(1560,PMT(...),-1000000,0.1173%) fails!
b. UK weekly rate: 0.1121% (1+6%)^(1/52)-1
monthly payment: 1,357.55
total interest: 1,117,772.69
5. CA monthly rate: 0.4939% (1+6%/2)^(1/6)-1
monthly payment: 5,948.23
total interest: 1,141,364.31
6. CA weekly rate: 0.1138% (1+6%/2)^(1/26)-1
monthly payment: 1,370.07
total interest: 1,137,308.66
But this shows that for both the US and Canada, the total interest is
affected the payment frequency. The Canadian method of determining annual
interest rate by so-called "compounding semi-annually" offers no
"protection" or less "attraction" whatsoever.
The same is true for the UK if the annual interest rate is (truly)
compounded by the payment frequency. However, if the UK periodic payment
is calculated according to the UK online calculators, the UK total
interest is unaffected by payment frequency. (By definition; no
surprise.)
In any case, that was not the point you made to which I responded as you
quoted above.
You wrote:
While the compounding period is somewhat annoying, the advantage is the
lender can't play games with the rate. In other counties that don't have
this protection, paying weekly can actually attract a higher APR than
paying monthly.
You were making a point about "attracting" a higher APR.
Note: Here, we are talking about APR, not necessarily interest rate.
As I have said, the US APR is not affected by the payment frequency, any
more than the Canadian APR. In both case, the APR is the annual interest
rate, if we ignore loan fees and period fees other than principal and
interest.
As for the UK, the APR is indeed affected by the payment frequency, if we
believe the MS KB article. But that is just a mathematical fact. I see
no sinister "attraction" in that and nothing to be "protected" from.
I guess I really do not understand the point that you are trying to make
either by the original "attraction" statement or by your follow-up here.
----- original message -----
"Fred Smith" wrote in message
...
"And somehow the Canadian method prevents that?! Please demonstrate."
OK. Let's take a $1,000,000 at 6% and calculate the future value using
different periodic rates (using your formulae to calculate the rate which
I agree is correct, although I've simplified the Canadian one).
US, monthly, rate = 6%/12, FV = 6,022,575.21
US, weekly, rate = 6%/52, FV = 6,043,373.22
UK, monthly, rate = (1+6%)^(1/12)-1, FV = 5,743,491.17
UK, weekly, rate = (1+6%)^(1/52)-1, FV = 5,743,491.17
Cdn, monthly, rate = (1+6%/2)^(2/12)-1, FV = 5,891,603.10
Cdn, weekly, rate = (1+6%/2)^(2/52)-1, FV = 5,891,603.10
In Canada and the UK, the frequency has no impact on future value. For a
mortgage, that means the payment frequency will not affect the interest
you are charged.
In the US, frequency does impact future value, meaning you're charged
more interest on your mortgage the more frequently you pay.
Our legislation requiring mortgages to be "compounded semi-annually not
in advance" is annoying, I agree, but we have to have some quirks, don't
we?
Regards,
Fred.
"JoeU2004" wrote in message
...
"Fred Smith" wrote:
Canadian mortgages are compounded semi-annually
That is indeed the terminology that Canadians use. But it is
incorrect --
and misleading, IMHO.
If I compound a daily rate for 30 days, then multiply by 12 to get an
annual
rate (30/360 basis), would you say that the rate is "compounded
monthly"?
Rhetorical question. Of course you wouldn't. I'm sure you know that
"compounded monthly" means (1+r)^12-1.
Likewise, the Canadian rate is not "compounded semi-annually" --
(1+r)^2-1.
Instead, the Canadian rate is "compounded monthly semi-annually" or
"compounded monthly over 6 months twice a year" or something like that.
That is, it is ((1+r)^6-1)*2.
That is corroborated by the aforementioned MS KB article and Canadian
online
mortgage calculators.
Well, that's for a loan with monthly payments. The annual rate for a
Canadian loan with weekly payments, for example, is the weekly rate
compounded over 26 weeks, then multiplied by 2. So it is "compounded
weekly
semi-annually".
So we can see why Canadians settle for the incorrect term "compounded
semi-annually". It is a mouthful to say "compounded F semi-annually,
where
F is the payment frequency", which is the correct term.
While the compounding period is somewhat annoying, the
advantage is the lender can't play games with the rate.
Horse-pucky! Lenders are just as adept at hiring financial
mathematicians
as anyone else.
A lender decides what ROI he needs, considering in actuarial factors
such as
foreclosure and other bad debt risks, expected life of a loan (usually
less
than full term), etc and considering in market factors such as
competition,
what the market will bear, etc.
Then he reverse-engineers the formula -- any formula -- to arrive at a
periodic rate.
In other counties that don't have this protection,
paying weekly can actually attract a higher APR than
paying monthly.
And somehow the Canadian method prevents that?! Please demonstrate.
In the US, the APR is a nominal rate. So, a 12% APR loan with weekly
payments means that the weekly rate is 12%/52, and a 12% APR loan with
monthly payments means that the monthly rate is 12%/12. The APR is 12%
regardless of the payment frequency.
The UK APR is a compounded rate. So, a 12% APR loan with weekly
payments
means that the week rate is (1+12%)^(1/52)-1, and a 12% APR loan with
monthly payments means that the monthly rate is (1+12%)^(1/12)-1.
Again,
the APR is 12% regardless of the payment frequency.
Note: In Canada and the US, the only difference between APR and annual
interest rate is the amount of funding and perhaps the amount of
periodic
payments that are used in calculation. Ignoring front-end and back-end
costs ("loan fees") and periodic fees other than principal and interest
(PMI, period maintenance fees, etc), the APR and annual interest rate
are
the same. This is true for both Canada and the US. It is not true for
the
UK.
Fred might be thinking that a UK loan with an annual interest rate of
12% is
about 12.88% APR for weekly payments and about 12.82% APR for monthly
payments. But I would not say the methodology "attracts" a higher APR
for
lack of protection against it. It is simply a mathematical consequence
of
how the APR is computed in the UK. So what?
(Note: The computation in the previous paragraph presumes an answer to
my
question which might be incorrect, according to "Trip's" first
response.)
----- original message -----
"Fred Smith" wrote in message
...
Bernard,
Canadian mortgages are compounded semi-annually. Why? Because they are.
Other than that, our mortgages are like any other. Everyone quotes an
annual rate, so do we. You can pay your mortgage monthly, weekly,
bi-weekly, semi-monthly, or any other period that you and the lender
agree
on.
While the compounding period is somewhat annoying, the advantage is the
lender can't play games with the rate. In other counties that don't
have
this protection, paying weekly can actually attract a higher APR than
paying monthly.
Regards,
Fred.
"Bernard Liengme" wrote in message
...
I would Google (or Bing) to see if I could find the rules
Do not go by the US rules. For example, Canadian law requires lender
to
quote an annual rate that is some how related to a 6-month rate but
charged monthly!
I entered: uk apr "monthly rate" and this seemed useful
http://en.wikipedia.org/wiki/Annual_percentage_rate
When you find the rules, use a website to double check your
calculation.
As an ex-Brit I still trust Auntie so I would go to
http://www.bbc.co.uk/homes/property/...lculator.shtml
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"JoeU2004" wrote in message
...
I would like to hear from UK readers.
How do lenders actually determine the monthly payment for your
mortgage
loan?
According to http://support.microsoft.com/kb/294396/en-us , an annual
interest rate of 12%, for example, is converted to a monthly rate of
about 0.9489% by NOMINAL(12%,12)/12, which is equivalent to
RATE(12,0,-1,1+12%). In other words, the annual rate is determined by
compounding the monthly rate.
Ergo, the monthly payment on a loan of 108,000 at 12% over 30 years
would be about 1060.18, computed by PMT(RATE(12,0,-1,1+12%),
30*12, -108000).
However, three online calculators[*] compute a different payment.
The
monthly payment is computed by PMT(12%, 30, -108000)/12, which
results
in about 1117.29.
That has an effective monthly interest rate of RATE(30*12,
PMT(12%,30,-108000)/12, -108000), which is about 1.0064%.
End notes
---------
[*] Three online calculators:
http://www.bbc.co.uk/homes/property/...lculator.shtml
http://www.cml.org.uk/cml/consumers/...mortcalculator
http://www.mortgages.co.uk/calculato...alculator.html
|