Using the nominal rate for a loan, is there a formula in Excel for
calculating the APR?

Thanks and regards,

PJF

"PJF" wrote:
Using the nominal rate for a loan, is there
a formula in Excel for calculating the APR?

That depends on how you define "APR" for a loan.

For US loans conforming to the Truth In Lending regulation, the APR __is__ a
"nominal" (non-compounded) annual rate. So the APR is the (nominal) annual
interest rate, ignoring closing costs and monthly service fees like PMI.

What distinguishes the APR from the annual interest rate is the inclusion of
specific fees in the cost of the loan, typically the so-called "points" and
most other closing costs, as well as PMI and any other monthly service
charges, if any. See http://banking.about.com/od/loans/a/calculateapr.htm ,
among others.

Suppose you have a 15-year fixed-rate loan of $100,000 paid off monthly at
6% with closing costs of 1% points and $1500 in additional "finance
charges".

The nominal monthly payment (excluding PMI and other monthly service
charges) is computed the normal way, based on the full loan amount, to wit:

=ROUNDUP(PMT(6%/12, 15*12, -100000), 2)

Note that PMT must be rounded to some multiple of legal currency. I chose
cents; a lender might choose dollars or any other unit. I chose to round
up; thus, the last payment is no more than the regular payment. A lender
might choose to round down; but in that case, the lender should disclose the
amount of the last payment, if it is substantially different.

The APR can be estimated using the nominal monthly payment and the loan
amount less qualifying closing costs, to wit:

=12 * RATE(15*12, monthlyPayment + PMI +
otherServiceCharges, -(100000*(1-1%) - 1500))

This is only an estimate because the last nominal payment is different from
the regular monthly payment computed with PMT above.

Note: Minor adjustments must be made for different payment frequencies.
Major adjustments must be made for loan structures that differ from US
loans, notably Canada and perhaps the UK.

Joel,

Thanks so much for your detailed explanation. I appreciate the time you
took to spell it out so thoroughly.

Regards.

PJF


"JoeU2004" wrote in message
...
"PJF" wrote:
Using the nominal rate for a loan, is there
a formula in Excel for calculating the APR?

That depends on how you define "APR" for a loan.

For US loans conforming to the Truth In Lending regulation, the APR __is__
a
"nominal" (non-compounded) annual rate. So the APR is the (nominal)
annual
interest rate, ignoring closing costs and monthly service fees like PMI.

What distinguishes the APR from the annual interest rate is the inclusion
of
specific fees in the cost of the loan, typically the so-called "points"
and
most other closing costs, as well as PMI and any other monthly service
charges, if any. See http://banking.about.com/od/loans/a/calculateapr.htm
,
among others.

Suppose you have a 15-year fixed-rate loan of $100,000 paid off monthly at
6% with closing costs of 1% points and $1500 in additional "finance
charges".

The nominal monthly payment (excluding PMI and other monthly service
charges) is computed the normal way, based on the full loan amount, to
wit:

=ROUNDUP(PMT(6%/12, 15*12, -100000), 2)

Note that PMT must be rounded to some multiple of legal currency. I chose
cents; a lender might choose dollars or any other unit. I chose to round
up; thus, the last payment is no more than the regular payment. A lender
might choose to round down; but in that case, the lender should disclose
the
amount of the last payment, if it is substantially different.

The APR can be estimated using the nominal monthly payment and the loan
amount less qualifying closing costs, to wit:

=12 * RATE(15*12, monthlyPayment + PMI +
otherServiceCharges, -(100000*(1-1%) - 1500))

This is only an estimate because the last nominal payment is different
from
the regular monthly payment computed with PMT above.

Note: Minor adjustments must be made for different payment frequencies.
Major adjustments must be made for loan structures that differ from US
loans, notably Canada and perhaps the UK.