CODEWITHSUNDEEP
excelexcel-2007excel-formula
user1828945
user1828945

Reputation: 41

How can I modify XIRR to measure different numbers of periods based on where a certain value occurs?

I'm trying to create an XIRR function that will adjust how many periods it is measuring based on the address of a specific value. For example, if the value occurs in month 48, I want the XIRR function to measure 48 columns of data, but if the value occurs in month 17,

I want XIRR to only measure 17 columns. I can get the address of the value and the corresponding dates using=CELL("address", MATCH(... etc., but I can't figure out how / if I can concatenate the XIRR inputs. Initially, I hoped I could do something like:

=XIRR(A1:Cell("address", [value_reference]), B1:Cell("Address", [date_reference]))

Can anyone help me with this? Using Excel 2007

Upvotes: 4

Views: 4282

Answers (1)

brettdj
brettdj

Reputation: 55692

You can use OFFSET to make a dynamic range

If for example

  • you had values in A1:A50
  • dates in B1:B50
  • a specific value in D2 (that occurs in your values in column A)

Then this formula

=XIRR(OFFSET(A1,0,0,MATCH(D2,$A$1:$A$50,0),1),OFFSET($B$1,0,0,MATCH(D2,$A$1:$A$50,0),1))

will do the dynamic range matching

Upvotes: 2

Related Questions