combination of XIRR. Indirect and match

[ASZ_Excel]

Hello Everyone,

I have been working on a spreadsheet created by someone else, however I cannot figure below formula. Can anyone help explain it? Thank you.


=XIRR(INDIRECT("R"&MATCH(B4,A:A,0)&":R"&MATCH(K4,A:A,0)),INDIRECT("A"&MATCH(B4,A:A,0)&":A"&MATCH(K4,A:A,0)))

 

[Gerald Higgins]

Hi, welcome to the board.

Which bit are you struggling with ?

The XIRR function is one that I have not used myself, but looking at Excel help, it "Returns the internal rate of return for a schedule of cash flows".
Take a look at Excel's built in help facility if you need more information about how it works.

The function requires at least two arguments, and the rest of your formula is basically working out two range references to use for those two arguments.

Using Excel's built in Formula / Evaluate tool might also help you here.

 

[Eric W]

Welcome to the Board.

XIRR returns the internal rate of return for a schedule of cash flows.

=XIRR(R10:R100,A10:A100)

where R10:R100 is the range of values, and A10:A100 is the range of dates.

Based on your formula, column A must be the dates. B4 has the starting date, and K4 has the ending date. So MATCH(B4,A:A,0) finds the row in column A where you find the starting date, and MATCH(K4,A:A,0) finds the row with the ending date. Then by using the & operator, the formula creates a text representation of the range you want, and the INDIRECT turns that into an actual range. Same thing on the second part, but with the date range.

So in summary, the formula finds the Internal rate of return using B4 as the start date, K4 as the end date, and the values in columns R and A.

 

[ASZ_Excel]

Thank you, Eric and Gerald.

I know how to use XIRR, but got confused by Indirect and Match combination. I never tried to combine the two together in the past.

(INDIRECT("R"& MATCH(B4,A:A,0)&":R"&MATCH(K4,A:A,0)),

Welcome to the Board.

XIRR returns the internal rate of return for a schedule of cash flows.

=XIRR(R10:R100,A10:A100)

where R10:R100 is the range of values, and A10:A100 is the range of dates.

Based on your formula, column A must be the dates. B4 has the starting date, and K4 has the ending date. So MATCH(B4,A:A,0) finds the row in column A where you find the starting date, and MATCH(K4,A:A,0) finds the row with the ending date. Then by using the & operator, the formula creates a text representation of the range you want, and the INDIRECT turns that into an actual range. Same thing on the second part, but with the date range.

So in summary, the formula finds the Internal rate of return using B4 as the start date, K4 as the end date, and the values in columns R and A.

 

[Gerald Higgins]

The INDIRECT function is basically a way of building cell or range references from multiple separate components.

The MATCH function is used to identify the position of a particular value within a row or column.

Do you need more help with this ?

 

[ASZ_Excel]

Thank you, Eric and Gerald.

I know how to use XIRR, but got confused by Indirect and Match combination. I never tried to combine the two together in the past.

(INDIRECT("R"& MATCH(B4,A:A,0)&":R"&MATCH(K4,A:A,0)),

I got it! Many thanks for your help