It’s not whether you get knocked down, it’s whether you get up. Vince Lombardi Playing around with DataNitro, an add-in that lets you run Python in Excel1.

What is the worst that could happen to someone who owns stocks, bonds, bills, over a 1-, 5-, 10-year time-frame? Here are the worst rolling periods for each asset class for 1928-2010, adjusted for inflation.

Stocks Bonds Bills
1-year -38.2% 1937, 1974 -15.5% 1980 -17.8% 1946
2-year -52.5% 1972-1974 -26.2% 1978-1980 -24.6% 1945-1947
5-year -44.7% 1936-1941 -37.5% 1976-1981 -27.3% 1945-1950
10-year -37.5% 1964-1974 -43.2% 1971-1981 -43.9% 1940-1950
20-year 10.7% 1961-1981 -40.8% 1961-1981 -48.9% 1932-1952
30-year 243.5% 1964-1994 -39.3% 1950-1980 -43.4% 1932-1962

 

Worst case real returns for rolling periods from 1 to 30 years, 1928-2010

Worst rolling returns

Over short timeframes, stocks can do quite a bit worse. The worst 2-year period is -52.5% for stocks, v. -26% for bonds and -25% for bills. Around year 8, stocks cross over. The worst 20-year period for stocks sees you up 10.7%, and the worst 30-year 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 post-war era doesn’t change much. Most of the worst-case 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, 1946-2010

Worst Case real returns, 1946-2010

Spreadsheet here.

?View Code PYTHON 
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 m-n rolling n-period returns"
    m = len(series)
    retarray = []
    for i in range(m-n+1):
        rr = 1.0
        for j in range(n):
            rr = rr * (1+ series[i+j])
        retarray.append(rr-1)
    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)

 

1Why 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.