ExcelBanter - mortgage calculator formula

"Charles Walker" wrote:
I need a Formula for Mortgage Calculations in EXCEL.

You are not really specific enough. I suspect you are
interested in the PMT() function. For example, given
a loan of $200,000 to be paid over 15 years at a 5%
nominal rate (not APR), the monthly payment is
approximately:

=PMT(5%/12,15*12,-200000)

It is important to reiterate some details.

5% is the nominal rate, not the APR. If you only know
the APR, you could use APR/12 above. It makes only
a small error in the computation (about $20 in this
example).

Alternatively, you could should compute the periodic
rate differently. For mortgages, the APR can be
misleading for the purpose of computing the monthly
payment because, following federal guidelines, lenders
include other fees in the APR computation. But if an
APR of 5.116%, for example, is based solely on the
principle of the loan, you might replace "5%/12" with:

=RATE(12,,-1,1 + 5.116%)

The result still is approximate because most lenders
compound daily during the monthly payment period.
Consequently, "5%/12" should be replaced with:

=FV(5%/365,30,,-1)-1 // 5% nominal rate
=FV(RATE(365,,-1,1 + 5.116%),30,,-1)-1 // 5.116% APR

But that is still only approximate because some months
have 31 days and one month has either 28 or 29 days,
depending on the year. So the precision might not be
worth the trouble, unless you are a lender.