asked on

XIRR MS ACCESS 2010

Hi All,
1. does anyone have some sample code to use xirr in ms access vba? (ideally using an array or query for the data)
2. does it require an excel object, or is it 'built-in' to access 2010 (like IRR?)
3. presumably it doesn't require the atpvbaen add-in anymore?

thank you

Jeffrey Coachman

Sure,
One technique is to "pull" the XIRR function into Access from Excel.
http://oreilly.com/pub/h/3310
(I have a sample if you are interested)

See his link for more resources:
https://www.experts-exchange.com/questions/24665271/Where-can-I-find-Statistical-Function-Add-ins-for-MS-Access.html

;-)

JeffCoachman
JeffCoachman

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

COACHMAN99

ASKER

Thanks Jeff.
1. your first (orielly) link has no mention of XIRR (or IRR)? - please send sample code if you have any (specifically 'using an array or query for the data')
2. That is unfortunate, but I will work with that.
3. Apparently IRR is built-in (and doesn't require an Excel Ref) while XIRR does and, prior to 2007, also required the atpvbaen add-in.

COACHMAN99

ASKER

Thanks Jeff
still looking for sample code.

Jeffrey Coachman

<1. your first (orielly) link has no mention of XIRR (or IRR)? - please send sample code if you have any (specifically 'using an array or query for the data')>

I am currently away from my home computer with my samples ...

The code listed in the O`reily link referred to FV and Percentile.
You can pretty much take the Percentile code function there and substitute the word "XIRR" for the word "Percentile" in the function.

You will only have to add another Field argument, and a date Variable (and the associated code for them) in the code.
(and IIRC, add a rst.MoveFirst to the second redim to loop the second field...)

Fairly straightforward.

Let me know how you get on...
;-)

JeffCoachman