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