*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 Excel^{1}.

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) |

^{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.