Results 1 to 3 of 3

Thread: IRR / Array ?

  1. #1
    Join Date
    Apr 2008

    Unanswered: IRR / Array ?

    Hello All, I have a query I've built that has three fields:

    AccountNumber; InvestmentDate; InvestmentValue


    45778; 01/25/2006; -$30,000.00
    45778; 01/15/2007; -$35,000.00
    45778; 03/31/2008; $84,986.53
    45779; 03/14/2005; -$20,000.00
    45779; 01/01/2006; -$1,000.00
    45779; 06/15/2007; $5,000.00
    45779; 03/31/2008; $26,413.82

    Explanation of my example:

    Account number 45778 deposited $30,000 into an investment account on 01/25/2006; they made an additional deposit of $35,000 on 01/15/2007; their investment value on 03/31/2008 is $84,986.53.

    Account number 45779 deposited $20,000 into an investment account on 03/14/2005; they deposited an additional $1,000.00 on 01/01/2006; they took a withdrawal of $5,000 on 06/15/2007; their investment value on 03/31/2008 is $26,413.82.

    My objective:

    Calculate the rate of return (I'm thinking IRR) for each account (my goal is to have an approximate annualized return of my clients investment). I can calculate this easily in excel with the XIRR formula, but am having difficulty producing the same result in access.

    More Detail...
    The data in my query is in no date sequential order and I have roughly 2,500 rows of data that indicate different account investment deposits and withdrawals; also included through a query I've built is the latest value which in this example is 03/31/2008. My last date of 03/31/2008 will change as I have a parameter query set to input the date (which will always be the latest investment value I have on file for the account).

    If anyone has a recommendation of how to calculate this I would really appreciate any help. Thanks.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if its easy in Excel.. then why not pump that data into an Excel spreadsheet for analysis... Play to strengths of Access AND Excel

    you can set an order for any data set in Access.. you can set multiple layers of order (eg by customer, account AND date)

    if you want to calculate an IRR within Access you can.. if you do the work yourself (there may be a function already but I'm not aware of it.. try googling IRR MS Access)

    if you can define how you calculate an IRR then you are part way towards writign a function to calcualte it. Heck its somehtign understood by Accountants and money men.. so it can't be "that" difficult for anyone else.. can it

  3. #3
    Join Date
    Nov 2007
    Adelaide, South Australia
    Totally agreed.

    Quote Originally Posted by healdem
    Heck it's something understood by accountants ... so it can't be "that" difficult...
    Ahhhh... NOW I have a new [corrected] [summarised] quote for my sig
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts