mortgage payment calculation
Errata....
I wrote:
Assuming a typical annuity loan with payment in arrears:
=round(pmt(6.5%/12, 30*12, -100000),2)
Although I believe that is, indeed, how many/most people compute the payment
if they even think to round it (most don't), I prefer to use ROUNDUP. That
ensures that the last payment is no more than the usual monthly payment,
which might justify the failure of most lenders to disclose it.
Nonetheless, I cannot say with impunity which method of rounding, if any, is
"typical".
----- original message -----
"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
|