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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,224
Office Version
  1. 365
Platform
  1. Windows
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

Banned user
Joined
Mar 2, 2014
Messages
3,080
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,015
Messages
5,834,939
Members
430,327
Latest member
Mr_Himalayan778

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top