ExcelBanter - Any Way To Specify XIRR Values and Dates Without Using Array?

"W" wrote:
Let's say I have a schedule of cash flows and dates that I want to make
calculations of the internal rate of return. But I don't have the data
organized in a way that lets me specify the cell range of the values or
the
cell range of the dates. Instead I need to explicitly name individual
cells in the series. Is there a way for me to do this in the XIRR
function? If not, what is a better alternative to XIRR that lets me name
discrete cells for values and dates in the time series?

I think you want syntax like the following:

=myxirr((E1:G1,I3:I5,K7,G8),(E11:E12,G14:H14,C17,E 19,J16,K12))

XIRR does not support that syntax. But the following VBA function does.

To enter the VBA function, right-click on the worksheet tab and click on
View Code, which opens the VBA window. In the VBA window, click on Insert,
then Module. Copy the following text and paste into the module editing
pane.

Function myxirr(v, d, Optional g As Double = 0.1)
Dim nv As Long, i As Long, a, c
nv = v.Count
ReDim vval(1 To nv) As Double, dval(1 To nv) As Double
i = 0
For Each a In Split(v.Address, ","): For Each c In Range(a)
i = i + 1
vval(i) = c.Value
Next c, a
i = 0
For Each a In Split(d.Address, ","): For Each c In Range(a)
i = i + 1
dval(i) = c.Value
Next c, a
myxirr = Application.Xirr(vval, dval, g)
End Function

The function assumes that v.Count = d.Count.

Using Application.Xirr instead of WorksheetFunction.Xirr allows any Xirr
error (typically #NUM) to be returned instead of a #VALUE error.

If you use Excel 2003 or earlier, you will need to access the Xirr function
differently. Let us know if need help with that.