IRR Calculation

[Guest]

My TI BA Real Estate calculator comes up with 15.6556% IRR for the following
scenario

Present Value: -$40,000
Future Value: $72,000
Term: 5 years
Cash Flow: $167.00/month ($10,000 over life of investment)

My formula was this: =IRR(e68:e70)

e68 represented -40,000
e70 represented 10,000 (cashflow over life of the loan)

Any ideas on what i am doing wrong? I get the

 

[JE McGimpsey]

IRR requires an entry for each period. So:

A1: -40000
A2: 167
A3: =A$2
A4: =A$2
....
A60: =A$2
A61: =72000+A$2

Since the returns are monthly, estimate a monthly rate and multiply by
12 to get the annual rate:

=IRR(A1:A61,10%/12)*12 ===> 15.6556%

Note that you're overestimating the monthly returns (e.g., 10000/60 =
166.67). If you use 10000/60, the annual rate is 15.6479%.

 

[joeu2004]

My TI BA Real Estate calculator comes up with 15.6556% IRR
for the following scenario
Present Value: -$40,000
Future Value: $72,000
Term: 5 years
Cash Flow: $167.00/month ($10,000 over life of investment)
My formula was this: =IRR(e68:e70)

You misusing the Excel IRR() function. But you do not need IRR() for
this problem. The following seems to match the way that your
calculator computes the annualized IRR:

=12*rate(5*12, 167, -40000, 72000)

There has been a lot of discussion in these newsgroups about whether
the monthly IRR (RATE) should be annualized simply by multiplying by
12, or whether it should be compounded, as follows:

=(1 + rate(5*12, 167, -40000, 72000))^12 - 1

I advocate compounding myself. But for years, I have been explaining
that that is not always the practice used in the real world. I think
your example with the TI calculator proves my point,.