Calculating an Annual Percentage Rate (APR) in Excel

Calculating an Annual Percentage Rate (APR) in Excel can be done using a simple formula. Here are the steps:

1. First, you need to know the periodic interest rate. This is the interest rate that is charged on a loan or investment over a specific period of time. For example, if the loan has a monthly interest rate of 1%, then the periodic interest rate is 1%.
   
2. Next, you need to know the number of periods in a year. For example, if the loan has a monthly interest rate, then there are 12 periods in a year.
   
3. Now, you can use the following formula to calculate the APR:
   
   
   Formula:

   =(1+periodic interest rate)^number of periods in a year-1 

   For example, if the periodic interest rate is 1% and there are 12 periods in a year, the formula would be:
   
   
   Formula:

   =(1+0.01)^12-1 

4. Once you have entered the formula into Excel, press enter and the result will be the APR. In this example, the APR would be 12.68%.
   

That's it! You can now use this formula to calculate the APR for any loan or investment with a known periodic interest rate and number of periods in a year.

Depends on what information you start with...

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

In article ,
Lisa M <Lisa wrote:

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

"JE McGimpsey" wrote:

Depends on what information you start with...

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

In article ,
Lisa M <Lisa wrote:

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.


In article ,
Lisa M wrote:

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

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

"JE McGimpsey" wrote:

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.


In article ,
Lisa M wrote:

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

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



"Lisa M" wrote in message
...
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

"JE McGimpsey" wrote:

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.


In article ,
Lisa M wrote:

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

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

"N Harkawat" wrote:

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



"Lisa M" wrote in message
...
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

"JE McGimpsey" wrote:

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.


In article ,
Lisa M wrote:

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

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?

"N Harkawat" wrote:

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



"Lisa M" wrote in message
...
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

"JE McGimpsey" wrote:

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.


In article ,
Lisa M wrote:

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

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.
"MK Manzer" <MK wrote in message
...
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?

"N Harkawat" wrote:

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



"Lisa M" wrote in message
...
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

"JE McGimpsey" wrote:

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.


In article ,
Lisa M wrote:

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

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

"Alan" wrote:

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.
"MK Manzer" <MK wrote in message
...
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?

"N Harkawat" wrote:

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



"Lisa M" wrote in message
...
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

"JE McGimpsey" wrote:

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.


In article ,
Lisa M wrote:

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

Yes, just open Lisa's mail and cick 'Reply Group'
Regards,
Alan.
"MK Manzer" wrote in message
...
How do I do that exactly? Go to the first box where Lisa asked the
question
and reply there?

"Alan" wrote:

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.
"MK Manzer" <MK wrote in message
...
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?

"N Harkawat" wrote:

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



"Lisa M" wrote in message
...
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

"JE McGimpsey" wrote:

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.


In article ,
Lisa M wrote:

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

click even! lol
"Alan" wrote in message
...
Yes, just open Lisa's mail and cick 'Reply Group'
Regards,
Alan.
"MK Manzer" wrote in message
...
How do I do that exactly? Go to the first box where Lisa asked the
question
and reply there?

"Alan" wrote:

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.
"MK Manzer" <MK wrote in message
...
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?

"N Harkawat" wrote:

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



"Lisa M" wrote in message
...
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

"JE McGimpsey" wrote:

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.


In article ,
Lisa M wrote:

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

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?
--
JON


"Alan" wrote:

Yes, just open Lisa's mail and cick 'Reply Group'
Regards,
Alan.
"MK Manzer" wrote in message
...
How do I do that exactly? Go to the first box where Lisa asked the
question
and reply there?

"Alan" wrote:

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.
"MK Manzer" <MK wrote in message
...
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?

"N Harkawat" wrote:

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



"Lisa M" wrote in message
...
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

"JE McGimpsey" wrote:

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.


In article ,
Lisa M wrote:

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

JON wrote:
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).

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.

--
Regards,
Fred


"JON" wrote in message
...
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?
--
JON


"Alan" wrote:

Yes, just open Lisa's mail and cick 'Reply Group'
Regards,
Alan.
"MK Manzer" wrote in message
...
How do I do that exactly? Go to the first box where Lisa asked the
question
and reply there?

"Alan" wrote:

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.
"MK Manzer" <MK wrote in message
...
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?

"N Harkawat" wrote:

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



