Cumulative/Annualized Returns for seperate recordsets?

[Clorox]

Hello all,

I've got a table/query that has the returns for several hedge funds and is structured as follows:

ID Date Return

(there are more fields in the table, but these are the only relevant ones that appear in the query). The inception dates vary for each of the funds, but the ending date is always the same. Each date represents a month-end date.

Therefore, in this case, ID and Date are primary keys, and there can be duplicate IDs, but every ID/Date combination is unique (For example, there can only be one return for 1/31/2006 for the fund with ID #35.

What I would like to do is create a query or some code that takes the dates and returns for each fund and determines the annualized return since inception of that fund up until a specified date. Therefore, if fund ID #1 has 14 months of returns ending on 6/30/2006, I'd like to take ((1+% return in month 1)*(1+%return in month 2)*(1+% return in month 3 and so on))^(12/(# of months, in this case 14))-1 which would give me the annualized return over the 14 months (of course, for fund #2 or #3 it might be 20 months or even 50 months). I know that in Excel, if I have the array chosen I can do an array formula of {=PRODUCT(A1:A51+1)-1} to get the cumulative return. However, I'd like to avoid putting anything in Excel if possible because ideally I'd like to be able to run a report that will output MTD, QTD, YTD and since inception returns for a number of different funds. MTD, QTD and YTD I've obviously already figured out since those are easy, but this is a tricky one.

Then, I'd like the query to output the following:

ID AnnualizedReturn

Hope that makes sense.

Thanks for any and all help/suggestions!!!

Also, FYI - I don't know SQL very well, but I can write some VBA and I am decent at reading SQL to understand what it's doing.

[XLGibbs]

You could probably write a UDF (User Defined Function) in VBA that could do all of that.

I have written some complex function on our SQL server to calculate IRR and modified versions of an IRR, which require similar "looping" within the function itself.

It would be relatively straightforward as the function would accept the date parameters to determine the months, then cycle through months 1 through ?? (A While loop in SQL, or a DO UNTIL loop in VBA). Within each cycle it performs your 1+%Return. The %Return within the function could be a variable that is populated by a SQL based query of the table looking for the "Month on Book" of the investment.

The month on book of the investment would be best calculated within the table itself so it makes the query easier.

Problem is, it may not be a very fast function. My SQL IRR works great for straight amortization (nearly instant results on 1 million records), but when I have to tie in pre-payment or mortality curves, it slows to nearly 8 seconds per record. If the monthly return is already a populated field, then the function is much easier.

The loop would store the result of each formula in the loop and successively accumulate it.

Then it would just be the total divided by the "months on book" which is the loop counter itself.

I think I understand but would need more time and information to attempt to write the function myself. I don't think you can write a query to do it all in one.

[matthewspatrick]

I have written some complex function on our SQL server to calculate IRR and modified versions of an IRR,

Heresy! Pete, I'm sure your code was quite impressive. I just have no kind words for IRR :hammer: If it were up to me, it would be banished from every finance textbook, Excel, HP financial calculators, etc.



I think I understand but would need more time and information to attempt to write the function myself. I don't think you can write a query to do it all in one.

I would definitely need to know more about the specifics of the problem and have some (actual or fake) data to play with, but it might be possible with straight SQL. One of the ugliest queries you ever saw, but it just might :p

[Clorox]

Hi there - As requested, I've attached a sample database with two tables and one query (all the query outputs is the name of the fund, month, and return).

The tblMnthlyReturns has the fund ID, date, and monthly returns, and the tblMastername has the fund ID (autonumber), fund names and fund addresses. I made up the fund names/addresses to protect the innocent.

I can learn, so whichever you think between doing an SQL query vs. UDF in VBA would work better.

I'm assuming that in VBA I'd need to create a function that woul open the query, then basically tell it to take each fund from fund ID 1 through the last fund ID, create a recordset for whichever fund it is looping through (say fund 1 in this case), then within that recordset for fund 1, take each month's return from the beginning month through the end month, add one to each of those returns, and multiply in sequence from month 1 to the end, and then subtract one from it (this would get the cumulative return - I can work on annualizing it after the fact).

I'm just not sure how to write all that out...

Also, I'm not sure how to make it output the results into a table or query once the values are calculated.

I'm not too worried about the amount of time it will take for Access to compute the calculations. I am dealing with fewer than 4,000 total monthly returns (about 60 funds x 60 months per fund on average).

Thanks for your insight!

[XLGibbs]

Hey I don't like IRR either, especially with the mortality curves I had to build into to the function. And yes, it is quite impressive. :thumb

I will take a look, as will matthewspatrick to see what options are available on this one.

[matthewspatrick]

OK, now that I actually understand the question, I agree with Pete--SQL cannot do this, but VBA can. I guess we'll put our thinking caps on...:think:

[XLGibbs]

I haven't had time to look into this too much yet. This weekend frees up a little bit..will keep you posted...Sorry for the delays.

[Clorox]

No need to apologize. I'm patient and willing to accept any help I can get.

Thanks!