XIRR Function in Access

[Rory]

I need to use the XIRR function in a query in Microsoft Access 2003 and
cannot figure out how to call and use the excel function. Does anyone know
of an easy way to use the XIRR function in Microsoft Access?

 

[Damon Heron]

Look at IRR function in Access VBA Help

Damon

 

[Rory]

Thanks Damon, but I need to use the XIRR function which is available in
Excel, but not Access. I think I need to call the Excel function into
Access, but I am not sure how to do this.

-Rory

 

[Damon Heron]

It is my understanding that they are the same function. Did you look at it?

Damon

 

[Rory]

Yes I did look at it. XIRR and IRR functions are different. IRR requires
periodic cashflows, whereas, XIRR can handle nonperiodic cashflows.

 

[Damon Heron]

my bad, access only has the periodic cash flow.

Damon

 

[Damon Heron]

Will this work?

Public Sub TestXirr()
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) ' Result: 0.374858599901199
objExcel.Quit
Set objExcel = Nothing
End Sub


Damon

 

[Damon Heron]

Ahh, success!

Public Sub TestXirr()
Dim Result
Dim objExcel As New Excel.Application
Dim x
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#

Result = objExcel.WorksheetFunction.Xirr(p, d)


Debug.Print Result
objExcel.Quit
Set objExcel = Nothing
End Sub

' I am using Office 2007 with reference set for MS Office Excel 12.0 Library
Damon

 

[Rory]

It looks like this might work, but I don't know to use it in a query. My
programming knowledge is very elementary.

-Rory

 

[Rory]

No worries - I appreciate the help.

-Rory

my bad, access only has the periodic cash flow.

Damon

 

[Damon Heron]

Actually, it won't work as written. See my other post with a successful
subroutine.
Put the code in a module, and call it from the immediate window to see
result. The actual data is for test purposes, obviously, so you will need
to fill the arrays p and d with data either from a query or table, or direct
input from a form. To keep it simple, start with a form, with a series of
textboxes for the value, and another series for the dates. Then just change
the code in the subroutine TestXirr to read the data from the form - so
p(0)= me.textbox1, etc.
Add a command button to the form and in the click event call TestXirr.
Change the debug.print line to a textbox on your form (named "txtResult")
like this:
me.txtResult = Result
Realize that this is really rudimentory, and will only get you started...

Damon

 

[Rory]

Thanks Damon. I did this but am having difficulty calling the module with
the command button.

I am using Microsoft 2003.

-Rory

 

[Damon Heron]

To keep it simple, for now, do this - move the code to the click event:

Private Sub yourcommandbutton_Click()
Dim Result
Dim objExcel As New 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#

Result = objExcel.WorksheetFunction.Xirr(p, d)
Debug.Print Result
objExcel.Quit
Set objExcel = Nothing
End Sub

Make sure you set the reference (in VB Window, Tools, References -check the
MS Excel Object Library)

Post back with a description of any problem.
Damon

 

[Rory]

I receive a "run-time error '438': Object doesn't support this property or
method" and the debugger highlights the following code: "Result =
objExcel.WorksheetFunction.XIRR(p, d)"

I added the "Microsoft Excel 11 Object Library" to my references in VBA

Sorry for the struggle - I do appreciate all the help.

-Rory

 

[Damon Heron]

Since it works for me in 2007, and I successfully tested it in 2003 (but
with the 12.0 Library), then my guess (only a guess) is that the
WorksheetFunction was added to the Excel 12.0 Object Library and didn't
exist for 11.0...
Again, only a guess, and it is way outside my level of expertise. You know
you wanted to upgrade to 2007 anyway, right?

Damon

 

[Damon Heron]

just for curiousity's sake, could you go to the vb window, select View,
Object Browser, and where it says All Libraries, select Excel. Then select
Application under classes, and then in the members box, select
WorkSheetFunction. It will be highlighted below those boxes. If you
dbl-click on it, it should show all the members of WorksheetFunction. XIRR
should be one of the members.
If it is not listed, then my first instinct was right, it is missing from
the 11.0 Obj. Lib.

Damon

 

[Rory]

You are right - it does not show up under worksheetfunction. The XIRR
function does work in excel though - does this make sense?

I was already planning on upgrading to office 2007, so i'll give it another
try after the upgrade (won't be for a few weeks though...).

Thanks for all the help, Damon.

-Rory