"Bernard Liengme" wrote:
Canadian banks are required to quote the interested
as if it was semi-annual (ie if they quote 6% then
it is 3% semi-annually) BUT they compute the interest
on a month basis. Very odd, eh?
Of course they compute interest on a monthly basis. The issue is: what is
the monthly rate?
And the answer to that question impacts the monthly payment, total interest
and loan reduction computations.
The following is according to
http://support.microsoft.com/kb/294396/en-us .
It jibes with online Canadian calculators that I have tried.
According to the KB, Canadian law "permits a compounding frequency of 2".
The monthly rate is computed by RATE(6,0,-1,1+6%/2), or if you prefer:
(1+6%/2)^(1/6)-1. That results in a lower monthly rate, payment and total
interest than the nominal rate, 6%/12, which is used in the US.
Aside.... I don't know how much to read into the word "permits". Also, I
quibble with the terminology above.
FYI, according to the KB, UK loan rates are considered to be "effective"
annual rates. The monthly rate is computed by RATE(12,0,-1,1+6%), or if you
prefer: (1+6%)^(1/12)-1. That results in a lower monthly rate, payment and
total interest than the Canadian method.
I have not been able to vet the KB's method for UK loans. On the contrary,
I believe I have seen online UK loan calculators that use the US method.
Also, I believe I found one UK loan calculator that computed the monthly
payment, for a 25-year 100,000 loan for example, by PMT(6%,25,-100000)/12,
resulting in a monthly rate of RATE(25*12,pmt,-100000). That results in a
higher monthly rate, payment and total interest than the US method.
Caveat emptor: I might remember the 2nd UK methodology incorrectly; and
even if I remember it correctly, the original source might be incorrect.
For alternative methods in these and other countries, I would appreciate
pointers to online sources for my edification. Thanks.
----- original message -----
"Bernard Liengme" wrote in message
...
Canadian banks are required to quote the interested as if it was
semi-annual (ie if they quote 6% then it is 3% semi-annually) BUT they
compute the interest on a month basis. Very odd, eh?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"JoeU2004" wrote in message
...
"kacky" wrote in message
...
if I am borrowing $100,000 for 30 yrs. @ 6.5% interest what are the
monthly or yearly payments
Assuming a typical annuity loan with payment in arrears:
=round(pmt(6.5%/12, 30*12, -100000),2)
If that formula is in A1, the yearly payment is simply:
=12*A1
assuming 12 payments in the year.
Note: That also assumes that interest is not compounded mid-year. Loans
in most countries are that way, notably US loans. But Canada loans
sometimes (always, Fred?) compound interest semi-annually. If you need a
formula for such Canadian loans, post an update.
and also what is the principal balance due after 5
yrs. (60 mos.) of payments?
If the monthly payment is computed in A1, then:
=fv(6.5%/12, 5*12, A1, -100000)
Format with a number format and 2 decimal places. Note that the
underlying value will not be rounded (unless you set the calculation
"Precision as displayed"; not recommended).
Caveat: The payment should be rounded, not simply formatted to 2 decimal
places. But by doing so, the last payment is usually different. The
last payment can be computed by:
=round(fv(6.5%/12, 30*12-1, A1, -100000) * (1 + 6.5%/12),2)
The FV() expression computes the principal balance after 360 minus 1
payments. The (1+6.5%/12) add the interest for the last month.
----- original message -----
"kacky" wrote in message
...
What is the excel formula to calculate a mortgage payment? also is there
a
formula which can solve the amount of principal paid in a defined
period? for
example, if I am borrowing $100,000 for 30 yrs. @ 6.5% interest what are
the
monthly or yearly payments and also what is the principal balance due
after 5
yrs. (60 mos.) of payments?
thanks