XIRR function in group footer of Access report

  • Thread starter Thread starter Pam
  • Start date Start date
P

Pam

I am trying to calculate an XIRR within the group footer
section of an Access report, and getting a general error.
I can not find any type of "how to" documentation on
this. If anyone can direct me to a source of information,
I would appreciate it very much. Thank you.
 
I am trying to calculate an XIRR within the group footer
section of an Access report, and getting a general error.
I can not find any type of "how to" documentation on
this. If anyone can direct me to a source of information,
I would appreciate it very much. Thank you.
Click to expand...

Here is some test code that shows how to call the XIRR function from
Access. You will have to put this code in the report footers Format
event and modify the way you fill the required parameters p and d.

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

HTH
Matthias Kläy
 
Back
Top