When we want to know how the future will evolve we often look at historical data and ...
>But past performance doesn't imply future performance. Don't you know that?
Exactly! So here's something we can do:
- Collect, say, ten years of historical data on three stocks as well as some benchmark such as the S&P 500 or the DOW or whatever.
- Calculate various things like Annualized Return for the stock and benchmark, Beta, Alpha, Standard Deviation, Correlation etc. ... based upon this data.
- Then mix up the returns by randomly selecting ten years of returns from the downloaded historical data.
- Recalculate the various things ... using this mixed up data.
>And that's supposed to predict the next ten years?
Uh, not exactly, but it'll give you some indication of how much you can rely on historical stuff to estimate future performance.
Anyway, here's a fun spreadsheet:
Every time you press F9 you get the returns rearranged and ...
>They're re-ordered, eh?
No, mixed up. A month is selected at random and all returns are selected from that random month ... then this is repeated for 120 months
(that's ten years).
Note that you might get the same month appearing several times.
Anyway, the mixed up data is used to calculate Standard Deviation, Beta and other stuff ... and you can see how well the same set of returns
will yield similar values, if they occur randomly in the future.
Then you can choose an allocation of the four assets (including the benchmark) and get your portfolio plotted.
You can press F9 again and again and watch your portfolio change ... like this
>That's the future, right?
That's a possible future. To get the actual future you'll need this
>Yeah, very funny. But what're those question marks in cells F1 and G1?
That's so you can stick your own stuff, either based upon actual historical or random data.
>But how do I do that?
Larn a wee bit o' Excel.
There's a button called Monte Carlo.
When you click on it, the ritual of "mix up the returns" is repeated a bunch of times and the percentage of those which have Beta > 1 is calculated.
>Is that important?
I dunno. Maybe you like those stocks whose movements are greater than the benchmark. They have Beta > 1.
Aah, but if that's what happened in the past, will it happen in the future?
>I suggest using this
to continue ...