MiniMe7

It sounds like IRR is not the right indicator. You sound like you are trying to get a time weighted return.


Are the Cash Flows in the same units as the NAV? If not, we need to know how many units of NAV you have at each time.


So column A is date
Column B is CF
Column C is (Nav+CF)



Now you have to figure out what you really want to try to measure:
Time weighted return would imply this:

Column D = (Column C) - CF = NAV before CF
Column E = %Change in column D since the last entry.
Column F = (1+(column E))
Column G = (Product of all cells in Column F up to this point in time) - 1
Column H = (Column G)^(1/(# of years since start))



True IRR since inception would best be done with :

OK... I see your problem... you need an array of cells that has all the cashflows from 1 to (n-1) and has the NAV value in cell N. I've looked for a cell reference function that would allow you to concatenate a vector of cells from 1 to N-1 with an additional cell at the end, but can't find it.

You could try to write a VBA macro that does the following. It takes dates, an array of CFS and NAVS, and a guess like XIRR. Inside the macro it creates an array MYDATA equal to the array of CFS. Then it takes MYDATA(Length(NAVS)) = NAVS(Length(NAVS)).

Then call XIRR with Dates, MYDATA, and the guess.


Either that, or you have to find a function that allows you to concatenate row [B$2:B$(n-1)] and cell C$(n), where n is the number of periods to date.

(Note that my n might be off by one, depending on whether you've made room for column headers and such).