XIRR function - passing values to

  • Thread starter Thread starter Tom Zimoski
  • Start date Start date
T

Tom Zimoski

I'd like to calculate the internal rate of return at every date for
which additional money is invested. There are a few lines from the
tab-delimited file below.

I'd like to feed the XIRR function a range of dates like <a2:a5,a5>
and a range of values like <f2:f5,h5> but I can't get it to accept
those arguments.

I'm looking for alternatives. Thanks for your attention.

Tom

Date Transaction Shares Transacted Share Price Amount Shares
Balance Balance
3/19/2002 Exchange To 966.184 $10.35 "$10,000.00
" "(10,000.00)" 966.184 "$10,000.00 "
4/8/2002 Buy 480.769 $10.40 "$5,000.00
" "(5,000.00)" "1,448.86" "$15,068.13 "
9/9/2002 Exchange To "1,122.54" $10.69 "$12,000.00
" "(12,000.00)" "2,605.36" "$27,851.27 "
9/19/2002 Buy 129.07 $10.75 "$1,387.50
" "(1,387.50)" "2,734.43" "$29,395.09 "
11/29/2002 Buy 34.659 $10.69 $370.50 (370.50) "2,790.14" "$29,826.64
"
 
a range of dates like <a2:a5,a5>
and a range of values like <f2:f5,h5> but I can't get it to accept
those arguments.
Click to expand...

What does "can't get it to accept those arguments" mean?

What kind of a reference is <a2:a5,a5> and <f2:f5,h5> in terms of the XIRR
function which calls for =XIRR(values, dates, guess)?


Some suggestions.

The array of values should be contiguous.
The array of dates should be contiguous.

Both the values must be numeric values and the dates must be Excel dates. In
your posting you show quotation marks which will convert these entries into
strings. XIRR does not generally accept strings for either values or dates.

If you post some detail as to what exactly is the problem, and what equations
you are trying to use, more focused advice may be possible.


--ron
 
The array of values should be contiguous.
The array of dates should be contiguous.
Click to expand...

The SUM function doesn't require that it's arguments be contiguous and
I don't see why the XIRR function should either, but if it does, it
does. As long as the data in the cells specified is in an acceptable
format and the number of dates matches the number of values why
shouldn't the XIRR function work with that data? But again, if it
doesn't, it doesn't.

Thanks for taking the time to consider my question.

Tom

I saved my spreadsheet as a tab-delimited file but didn't see a way to
attach it to my post, so I tried to copy and paste. But I see that
some line breaks got inserted into my sample data - a failed
experiment.
 
Tom Zimoski said:
The SUM function doesn't require that it's arguments be contiguous and
I don't see why the XIRR function should either, . . .
Click to expand...

The order of the arguments for SUM doesn't matter. Commutivity is handy. The
order of the arguments for XIRR matters in the sense that amounts correspond
to dates. It looks like XIRR's developer(s) didn't consider it worthwhile to
accomodate iterating through potentially differently shaped multiple area
ranges, and checking the dimensions of such ranges is a pain.

If this is absolutely essential functionality for you, you could write a VBA
udf wrapper to collect your first and second arguments into arrays.


Function myxirr( _
v As Variant, _
d As Variant, _
Optional g As Double = 0 _
) As Variant
'-------------------------------------------------------
'this udf requires an explicit reference to APTVBAEN.XLA
'-------------------------------------------------------
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
 
Harlan, thanks for your help. While all XIRR really needs is pairs of
dates and values, I notice that this function works the same way in
both OpenOffice and the version of QuattroPro to which I have access
as it does in Excel. Seems odd to me but what do I know.

Thanks for the VBA help - I will also think about changing the layout
of my spreadsheet.

Tom
 
Back
Top