XIRR Function

[ripcure]

Hello All,



I am getting a run-time error with the following piece of code while using it in access 2007.

Sub Command19_Click()

Dim objExcel As Excel.Application


Dim p(4) As Double
p(0) = -10000
p(1) = 2750
p(2) = 4250
p(3) = 3250
p(4) = 2750


Dim d(4) As Date
d(0) = #1/1/1998#
d(1) = #3/1/1998#
d(2) = #10/30/1998#
d(3) = #2/15/1999#
d(4) = #4/1/1999#


Set objExcel = New Excel.Application
objExcel.RegisterXLL objExcel.Application.LibraryPath & "\ANALYSIS\ANALYS32.XLL"
Debug.Print objExcel.Run("XIRR", p, d, -0.1) ' Result: 0.374858599901199
objExcel.Quit
Set objExcel = Nothing

End Sub


I am getting the following Runtime Error:

Run-time error '1004':

Cannot run the macro 'XIRR'. The macro may not be available in this workbook
or all macros may be disabled.

[ajetrumpet]

ok so you're getting the error. do you know what it means?? there are many possible causes to the problem. could we get some ideas from you first?? more than likely, the macros are disabled in the wkbk because either it's saved as a file type that way or you haven't disabled them in excel manually.

[ripcure]

Hello All,

I am facing a error during the XIRR calculation.

I am using the following peice of code:


Dim ValVect() As Double
Dim DateVect() As Date

Dim xlsApp As Excel.Application
Set xlsApp = New Excel.Application
Text29 = xlsApp.WorksheetFunction.Xirr(ValVect, DateVect)

I am getting the following error

Run-time error '5':

Invalid Procedure call or argument

Thanks and Regards!
Ashish Naik

[gilesy]

It is very strange thing about the worksheet function XIRR. if the days are more than 11, then it doesn't work. Could this be a bug?

viz.

----------------------
Sub Command19()

Dim objExcel As Excel.Application
Dim p(4) As Double, d(4) As Date

Set objExcel = New Excel.Application

p(0) = -10000
p(1) = 2750
p(2) = 4250
p(3) = 3250
p(4) = 2750

d(0) = DateSerial(1998, 1, 1) '#1/1/1998#
d(1) = DateSerial(1998, 3, 1) '#3/1/1998#
d(2) = DateSerial(1998, 10, 11) '#10/30/1998#
d(3) = DateSerial(1999, 2, 11) '#2/15/1999#
d(4) = DateSerial(1999, 4, 1) '#4/1/1999#


Debug.Print objExcel.WorksheetFunction.Xirr(p, d) 'N.B. no 3rd parameter.

Set objExcel = Nothing

End Sub
------------------------
Conclusion:
This returns: 0.339803642034531
If the days are 30 and 15 respectively, as in your example, then it doesn't work. There can not possibly be any work-around to this. Or can there? Is there anyone out there who can shed light on this bug?