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

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

?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.