XIRR formula in non-normal struction
 

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

XIRR formula in non-normal struction
 

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

On Oct 10, 3:23 pm, Lary wrote:
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

XIRR formula in non-normal struction
 

I unfortunately dont 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.

XIRR formula in non-normal struction
 

On Wed, 10 Oct 2007 12:23:02 -0700, Lary
wrote:

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

XIRR formula in non-normal struction
 

On Oct 10, 12:23 pm, Lary wrote:
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.

XIRR formula in non-normal struction
 

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

Mark Lincoln

On Oct 10, 7:12 pm, joeu2004 wrote:
On Oct 10, 12:23 pm, Lary wrote:

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.

XIRR formula in non-normal struction
 

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

Mark Lincoln

On Oct 10, 7:12 pm, joeu2004 wrote:
On Oct 10, 12:23 pm, Lary wrote:

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.