XIRR with 0 starting value and non contiguous final value

[spreadsheetmaker]

I'm currently trying to get XIRR formula to work. I have a list of Dates/Values with first value being 0. The

Date	Value	Blank	Today's Value	35000
2/28/2017	$ -
2/28/2017	$ (700.00)		XIRR	????
2/28/2017	$ (210.00)
2/28/2017	$ (175.00)
2/28/2017	$ (52.50)
3/15/2017	$ -
3/15/2017	$ (700.00)
3/15/2017	$ (210.00)
3/15/2017	$ (175.00)
3/15/2017	$ (52.50)
3/21/2017	$ -
3/21/2017	$ -
3/23/2017	$ -
3/23/2017	$ -
3/23/2017	$ -
3/23/2017	$ (19,191.98)
3/23/2017	$ (4,798.00)
3/31/2017	$ -
3/31/2017	$ (700.00)
3/31/2017	$ (210.00)
3/31/2017	$ (175.00)
3/31/2017	$ (52.50)
4/13/2017	$ -
4/13/2017	$ (700.00)
4/13/2017	$ (210.00)
4/13/2017	$ (175.00)
4/13/2017	$ (52.50)
4/28/2017	$ -
4/28/2017	$ (700.00)
4/28/2017	$ (210.00)
4/28/2017	$ (175.00)
4/28/2017	$ (52.50)
5/15/2017	$ -
5/15/2017	$ (700.00)
5/15/2017	$ (210.00)
5/15/2017	$ (175.00)
5/15/2017	$ (52.50)
5/31/2017	$ -

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

I have gotten so far: =XIRR(INDEX(B2:B40,MATCH(TRUE,B2:B40<0,0)):B49,INDEX(A2:A40,MATCH(TRUE,B2:B40<0,0)):A49)

My formula takes care of the issue with leading value being 0 but it requires Today's date to be in cell A49 and todays value to be in date B49. If I move those values to a different column, like what I need to do, the entire formula breaks.

 

[Domenic]

First, copy/paste the following code into a regular module (Visual Basic Editor (Alt+F11) >> Insert >> Module)...

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003

    Dim TempUnion() As Variant
    Dim i As Long, Itm As Variant, Ctr As Long
    
    For i = LBound(Arg) To UBound(Arg)
        Arg(i) = Arg(i)
        If IsArray(Arg(i)) Then
            For Each Itm In Arg(i)
                Ctr = Ctr + 1
                ReDim Preserve TempUnion(1 To Ctr) As Variant
                TempUnion(Ctr) = Itm
            Next Itm
        Else
            Ctr = Ctr + 1
            ReDim Preserve TempUnion(1 To Ctr) As Variant
            TempUnion(Ctr) = Arg(i)
        End If
    Next i
    
    ArrayUnion = TempUnion


End Function

Then, let's say that you have the following data...

A1:E5

Dates	Values		Today's Date	Today's Value
1/1/2008	(10,000.00)		4/1/2009	2,750.00
3/1/2008	2,750.00
10/30/2008	3,000.00
2/15/2009	3,250.00

<tbody>
</tbody>

Then, your worksheet formula would be as follows...

=XIRR(ArrayUnion(B2:B5,E2),ArrayUnion(A2:A5,D2),0.1)

Hope this helps!

 

[XOR LX]

Using Domenic's example you could also use:

=XIRR(IF(B2:B6,B2:B5,E2),IF(B2:B6,A2:A5,D2),0.1)

committed as an array formula**.

Note the ranges offset by an additional row (assumed empty) in the first parameter of each IF statement.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[joeu2004]

Assuming that your historical dates are in A2:A39 and values are in B2:B39, and today's date is in A49 and current value is in B49, array-enter the following formula (press ctrl+shift+Enter instead of just Enter):

=XIRR(IF(ROW(B2:B40)=ROW(B2), IF(B2=0, -1E-300, B2), IF(ROW(B2:B40)=ROW(B40), B49, B2:B40)),
IF(ROW(A2:A40)=ROW(A40), A49, A2:A40))

Note that the range B2:B40 is one more row than the range of historical data in order to allow for "appending" the current date and value.

The formula replaces the first historical value (B2) with -1E-300, but only if it is zero. The negative value is small enough to not have a significant effect (if any) on the approximate IRR that XIRR derives. (The formula does assume that the cash flow should be negative.)

For no good reason (read: defect !), XIRR does not allow zero for the first cash flow. By "not allow", I mean: XIRR always returns about 2.98E-09, which seems to be a default or degenerative value; not a valid result, in any case. Using -1E-300 is a kluge to work around this defect.

The formula also replaces the last row of data with the value and date in B49 and A49. If you literally move A49:B49, the formula will adjust automagically. If by "move", you really mean "enter somewhere else", you will need to modify those references in the formula. I presume that is acceptable, because I cannot imagine any "automagic" alternative.