XIRR with 0 starting value and non contiguous final value

spreadsheetmaker

New Member
Joined
Nov 2, 2018
Messages
5
I'm currently trying to get XIRR formula to work. I have a list of Dates/Values with first value being 0. The

DateValueBlankToday's Value35000
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,300
First, copy/paste the following code into a regular module (Visual Basic Editor (Alt+F11) >> Insert >> Module)...

Code:
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

DatesValuesToday's DateToday'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...

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

Hope this helps!
 
Last edited:

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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).
 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,811
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,984
Messages
5,526,060
Members
409,682
Latest member
HisHailo

This Week's Hot Topics

Top