Kurt Johnson

Updated August 10, 2005:  I've updated the model to batch generate 3d surfaces (varying vol and principle) along with 2d surfaces (varying vol).  Now generating data for a chart like the one below is a single-click process.  There's also a status window since a 3d surface can take 5-10+ hours to run.  The new model is here; the old model is here.  All links below point to the new model, but VB commenting is cleaner in the old one if you care about that kind of thing.  Scott Neuendorf helped develop and implement these ideas.

Updated January 27, 2006: I should note that I learned some of the things I discuss below "the right way" at Wharton, i.e., using the terms economists use.  If this is interesting you may want to do more research on personal utility functions (i.e., risk aversion) and how they might not be well represented by mean-variance portfolio optimization metrics (e.g., CAPM).

Most consumer financial planners are horribly vague about the uncertainty of expected returns.  Their analysis is limited to math that can be done in a dozen keystrokes of a calculator; “$500,000 invested for 30 years at 8% gives $2,600,000”.  There’s very little discussion of variance or timing except encouragement to save as much as possible early.  In managing my own investments I’ve tried to determine the right amount of leverage (also called ‘risk’) to take on; here I share the parts of my analysis that I think would be interesting to others.  I’m also sharing the Excel model I built for this* – it should not require any special add-ins or software to work.  If you find the model or discussion useful I’d appreciate an e-mail to let me know. 

For me personally this exercise helped me make some decisions about how to allocate investments – boosting volatility by investing surplus student loan funds, for example, or using options to increase leverage.  More simply, it gives a better picture of how likely I am to hit my retirement goals given where I'm starting from - are my expectations realistic?  If I'm only 55% likely to hit them is that good enough?

Finally, the disclaimer: at the time of this posting I am not a financial planner, financial advisor, accountant, lawyer, or anything else worthy of your trust (or litigation).  All models are only to be used as intellectual exercises and all figures used below or in the spreadsheet are for example only.

WHAT’S THE POINT?

Academic finance is centered on a direct relation between risk and reward where portfolios with higher risk  provide a higher mean reward.  My curiosity centers on what level of risk is most efficient for individuals; say a 5% mean return came with 20% volatility but a 10% mean return could be had for 40% volatility.  Clearly a simple mean-return approach indicates that the 10% return is a better investment.  However, what if an individual isn't concerned with mean return but instead wants 90% confidence that they will have X dollars at retirement?  Does the higher variance of the 10% investment push the 90% confidence level lower than taking a 5% return with a tighter variance?  More tangibly, what is the right level of leverage for an individual with specific assets and timing constraints?  Who should play it safe and who should bet the farm?

DESCRIPTION OF BASE MODEL

The first tab of the model ("BaseModel") uses random annual returns (log normally distributed) and a random year of death (based on generic life insurance mortality tables).  The user inputs how much principle (investable assets) they are starting with, their annual contribution, the year of retirement, and the annual withdrawal starting at retirement.  In the spreadsheet the inputs look like this:

INPUT	EXPLANATION
Starting age	Current age
Starting principle	How much principle is currently available for investment
Annual deposit	How much principle will be contributed each year until retirement
Age to start withdrawal	The age of retirement -  when contributions stop and withdrawals start
Annual withdrawal	Amount withdrawn each year after retirement
Male/Female	Separate mortality rates exists for males
Iterations to run	How many iterations the Monte Carlo should run for (probably 500-10,000)
Risk free interest rate	Real interest rate (ex inflation) on US T-bills
Market risk premium	Real rate of return (ex inflation) for the ‘market portfolio’
Market annual vol	Annualized standard deviation of market portfolio

 

For each iteration the model outputs a "Value in Terminal Year" which represents how much principle exists at the year of death (where the year of death is random based on the mortality tables).  This is recorded, all variables are re-randomized, recalculated and recorded again for the number of "Iterations to run".  Once the model completes all iterations the green cells show the percentiles of value in the terminal year:

Percentiles	Terminal Value
5%	(2,348,872)
10%	(1,305,938)
25%	(66,944)
50%	1,478,595
75%	4,577,851
p of <$0	26%

In this figure (for 1,000 iterations) the model is telling us that 5% of the random trials ended up with negative $2,348,872 or less; in other words we are 95% likely to not die more than $2.3mm in the hole.  The median outcome is to die with $1,478,595 in assets.  The last row is the simplest; we show a 26% probability of negative assets in the terminal year (alternately stated as 74% chance we will not run out of money before death).  For my purposes the key terminal value breakpoint was $0; if you’d rather use some other level of assets as the “p of <$0” breakpoint it’s easy to change the formula in Excel.

 

DESCRIPTION OF SURFACE MODEL

The next step in the model is to tune portfolio volatility in search of the most efficient level of risk to take on.  In simple terms, someone who starts with few assets but has grand ambitions for retirement should bet the farm; if they play conservatively they are guaranteed to fail whereas more risk means they have a 75% chance of failure but a 25% chance of hitting their goals.  Conversely an individual with substantial assets and modest retirement goals would be foolish to take on much volatility; with very low volatility they have a 95% chance of funding their goals and taking on risk means increasing the chance of “blowing up” their savings – the extra return potential for higher risk isn’t necessary to meet their goals. ¹ The challenge is to find the optimal balance of risk and return based on how much principle exists at inception, when the withdrawals begin, and the magnitude of periodic deposits and withdrawals.  In the spreadsheet this is done on the “SurfaceModel” tab by filling the yellow range with the list of volatilities you want to test.  The outcome might look like this:

			Volatility
	0.0%	10.0%	20.0%	22.5%	25.0%	30.0%	35.0%	37.5%
