# XIRR with 0 starting value and non contiguous final value

##### New Member
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.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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

 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...

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

Hope this helps!

Last edited:
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:
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.

Replies
10
Views
317
Replies
4
Views
427
Replies
1
Views
572
Replies
4
Views
697
Replies
2
Views
450

1,216,084
Messages
6,128,729
Members
449,465
Latest member
TAKLAM

### 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?

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