All geek questions in one place

How to change XIRR to measure different numbers of periods based on where a particular value occurs?

I am trying to create an XIRR function that will adjust the number of periods it measures based on the address of a specific value. For example, if a value occurs in month 48, I want the XIRR function XIRR measure 48 columns of data, but if a value occurs in month 17,

I want XIRR to XIRR only 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 combine the XIRR inputs. Initially, I was hoping I could do sort of:

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

Can anyone help me with this? Using Excel 2007

+4
source share
1 answer

You can use OFFSET to create a dynamic range.

If, for example,

  • you had values ​​in A1:A50
  • dates in B1:B50
  • a specific value in D2 (which 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 perform dynamic range matching

+2
source

Source: https://habr.com/ru/post/1446393/

More articles:


All Articles