Opportunity cost of ownership vs capital gains of sale tool

	A	B	C
1		Disclaimer: This spreadsheet is not intended as financial advice
2	This tool is designed to help you compare the opportunity costs of holding a nonpreferred investment against the capital gains taxes incurred from selling appreciated investments. Scroll down for formulas and examples.
3
4	Parameter	Explanation/methodology	Where to find this info
5	Assetholder's long term capital gains rate	An assetholder pays long term capital gains taxes on realized cap gains (either from selling an asset that has gone up in value, or holding a fund that has sold investments within the fund for a gain and passes on the capital gains to the fundholder), for over one year.   An asset holder also pays long term capital gains on qualified dividends (the most common type) from stocks, bonds, ETFs and mutual funds. Depending on the assetholder's income level and marital + filing status, they'll either owe 0%, 15%, or 20% on long term capital gains incurred.   The 2024 cutoffs for long term cap gains rates are in the table linked here	Speaking with the assetholder about their projected income for this tax year
6	Assetholder's marginal income tax rate	Depending on the assetholder's income level and marital + filing status, they'll owe between 10% and 37%, plus state income tax, on short term capital gains incurred.   An assetholder also pays short term cap gains on non-qualified dividends. The 2024 cutoffs for short term cap gains rates are in the table linked here	Speaking with the assetholder about their projected income for this tax year
7	Cost Basis	How much the assetholder paid per share	The assetholder's brokerage
8	Current market value	The current value per share	The assetholder's brokerage (or Google)
9	Net expense ratio	Annual fee (a percentage) charged to own the asset. Stocks typically don't have an expense ratio, but ETFs and mutual funds do	Any investment information aggregator (e.g. Morningstar)
10	Avg capital gain distributions	The average over the past ten years, as a percentage of the value of the fund's share price, that a fund has incurred in capital gains by selling investments within the fund when rebalancing. Fundholders owe taxes on this amount just from holding the fund. I assume that past capital gains distributions are mostly predictive of future capital gains distributions. Most capital gains distributions are taxed as long-term capital gains.	Certain investment information aggregators, such as Marketwatch. You can compare the capital gains distributions column with share price for each corresponding year to find the average. One way to imperfectly 80-20 this is to paste the cap gains distributions table from Marketwatch into a large language model and tell the model "Please find average % cap gains distributions taking the second number as cap gains distributions assuming the average share price of $X. If the number is a dash, that means there were 0 cap gains distributions." Where X is roughly the average share price of the last ten years.
11	Net Investment Income Tax	If the filer's modified gross adjusted income for the tax year will be at least $200k if a single filer and at least $250k if filing jointly, they will pay an additional 3.8% in federal income tax	Forecast the assetholder's modified adjusted gross income for the year. The easiest way to do this is to look at the assetholder's tax return for the last few years, to check any capital gains from asset sale, and the capital gains distributions and dividend yield percentage of the largest investments in the accountholder's portfolio.
12	Medicare IRMAA payment rate	If the filer qualifies for Medicare (is at least 65) and has a modified gross adjusted income for the tax year at least $103k as a single filer or $206k as a joint filer, the individual will start paying more in medicare taxes. These higher rates fully kick in at different income thresholds (i.e. it's better to have a $205,999 MAGI than $206,000 as a joint filer, because $206k would automatically cost you an additional $1677). But in practice it's really hard to forecast your MAGI because it depends largely on investment income which is highly variable. Assuming you have no ability to predict, this works out to be around a 2.2% additional tax on capital gains a if one spouse qualifies for Medicare and a 4.4% additional tax on capital gains if both spouses qualify for Medicare.	Forecast the assetholder's modified adjusted gross income for the year. The easiest way to do this is to look at the assetholder's tax return for the last few years, to check any capital gains from asset sale, and the capital gains distributions and dividend yield percentage of the largest investments in the accountholder's portfolio.
13	State capital gains tax	Varies by state, check your local laws. For example, in California, long term capital gains are taxed at the state level of ordinary capital gains. In Washington state, long term capital gains in excess of $250k in investment income are taxed at a 7% level	Looking up local laws
14	Dividend yield percentage	A stock or fund's annual dividend payments to shareholders expressed as a percentage of the stock's current price. Dividend payments are taxable, usually at the long term capital gains rate if the shareholder has held the fund for the majority of the last 4-6 months.	Dividend.com
15	Subjective discount	The amount you personally expect the holding to underperform your preferred investment less any ownership cost.   You might, for example, think that a fund that holds bonds or cash rather than exclusively equities might have a lower expected return.	A personal judgement call
16	Formulas
17	Annual opportunity cost of ownership	Annual amount (in percent terms) that you expect a given investment to underperform relative your preferred investment based on fees (Net expense ratio), taxes (Avg capital gain distributions and Dividend yield percentage), and any subjective discount based on the nature of the preferred investment	Net expense ratio +                                                                                                                                        (Avg cap gain distribution * (long term cap gains rate + state long term cap gains rate if applicable + net investment income tax if applicable + higher IRMAA payments if applicable + forgone income-capped benefits)) +                                                                                                                                (Dividend yield percentage * (long term cap gains rate + state long term cap gains rate if applicable + net investment income tax if applicable + higher IRMAA payments if applicable + forgone income-capped benefits) [assuming the shareholder has held the stock for >60 of the last 120 days, otherwise use marginal income tax rate]) +                Subjective discount - Ownership expenses of preferred holding
18	Breakeven number of years between selling and holding an asset	Breakeven number of years after which the opportunity cost of ownership for a given investment will overtake the capital gains taxes generated by the sale of an investment that has appreciated in value (and thus picked up unrealized capital gains). This is important for informing whether to keep the asset and wait for a step up in cost basis (thereby saving capital gains taxes on the sale of the asset but paying opportunity cost of ownership in the interim), or sell the asset now (and pay taxes on gains) but negate opportunity costs of ownership before a step up in cost basis.	((1 - (Cost basis / current market value)) * (long term cap gains rate + state long term cap gains rate if applicable + net investment income tax if applicable + higher IRMAA payments if applicable + forgone income-capped benefits) [if held for >1 year, otherwise use marginal income tax rate]) / Opportunity cost of ownership formula [the above formula in cell C14]
19	Examples
20	Example 1:	Value	Notes
21	Asset: FDCPX. Purchased 3 years ago. Held by a 50 year old single filer with an estimated modified adjusted gross income of $50,000 living in Nevada
22	Assetholder's long term capital gains rate	15%
23	State capital gains tax	0%	NV doesn't have state income tax or capital gains
24	Net Investment Income Tax	0%	The assetholder is below the $200k single filer NIIT threshold
25	IRMAA payment increases	0%	The assetholder is not a Medicare beneficiary and would be below the $103k singe filer threshold for increased Medicare payments
26	Cost Basis	$3,817.51
27	Current market value	$3,906.35
28	Net expense ratio	0.70%
29	Avg capital gain distributions	8.13%
30	Dividend yield percentage	0.90%
31	Subjective discount	0%
32	Preferred asset cost of ownership	0.27%	This is the ownership costs (expense ratio, cap gains distributions, dividend yield of the investment you would reinvest money into
33	Annual opportunity cost of ownership	1.78%
34	Breakeven number of years between selling and holding an asset	0.19
35	Decision: Sell	Sell the asset now if assetholder's death is anticipated in >.19 years (or ~2.4 months). Otherwise hold
36
37	Example 2:
38	Asset: PRSVX. Purchased 6 months ago by a 60 year old single filer with an estimated adjusted gross income of $70,000 living in Washington state
39	Assetholder's long term capital gains rate	15%
40	State capital gains tax	0%	WA doesn't have state income tax
41	Net Investment Income Tax	0%	The assetholder is below the $200k single filer NIIT threshold
42	IRMAA payment increases	0%	The assetholder is not a Medicare beneficiary and would be below the $103k singe filer threshold for increased Medicare payments
43	Assetholder's marginal income tax rate	22%
44	Cost Basis	$2,637.52
45	Current market value	$6,026.95
46	Net expense ratio	0.82%
47	Avg capital gain distributions	5.70%
48	Dividend yield percentage	0.60%
49	Subjective discount	0%
50	Preferred asset cost of ownership	0.35%	This is the ownership costs (expense ratio, cap gains distributions, dividend yield of the investment you would reinvest money into
51	Annual opportunity cost of ownership	1.42%
52	Breakeven number of years between selling and holding an asset	8.74
53	Breakeven number of years between selling and holding an asset after waiting 6 months (when the asset hits the 1 year mark and qualifies for long-term capital gains)	6.46
54	Decision: Probably sell	Wait six months. Then sell the asset if the assetholder's death is anticipated in > 6.46 years, otherwise hold
55
56	Example 3:
57	Asset: VPMAX. Purchased 7 years ago by a 75 year old joint filer (married to a 72 year old) with an estimated adjusted gross income of $300,000 living in California
58	Assetholder's long term capital gains rate	15%
59	State capital gains tax	9.30%	The assetholder's marginal CA long term cap gains tax rate
60	Net Investment Income Tax	3.80%	The assetholder is above the $250k joint filer NIIT threshold
61	IRMAA payment increases	4.40%	The assetholder and their spouse are Medicare beneficiaries and this sale could potentially take them
62	Cost Basis	$10,000.00
63	Current market value	$23,000.00
64	Net expense ratio	0.31%
65	Avg capital gain distributions	6.56%
66	Dividend yield percentage	1.09%
67	Subjective discount	0%
68	Preferred asset cost of ownership	0.40%	This is the ownership costs (expense ratio, cap gains distributions, dividend yield of the investment you would reinvest money into
69	Annual opportunity cost of ownership	2.40%
70	Breakeven number of years between selling and holding an asset	7.67
71	Decision: It seems close given the age profile of both spouses	Sell the asset if the death of either the assetholder or their spouse is anticipated in > 7.67 years, otherwise hold	Worth considering the possibility that the step up in cost basis law may get overturned, which incentivizes immediate sale much more
72
73
74	Example 4:
75	Asset: VSMAX. Purchased 5 years ago by a 60 year old joint filer with a 55 year old spouse living in California whose adjusted gross income is $100,000
76	Assetholder's long term capital gains rate	15%
77	State capital gains tax	8%	The assetholder's marginal CA long term cap gains tax rate
78	Net Investment Income Tax	0%	The assetholder is below the $200k single filer NIIT threshold
79	IRMAA payment increases	0%	The assetholder is not a Medicare beneficiary and would be below the $206k joint filer threshold for increased Medicare payments
80	Cost Basis	$1,078.41
81	Current market value	$2,956.79
82	Net expense ratio	0.05%
83	Avg capital gain distributions	0.00%
84	Dividend yield percentage	1.90%
85	Subjective discount	0.00%
86	Preferred asset cost of ownership	0.30%	This is the ownership costs (expense ratio, cap gains distributions, dividend yield of the investment you would reinvest money into
87	Annual opportunity cost of ownership	0.19%
88	Breakeven number of years between selling and holding an asset	78.14
89	Decision: Hold	Sell the asset if assetholder's death is anticipated in >78.14 years, otherwise hold
90
91
92
93
94
95
96
97
98
99
100