IRR with Missing Cash Flows???

Given the following assumptions, calculate IRR within the cell below (using only one formula within the cell).

Cash invested time 0: 1000
Cash at end of year 3: 1159

IRR Formula: ?

No..... What I did was to the right of the question I did -1000,0,0,1159. Then I whited out the numbers. Then i Irr(...) to get the answer. I'm following the directions but maybe not the spirit. IS there a different way to do this?

Originally Posted by $J$L$G

Given the following assumptions, calculate IRR within the cell below (using only one formula within the cell).
Cash invested time 0: 1000
Cash at end of year 3: 1159

When you have only an initial investment and final balance, the IRR is the same as the CAGR. Based on your Excel file:

=(E5/E4)^(1/3) - 1

If you want to use Excel IRR, note the cash flow must signed correctly: negative initial investment and positive final balance, or vice versa. Thus, in your example, E4 should be -1000. But then you would need to make a lot of other changes.

Alternatively, use the following normally-entered formulam(just press Enter):

=IRR(CHOOSE({1,2,3,4},-E4,0,0,E5))

IMHO, the CAGR formula is better because the time period (3) can easily be a "variable"; that is, a value in a cell.