View Single Post
  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] joeu2004@hotmail.com is offline
external usenet poster
 
Posts: 418
Default How do I caluclate an Annual Percentage Rate in Excel for an A

JON wrote:
Joe, In the IRR function you used 8%/12! Why Why does a functuion say guess?
and why did you use 8%?


All good questions. This is a limitation of the Excel implementation,
in my opinion. I am familiar with IRR implementations that work
perfectly well without a guess -- at least for a wider range of
solutions.

The answer is.... First, I tried IRR(B1:B361) without a "guess", and I
got the #NUM! error. Ergo, I knew we needed a "guess". Ordinarily, it
is very difficult for us humans to come up with a "guess". That is why
we invented computers. Klunk! But since you provided a likely
solution (7.981%), and since I knew that it should be computed by
12*monthlyRate, I decided to try 8%/12 as an approximation of that
monthly rate.

In short, you already provided the answer. I just encouraged Excel to
compute it. ;-)

I keep coming up with the $NUM!
I had created an amortization chart and came up with $401,339.68 and finance
charges of $252,299.45. Thus I am using "=12*IRR(-252299.45,(.08/12))" &
getting the $NUM! feedback. What am I doing wrong?


Just about everything.

For starters, the first argument for IRR() is incorrect. It must be a
series of equally-spaced cash flows (although some cash flows can be
zero). Look at the IRR help page.

Secondly, it is not clear to me what you intend to do with
"$252,299.45" and "$401,339.68". (I come up with slightly different
numbers -- $252,300.07 and $401,340.30.) Yes, that is the total
interest (based on the loan amount less loan costs); and yes, that is
the total payments. But I am not aware of any mathematical use of
those two numbers that would result in the correct ARM APR -- where by
"correct", I mean: in compliance with Reg Z, assuming you are talking
about a US loan.

That __is__ the kind of simplication I was hoping might work in some
way. But after thinking about it and after Fred's corroborating
response, I feel more confident that a simpler solution does not exist
-- at least, not using Excel.

I think we could break up the payment series into three groups, each
with equal payments. The sum of the PV of each group, properly
adjusted for time, should equal the loan amount less the loan costs.
But therein lies the rub: in order to compute the PVs, I believe we
need to know the IRR. We could write a VBA function to do that
computation, iterating with successively better guesses. That is what
the IRR() function does. But I do not believe any Excel function
allows us to specify groups of equal payments, like the HP 12C
calculator does.

By the way, that approach is wild speculation. I have not tried to
implement that algorithm to see if really works.