XIRR formula in non-normal struction

[Guest]

How do you enter the XIRR formula when the numbers and dates are not in the
normal column format structure.

For example, the cash flow numbers are in cells A8,G8,K8 and the dates are
in cells B8,H8,L8.

Thanks in advance for the help

 

[Mark Lincoln]

You could set some helper cells (in a column structure) to the values
of the cells in question, then base the XIRR function on the new
cells. For example, set R1:R3 to the values A8,G8,K8 and S1:S3 to the
values B8,H8,L8.

Mark Lincoln

 

[Guest]

I unfortunately don’t have the luxury to do this in this worksheet. I
over-simplified my example. My worksheet actually contains over 1500 rows
with each row containing a separate record. Each record contains 226 columns
with the XIRR being one of them. I would like to copy this formula for each
of the 1500 records.

 

[Ron Rosenfeld]

How do you enter the XIRR formula when the numbers and dates are not in the
normal column format structure.

For example, the cash flow numbers are in cells A8,G8,K8 and the dates are
in cells B8,H8,L8.

Thanks in advance for the help

You can do this with a UDF.

Here is one written by Harlan Grove. See the comments within the body for some
of the specific requirements.

To enter this, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

In the argument list, v is the named range of values; d is the named range of
dates, and g is the optional guess.

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

 

[joeu2004]

How do you enter the XIRR formula when the numbers and dates are not in the
normal column format structure.

For example, the cash flow numbers are in cells A8,G8,K8 and the dates are
in cells B8,H8,L8.

Would the following form satisfy your requirements:

=xirr((A8,G8,K8),(B8,H8,L8))

XIRR() seems to be one of the few functions that works with the union
reference operator. See the "About calculation operators" help page.

 

[Mark Lincoln]

That returns the #VALUE! error for me. It looks like the code posted
by Ron is the best answer.

Mark Lincoln

 

[Mark Lincoln]

That returns the #VALUE! error for me. It looks like the code posted
by Ron is the best answer.

Mark Lincoln