Search code examples
excelarray-formulasxirr

Excel XIRR information without additional columns

I have an Excel sheet such that each row contains

  • A date
  • The total amount of money used to purchase stock up to that date
  • The current value of the stock on that date

What is the most efficient way to use xirr to compute the return on each row as a fourth column?

My goal is to do this in a manner such that I do not need to introduce a dummy column that computes differences from the second column.

Solution

  • If the header information is in row 1, and columns A--C respectively correspond to those in the question, this seems to work for column D, beginning with row 3:

    =XIRR(ArrayFormula(IF(1-FREQUENCY(9^9,A$2:A10),ArrayFormula((B$2:B10)-if(isnumber(B$1:B9),B$1:B9,0)),-C10)),ArrayFormula(IF(1-FREQUENCY(9^9,A$2:A10),A$2:A10,A10)))