Two Stocks ... and some Monte Carlo
motivated by e-mail from Dan M.

Once in a while I get e-mail asking for some spreadsheet that I ain't got ... I don't think ?
For example, I have spreadsheets where you assign some Mean and Standard Deviation to each of two assets as well as some inflation rate, then you pretend to withdraw with annual rebalancing to maintain a certain allocation and ...

>Yeah? So?
Well, my Monte Carlo spreadsheets usually invent returns, selected at random from maybe a normal distribution or maybe a lognormal distribution or ...

>Yeah? So?
So if you're really interested in what your portfolio survival rate might be, I think it'd be better to use actual returns for the two stocks ... especially if'n you can't generate returns with a prescribed and unusual correlation.

Yes. For example:

It ain't easy (for me!) to pick random returns from some distribution and have them correlated in a specified manner.
(We'll talk about this problem later.)

>Yeah? So?
So here's a spreadsheet:

Click the picture to download the spreadsheet

Note, however, that it's often difficult to get a jillion years worth of stock (or mutual fund) data, so the spreadsheet downloads five years worth of weekly data. That'd give about 260 prices & weekly returns.

Then, when you do the Monte Carlo thing, 52 returns are selected at random (from these weekly returns) for each year of your portfolio evolution.

Further, it's assumed that withdrawals are made every four weeks (rather than "monthly"). Hence, for a 52-week year, there are 13 withdrawals per year.

>Because 52 / 4 = 13?
Yes. Also, rebalancing is done every 52 weeks (after 13 withdrawals).

>That five years is the last five years?
Yes, although if you browse the spreadsheet you'll find some start and end dates that you can change to get more weekly data and ...

>And it's guaranteed to work flawlessly??
The spreadsheet? I offer a money-back guarantee, don't I?

>So you use actual returns 'cause you don't know how to incorporate a specified correlation, right?
Yes. We'd like to generate random returns (for, say, stocks and bonds) where the Mean and Volatility are specified for each as well as the correlation.

>Good luck!
Well, that bring us to Copulas.


for more stuff on correlations ...