"Lisa M" wrote in message
...
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

"JE McGimpsey" wrote:

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.


In article ,
Lisa M wrote:

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

Fred Smith wrote:
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. ;-)

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?
--
JON


" wrote:

JON wrote:
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).

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.

--
Regards,
Fred


wrote in message
ups.com...
Fred Smith wrote:
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. ;-)

Fred Smith wrote:
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.

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.

Clarification....

I wrote:
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.

I did not intend to imply that Canadians "nominalize" the monthly rate
in the same way as the US, namely 12*RATE(...) in the simple case. And
perhaps I should not use the term "nominal" altogether for the Canadian
APR, since Canadian mortgage interest rates are specified as twice a
semiannual compounded rate.

Using the OP's terms, the following is my __guess__ as to how a
Canadian APR might be computed for both fixed-rate mortgages and ARMs.
I cannot find a (free) online Canadian mortgage calculator that shows
APR, perhaps because Canadians are enlightened enough to realize that
the APR is virtually useless ;-). So I cannot confirm my guess. If
someone has access to professional mortgage software for Canada, it
would be great if you would post its results for these examples.


Canadian FIXED RATE, FIXED PAYMENT LOAN (simplified):
Amortization term: 360 months
Loan amount: $156,462
Balloon payment: $0
Prepaid loan cost: $7421.77
Additional financial payments: $0
Fixed rate: 5.125%

Monthly payment ($846.74):
A1: =round( pmt( rate(6, 0, -1, 1+5.125%/2), 360, -156462 ), 2 )

APR (5.5689%):
=2 * ( fv( rate(360, A1, -(156462 - 7421.77)), 6, 0, -1 ) - 1 )


Canadian VARIABLE RATE, VARIABLE PAYMENT LOAN:
Amortization term: 360 months
Loan amount: $156,462
Balloon payment: $0
Prepaid loan cost: $7421.77
Additional financial payments: $101.70 for 1st 153 payments (mortgage
insurance)
Fixed rate: 5.125%
Fixed term: 60 months
Initial variable rate: 8.049%
Variable term: 300 months

Loan payment (excluding the mortgage insurance premium) for the
first 60 payments ($846.74):

A1: =round( pmt( rate(6, 0, -1, 1 + 5.125%/2), 360, -156462 ), 2 )

Remaining balance of the loan after the first 60 payments
($143,821.10):

A2: =fv( rate(6, 0, -1, 1 + 5.125%/2), 60, A1, -156462 )

Loan payment (excluding the mortgage insurance premium) for the
remaining 300 payments ($1102.16):

A3: =round( pmt( rate(6, 0, -1, 1 + 8.049%/2), 300, -A2 ), 2 )

Cash flow table:

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

APR (8.0147%)
=2 * ( fv( irr(B1:B361, 8%/12), 6, 0, -1 ) -1 )

Caveat: As I noted elsewhere, I am not sure that the mortgage
insurance premium should be included in the APR calculation. I retain
it, in part, for comparison with the US loan and, in part, because it
makes the problem more interesting ;-).

Hi,

I trying to solve and validate the APR on an ARM loan with the IRR function
based on the following,
- 3/1 ARM with initial of 5.75% (rate good for 36 months)
- Loan term of 30 years (360 months)
- Loan amount of $250,000
- Initial payment of $1,458.93; pmt(.0575/12,360,250000)
- 1% in origination points; $2,500
- $750 in processing fees
- Prepaid interest of $598.96 ($250k @ 5.75% for 15 days/360 day basis)
- An index equal to 5.5%, plus a 1.75% margin; 7.25% rate starting in the
37th month
- Loan balance at end of 36 month period is $239,771.05;
fv(.0575/12,36,1458.93,250000)
- Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05)

My worksheet is set up as follows,
A1:A3; header information
A4; 12*IRR(A5:A365,0.1/12)
A5; $250,000 (the loan amount)
A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96))
A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05)

This produces an APR of 6.887%; the APR on one website illustrates an APR of
7.168% (includes the prepaid interest); another site that does not take into
account the prepaid interest illustrates an APR of 7.144% (note: when I strip
out the prepaid interest at the first website, it also produces a 7.144%
APR). What am I missing?


"Lisa M" wrote:

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

"JE McGimpsey" wrote:

Depends on what information you start with...

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

