Spreadsheet
Concentration Risk Worksheet
What a large position in one company costs you, in dollars. Usually more than the tax you are avoiding by keeping it.
Download the worksheet
Opens in Microsoft Excel, Google Sheets, Apple Numbers, and LibreOffice Calc. To use it in Google Sheets, upload the file to Drive and open it with Sheets.
Half in one stock, ten years
$200,000 invested, same expected return either way
- Cost at the median
- $95,166
- Chance of going nowhere, diversified
- 13.1%
- Chance of going nowhere, concentrated
- 35.8%
The ninetieth percentile barely moves. That is the finding: the distribution widens at the bottom and not at the top, because single-name risk carries no expected return to pay you for it.
Two arguments, both arithmetic
First, the identity. Restricted stock is taxed as ordinary income the day it vests. By the next morning you are holding a position you have already paid full tax on, bought at that day's price. Holding it is economically identical to taking the cash and buying your employer's shares with it. Not similar: identical. Which makes the test simple, and the worksheet asks it directly. If your employer handed you $34,000 in cash instead, how much of it would you put into their stock? Whatever gap there is between that answer and what you are actually holding is inertia, and the sheet prices it.
Second, the cost. Company-specific risk is not compensated. You are paid for bearing market risk because nobody can avoid it; you are not paid for the risk that one firm has a bad decade, because that can be removed for free by owning more companies. So a concentrated portfolio has the same expected return as a diversified one and a far worse distribution, and the damage lands in the lower percentiles rather than the middle.
What is inside
- Vest and hold, the identity in dollars: grant value, tax at vest, what you are actually holding, and the tax cost of selling the whole position today.
- Distribution, your concentration against fully diversified, at the median, the tenth and the ninetieth percentile, plus the probability of ending below where you started.
- Sensitivity, two grids across concentration and single-stock volatility, which show that volatility is almost irrelevant until you own enough of the stock for it to matter.
- Every parameter is yours to change, including the market return, which is applied identically to both portfolios so the comparison measures concentration and nothing else.
The part the model leaves out
Your salary is already a position in the same company. A bad enough year takes the job and the portfolio together, which is precisely when you would need the portfolio. That correlation is not in the arithmetic below, because it is hard to estimate and easy to argue about, so treat every figure the worksheet prints as an understatement.
Method, and how it compares to the essay
The published essay runs 50,000 simulated paths. A spreadsheet cannot do that without a macro, so this uses the closed-form version of the same process. Checked against the five concentration levels in the published table, the medians differ by at most half a percent, the tenth percentiles by at most one and a half percent, and the probability of ending below the starting value by at most two tenths of a percentage point. Close enough that no decision turns on the difference, and the worksheet states the comparison on its own face rather than asking you to take it on trust.
Before you act on it
Selling employer stock can involve blackout windows, insider-trading rules, plan-specific holding requirements and tax consequences this worksheet knows nothing about. Check with your plan administrator. The arithmetic tells you what concentration costs; it does not tell you that you are free to trade on a given day.
Found an error, or think a calculation should work differently? Tell me. Corrections get published with a date attached, the same as they do for the research reports.