How do I caluclate an Annual Percentage Rate in Excel?

[Guest]

 

[JE McGimpsey]

Depends on what information you start with...

Take a look at the RATE() function in XL Help.

Lisa M <Lisa (e-mail address removed)> wrote:

 

[Guest]

I tried that but it didn't work. Thanks!!!! I need to calculate for
mortgages. Any other sugestions?
Lisa M

 

[JE McGimpsey]

How could anyone know what to suggest when you don't tell what
information you have available?

RATE() certainly can "calculate for mortgages". What does "it didn't
work" mean? Did you get the wrong answer? no answer? an error? a crash?

Nobody can see your worksheet (and please don't post it). Instead you
need to actually describe how your data is laid out, what you've tried,
and what hasn't worked.

 

[Guest]

Hope this info helps. It's kind of hard to type it and I wouldn't think it
be a good idea to post it. I can use a HP Financial Calculator to get the
number I need but I would rather have it on the spreadsheet that I am using.
I need to calculate an APR with Fees included. I tried the =Rate(TERM, -PMT,
$ AMT)*12 and it gave me the actually rate of interest charged. That is not
what I need. The APR is greater than the interest rate charged because it
takes the original loan amt plus certain fees charged. An example: loan
amount of $56,000 at rate of 5.75% for 30 yrs with a P&I pmt of $326.80 and
fees of $361.22. If I plug the numbers into my HP, it calcs an APR of
5.810%. I would like to have the speadsheet do the calculation that my HP
calculator does. I have in the spreadsheet the loan amount, int rate, pmt
and fees. Do you have any other suggestions?
Thanks for all of your help. I hope that I gave you better info than before.
Lisa M

 

[N Harkawat]

=RATE(360,PMT(5.75%/12,360,56000),56000-361.22)*12
gives me 5.81%

 

[Guest]

Thanks. I was missing part of the formula. Have a good day.

 

[Guest]

GREAT help. Can you help with TWO interest only loans?

First -- $500,000 loan for 2 years (24 months) at 9.5% (3,958.33/mo). Fees
of $11,090.56 are deducted from loan amount for a net funding of 488,909.44
and include a 2 point origination fee ($10,000); prepaid interest from
6/23-6/30 of $1,055.56; and a $35 wire fee. Is prepaid interest is
considered a fee for this calculation???

Second - $1,250,000 loan for 3 years (36 months). First 2 years at 9.5%
(9,895.83/mo); 3rd year at 10.5% (10,937.50/mo). Fees of $54,479.44 are
deducted from loan amount for a net funding of $1,195,520.56 and include a
4.25% origination fee (53,125); prepaid interest from 6/27-6/30 ($1,319.44);
and a $35 wire fee. Again, is prepaid interest considered a fee for this
calculation?

 

[Alan]

A word of advice, repost this as a reply in the original thread, there's a
good chance it wont be seen by the person you're addressing by starting a
new one,
Regards,
Alan.

 

[Guest]

How do I do that exactly? Go to the first box where Lisa asked the question
and reply there?

 

[Alan]

Yes, just open Lisa's mail and cick 'Reply Group'
Regards,
Alan.

 

[Alan]

click even! lol

Yes, just open Lisa's mail and cick 'Reply Group'
Regards,
Alan.

 

[Guest]

I have a term of 30 years or 360 payments
loan amount of $156,462
Prepaid finance costs of $7,421.77
Amount Financed after Prepaid Fin costs of $149,040.23
zero future value
starting rate for 5 years @ 5.125%
Mortgage insurance of $101.7 for 153 payments and then it drops
Rate goes to 8.049 after 60 payments.

My mortgage processing software is coming up with 7.981% for APR
I don't believe the starting rate or adjusting rate is a factor needed.

What function or formula can I use in excel?

 

[joeu2004]

I have a term of 30 years or 360 payments
loan amount of $156,462
Prepaid finance costs of $7,421.77
Amount Financed after Prepaid Fin costs of $149,040.23
zero future value
starting rate for 5 years @ 5.125%
Mortgage insurance of $101.7 for 153 payments and then it drops
Rate goes to 8.049 after 60 payments.
My mortgage processing software is coming up with 7.981% for APR
I don't believe the starting rate or adjusting rate is a factor needed.
What function or formula can I use in excel?

For a US loan, the following describes one way. I suspect it is not
the best way. But it does produce the same result as your mortgage
software.

The loan payment (excluding the mortgage insurance premium) for the
first 60 payments can be computed as follows ($851.92):

A1: =ROUND(PMT(5.125%/12, 360, -156462), 2)

The remaining balance of the loan can be computed as follows
($143,929.57):

A2: =FV(5.125%/12, 60, A1, -156462)

The loan payment (excluding the mortgage insurance premium) for the
remaining 300 payments can be computed as follows ($1115.55):

A3: =ROUND(PMT(8.049%/12, 300, -A2), 2)

Now fill in the following table. The first entry is the mortgage
amount less finance costs ($149,040.23). The next 153 entries are the
appropriate payment plus mortgage insurance, expressed as a negative
number.

B1: =156462 - 7421.77
B2:B62: =-($A$1 + 101.70)
B63:B154: =-($A$3 + 101.70)
B155:B361: =-$A$3

Finally, in any cell, compute APR as follows (7.9812%):

=12*IRR(B1:B361, 8%/12)

Note: I did not think that mortgage insurance premiums (as well as any
other periodic insurance premiums and taxes) are included with the loan
payment for the purpose estimating APR. I base that primarily on the
results of some online ARM APR calculators the allow you to specify
such additional periodic amounts separately. Their APR estimate is the
same whether the additional amounts are zero or not. Excluding the
mortgage insurance premium, the ARM APR is 7.0198%.

On the other hand, after a quick reading of Reg Z (Truth in Lending
Act), it seems that such additional amounts might be included, at least
under some circumstances. See TLA section 226.4(b) and exceptions in
226.4(c)-(e).

 

[Fred Smith]

You can't use the standard Rate function, because your payments change after
five years.

So your choices are IRR and XIRR. Both will require that you set up a table of
your 361 cash flows.

XIRR would be a more popular choice among knowledgeable users, because it will
automatically calculate the annual APR. IRR works like the other financial
functions -- the rate and the payment must be for the same period, so you would
be calculating a monthly rate, then converting it to annual.

XIRR has the additional benefit that you can tweak the payment dates to actual.
If the normal payment date falls on a weekend or holiday, you can specify the
following Monday or preceding Friday depending on your bank's policy.

 

[joeu2004]

XIRR would be a more popular choice among knowledgeable users, because it will
automatically calculate the annual APR.

I suppose you could say it depends on your goal.

If your goal is to compute a (US) RegZ-compliant APR, XIRR gives the
wrong answer. (Reg Z is called the Truth in Lending Act.)

The reason is that XIRR annualizes the rate by compounding the daily
rate over 365 days. In other words, it is a compounded or effective
annual rate. But Reg Z specifies that the APR is "the __nominal__
annual percentage rate determined by __multiplying__ the unit-period
rate by the number of unit-periods in a year".

(The unit-period is determined by the payment frequency -- monthly, in
the OP's case.)

For example, using the OP's loan specifications and assuming payments
on the 1st of each month starting in Oct 2006, XIRR computes 8.2736%.
IRR computes 7.9812%. The IRR number matches the APR computed by the
OP's mortgage software, which presumably computes the APR in accordance
with the laws of some jurisdiction, perhaps the US.

By the way, "nominalizing" the XIRR number results in 7.9755%. Since
that is within 0.125% of the RegZ-compliant APR, as generally required
by Reg Z, arguably you could use XIRR if you "nominalize" its result.
One way to do that is:

=12 * rate(12, 0, -1, 1+XIRR(...))

Seems to me like a lot of trouble to compute the wrong answer. ;-)

 

[Guest]

Joe, In the IRR function you used 8%/12! Why Why does a functuion say guess?
and why did you use 8%? 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?

 

[Fred Smith]

Thanks for the information. Now I can see why US banks use daily compounding,
because it allows them to advertise a lower APR. I'm amazed the regulations
don't require the same annualizing as XIRR does, because that's the only
effective way of comparing rates. Live and learn. I'm glad Reg Z doesn't apply
in Canada.

 

[joeu2004]

I'm glad Reg Z doesn't apply in Canada.

Can you provide a pointer to an online copy of the equivalent Canadian
regulations?

I inferred that, in fact, Canada does also compute a nominal rate for
the APR, perhaps the same way as the US, because one online Canada APR
calculator states that the APR is the same as the nominal rate if there
are no "borrowing charges" (loan fees). I do not believe that would be
the case if the Canadian APR is computed by compounding.

 

[joeu2004]

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.