XIRR question

[Carmen]

I have the following numbers/dates in an excel sheet A13


1/1/2007 1/1/2008 1/1/2009 1/1/2010
$ (10) $ 1
$ 2 $ 20


I want to run an XIRR on the entire stream, something to the effect of XIRR(A2:B2&C33,A11)

This will not work. I could create a 4th row that sums rows 2 and 3 and do XIRR(A44,A11) but I have literally 1000 of these and for reasons too long to explain here, do not want to/can not do that way.

Any way to run on XIRR on A13 without creating a new summed row?

Thanks

 

[joeu2004]

I have the following numbers/dates in an excel sheet A13
1/1/2007 1/1/2008 1/1/2009 1/1/2010
$ (10) $ 1
$ 2 $ 20
I want to run an XIRR on the entire stream, something to the effect of XIRR(A2:B2&C33,A11)
[....]
Any way to run on XIRR on A13 without creating a new summed row?

If all of your cash flows are in a contiguous range ordered in time
left-to-right top-to-bottom, as in your example, perhaps you could do
something like:

=xirr(A2:C3, A11)

The dates could also be in a rectangular range as long as their order
corresponds to the cash flows left-to-right top-to-bottom.

PS: I wonder if you truly need, or even want, XIRR instead of IRR. Do
you want all the years' cash flows to be considered equally spaced
(which IRR assumes), or do you truly want to differentitate between
365-day and 366-day years (which XIRR will do)?

 

[Carmen]

Joe, thanks for the reply, but I guess I made my example too simple relative
to the real problem. The cash flows in my real file are not on adjacent
lines, they are on lines separated by up to 20 lines, all with data on them
so your solution would not work. Also, the dates are random, not all at
first of years as I made the example so I do need XIRR.

Any other thoughts?

--
CARMEN HOVENDICK

I have the following numbers/dates in an excel sheet A13
1/1/2007 1/1/2008 1/1/2009 1/1/2010
$ (10) $ 1
$ 2 $ 20
I want to run an XIRR on the entire stream, something to the effect of
XIRR(A2:B2&C33,A11)
[....]
Any way to run on XIRR on A13 without creating a new summed row?

If all of your cash flows are in a contiguous range ordered in time
left-to-right top-to-bottom, as in your example, perhaps you could do
something like:

=xirr(A2:C3, A11)

The dates could also be in a rectangular range as long as their order
corresponds to the cash flows left-to-right top-to-bottom.

PS: I wonder if you truly need, or even want, XIRR instead of IRR. Do
you want all the years' cash flows to be considered equally spaced
(which IRR assumes), or do you truly want to differentitate between
365-day and 366-day years (which XIRR will do)?

 

[Ron Rosenfeld]

I have the following numbers/dates in an excel sheet A13


1/1/2007 1/1/2008 1/1/2009 1/1/2010
$ (10) $ 1
$ 2 $ 20


I want to run an XIRR on the entire stream, something to the effect of XIRR(A2:B2&C33,A11)

This will not work. I could create a 4th row that sums rows 2 and 3 and do XIRR(A44,A11) but I have literally 1000 of these and for reasons too long to explain here, do not want to/can not do that way.

Any way to run on XIRR on A13 without creating a new summed row?

Thanks

I'm not sure exactly what you mean, but XIRR requires that the dates be in a
contiguous range; and that the values be in a contiguous range. (The values
don't need to be next to the dates, however).

It is also a requirement that there be one date for each value (and one value
for each date).

So you need to set up an area on your worksheet where you have a layout that
meets those requirements.

Alternatively, you could NAME the non-contiguous range of dates; and the range
of values, and use this VBA user defined function supplied by Harlan Grove:

==========================================
Option Explicit

Function myxirr( _
v As Variant, _
d As Variant, _
Optional g As Double = 0 _
) As Variant
'-------------------------------------------------------
'this udf requires an explicit reference to APTVBAEN.XLA
'if v and/or d represent non-contiguous ranges, they should be NAME'd
'-------------------------------------------------------
Dim vv As Variant, dd As Variant, X As Variant, i As Long

If TypeOf v Is Range Then
ReDim vv(1 To v.Cells.Count)
i = 0
For Each X In v
i = i + 1
vv(i) = X.Value
Next X
Else
vv = v
End If

If TypeOf d Is Range Then
ReDim dd(1 To d.Cells.Count)
i = 0
For Each X In d
i = i + 1
dd(i) = X.Value
Next X
Else
dd = d
End If

myxirr = IIf(g <> 0, xirr(vv, dd, g), xirr(vv, dd))
End Function
=========================================
--ron

 

[Carmen]

Ron, thanks for Harlen's solution, but it is way over my head and the people
I would be sending the file to (to understand).

 

[Ron Rosenfeld]

Ron, thanks for Harlen's solution, but it is way over my head and the people
I would be sending the file to (to understand).

Then you will have to set up a section where you have the data in a format that
XIRR can use.

Best,
--ron