How can I use excel to calculate average annual ROI...

[pittsburgcat]

or IRR. Any help would be appreciated.

 

[Stan Brown]

Sat, 19 May 2007 08:53:49 -0700 from pittsburgcat

or IRR. Any help would be appreciated.

There's an IRR() function -- check the description in Help to see
whether it does what you need.

 

[joeu2004]

RE: How can I use excel to calculate average annual ROI...
or IRR. Any help would be appreciated.

I might not understand your question correctly because the subject line
might be truncated and you did not repeat/rephrase your complete question in
the body of your posting.

First, there are many definitions of ROI. You need to tell us which one you
are interested.

Second, if you are saying that you want to use the IRR as the ROI, beware
that Excel's IRR() function returns a rate per period of the cash flows. For
example, if you have monthly cash flows, IRR() returns a monthly rate.

So you might need to annualize the rate returned by IRR(), and there are two
schools of thought on how to do that. For example, if you compute a monthly
IRR, you would annualize either by simply 12*IRR(...) or by
(1+IRR(...))^12-1. The latter compounds the monthly rate. For several
reasons (too complex to get into here), I prefer the latter.

Also, bear in mind that Excel's IRR() assumes that cash flows occur at a
constant frequency (e.g. monthly). If your cash flows occur with irregular
frequency, you might want to use XIRR(). See the Help description of XIRR()
for how to access the function.

 

[pittsburgcat]

I should have been more specific. I apologize. What I am specifically
trying to determine is: If I purchase a business for $200,000 and sell it 5
years later for $500,000, how can I get excel to calculate the average annual
rate of return on the initial $200,000 investment. Thanks.

 

[joeu2004]

I should have been more specific. I apologize. What I am specifically
trying to determine is: If I purchase a business for $200,000 and sell
it 5 years later for $500,000, how can I get excel to calculate the
average annual rate of return on the initial $200,000 investment.

The specifics simplify things quite a bit. But there still are
different, yet equally valid ways of stating the return on investment.

The simple annual return on investment can be computed by:

=(500000/200000 - 1) / 5

There is nothing wrong with that view of ROI. But many people prefer
a compounded rate of return -- the IRR. But in this case, you do not
need to use Excel's IRR() function.

The compounded annual rate of return can be computed by either the
following equivalent formulas:

=rate(5, 0, -200000, 500000)

=(500000/200000)^(1/5) - 1