CODEWITHSUNDEEP
excelarray-formulasxirr
BaronFiner
BaronFiner

Reputation: 154

Excel XIRR information without additional columns

I have an Excel sheet such that each row contains

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.

Upvotes: -1

Views: 171

Answers (1)

BaronFiner
BaronFiner

Reputation: 154

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)))

Upvotes: 0

Related Questions