mortgage payment calculation

[kacky]

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

 

[JoeU2004]

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 -----

 

[Bernard Liengme]

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

 

[kacky]

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.



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 -----

 

[MyVeryOwnSelf]

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?

There are fully worked-out templates already available for this.

Look at
http://office.microsoft.com/en-us/templates/default.aspx
and search for "mortgage."

 

[JoeU2004]

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 -----

 

[JoeU2004]

Errata....

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 -----