This spreadsheet shows how to compute the average growth rate (r) and volatility (σ
for a function of the form f(t) = aert, from original Google stock price data
prepared by Michel-Alexandre Cardin, 11/2007, updated 08/2008
Reconstructed (% Difference
Date Month (t) P ($) LN(P) fit of form f(t) = with
rt
ae observation)2
800.00
8/31/2004 0 102.37 4.63 193.47 0.79
9/30/2004 1 129.60 4.86 198.94 0.29 700.00
###### 2 190.64 5.25 204.57 0.01
###### 3 181.98 5.20 210.36 0.02 Fitted curve obtained fro
###### 4 192.79 5.26 216.31 0.01 600.00 "Add Trendline" function
1/31/2005 5 195.62 5.28 222.43 0.02
2/28/2005 6 187.99 5.24 228.72 0.05
500.00
3/31/2005 7 180.51 5.20 235.19 0.09
Price ($)
4/29/2005 8 220.00 5.39 241.85 0.01
5/31/2005 9 277.27 5.62 248.69 0.01 400.00
6/30/2005 10 294.15 5.68 255.72 0.02
7/29/2005 11 287.76 5.66 262.96 0.01
8/31/2005 12 286.00 5.66 270.40 0.00 300.00
9/30/2005 13 316.46 5.76 278.05 0.01
###### 14 371.81 5.92 285.92 0.05 200.00
###### 15 404.91 6.00 294.01 0.08
###### 16 414.86 6.03 302.32 0.07 Original price da
1/31/2006 17 432.66 6.07 310.88 0.08 100.00
2/28/2006 18 362.62 5.89 319.67 0.01
3/31/2006 19 390.00 5.97 328.72 0.02
0.00
4/28/2006 20 417.94 6.04 338.02 0.04
0 10
5/31/2006 21 371.82 5.92 347.58 0.00
6/30/2006 22 419.33 6.04 357.42 0.02
7/31/2006 23 386.60 5.96 367.53 0.00
8/31/2006 24 378.53 5.94 377.93 0.00
9/29/2006 25 401.90 6.00 388.62 0.00
###### 26 476.39 6.17 399.61 0.03
###### 27 484.81 6.18 410.92 0.02
7.00
###### 28 460.48 6.13 422.55 0.01
1/31/2007 29 501.50 6.22 434.50 0.02 Regression obtained from Ex
2/28/2007 30 449.45 6.11 446.80 0.00 6.00 "Add Trendline" function
3/30/2007 31 458.16 6.13 459.44 0.00
4/30/2007 32 471.38 6.16 472.43 0.00
5.00
5/31/2007 33 497.91 6.21 485.80 0.00
6/29/2007 34 522.70 6.26 499.55 0.00
7/31/2007 35 510.00 6.23 513.68 0.00 4.00
LN(P)
8/31/2007 36 515.25 6.24 528.21 0.00
9/28/2007 37 567.27 6.34 543.16 0.00
3.00
###### 38 707.00 6.56 558.53 0.04
###### 39 693.00 6.54 574.33 0.03
###### 40 692.60 6.54 590.58 0.02 2.00
1/31/2008 41 564.30 6.34 607.29 0.01
2/29/2008 42 472.00 6.16 624.47 0.10
1.00
1.00
3/31/2008 43 440.47 6.09 642.14 0.21
4/30/2008 44 574.29 6.35 660.30 0.02
5/30/2008 45 585.80 6.37 678.99 0.03 0.00
6/30/2008 46 526.42 6.27 698.20 0.11 0 10
7/31/2008 47 473.75 6.16 717.95 0.27
Regression Results
α
Alpha (α) 5.2651 => a = e 193.47
Beta (β) 2.79% => average growth rate r per month
(since 1 period = 1 month)
Mean squared difference (variance σ2) in % 5.51%
Standard deviation (volatility σ) in % 23.46%
(r) and volatility (σ)
y = 193.47e0.0279x
R² = 0.7776
Fitted curve obtained from Excel
"Add Trendline" function
Reconstructed curve from
regression results "a" and
Original price data
10 20 30 40 50
Time (months starting 08/2004)
Regression on LN(P)
gression obtained from Excel
dd Trendline" function
y = 0.0279x + 5.2651
R² = 0.7776
LN(P) data
10 20 30 40 50
Time (months since 08/2004)
Random Draw from standardized Realized return (expected return +
Time Stock Price
normal distribution random draw * volatility)
September $473.75 2.347378532 0.578682417
October $747.90 1.321189298 0.337900209
November $1,000.62 -0.499841439 -0.089381415
December $911.18 -1.089702852 -0.227784868
January $703.63 -0.821257253 -0.164797534
February $587.67 -0.124795273 -0.001381618
March $586.86 0.967262063 0.2548557
April $736.42 2.415182446 0.59459174
May $1,174.29 -0.365918901 -0.057958201
June $1,106.23 0.796184123 0.214714444
July $1,343.76 -1.169818463 -0.246582974
August $1,012.41 0.652452119 0.180989564
Google Stock Price Simulation
$1,600.00
$1,400.00
$1,200.00
$1,000.00
$800.00
$600.00
$400.00
$200.00
$0.00
Random Draw from standardized Realized return (expected reversion +
Time Interest rate
normal distribution random draw * volatility)
2006 4.25% -1.296266662 -0.011194
2007 3.13% -0.950250057 -0.002645551
2008 2.87% -0.33406483 0.007390893
2009 3.61% -0.296002669 0.005744557
2010 4.18% -0.785757992 -0.00332514
2011 3.85% -2.228717523 -0.023971991
2012 1.45% -0.410372345 0.010494784
2013 2.50% -0.749655325 0.002257104
2014 2.73% 0.41992226 0.019123637
2015 4.64% 0.995427169 0.022019119
2016 6.84% 0.811955871 0.012661314
Interest rate Simulation
8.00%
7.00%
6.00%
5.00%
4.00%
3.00%
2.00%
1.00%
0.00%
2004 2006 2008 2010 2012 2014 2016 2018
Mean reversion (η): 0.3
Long-term mean (u): 7.00%
Annual volatility (σ): 1.50%
Inputs:
Demand in year 0 80
Limit of demand (M) 1600
Translation parameter (a) 19
Sharpness parameter (b) 1
Annual volatility 10%
Realized demand in year 0 can differ from projected demand by 20%
Limit of demand can differ from projected demand by 40%
Sharpness parameter (b) may differ from projection by 40%
Calculation:
Realized demand in year 0 88.92566588
Actual limit of demand 1964.324032
Translation parameter (a) 21.08950602
Sharpness parameter (b) 1.206461875
Demand Growth
Year Demand projection
projection
0 88.92566588
1 268.6760875 202.1%
2 680.0192045 153.1%
3 1255.014344 84.6%
4 1680.154962 33.9%
5 1869.692075 11.3%
6 1935.015608 3.5%
7 1955.460693 1.1%
8 1961.663227 0.3%
9 1963.527018 0.1%
10 1964.085454 0.0%
11 1964.25263 0.0%
12 1964.302664 0.0%
13 1964.317637 0.0%
14 1964.322118 0.0%
15 1964.323459 0.0%
16 1964.32386 0.0%
17 1964.323981 0.0%
18 1964.324017 0.0%
19 1964.324027 0.0%
20 1964.32403 0.0%
21 1964.324031 0.0%
22 1964.324032 0.0%
23 1964.324032 0.0%
S-curve simulation
3000
2500
2000
Demand
1500
1000
500
0
0 5 10 15 20 25
Year
with uncertainty deterministic
Random draw from
Realized Realized Deterministi
standardized normal
Growth Demand c
distribution
88.92567 80
1.540622153 217.5% 282.3762 200.2575968
0.442439217 157.5% 691.9065 448.0072995
0.024845234 84.8% 1256.704 822.2186928
-1.601491324 17.9% 1479.166 1186.946139
-0.139404128 9.9% 1846.27 1418.413319
-1.022782638 -6.7% 1743.787 1528.035209
0.362881035 4.7% 2025.679 1572.7509
0.069337978 1.0% 1975.222 1589.866525
-1.557230077 -15.5% 1658.051 1596.257118
-0.553125895 -5.5% 1855.478 1598.621032
0.970917412 9.7% 2154.949 1599.492429
2.630040054 26.3% 2480.909 1599.813238
-0.432982256 -4.3% 1879.267 1599.931289
-0.076171762 -0.8% 1949.36 1599.974722
1.344198961 13.4% 2228.367 1599.990701
0.744497388 7.4% 2110.567 1599.996579
0.819654345 8.2% 2125.331 1599.998741
-0.623033425 -6.2% 1841.94 1599.999537
-1.157487426 -11.6% 1736.956 1599.99983
-2.162461751 -21.6% 1539.546 1599.999937
-0.153354466 -1.5% 1934.2 1599.999977
-1.095366101 -11.0% 1749.159 1599.999992
1.017006763 10.2% 2164.097 1599.999997