In article ,
Lisa M <Lisa wrote:

On Jul 25, 12:20 pm, mlion wrote:
I trying to solve and validate the APR on an ARM loan with the IRR
function based on the following,
[....]
- Initial payment of $1,458.93; pmt(.0575/12,360,250000)
[....]
- Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05)

My worksheet is set up as follows,
[....]
A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96))
A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05)

Why did you choose to use different payment amounts in your
worksheet? The first pair of payment amounts ("based on the
following") is the correct one to use.

In your computation, the last parameter to PMT() for the first 36
months is incorrect. It should be simply 250000. It is computed as
if the lender gave you $250,000, and you paid the lender the amount of
the initial costs out-of-pocket.

The parameters to PMT() for the last 324 months seem correct; and
indeed, with those parameters, PMT() returns the correct amount,
namely $1688.45. I don't know how you got $1693.05; perhaps you
mistyped ".0728" for 7.25%.

A4; 12*IRR(A5:A365,0.1/12)
A5; $250,000 (the loan amount)

For the IRR, each cash flow is the net of inflows and outflows in that
period. For the initial period, the inflow is the initial loan amount
($250,000), and the outflow is the sum of the initial loan costs (at
least 2500 + 750 + 598.96). That is, A5 should be (at least):

=250000 - 2500 - 750 - 598.96

the APR on one website illustrates an APR of
7.168% (includes the prepaid interest); another site that does not take into
account the prepaid interest illustrates an APR of 7.144% (note: when I strip
out the prepaid interest at the first website, it also produces a 7.144%
APR). What am I missing?

With the corrections above, I compute an annualized IRR of 7.0079%
(rounded), including the prepaid interest. To get 7.168% and 7.144%,
I would have to assume that the lender included an addition $4010 to
$4040 in initial costs, which you (or the lender) did not mention.
Although it seems unlikely that two lenders would make the same such
assumption, note that often different online APR calculators use the
same underlying "engine".

What am I missing?

Only that the APR is really not a good way to compare loans, at least
in the US. I would compare the loan terms and the initial loan costs
separately. But even then, comparing ARMs is tricking business
because of the flexibility that lenders have in determining rates and
payments after the initial fixed period.


--- complete original posting ---

On Jul 25, 12:20 pm, mlion wrote:
I trying to solve and validate the APR on an ARM loan with the IRR function
based on the following,
- 3/1 ARM with initial of 5.75% (rate good for 36 months)
- Loan term of 30 years (360 months)
- Loan amount of $250,000
- Initial payment of $1,458.93; pmt(.0575/12,360,250000)
- 1% in origination points; $2,500
- $750 in processing fees
- Prepaid interest of $598.96 ($250k @ 5.75% for 15 days/360 day basis)
- An index equal to 5.5%, plus a 1.75% margin; 7.25% rate starting in the
37th month
- Loan balance at end of 36 month period is $239,771.05;
fv(.0575/12,36,1458.93,250000)
- Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05)

My worksheet is set up as follows,
A1:A3; header information
A4; 12*IRR(A5:A365,0.1/12)
A5; $250,000 (the loan amount)
A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96))
A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05)

This produces an APR of 6.887%; the APR on one website illustrates an APR of
7.168% (includes the prepaid interest); another site that does not take into
account the prepaid interest illustrates an APR of 7.144% (note: when I strip
out the prepaid interest at the first website, it also produces a 7.144%
APR). What am I missing?

Errata....

I On Jul 25, 2:15 pm, I wrote:
With the corrections above, I compute an annualized IRR of 7.0079%
(rounded), including the prepaid interest. To get 7.168% and 7.144%,
I would have to assume that the lender included an addition $4010 to
$4040 in initial costs, which you (or the lender) did not mention.

I think it is more likely that that is simply the NPV of costs added
to each payment, for example to cover PMI. If you provide the URL for
the mortgage calculators that you used, perhaps I can explain the
descrepancy better.

What am I missing?

Only that the APR is really not a good way to compare loans, at least
in the US.

