XIRR with multiple arrays

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

XIRR with multiple arrays

Post by Rudi »

XIRR uses an array of values, and an array of dates and an optional guess %.
=XIRR(Data,Dates,Guess)
=XIRR(A2:A10,B2:B10,0.1)

What if I have two (or more) arrays that need to go into the Data argument?
Can one "join" arrays?

In searching I came across this formula, but I cannot modify it for my needs.

=XIRR({-1,1}*N(OFFSET(C29,{0,-16},{0,3})),N(OFFSET(C4,{0,0},{0,3})),0.7)
with values in C29 and F13 and dates in C4 and F4

Any ideas?

TX
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: XIRR with multiple arrays

Post by Rudi »

My friend (who asked the question) figured it out...

A sample of his formula - entered as an ARRAY (CTRL+SHIFT+Enter)
The blue part is the two individual arrays joined.
$A$10:A15 is the column of dates

{=XIRR(($K$10:K15)-($I$10:I15)*(--($A$10:A15=A15)),$A$10:A15)}

Autofill down the column....
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.