A
andrew_m
Hi,
I'm trying to use Excel to keep track of the running internal rate of
return (IRR) for a stock portfolio. I have my spreadsheet set up
something like this:
Date | Cash In | Current Value | IRR
1/1/04 | 100 | 100 | 0 %
1/1/05 | 200 | 320 | 19.94 %
1/1/06 | 100 | 475 | 17.93 %
1/1/07 | 0 | 450 | 6.02 %
Each IRR cell can be generated from all of the preceding "date" and
"cash in" values, along with the "current value" cell in the same
row. Right now, I manually calculate the IRR by generating temporary
sheets like the following:
Date | Flow
1/1/04 | 100
1/1/05 | 200
1/1/06 | 100
1/1/07 | 0
1/1/07 | -450
Then use the XIRR() function to generate the value:
=XIRR(B2:B6,A2:A6)
This gives me the IRR value for 1/1/07 (i.e. cell D5).
Unfortunately, I haven't found a way to get Excel to generate the IRR
column automatically. I've tried playing around with the XIRR()
function, but it seems to insist that all the data be in two columns,
where positive values represent inflows and negative values outflows.
In contrast, I need the function to pull data from three columns, as
shown above.
I've tried playing around with expressions like this (for the cell D5,
which contains 6.02% in the example above):
=XIRR(($B$2:B5, -1 * C5), ($A$2:A5, A5))
.... but unfortunately, this doesn't work.
Does anyone know how to have Excel fill in the IRRs in the first chart
automatically, without having to manually generate the second?
More generally, is it possible to glue together two range expressions
into a single range for use with a function expecting just one range?
Sorry if this doesn't make too much sense -- I have only basic Excel
experience.
-- Andrew
I'm trying to use Excel to keep track of the running internal rate of
return (IRR) for a stock portfolio. I have my spreadsheet set up
something like this:
Date | Cash In | Current Value | IRR
1/1/04 | 100 | 100 | 0 %
1/1/05 | 200 | 320 | 19.94 %
1/1/06 | 100 | 475 | 17.93 %
1/1/07 | 0 | 450 | 6.02 %
Each IRR cell can be generated from all of the preceding "date" and
"cash in" values, along with the "current value" cell in the same
row. Right now, I manually calculate the IRR by generating temporary
sheets like the following:
Date | Flow
1/1/04 | 100
1/1/05 | 200
1/1/06 | 100
1/1/07 | 0
1/1/07 | -450
Then use the XIRR() function to generate the value:
=XIRR(B2:B6,A2:A6)
This gives me the IRR value for 1/1/07 (i.e. cell D5).
Unfortunately, I haven't found a way to get Excel to generate the IRR
column automatically. I've tried playing around with the XIRR()
function, but it seems to insist that all the data be in two columns,
where positive values represent inflows and negative values outflows.
In contrast, I need the function to pull data from three columns, as
shown above.
I've tried playing around with expressions like this (for the cell D5,
which contains 6.02% in the example above):
=XIRR(($B$2:B5, -1 * C5), ($A$2:A5, A5))
.... but unfortunately, this doesn't work.
Does anyone know how to have Excel fill in the IRRs in the first chart
automatically, without having to manually generate the second?
More generally, is it possible to glue together two range expressions
into a single range for use with a function expecting just one range?
Sorry if this doesn't make too much sense -- I have only basic Excel
experience.
-- Andrew