Why can't I calculate an excellent rate of return? (IRR)

[ChrisGreaves]

I understand that the IRR is trying to give me a sort-of average of how well I might expect an investment to do.
The attached workbook shows figures for both Canada and India.
The equipment is effectively the same cost in each country.
(It's expressed in Rs in India, but it's just a number, right?)
The equipment uses energy, and the energy savings in India ($64,285) is HUGE, so much so that the energy savings in any year dwarfs the installation cost (-$18,062). (This must happen for a great many equipment replacements, not only in India, but all over the place, right?0

Well, Excel's IRR function expects that in the data array there must be at least one +ve value and at least one -ve value.
But in the case of India, this is not the case, hence the #DIV/0! error.

But why can't I calculate an excellent rate of return?

A thought-experiment:
I replace 20 clerks (annual labor cost $500,000) with a Personal Computer and some software ($10,000).
Why isn't my internal rate of return 5,000% in the one year?
Why can it not be calculated?

[HansV]

IRR is about cash flows. Energy expenditure would be a cash flow, but energy savings isn't.

[ChrisGreaves]

IRR is about cash flows. Energy expenditure would be a cash flow, but energy savings isn't.

2.JPG

Well, aren't we just playing with words?
I have changed the text labels (but not the workbook available for d/l) to look more like a program for some charity I'm organizing.
It will cost me something to set it up ($18,062 in the first year, I suppose advertising, consultants, office equipment), but it's such a good cause that people are throwing money at me (over $64,285 in the first year alone!).

My investment, still, is/was $18,062.
The revenue exceeds my wildest expectations.
Why can't I calculate an IRR?
What is it about IRR that demands a negative value in at least one of the array elements, that is, a 'loss" in at least one of my 20 years?

[HansV]

Making money out of nothing isn infinite IRR, hence the #DIV/0 error. You'd have to put the initial cost (negative) at t=0 and the first return (positive) at t=1 even if they occur in the same year.

[ChrisGreaves]

Making money out of nothing isn infinite IRR, hence the #DIV/0 error. You'd have to put the initial cost (negative) at t=0 and the first return (positive) at t=1 even if they occur in the same year.

Thnaks for the tenacity, Hans.
I see that if I split the first element of the array into two terms ( -18,062 and +64,470) that I remove the $#DIV/0! error, but this technique falsifies the IRR returned for valid values. That is, I'm now looking at a 21-year term rather than a 20-year term.

My stumbling block is trying to understand why there has to be a negative term.
Presumably many startup-businesses make a loss in their first year, but that is not a necessary format for all businesses.

There must be many a business that never makes a loss.
Another example: I get laid-off in January and start a training business. My initial investment is some snail-mailed postcards, paper, and a few laser cartridges. By mid-March I am taking in clients at $500/day, 12 per class, twice a month, and my business never shows a loss.
That is, from the first year onwards, there's never a negative year.

[HansV]

You shouldn't think of the first amount as "profit minus costs" for the first year, but as "initial costs". If you prefer, you can consider the negative value to be for t = -1.

Using only positive values would mean creating money out of nothing.

[ChrisGreaves]

You shouldn't think of the first amount as "profit minus costs" for the first year, but as "initial costs". If you prefer, you can consider the negative value to be for t = -1.

Ahhah!
That makes sense (see what using a bigger hammer can do? )
I was thinking that a 20-year scheme had to have 20 elements of the array.
Setting up an array with the first two elements representing (a) expenditure for first year and (b) revenue for first year makes sense.

Using only positive values would mean creating money out of nothing.

I hate to quibble (especially when seems to lead nowhere) but isn't "creating money out of nothing" what goes on when I stand on a street-corner and "tell you a joke for a dollar"? Or many similar kinds of business.
Right now I ought to be making a small fortune teaching people all about Alt-tab in Windows; also the Ctrl-key and the left- and right-arrow keys. There's money to be made in training with, if one chooses one's topics wisely, with no initial investment.

[HansV]

Consider all the time you spent memorizing jokes - or the money you spent on a PC with Windows so that you could learn about Alt+Tab etc...

[ChrisGreaves]

Consider all the time you spent memorizing jokes

Memorizing?
Memorizing?! .....

I agree that there is always/usually some way to assign a financial cost, but the business model (as distinct from the system model) can elect to ignore these costs.
An example:
A "green" business sets up to absorb wet waste from a township (vegetable scraps, paper etc).
The revenue is defined as the money paid by businesses to truck waste away from their sites (typically $50/ton)
The waste is vermicomposted in a large barn.
By products are worms and worm-castings.
Two "afterthought" businesses are spun off to sell the by-products, but in their business model there is no initial investment.
(Actually, the same revenue model applies in that the original green-business would pay the two afterhtought business to take its waste of its hands - the worms and castings!)

Not to spin this out too long, but I'm happy in splitting the first year into two terms - a negative investment and a 1st-year revenue, so thanks again.

[StuartR]

Chris,

IRR is an accountants tool for comparing the return you get with money you invest. If you are not investing any money then IRR not an appropriate tool to use.

[ChrisGreaves]

If you are not investing any money then IRR not an appropriate tool to use.

Thanks Stuart, I can go along with that.
But what of the case (in my original example) where $1800 was invested in the first year and revenues were $64,000.
There is no doubt that $1800 was invested. That was the cost of the equipment (or mailout or ...).
The revenue of $64,000 swamped the outlay so that in no period was there a negative cash flow.

I think I will have to settle for not-being-an-accountant and hence not using IRR where it doesn't make sense.
My responsibility to the client (who we can safely assume knows more about accounting than do I) lies in informing them that in specific cases IRR does not, and will not work, so that they need to implement a better measure-of-success.

[StuartR]

...But what of the case (in my original example) where $1800 was invested in the first year and revenues were $64,000...

IRR does not have to use 1 year as the time period. It does need to have the investment separate from the return.

You could put the investment in year 0, and then measure the return at the end of 12 months, in year 1.
You could calculate IRR based on monthly or quarterly returns if this is more sensible for your data.

[ChrisGreaves]

You could put the investment in year 0, and then measure the return at the end of 12 months, in year 1.

Now we're getting somewhere!
I invested $1800 in equipment at the start of year 1, a.k.a. the end of year 0.
I took in $64,000 p.a. over 20 years of business.
Excel's IRR tells me that over those 20+1 items the internal rate of return is 3556% (immediately below the last $64,000)
Excel's SUM tells me that my gross revenue was 20*$64,000 i.e. $1,280,000.
My revenue divided by my investment is 71111%.
THAT divided by 20 years yields 35.56%.

The digits are all there. I seem to be out by a factor of 100 (which is no laughing matter), but I am starting to see how it works.
The key lies in assigning the investment as a separate item prior to the 20 revenue items.

Now, thanks to all who have tried in this thread to make me see that.
Sometimes it depends not so much on the mass of the 2x4, but where that mass is applied to this thick skull.

6.JPG

[sdckapr]

My revenue divided by my investment is 71111%.
THAT divided by 20 years yields 35.56%.

The digits are all there. I seem to be out by a factor of 100 (which is no laughing matter), but I am starting to see how it works.

You are off by a factor of 100 since 71111% / 20 does NOT equal 35.56% it equals 3556% which is about the same number that Excel's IRR gives you. [You are off by the negative numbers since you should be dividing by the negative of the investment]. Your "quick method" works out since your IRR is so large that the sum of the inverse terms [1/1+IRR) + 1/(1+IRR)^2 + 1/(1+IRR)^3 + ...+ 1/(1+IRR)^20] is approximately 1/(1+IRR) since the other terms are so small, that the IRR can be solved more or less directly. It is also critical for this rough calc that all the payouts are about equal.

When the IRR is about 1000% (ann Cash flow is 10X investment) the rough calc is about 10% off. With an IRR of about 10000% (ann cash flow is 100x investment) the rough calc is only 1% off.

If you had a more normal investment (payout variable year to year or less than the investment, the rough calc can be off a great deal...

What XL is calculating is (where N = number of periods, in your example 20)
NPV = Sum (from i=0 to N) of [CFi / (1 + IRR)^i] which = 0

So in your example:
NPV = -1800 + 64000/(1+IRR)^1 + 64000/(1+IRR)^2 + 64000/(1+IRR)^3 + ...+ 64000/(1+IRR)^20 = 0
This can be rearranged into:
(1/(1+IRR)^1 + 1/(1+IRR)^2 + 1/(1+IRR)^3 + ...+ 1/(1+IRR)^20) = 1800 / 64000

Excel then "uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can't find a result that works after 20 tries, the #NUM! error value is returned. " Also "In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent)."

As mentioned earlier, in your case, the IRR is so large that the problems reduces to:
1/(1+IRR) ~ 1800 / 64000
1+ IRR ~ 64000 / 1800
IRR ~ 64000 / 1800 -1 = 34.56 = 3456% which is about 3% lower than the actual 35.56% due to the dropping of those terms.

Steve

PS. I thought of a better approximation of that sum that works that is (wioth larger periods) 3% off with IRRs as low 20% and gets to less than 1% with IRRs of >28%. The sum:
1/(1+IRR)^1 + 1/(1+IRR)^2 + 1/(1+IRR)^3 + ...+ 1/(1+IRR)^k
can be shown to be equal to (I won't go thru the math)
1/IRR - 1/(IRR*(1+IRR)^k) which is approx 1/IRR

So using this approx, then:
(1/(1+IRR)^1 + 1/(1+IRR)^2 + 1/(1+IRR)^3 + ...+ 1/(1+IRR)^20) = 1800 / 64000

is approximated by:
1/IRR = 1800 / 64000
IRR = 64000 / 1800 = 35.56 = 3556%

But note this will not work well if the Cash flows are not the same (or about the same) since the SUM of them is weighted by a year factor, with earlier years having much more importance than later years. Also if the number of periods is small the approximation is not as good...