PJF said:
Any suggestions or solutions would be appreciated.
Are you asking about the APR of a loan or about the "APR" (really APY) of an
investment?
If the former (loan), how is this question different from your previous
posting (Subject: Annual % Rate calculation)?
If they are the same question, what was wrong with my response to your
previous posting?
Even if you are not interested in my trying to clarify any points that might
have confused you, it would be better to post that fact and keep all
relevant postings in the original thread rather than hope that I will not
find you if you post a new thread.
----- my previous response from another thread -----
PJF said:
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.