Interestingly, the Consumer Handbook on ARMs
(at http://www.federalreserve.gov/Pubs/a...msbrochure.pdf)
says exactly the opposite. It states: "Because all lenders follow
the same rules when calculating the APR, it provides you with a good
basis for comparing the cost of loans".

Well, yes, "all lenders follow the same rules". But the rules have
such great flexibility that two lenders with identical loan terms and
costs can disclose different APRs in advertisements and pre-loan
documents as long as they state that the APR might increase after
consummation.

Hi,

Thanks for the response. I made the adjustments and came up with the same
answer. In regard to that $1,693.05 payment reference for cells a42:a365,
that was a typo. Here are the websites that I was using to validate my APR;

http://www.lenderhomepage.com/tools/...calculator.php

http://www.dinkytown.net/java/Mortga...djustable.html

The information displayed for their calculation of the APR is pretty
straight-forward and there is no mention of any additional fees ($4010 plus).
I've ran my IRR function with many different variables trying to match their
APR, and just could not accept that their number could be "wrong" and the
solution via excel was "correct". Take a look at the URLs and let me know
what you think they are doing to derive their number other than to add some
mystery fees.

Once again, thank you for your input.

"joeu2004" wrote:

On Jul 25, 12:20 pm, mlion wrote:
I trying to solve and validate the APR on an ARM loan with the IRR
function based on the following,
[....]
- Initial payment of $1,458.93; pmt(.0575/12,360,250000)
[....]
- Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05)

My worksheet is set up as follows,
[....]
A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96))
A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05)

Why did you choose to use different payment amounts in your
worksheet? The first pair of payment amounts ("based on the
following") is the correct one to use.

In your computation, the last parameter to PMT() for the first 36
months is incorrect. It should be simply 250000. It is computed as
if the lender gave you $250,000, and you paid the lender the amount of
the initial costs out-of-pocket.

The parameters to PMT() for the last 324 months seem correct; and
indeed, with those parameters, PMT() returns the correct amount,
namely $1688.45. I don't know how you got $1693.05; perhaps you
mistyped ".0728" for 7.25%.

A4; 12*IRR(A5:A365,0.1/12)
A5; $250,000 (the loan amount)

For the IRR, each cash flow is the net of inflows and outflows in that
period. For the initial period, the inflow is the initial loan amount
($250,000), and the outflow is the sum of the initial loan costs (at
least 2500 + 750 + 598.96). That is, A5 should be (at least):

=250000 - 2500 - 750 - 598.96

the APR on one website illustrates an APR of
7.168% (includes the prepaid interest); another site that does not take into
account the prepaid interest illustrates an APR of 7.144% (note: when I strip
out the prepaid interest at the first website, it also produces a 7.144%
APR). What am I missing?

With the corrections above, I compute an annualized IRR of 7.0079%
(rounded), including the prepaid interest. To get 7.168% and 7.144%,
I would have to assume that the lender included an addition $4010 to
$4040 in initial costs, which you (or the lender) did not mention.
Although it seems unlikely that two lenders would make the same such
assumption, note that often different online APR calculators use the
same underlying "engine".

What am I missing?

Only that the APR is really not a good way to compare loans, at least
in the US. I would compare the loan terms and the initial loan costs
separately. But even then, comparing ARMs is tricking business
because of the flexibility that lenders have in determining rates and
payments after the initial fixed period.


--- complete original posting ---

On Jul 25, 12:20 pm, mlion wrote:
I trying to solve and validate the APR on an ARM loan with the IRR function
based on the following,
- 3/1 ARM with initial of 5.75% (rate good for 36 months)
- Loan term of 30 years (360 months)
- Loan amount of $250,000
- Initial payment of $1,458.93; pmt(.0575/12,360,250000)
- 1% in origination points; $2,500
- $750 in processing fees
- Prepaid interest of $598.96 ($250k @ 5.75% for 15 days/360 day basis)
- An index equal to 5.5%, plus a 1.75% margin; 7.25% rate starting in the
37th month
- Loan balance at end of 36 month period is $239,771.05;
fv(.0575/12,36,1458.93,250000)
- Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05)

My worksheet is set up as follows,
A1:A3; header information
A4; 12*IRR(A5:A365,0.1/12)
A5; $250,000 (the loan amount)
A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96))
A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05)

This produces an APR of 6.887%; the APR on one website illustrates an APR of
7.168% (includes the prepaid interest); another site that does not take into
account the prepaid interest illustrates an APR of 7.144% (note: when I strip
out the prepaid interest at the first website, it also produces a 7.144%
APR). What am I missing?

