XIRR in Access 2003

[Guest]

I would appreciate it if anyone can let me know how to calculate the XIRR
function in Access 2003.

 

[Tom Wickerath]

Hi Geoff,

This example should get you started in the right direction.

Tom
______________________________________

Option Compare Database
Option Explicit

Sub XIRRTest()
' Requires a reference set to the "Microsoft Excel (version) Object Library"
' Written by Matthias Klaey
' http://www.mcse.ms/archive153-2004-3-463062.html

Dim objExcel As Excel.Application
Set objExcel = New Excel.Application

objExcel.RegisterXLL objExcel.Application.LibraryPath & _
"\ANALYSIS\ANALYS32.XLL"

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#

Debug.Print objExcel.Run("XIrr", p, d) ' Result: 0.374858599901199

objExcel.Quit

Set objExcel = Nothing


End Sub

______________________________________


I would appreciate it if anyone can let me know how to calculate the XIRR
function in Access 2003.

 

[Guest]

Hi Tom

Many thanks, that should help me finish my application at last!

Geoff

 

[Guest]

Hi Geoff,
I realize this is an old post. I hope you're still there. Did this work
for you? Also, I am attempting to create an IRR app myself and it looks like
you may have some advice...

 

[Guest]

Hi Chris

Yes, this was great advice from Tom and it worked. My application was
successfully completed and has been running since 2004. The important thing
to do is to set the gobj Excel only once for all your calls and to quit after
their completion. This will result in the fastest possible response time.
If you do the set and quit for each call to Excel your response time will
probably be totally unacceptable - of course it depends on how many calls you
make.

Feel free to email if you have further queeries

Geoff