XIRR and Non Contiguous Cells

[John Taylor]

Hello,

I've been attempting to use XIRR with non contiguous cells, in Excel 97, but
without success.

My data (investment fund Unit Prices) is arranged in columns (dates in A,
values in B, sorted in ascending order) and I'm wanting to calculate
annualized returns over various periods.

My lack of success lead to a very long search of the newsgroup archives, as
well as other sources. I finally found out that it's not possible to use
XIRR 'directly' where the cells are non contiguous, but there were a couple
of workarounds suggested. One, a UDF from Harland Grove (submitted by Ron
Rosenfeld), and the second a formula using Offset, from Domenic. I also
came across the Function XXIRR, submitted by Myrna Larson.

However, I still have a couple of questions related to this and would
appreciate any feedback that people can offer.

1. The UDF works fine but has the drawback that the non-contiguous ranges
must be named. This is quite laborious when there are about 2000 data sets
and one wants to do many XIRR comparisons. Also, at least one of the values
for XIRR must be a negative and therefore the UDF doesn't work on my
original data as they are all positive values. Is there any way that the
UDF can be modified to change one value (the first, corresponding to the
earliest date, presumably) to a negative?

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

2. Another query I found posted, but for which I didn't see any reply, also
applies to my situation. Any help would be most appreciated. Namely:

I use the XIRR function regularly but would like to input relative
references instead of always using arrays or constants (as in the Help
example) but if I try this I get an error. Specifically I would like
to do something like this: xirr({-b1,b2},{a1,a2}). This way I can
calculate the period return from a list of positive values (e.g.
balances as of a certain date).

The '-b1' causes the error, with dates in column A and values in column B.

3. From a thread titled "XIRR in VBA" - by Myrna Larson

Option Explicit


Const MaxChange As Double = 0.00000001
Const MaxTries As Long = 100


Enum ArrayDims '07/26/2003
NotArray = 0
SingleDim = 1
Horizontal = 2
Vertical = 3
Rectangular = 0
End Enum

etc. etc.

When I paste the code into the VBE the lines "Enum ArrayDims" and "End Enum"
are both highlighted in red.

Does this mean that "Enum" is not available in Excel 97?

If it's not, is there any alternative that would allow use of this function
in Excel 97?

Apologies for the length of this post, but I thought it was best to pose all
the questions at the same time since they're related.

Regards,

John

 

[Fred Smith]

I can't help you with your two questions, but thought you might be able to
simplify things so as to make the use of XIRR unnecessary.

If you have unit prices over time, and want to calculate a return for a
particular period, you don't need the intermediate unit prices. The total return
is simply (endprice - startprice) / startprice. You can then annualize this
return using the Rate function.

Does this help?

 

[John Taylor]

Fred,

Thanks for the suggestion. It certainly sounds like it may be the way to
go, and I'll give it some further consideration and see how it fits with my
requirements.

Regards,

John