It’s not whether you get knocked down, it’s whether you get up. – Vince Lombardi
Playing around with DataNitro, an addin that lets you run Python in Excel^{1}.
What is the worst that could happen to someone who owns stocks, bonds, bills, over a 1, 5, 10year timeframe? Here are the worst rolling periods for each asset class for 19282010, adjusted for inflation.

Stocks 

Bonds 

Bills 

1year 
38.2% 
1937, 1974 
15.5% 
1980 
17.8% 
1946 
2year 
52.5% 
19721974 
26.2% 
19781980 
24.6% 
19451947 
5year 
44.7% 
19361941 
37.5% 
19761981 
27.3% 
19451950 
10year 
37.5% 
19641974 
43.2% 
19711981 
43.9% 
19401950 
20year 
10.7% 
19611981 
40.8% 
19611981 
48.9% 
19321952 
30year 
243.5% 
19641994 
39.3% 
19501980 
43.4% 
19321962 
Worst case real returns for rolling periods from 1 to 30 years, 19282010
Over short timeframes, stocks can do quite a bit worse. The worst 2year period is 52.5% for stocks, v. 26% for bonds and 25% for bills. Around year 8, stocks cross over. The worst 20year period for stocks sees you up 10.7%, and the worst 30year period sees you up 243%! When bonds and bills get hurt by inflation, they stay down for very long periods.
Rerunning the analysis for the postwar era doesn’t change much. Most of the worstcase periods for stocks and bonds were after 1946, but bills did worst around the war and better afterwards.
Worst case real returns for rolling periods from 1 to 30 years, 19462010
Spreadsheet here.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 import numpy as np # not used in this example, but works!
def rolling_return(series, n):
"given a series of m returns, compute mn rolling nperiod returns"
m = len(series)
retarray = []
for i in range(mn+1):
rr = 1.0
for j in range(n):
rr = rr * (1+ series[i+j])
retarray.append(rr1)
return retarray
active_sheet("Returns")
stocks = CellRange("Equities").value
bonds = CellRange("Bonds").value
bills = CellRange("Bills").value
cpi = CellRange("CPI").value
realbonds = [bonds[i]cpi[i] for i in range(len(cpi))]
realbills = [bills[i]cpi[i] for i in range(len(cpi))]
realstocks = [stocks[i]cpi[i] for i in range(len(cpi))]
active_sheet("Data_1928")
for i in range(1,31):
tempbonds = rolling_return(realbonds,i)
tempbills = rolling_return(realbills,i)
tempstocks = rolling_return(realstocks,i)
Cell(i+1,2).value = min(tempstocks)
Cell(i+1,3).value = min(tempbonds)
Cell(i+1,4).value = min(tempbills)
active_sheet("Data_1946")
realbonds46=realbonds[18:]
realbills46=realbills[18:]
realstocks46=realstocks[18:]
for i in range(1,31):
tempbonds = rolling_return(realbonds46,i)
tempbills = rolling_return(realbills46,i)
tempstocks = rolling_return(realstocks46,i)
Cell(i+1,2).value = min(tempstocks)
Cell(i+1,3).value = min(tempbonds)
Cell(i+1,4).value = min(tempbills) 
^{1}Why is Python a good thing? Lots of very powerful packages for data manipulation, optimization, statistical analysis, machine learning are available in Python. Also, Python is a powerful, expressive, readable language that makes it easy to manipulate complex data structures.