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
XIRR with multiple arrays
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
XIRR with multiple arrays
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: XIRR with multiple arrays
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....
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.
Rudi
If your absence does not affect them, your presence didn't matter.