2

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.

-2

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.

  • Not sure why the downvote; I'd appreciate clarification of what it is you think I've misunderstood. – keshlam Jan 4 '15 at 4:30
  • Thanks for the detailed step-by-step. I didn't downvote, but I will say that what you're describing is what I would like to avoid--namely, manually calculating the CAGR for each security. I know I could dig up all the data for all securities, and run the numbers myself, but I'm looking for a tool that knows all the data already, and can do this for me. Since there are so many "portfolio analysis" tools out there that can do much more complicated calculations involving cashflows in and out, I assumed there was a "light duty" variant somewhere that would do what I'm interested in. – Josh Jan 4 '15 at 21:13
  • 1
    @Josh: I'm not sure I consider setting up a spreadsheet "manually calculating", and what I'm suggesting gives you the overall CAGR rather than that for each security, but OK... Unfortunately, the "recommend a product" question is generally considered offtopic for Stack Exchange. Good luck, in any case. – keshlam Jan 4 '15 at 21:21

protected by GS - Apologise to Monica Jan 25 '16 at 22:46

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.