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.
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.
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.
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