On Jul 25, 8:30 pm, mlion wrote:
Here are the websites that I was using to validate my APR;
http://www.lenderhomepage.com/tools/...age-apr-calcul...
http://www.dinkytown.net/java/Mortga...djustable.html
[....]
I've ran my IRR function with many different variables trying to match their
APR, and just could not accept that their number could be "wrong" and the
solution via excel was "correct".

In this posting, I'll pick apart the dinkytown calculator. I do
conclude that its APR computation is incorrect when the interest rate
varies. Like you, I am relunctant to reach that conclusion. But
consider the following.

First, it appears that the dinkytown calculator treats the starting
interest rate plus margin as the (lifetime) interest rate cap. Also,
it appears that it adds the "max adjustment" to the starting rate
until that rate cap is reached, starting after "months before first
adjustment" at intervals of "months between adjustments". The current
index does not seem to be a factor at all. I believe that is a misuse
of those factors -- or those factors are misnamed. But I will take
their usage for granted.

If we set "max adjustment" to 0%, we get a fixed-rate loan. With a
starting rate of 5.75% for a loan amount of 250,000 over 30 years, the
first 359 payments are 1458.93 (rounded), and the last payment is
1460.88 (rounded). [Aside: Using FV() in Excel, I compute a final
payment of 1460.98 rounded. The descrepancy arises because it appears
the dinkytown rounds each periodic interest amount. I cannot say with
impunity whether that is right or wrong, or common or uncommon among
lenders.]

With points of 2500 (1%), other fees of 750 and 0% origination fee
(and no prepaid interest), the dinkytown calculator reports an APR of
5.871%. In Excel, I compute an IRR of 5.8706% (rounded) --
essentially the same -- by setting the initial cash flow to 250000 -
2500 - 750 and by multiplying the resulting monthly IRR by 12.

I believe that demonstrates that the IRR paradigm that I suggested in
my previous posting is correct, at least to the extent that it matches
the dinkytown calculator.

So far, so good. But....

If we set "max adjustment" to 0.5%, the dinkytown calculator computes
payments of 1458.93, 1533.77, 1608.39 and 1682.64 and interest rates
of 5.75%, 6.25%, 6.75% and 7.25% starting with payment numbers 1, 37,
49 and 61 respectively, with a last payment of 1682.46. The reported
APR is 7.144%.

But the calculator reports the same 7.144% APR when "max adjustment is
1.0% and 1.5% or more, even though: (a) for a 1.0% adjustment, the
payments are 1458.93, 1610.30 and 1686.58 starting with payment
numbers 1, 37 and 49, with a last payment of 1678.71; and (b) for a
1.5% adjustment or more, the payments are 1458.93 and 1688.45 starting
with payment numbers 1 and 37, with a last payment of 1681.81.

I do not believe the APR should be the same in all 3 cases. FYI, I
compute IRRs of 6.8779%, 6.9479% and 6.9844% (rounded) for the 3 cases
respectively.

