WorksheetFunction.Xirr problem

[giovipi]

Apparently in an excel2007 macro the instruction:

dblXIRR = WorksheetFunction.Xirr(strValues, varDates)

does not work properly if varDates has a date element with day>12.

The little test sub below works but if you change any of the days to be > 12
you get a
"Runtime error 1004"
"Unable to get the XIRR property of the WorksheetFunction class"

Same error is reported if you want to use the WorksheetFunction.Xirr with
only two payments and respective dates.
Any suggestion on how to solve the problem (without writing the data in a
sheet and use the xirr on the sheet)?

Thanks
Giovanni

'*********************************************
Sub testxirr()

Dim myStrPrezzoNetto() As String
Dim myVarDate(2) As Variant


myStrPrezzoNetto = Split("-1789.7956 -4292.6008 5623.2", " ")
myVarDate(0) = CDate("11/03/2008")
myVarDate(1) = CDate("11/03/2009")
myVarDate(2) = CDate("11/06/2010")
dblXIRR = WorksheetFunction.Xirr(myStrPrezzoNetto, myVarDate)

End Sub
'**********************************************

 

[Rick Rothstein \(MVP - VB\)]

I don't use the financial functions myself (so I can't answer "technical"
questions regarding them), but whenever I see a question about dates and
days over 12 not being recognized, it just has to mean what you think is a
day is being interpreted as a month by VB. This could, perhaps, have to do
with your regional settings, I don't know; however, it is usually a good
idea not to use string representations for your dates. Try parsing the
values out of your date string and putting them into the appropriate
argument positions of the DateSerial function and see if that works for
you...

dblXIRR = WorksheetFunction.Xirr(strValues, DateSerial(Y, M, D))

where Y is your parsed out year, M is your parsed out month and D is your
parsed out day.

Rick

 

[giovipi]

Rick,
it does not work. I have tried the following putting days > 12.

******************
Sub testxirr2()

Dim myStrPrezzoNetto() As String
Dim myVarDate(2) As Variant
myStrPrezzoNetto = Split("-1789.7956 -4292.6008 5623.2", " ")
myVarDate(0) = DateSerial(2008, 11, 1)
myVarDate(1) = DateSerial(2008, 11, 4)
myVarDate(2) = DateSerial(2008, 11, 5)
dblXIRR = WorksheetFunction.Xirr(myStrPrezzoNetto, myVarDate)

End Sub
************************************
Thanks
Giovanni

 

[Rick Rothstein \(MVP - VB\)]

As I said, I cannot help you with the technical aspects of the XIRR formula,
but I started my copy of XL2007, put in your originally posted code, add 10
days to the dates to make sure the day values were greater than 12 and ran
it... your original code worked fine (as far as I can tell) for me. Using
your originally posted code with the 10 days added to each date returned the
value -5.87608486413956E-02 (I have no idea if that number makes sense or
not) into the dblXIRR variable. There were no errors and no indication of
any problems. To clarify, the date order for my system is mm/dd/yyyy. Next,
I ran the code you posted that used the DateSerial, added 10 days to the
values you used and that too worked fine, returning -0.999999821801885 into
the dblXIRR variable (note the dates and years for your second posting were
different that in your first posting).

So, I'm not sure what to tell you is wrong given that all the code I have
tried works fine on my copy of XL2007.

Rick

 

[giovipi]

Thanks for the suggestion,
I have changed my locale settings and the test macro works. Obviously I now
have to check the impact of this change on all the other macros...
The WorksheetFunction.Xirr behaves differently from the corresponding XIRR
function.

Giovanni