Model a guaranteed Retirement income Product against a mutual fund investment

I want to use a Monte Carlo analysis to compare the long term performance of a guaranteed retirement income product (5% guarantee, 3 year resets, and 1% higher management fees) with the equivalent underlying mutual fund product. I have built what I want in Excel, using randomized returns from the last 50 years, but am limited by my technical skills to automate the process and show the output in clear graphical form. I acknowledge there may be a far easier way to create this program than excel! A programmer with an understanding of Guaranteed retirement income products would help the process, but should not be a limitation as I can easily explain the math involved. ## Deliverables Background: In a life annuity, an insurance company provides a guaranteed income stream for life in exchange for a lump sum of money up front. A Guaranteed Retirement Income Product (GRIP) is similar in that I give a lump sum of money to the insurance company and they will guarantee me an income for life, but there is also the possibility that my monthly withdrawal amount will increase over time if the underlying investments perform well. The theory of a GRIP is that it will protect against the 'bad luck' of retiring and starting to draw income from an investment portfolio at the time of a down market. The cost for this protection to the end user is a higher management fee to cover the insurance involved. The success or failure of the GRIP strategy is very much dependant on the sequence of returns. It could be advantageous to an investor who retires only to see markets drop in the early years of their retirement, yet it is unlikely to be of benefit in cases where the retiree enjoys stable or strong markets in the early years of their retirement. I want to understand in detail the trade off between these two alternatives. In order to compare the GRIP with the underlying investment that the GRIP is based on, I think I need to run at least a thousand simulations and compare the range of outcomes. I anticipate that the return of the GRIP in the average case will be lower (reflecting the higher management fees), but that the GRIP will also eliminate both very bad outcomes and very good outcomes. This is what I want to illustrate graphically. I also want a program where I can adjust the features of a generic GRIP to model different insurance companies offerings to judge their affect on the return outcomes across the wide range of return sequences. A typical GRIP: In the simplest example, I can deposit money to the program as early as age 50. For every year that I don't take withdrawals, they have a notational account called the Guaranteed Withdrawal Balance (GWB) that will be incremented by 5% of the original deposit. Every three years there is a comparison of the GWB and the value of the underlying investment pool - if the underlying investment value has outperformed the GWB, there will be a 'reset' that will increase the GWB to the current value of the underlying investments (The GWB can go up, but never down). When I start taking an income stream, I will receive an annual income stream equal to 5% of the GWB guaranteed for 20 years. If I don't start taking an income stream until age 65 (the product's intended usage), they will guarantee me an annual income stream equal to 5% of the GWB for life. Finally, the 'resets' can continue in retirement if the underlying investment pool grows at a rate exceeding the withdrawals - every three years, the underlying investment account is compared with the GWB, and if the underlying investment account is larger, there will be an increase in the GWB, and in the guaranteed income stream from that point forward. Features of the GRIP that I want to model and compare to a mutual fund investment. The return sequence will be created by assembling monthly returns in random order to cover the period covered by the illustration. (I need to be able to use different sets of returns to model differing underlying investment options) The monthly returns used in the GRIP side of the illustration will be the same as those used in the mutual fund side of the illustration with the exception that they will be reduced by an adjustable factor to illustrate the additional management fee The period in question will be of varying lengths to model different life expectancies Current reset interval are at 36 month intervals - this needs to be a variable as I suspect that companies will move to a more frequent reset this year, probably 12 month, with one month to come in the future. Variables: Invested amount additional management fee to be applied to the investments within the GRIP (~1% annually) lifespan of investor reset frequency sequence of returns (randomized from a file of monthly return values) ----------------------------------- Attached Spreadsheets: I have attached two spreadsheets to illustrate my thinking. In my first try at this spreadsheet (attached as Sunwise Elite [login to view URL]), I tried to illustrate the position at the end of 15 years (180 months) - a client who deposited money at age 50 and is now 65. The returns data file is on Sheet 2. To run, I select cells B2 through G372 and then sort on the random column. I test a different random sequence of a selection of these returns with each sort. I tracked the GWB in column H (you will see a fixed reset frequency at 36 month periods, represented in the green rows) In J2 through M4, I report the results at month 180 - here I see the comparison of how the investment fund would have done, the SWE fund (the investment fund inside the GRIP, reduced performance due to higher management fees), and the GWB itself. In the second attached spreadsheet (Sunwise Elite Analysis [login to view URL]), I built a macro that would repeat this random sort and comparison 1500 times and record the results on Sheet 3. From this list of results, I added columns E-G to create a histogram comparing the results of the stand-alone investments after 15 years with the GWB, and using columns H-J another chart showing the comparison I did this initial work several months ago. I recall that I have a logic error somewhere in the calculations, so please don't base your program off of mine - other than to see what I am trying to achieve. Also, where I stopped at the accumulation phase, I need a program that will accurately illustrate both accumulation and depletion through life span. I hope this gives enough information for an accurate bid. Tim. * * *This broadcast message was sent to all bidders on Thursday Jan 1, 2009 10:04:44 AM: This is my first use of this site, and after reading a number of questions, I think I should have added more detailed information to start. I added additional information in response to questions, along with the excel spreadsheets that I compiled on my own. Please review this material and feel free to revise your bids accordingly. I hope to find a programmer that I can work on a number of similar projects with over the months to come.