(I will examine the lenderhomepage calculator in a later posting.
It's late now for me.)

Errata...

On Jul 26, 2:06 am, I wrote:
First, it appears that the dinkytown calculator treats the starting
interest rate plus margin as the (lifetime) interest rate cap.

Correction: It treats the current index plus margin as the lifetime
rate cap.

On Jul 25, 8:30 pm, mlion wrote:
Here are the websites that I was using to validate my APR;
http://www.lenderhomepage.com/tools/...age-apr-calcul...
http://www.dinkytown.net/java/Mortga...djustable.html

Aha! I believe I know how those calculators arrive at an "APR" of
7.168% and 7.144% with and without the prepaid interest of 598.96 --
at least in the case of an initial rate of 5.75% for 36 months and
7.25% for the remaining 324 months.

Note that in that case, the total of payments is 599,582.80 and,
dividing by 360, the average payment is 1665.51. (The lenderhomepage
calculator truncates the average to 1665.50.)

Making the simplifying assumption of a constant payment of 1665.51
over the full 360-month term, we do indeed compute an annual rate of
7.168% and 7.144% with and without the prepaid interest of 598.96. In
Excel, they can be computed with the RATE() function as follows:

=12*rate(360, 1665.51, -(2500000-2500-750-598.66))
=12*rate(360, 1665.51, -(2500000-2500-750))

Clearly, that is only an approximation of the APR -- and not a good
one(!). I do not believe it follows the algorithm for computing the
APR in Appendix J of Reg Z -- the US regulation commonly called "Truth
in Lending".

PS: That still does not explain how the dinkytown calculator computes
the same "APR" of 7.144% (without prepaid interest) with different
payment schedules based on varying adjustments. The average payments
differ significantly, resulting in different "APRs" based on the
simplifying paradigm above. For example, for adjustments of 0.5% and
1.0%, the simplified "APR" would be 7.067% and 7.118% respectively. I
think the dinkytown calculator simply has a defect.

Kudos for your effort; that was outstanding! Im interested to see the TIL
position on calculating the APR. Thanks again for your assistance.

"joeu2004" wrote:

On Jul 25, 8:30 pm, mlion wrote:
Here are the websites that I was using to validate my APR;
http://www.lenderhomepage.com/tools/...age-apr-calcul...
http://www.dinkytown.net/java/Mortga...djustable.html

Aha! I believe I know how those calculators arrive at an "APR" of
7.168% and 7.144% with and without the prepaid interest of 598.96 --
at least in the case of an initial rate of 5.75% for 36 months and
7.25% for the remaining 324 months.

Note that in that case, the total of payments is 599,582.80 and,
dividing by 360, the average payment is 1665.51. (The lenderhomepage
calculator truncates the average to 1665.50.)

Making the simplifying assumption of a constant payment of 1665.51
over the full 360-month term, we do indeed compute an annual rate of
7.168% and 7.144% with and without the prepaid interest of 598.96. In
Excel, they can be computed with the RATE() function as follows:

=12*rate(360, 1665.51, -(2500000-2500-750-598.66))
=12*rate(360, 1665.51, -(2500000-2500-750))

Clearly, that is only an approximation of the APR -- and not a good
one(!). I do not believe it follows the algorithm for computing the
APR in Appendix J of Reg Z -- the US regulation commonly called "Truth
in Lending".

PS: That still does not explain how the dinkytown calculator computes
the same "APR" of 7.144% (without prepaid interest) with different
payment schedules based on varying adjustments. The average payments
differ significantly, resulting in different "APRs" based on the
simplifying paradigm above. For example, for adjustments of 0.5% and
1.0%, the simplified "APR" would be 7.067% and 7.118% respectively. I
think the dinkytown calculator simply has a defect.

On Jul 26, 5:52 pm, mlion wrote:
I'm interested to see the TIL position on calculating the APR.

The Truth in Lending method of computing APR is essentially the IRR.
And I might note that it is the Excel function IRR(), not XIRR(). A
(US) loan APR is a nominal rate, not a compounded rate like the APY.

You can find all of Reg Z at
http://www.fdic.gov/regulations/laws...6500-1400.html .
The link to Appendix J takes you to
http://www.fdic.gov/regulations/laws...ndixjtopart226
..

Unfortunately, the presentation and confusing terminology leave much
to be desired, IMHO . But you might recognize the "general equation"
as an NPV formula, where the left side is the NPV of the advances
(e.g. loan amount) and the right side is the NPV of the payments.

Thanks again for your assistance.

My pleasure. Helping others is always an education for myself, too.

On Tuesday, February 8, 2005 at 1:52:20 PM UTC-6, N Harkawat wrote:
=RATE(360,PMT(5.75%/12,360,56000),56000-361.22)*12
gives me 5.81%



"Lisa M" wrote in message
...
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

"JE McGimpsey" wrote:

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.


In article ,
Lisa M wrote:

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


CAN YOU TELL ME HOW TO ADD PMI INTO THIS CALCULATION? FOR EXAMPLE, IF THE PMI IS $43.22/MO FOR 58 MONTHS UNTIL IT REACHES 78% LTV... HOW WOULD I MODIFY THE "RATE" CALCULATION TO CONSIDER THIS INTO THE APR?

This is an old post but I dont seem to find an answer to this question anywhere online:
CAN YOU TELL ME HOW TO ADD PMI INTO THIS CALCULATION? FOR EXAMPLE, IF THE PMI IS $43.22/MO FOR 58 MONTHS UNTIL IT REACHES 78% LTV... HOW WOULD I MODIFY THE "RATE" CALCULATION TO CONSIDER THIS INTO THE APR?