Run-time error '1004': Unable to get the Xirr property of the WorksheetFunction class

[modakindia]

I am getting the subject mentioned error while processing the following code:

Public Function CheckIRR1(Company As String, Term As Integer, Premium As Integer, ProposalDate As Date, YearlyInstalment As Integer, DateOfLastPayment As Date, DateOfMaturity As Date)
   Dim nArraySize As Integer
   nArraySize = (Term * YearlyInstalment) + 1
   Dim VResult
   Dim arrAmount() As Double
   ReDim arrAmount(1 To nArraySize)
   Dim arrYears() As Long
   ReDim arrYears(1 To nArraySize)
   Dim Guess As Double
   Dim LoopDate As Date
   LoopDate = ProposalDate
   i = 1
   Do While LoopDate <= DateOfMaturity
     If i < nArraySize Then
        arrAmount(i) = -Premium
     End If
     arrYears(i) = LoopDate
     LoopDate = WorksheetFunction.EDate(LoopDate, 12 / YearlyInstalment)
     i = i + 1
   Loop
   Guess = 0.1
   VResult = WorksheetFunction.Xirr(arrAmount, arrYears, Guess)
   MsgBox CStr(VResult)
End Function

Error coming at line: VResult = WorksheetFunction.Xirr(arrAmount, arrYears, Guess)

Please help.

 

[Firefly2012]

What version of Excel are you using?

 

[modakindia]

Excel 2010.

What version of Excel are you using?

 

[Firefly2012]

I've found Xirr really hates non-date format values for the dates so can you rty changiung the Years definition to:

Dim arrYears() As Date

 

[modakindia]

I have changed "Double" by "Date".

Same error is coming.

Moreover, I would like to inform you that the following Error Message is coming during debug.

Run-time error '1004':
Application-defined or object-defined error

I've found Xirr really hates non-date format values for the dates so can you rty changiung the Years definition to:

Dim arrYears() As Date

 

[ChrisM]

I was playing around with this and got the same error even when I ran the formula in Excel. Turns out the XIRR function is pretty picky on how the data is presented to it. I had to make the first value a negative, then it worked successfully in Excel, then I was able also successfully run it in VBA.

Take a sample of your data and do the same thing, make sure you can calculate it correctly in a worksheet first. Here is the code I successfully ran:

Sub test()

Dim aAmt(0 To 2) As Double
Dim aYrs(0 To 2) As Date
Dim dAns As Double

aAmt(0) = -10
aAmt(1) = 5
aAmt(2) = 8

aYrs(0) = #1/1/2010#
aYrs(1) = #1/1/2011#
aYrs(2) = #1/1/2012#

dAns = Application.WorksheetFunction.Xirr(aAmt, aYrs, 0.01)
MsgBox dAns

End Sub

 

[DanXSS]

Hi modakindia.

Can you try it after amending the following lines in your code?

'[...]
    Dim arrYears() As String
'[...]
    arrYears(i) = Format(LoopDate, "m/d/yyyy")
'[...]

I tried to do it but you are sizing your arrays based on some worksheet data, so I could not replicate the behavior. I ended up with arrays half empty. Also, I think my following answer to another forum, on a similar question might help:

I did not yet managed to make the XIRR function to work in VBA, unless I used string version of dates.

My current date settings are dd/mm/yyyy and no matter what I do, all my Date variables in VBA are #dd/mm/yyyy#. The only place where I can only use the #m/d/yyyy# format is in the VBA editor. And even if I force feed a date value in m/d format to a Date (or even a String) variable (StrVar = #12/31/2012#), the variable will still hold #31/12/2012# or "31/12/2012".

And XIRR (no matter how I will call it) won't work with #dd/mm/yyyy#. So what I do to make it work is to feed it strings instead of dates. Tested it thoroughly and it works just fine. An alternative is, of course, to write your own XIRR function that works with any kind of dates, in any format.

Public Function XIRRfix(ByRef CFArray() As Double, ByRef DateArray() As Date, Optional ByVal Guess As Double = 0.1) As Double 
     
    Dim CFArrayFix() As Double 
    Dim DateArrayFix() As String 
    Dim x As Long 
     
    x = LBound(CFArray()) 
    Do Until CFArray(x) <> 0 
        x = x + 1 
    Loop 
    Redim CFArrayFix(x To UBound(CFArray())) 
    Redim DateArrayFix(x To UBound(CFArray())) 
    For x = LBound(CFArrayFix()) To UBound(CFArrayFix()) 
        CFArrayFix(x) = CFArray(x) 
        DateArrayFix(x) = Format(DateArray(x), "m/d/yyyy") 
    Next x 
     
    XIRRfix = Application.Xirr(CFArrayFix(), DateArrayFix(), Guess) 
     
End Function

Since XIRR, in general, goes crazy if initial cash-flows are 0, the function also removes zero cash-flows and their dates. This one is meant to be used in VBA, so it does not test for array lengths, but if you use Ranges instead of Arrays as parameters and add length test, you can also use it as a custom worksheet function.