APR (Annual Percentage Rate) calculator

[Guest]

Can anyone tell me how do i create APR (Annual Percentage Rate) calculator in
excel or where can i find any examples.
Thanx,

 

[joeu2004]

Can anyone tell me how do i create APR (Annual Percentage Rate) calculator in
excel or where can i find any examples.

Because the term APR is so often misused, it would be helpful to know
exactly what you are trying to calculate. For example, the APR of a
loan, the APY of a savings account, or the IRR of a series of cash
flows?

 

[Guest]

Because the term APR is so often misused, it would be helpful to know
exactly what you are trying to calculate. For example, the APR of a
loan, the APY of a savings account, or the IRR of a series of cash
flows?

Ok, thanks for the reply, It is a APR for a Loan.

 

[joeu2004]

Ok, thanks for the reply, It is a APR for a Loan.

I neglected to ask you: (1) what type of loan (fixed or variable); (2)
in what jurisdiction (US or otherwise, notably Canada); and (3) do you
need a solution for "abnormalities" such as irregular payments (which
is true for ARMs by definition) and fractional periods initially or at
the end.

For a "normal" US fixed-rate loan of $100,000 at 6% and no "loan fees"
(a useless hypothetical example ;->), the APR is simply 6%. This is
because the APR calculation should conform to Reg Z (aka the Truth in
Lending Act), which specifies that the APR is a nominal rate. (That
might surprise some people [1].)

(By "normal loan", I mean: the payment is calculated to reduce the
loan to zero over the full term of the loan. See the PMT() function in
the next example.)

For a US fixed-rate loan of $100,000 at 6% with monthly payments over
30 years and $1500 in "loan fees" (see below), the APR can be computed
as follows:

=12 * rate(30*12, pmt(6%/12, 30*12, -100000), -(100000-1500))

In other words, the payment is computed based on the full principal of
the loan, but the principal is reduced by the "loan fees" in order to
compute the APR. (See note [2] for some computational details.) This
is why the APR is typically greater than the loan "interest rate".

At issue is: what are "loan fees"? For simplicity, I lump "points"
into the loan fees, but some lenders distinguish between "points" and
(other) "loan fees". So in the example, above, $1500 might represent
1% in "points" and other "loan fees".

That still begs the question: what are the "other" loan fees? Ah,
there's the rub. Federal law is somewhat vague, at least for
advertising purposes, so it varies from lender to lender [3]. That is
why the advertised APR is not really useful for comparing loans, even
though that was one of the intentions of Reg Z in the first place.

And if the APR is "not really useful" for comparison shopping, what
good is it anyway? It is important to note that the APR has no bearing
on how the terms of the loan are computed -- notably the periodic
payment amount. Those terms are based on the loan "interest rate", not
the APR.

Of course, I presented the simplest examples. The APR computation for
ARMs is more involved, as is the case for the other "abnormalities"
that I mentioned above. But I hope I convinced you that you simply do
not care ;-) [4].

HTH


Footnotes
--------------

[1] I presume the confusion over whether "APR" is a nominal rate or a
compounded (effective) rate arises from the mixed use of the term in
the financial industry. The savings industry used to use "APR" to
describe the compounded rate of a savings account and similar
investments. Now they typically use "APY" instead. I don't know if
that was motivated by a conscious desire to avoid confusion with
mortgage industry term (a good reason, IMHO), or if "APY" became the
favored term because so many savings accounts now have variable rates
based on money market indexes, and the money markets speak of "yields".

[2] I really should write round(pmt(6%/12,30*12,-100000),2) because the
payment must be in dollars and cents. (Some lenders round to even less
precision.) Reg Z also permits certain rounding latitude for the
disclosed APR (generally +/- 0.125%, but there are exceptions). Such
rounding creates certain mathematical anomalies, resulting in
disparities among inverse functions, for example. So I dispense with
those details here.

[3] Regarding APR advertisements, Reg Z requires only that the
advertisement state that the APR might "increase after consummation" of
the loan, if that is the case. And it almost always is the case
because the amount of "prepaid interest", which is usually included in
"loan fees", cannot be determined until the date is set for executing
the loan agreement.

[4] On the other hand, the APR computation could be useful as long as
__you__ do the computation to ensure consistency in how "loan fees" are
determined. But that requires that you get a full disclosure of all
fees, something some lenders will do only when you apply for a loan.
Catch-22! Online calculators seem often to be more up-front; but keep
in mind that most stated loan fees are mere approximations, not
adjusted for your locale, for example.