Internal Rate of Return

[tom]

I have a very specific question regarding a real estate investment
analysis using the financial function, Internal Rate of Return (IRR).
I have calculated my series of cash flows in months, starting with an
initial cash outlay in Month 0 followed by negative cash flows in
Months 1 thru 11, and then positive cash flows in Months 12 thru 21.

Since it's monthly, do I need to multiply the calculated IRR by 12 (to
annualize)? I was told as long as the intervals are consistent
(monthly), that's not necessary. But the outcome seems way too low.
(ie I put in $6.2 today and end up with $13.3 in 21 months - it's
saying the IRR is 4.59%)

Anyone with some insights?

 

[JE McGimpsey]

I have a very specific question regarding a real estate investment
analysis using the financial function, Internal Rate of Return (IRR).
I have calculated my series of cash flows in months, starting with an
initial cash outlay in Month 0 followed by negative cash flows in
Months 1 thru 11, and then positive cash flows in Months 12 thru 21.

Since it's monthly, do I need to multiply the calculated IRR by 12 (to
annualize)? I was told as long as the intervals are consistent
(monthly), that's not necessary. But the outcome seems way too low.
(ie I put in $6.2 today and end up with $13.3 in 21 months - it's
saying the IRR is 4.59%)

Anyone with some insights?

If your cash flows are in months, then IRR will return a monthly rate
(after all, XL can't know whether you mean years, months, weeks or days).

Yes, multiply the monthly rate by 12 to get the APR.

You can do a rough check on yourself by figuring that, to turn $6.2 into
$13.3 in 22 months (not 21, since your outlay occurs in month 0), you
could calculate an annual rate for zero interim cash flows using

=RATE(22/12, 0, -6.2, 13.3) ===> 51.63%

which is roughly on a par with 4.59% * 12 = 55.08%. Of course, depending
on your cash flows, you might get some larger differences, but it should
provide some assurance that you're in the right ballpark.

 

[square_cube]

"> Yes, multiply the monthly rate by 12 to get the APR."


You need to be careful here... this gives you the nominal annual rate
of return. The effective annual rate (the annual interest rate that
would yield the same result if interest was compounded annually rather
than monthly) must be calculated

R = ((1+r)^12) - 1

where R is the anual rate and r the monthly rate, both expressed as
decimals

 

[JE McGimpsey]

"> Yes, multiply the monthly rate by 12 to get the APR."


You need to be careful here... this gives you the nominal annual rate
of return. The effective annual rate (the annual interest rate that
would yield the same result if interest was compounded annually rather
than monthly) must be calculated

R = ((1+r)^12) - 1

where R is the anual rate and r the monthly rate, both expressed as
decimals[/QUOTE]

Yup, "annual rate" is not a meaningful term - Annual Percentage Rate
(APR) is the mandated standard expression of effective interest rate for
loans in the US.

As you point out, that differs from the Effective Annual Rate (EAR)
which is the simple rate paid on a stable balance.

And neither necessarily represent the OP's situation with variable cash
flows very well.

Note that EAR can also be calculated using the EFFECTIVE() function in
the Analysis Toolpak add-in. I'd stick to built-in worksheet functions,
in general, but the ATP functions have been incorporated in WinXL07,
and, I expect, will be in MacXL08.

 

[peterthebag]

Hi,
Taking the periodic (monthly ) rate * 12 gives what is sometimes
called the simple annual rate. (rs)

Calculating ((1+rs)^12 ) - 1 gives the effective annual rate.

You can find more details on APR at: http://en.wikipedia.org/wiki/Annual_percentage_rate

Whether the APR = the effective annual rate depends upon how you
calculate it.

If using IRR please do note that IRR can give misleading results when
we consider mutually exclusive projects, where it may lead to
incorrect decisions; and unconventional cash flows, where it may
report multiple internal rates of return, or you may find yourself
unable to calculate a rate of return at all!
cheers,

Peter