What Return: part II ... a continuation of Part I

We're checking out the magic formula:
where Po is the current stock price, P is the price at some time T in the future and r and s are some (as yet unknown) "average" return and standard deviation.

>And you want to identify the "best" choice for r and s.
Exactly. To this end, we'll do this:

  1. Generate a set of random, lognormally distributed returns.
  2. Choose some "average" return and standard deviation
    (Mean, Annualized, Continuous, logarithmic, instantaneous ... whatever).
  3. Having made our choice, (r, s), we generate a distribution f(P) according to the above formula.
    (It will depend upon our choice).
  4. We then generate a jillion random, lognormally distributed returns hence a price P, at time T in the future, and compare to the distribution f(P).
  5. Then we repeat steps 2 to 4 a jillion times to see which choice (in step 2) gave the "best" match.
>Are we talkin' annual returns ... or weekly or monthly or ... ?
We'll play with this scheme for, say, weekly returns.. just to see what happens.
[1]
First we generate 1000 of our so-called "historical" weekly returns.
Of course, they aren't really historical.
They're generated via Excel's LOGINV(RAND(),Mean, StandardDeviation) function.

Then we apply these 1000 weekly returns to a stock with starting price of P(0) = $10.00
... and get something like Figure 1 for the stock evolution over 1000 weeks ... about 20 years.

>Looks about right to me?
Yes, don't they? We'll want to generate lots of these "historical" price data.
For this example, we assumed a Mean and Standard Deviation (for our weekly returns) of 0.20% and 3.0% respectively.


Figure 1
some "sample" Historical Prices
[2]
Having 1000 returns, we pick some "average return" and some "standard deviation" for r and s.
>Some?
Yeah. We got lots of possibilities for these guys. That's where the fun comes in, eh?

Then we plot the price distribution f(P), as in Figure 2, for say T = 30 weeks.

[3]
Then we randomly pick T = 30 of these 1000 returns, and, starting with $P(0) = $10.00, determine the price after T = 30 weeks ... and repeat this 1000 times.


Figure 2
Theoretical Future Distribution with selected r and s
[4]
We'll get 1000 "possible" prices, 30 weeks into the future.
We plot the distribution of these 1000 prices.
That'd give something like Figure 3.

Note that we're making 30 random selections from the 1000 "historical" returns and applying them to a $10 stock price ... and repeating this ritual 1000 times to get a distribution of prices; that's Fig. 3.

>Then we compare Figure 3 with Figure 2, right?
Right.
>I can tell you right now they ain't the same!!


Figure 3
Distribution of Future Prices
Okay, so we try a different set of 30 random selections from our 1000 "historical" returns ... and repeat [3] and [4].
Then we try a different set of historical returns, and start all over at [1] while retaining the same choice of r and s in step [2].

Eventually we get a feel for how well (or poorly) we did with our particular choice of r and s.
Then we start again at [1], but this time we choose a different r and/or s at step [2]

>You're kidding, right?
Well, there's a spreadsheet to play with. It looks like this:


Click on picture to download spreadsheet

Okay, so here's what you do:

  1. Pick some Mean and Standard Deviation in cells H1 and J1 and some starting stock price in L1, say Po = $10.
  2. Then click the Generate New Historical Prices button:
    That'll fill in 1000 returns and prices in columns B and C and copy them to columns E and F (for safe-keeping).
  3. Pick a time period in L2, say T = 30 weeks.
  4. Each time you click the Generate Future Price Distribution you pick a random set of T = 30 returns and prices.
    These are selected from the 1000 returns in column E and applied to your $10 stock.
  5. Keep pressing Generate Future Price Distribution to see how well (or poorly) the Future Price Distribution compares to the theoretical f(P).
>But if I want choose a different r and s, what then?
Oh, almost forgot. That's this set of buttons:
You can choose:
1:   the Average and SD of the Logarithmic returns
2:   the simple Mean and Standard Deviation of the weekly returns,
3:   maybe you'd like to try the Annualized return for r and the SD of simple weekly returns

>And if I want some other choice, what then?
There's a place to stick three of 'em, over by columns U, V and W:
By clicking on one of them buttons, 1: or 2: or 3:, you'll get the Mean and SD copied to cells N1 and P1.

>So I can just stick my own numbers to replace those in U, V and W?
Sure. Or, since picking one of those 1: 2: or 3: buttons simply copies from U, V or W to N1 and P1, you can type in your own numbers in N1 and P1.

Try it! It's fun!!