Simple tool for running what-if analysis on a set of ticker symbols?

Question

There are a lot of heavy-duty portfolio analysis tools out there, but what I'm looking for is (I think) much simpler. I simply want to:

• Enter a ticker symbol (could be mutual fund or ETF), along with a percentage
• The percentages for all symbols will add up to 100%
• Enter a start and end date, and see the total returns (CAGR) for that period.

For this particular analysis I'm not interested in adding particular buy/sell transactions, I just want to see the CAGR for a portfolio for a particular period.

Surely such a tool exists, but I haven't been able to find it yet, perhaps because I'm searching for the wrong terms? I've been looking at a lot of tools that pitch themselves as offering "Portfolio Analysis" or "What-if analysis," but either I haven't figured out how to use them properly, or they don't offer this.

I've tried Google Spreadsheets, but I can't figure out how to get the total returns for a mutual fund for a specific date range, or for ETFs in general. The GOOGLEFINANCE function allows you to get the total returns for specific ranges (1 day, 1 week, 4 week, 13 week, 1 year, 3 year, and 5 year), but you can't specify a specific date range. And with ETFs, I don't see any way to get the total return.

Does such a tool exist? I'm fine with building this in a Google Spreadsheet or Apache OpenOffice Calc, if it's possible. What I want to avoid is having to go grab all the historical data (e.g., past dividends) for a ticker symbol every time I want to do a "what-if" for a new symbol.

Answer 1

It sounds like you aren't looking for a full simulation, but for a simple calculation.

• Start with a $X investment
• Divide it up into separate amounts a, b, c, etc, according to the percentages you specified.
• Look up the price of each security on the starting date.
• Convert dollar amounts a, b, c, etc. to the correct number of shares in their corresponding security. Call those a', b', c', etc.
• Remember to allow for transaction fees, if any.
• Dividend calculation goes here:
• Look up when dividends were paid and how they were calculated for each security during this time period, decide how you would have reinvested them, look up prices on those dates and calculate how many shares were added each time, then add those into a', b', c', etc.
• Lather, rinse, repeat for each dividend payment.
• Look up the price of each security on the ending date.
• Use the ending-date prices to convert from number of shares a', b', c', etc. back to dollar values a'', b'', c'', etc.
• Remember to allow for transaction fees, if any.
• Add a'', b'', c'', etc. to get ending-date value of your investment.
• Calculate effective CAGR based on starting value, ending value, starting date and ending date.

The actual calculation is simple enough to do by hand, or in a spreadsheet, or to throw together a program in whatever language you happen to know.

The hardest part of this is gathering the data about prices on specific dates and what dividends were paid when. That can certainly be done manually; there are probably ways to download it automagically but I'm leaving that as an Exercise For The Programmer.