Percentile: 5%	(2,187)	(2,261)	(2,685)	(2,708)	(3,272)	(4,513)	(6,299)	(12,086)
10%	(2,088)	(1,573)	(1,318)	(1,402)	(1,500)	(2,072)	(3,039)	(4,950)
25%	(1,642)	(538)	338	425	337	76	(239)	(916)
50%	(1,037)	462	3,052	3,385	3,977	4,363	4,084	2,684
75%	(289)	1,528	11,326	14,043	21,873	27,565	35,871	33,380
p of <$0	80.2%	38.4%	21.7%	19.9%	21.7%	23.8%	26.8%	37.1%
Optimal Vol	22.5%

 

This table shows a breakout of percentiles for each simulated portfolio volatility.  For example, the median outcome for a 10% portfolio Stdev is 462.  Boosting portfolio Stdev to 20% raises the median outcome to 3,052.  For my purposes the “Optimal Vol” is the one that minimizes the chance (percentile) of negative principle at death, in this case 22.5%.² In practical terms this means the optimal portfolio may be very near a 1:1 leverage to the market portfolio.  In contrast a 5% optimal volatility would mean investing primarily in low risk/risk free bonds giving a leverage of 1:4 to the market portfolio.  Likewise a 30% optimal volatility means taking on something like 3:2 leverage by borrowing money to buy equities or similar high risk/high return investments. ³

ADDING ANOTHER DIMENSION TO THE SURFACE (CHANGING STARTING PRINCIPLE)

Next, I thought it would be interesting to see how optimal volatility changes as starting principle changes.  Given these factors:

Starting age	20
Annual deposit	$0
Age to start withdrawal	60
Annual withdrawal	($80,000)
Male/Female	Male
Iterations to run	1,000
Risk free interest rate	0.50%
Market risk premium	7.00%
Market annual vol	21%

 

Next, Here is the graph of the “p of <$0” outcomes (i.e., probability of running out of money before dying):

 

The red marks indicate the optimal volatility for each starting principle.  It’s intuitive that starting with more money (the bottom line, $2mm) produces a much lower risk of running out of money.  As I suggested earlier it also makes sense that optimal volatility increases as starting principle decreases – this is the progression of red ticks from lower left to upper right.  The graph also makes it easy to see that for a relatively low wealth individual there is a fairly linear function where more risk is better (‘nothing to lose, everything to gain’).  For moderate starting principles the graph is flatter - outcomes are not dramatically different between 20% volatility and 35% volatility.  For high starting principles (>$500k) there is a clear optimal volatility. ^4,5 

 

Note that using the specified retirement demands a starting principle of $50k at age 30 gives at best a ~38% probability of being financially independent until death.  

 

FOOTNOTES

*First download the Excel model.  If you are using Excel XP or newer and you do not get a prompt to enable macros when opening the file, go to Tools/Options/Security/Macro Security and set macro security to “Medium”.  Make sure that in Excel under Tools/Add-ins you have checked the boxes for “Analysis Toolpak” and “Analysis Toolpak VBA”.  If these are not checked, check them, exit Excel, and restart Excel.  For any other problems please email me.  Runtime for 5,000 trials on my Pentium M 1.6 is around 2.5 minutes.

The model I'm sharing is based on simple Excel formulas and about 20 minutes of VB coding.  I started in Crystal Ball but switched back to simple VB so I could distribute the model to people who don't have access to CB.  The code is commented as I may give this to some folks who are just starting out in Excel macros and need a project to play with.  I use the native Excel random number and statistics functions although an academic would want to write more robust tools in VB or a dedicated statistical package.  Another notable flaw in the model is the static 2001 mortality table; some allowance should be built in for increasing future life expectancy.

¹I use the basic CAPM to model the relationship between risk and returns.  Expected mean return in my model is:

Mean portfolio return = risk free rate + market risk premium (σ_portfolio / σ_market)

When time permits I’m interested in violating this assumption – I suspect that the general shape of the graphs will not change and that optimal volatility will decrease overall but especially at higher starting principles.

I use real dollars and real rates of return (ex inflation) in all of my calculations.  For an overview of historical returns and volatilities of various asset classes see http://www.duke.edu/~charvey/Classes/ba350/history/history.htm

²Note that the output does not appear as a smooth curve (the second derivative changes signs across the range); I assume this is an artifact of small trial counts (1k per node for this graph) and would disappear as the number of trials was boosted to 5k or 10k per volatility.  There’s probably an analytic solution to this problem that would permit partial differentiation with regard to each model input…but I’m not comfortable enough with stochastic calculus to figure it out so I ‘brute force’ the analysis via a Monte Carlo.

³ What is the “market portfolio” and the corresponding market risk/return?  There doesn’t seem to be a consensus on what a good proxy is.  The easy shortcut used in class is often the S&P 500 index, which probably has a volatility around 22% and mean real return of ~8%.  The more sophisticated answer might be “the global portfolio of all investments including human capital and intangible assets” which is clearly impractical.  The whole notion of getting efficient leverage to the market portfolio was initiated by Markowitz in the 1950s and has become a staple of academic finance.  Lots of background is available via Google.

⁴I obtained these results by running the surface model 6 times, changing the starting principle each time (each line on the graph is one run of the surface model).   Since each surface model iterates through 13 different volatilities for the Base model the result is effectively 6*13=78 runs of the base model @ 1,000 trials per run.

⁵Note that the 0% volatility is a sort of special case where principle grows at the risk free rate with no volatility; the only risk in this case is what year death occurs.  Even starting with a small principle there is some probability of dying asset-positive because death could occur pre-retirement - before withdrawals start to occur.