I know IRR works with non-continguous values, but I can't
get XIRR to work. Here's what I'm doing:
=XIRR((B1,B5), (A1,A5), 0.07)
A B
1/21/2005 -750
-
-
-
12/31/2005 795
I get #VALUE! IRR works on the same cells, minus
the date arguments of course.
Bob
XIRR will not work with non-contiguous values for dates or values (unless there
are only blanks between them).
However, Harlan Grove wrote a UDF which will, if you NAME the non-contiguous
ranges.
<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.
Read the comments in the UDF.
You can then use it as =myxirr(values, dates, 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
