Here is my dividend growth portfolio that is housed in my 401k plan. The first table contains my dividend income for this year. A full table of my holdings and related columns are in the second table below. This is all real time – any change I make in my Google Sheet is immediately visible.
[iframe src=”https://docs.google.com/spreadsheets/d/e/2PACX-1vQ_XvqR4uG_jzBwg2Op1zVN4w8qELwkPWWVv2g-Hy2OnDGx1xq7M1p0zfYxBNTmfYYX6NNLHBoy07AS/pubhtml?gid=0&single=true&widget=true&headers=false” width=”100%” height=”1100″]
[iframe src=”https://docs.google.com/spreadsheets/d/e/2PACX-1vQ_XvqR4uG_jzBwg2Op1zVN4w8qELwkPWWVv2g-Hy2OnDGx1xq7M1p0zfYxBNTmfYYX6NNLHBoy07AS/pubhtml?gid=1484282576&single=true&widget=true&headers=false” width=”100%” height=”1000″]
- Purchased Shares: The shares I actually bought
- Shares Total: Total shares after dividend reinvestment / splits
- Cost: My transaction cost, including fees
- My Basis: “Cost” / Purchased Shares
- DRIP Basis: “Cost” / Shares Total
- Percent of Cost: “Cost” / sum(All Costs)
- Current Value: This will use Google Finance to get the price for the ticket * “Shares Total”
- Gain (Loss): “Current Value” – “Cost”
- Gain (Loss) Percent: “Gain (Loss)” represented as a percent
- Annualized Return: (“Gain (Loss) Percent” * 365.25) / (NOW – “Owned Since”). It won’t be perfect because each tax lot will have a different return, but it’s close enough.
- Current P/E (GAAP): This is another Google Finance call to pull the P/E. I haven’t decided if I’ll keep this, but it’s interesting.
- Percent From 52-Week Low: Using all Google Finance calls, this subtracts the 52-week low from the current price to determine the percent away. This can make a stock more interesting to me if it’s been hated by the market.
- Dividend: The annualized dividend – this is sourced dynamically from Yahoo Finance. I have a new method for getting this data, see below.
- Income: Dividend * Shares Total
- Percent of Income: Income / Sum(All Income)
- Yield On Cost: Income / Cost
- CCC Status: From David Fish’s “CCC” list, what rank from the list?
Importing The Dividend Rate I ran into an that I hadn’t seen before. I’m not sure if any internal change happened with Google Sheets or Yahoo Finance, but I couldn’t consistently get all of my dividend rates to populate, I would get a lot of “fetch errors”. For cleanliness I made another sheet and first got a comma separated list of all my tickers using this (this is set in B1):
=ArrayFormula(concatenate(‘2017_401K Portfolio’!B$2:B$53&”,”)) I think used the modified IMPORTDATA function to bring in a long column of my dividends.
=IMPORTDATA(CONCATENATE(“finance.yahoo.com/d/quotes.csv?s=”;,$B1,”&f=d”)) Here is a sample of that list: That spits out a list of the dividend payments, one in each cell. Then back on my main portfolio sheet I updated the dividend formula to map to the list of dividends that was parsed out by just 1 IMPORTDATA call.
=’Dividend Rates’!B$2:B$53 You can see the example of that in the screenshot below. I am hovering on the column for Apple’s dividend. The formula references the cell that I created in the previous step on the other sheet I created.
Unfortunately that is all deprecated at the moment. Yahoo Finance killed off their API and while I have created an API myself to pull the data, it is not complete and currently only can source the values from the U.S. Dividend Champion sheet created by David Fish.
Sample Income Calculator
[iframe src=”https://docs.google.com/spreadsheets/d/1icnxQhZ6K82MjUkx9E1CBfF1-5DQ-EjC_wCik3wwcrE/pubhtml?gid=2058274120&single=true&widget=true&headers=false” width=”100%” height=”1000″]
What I wanted to do was just get a rough ballpark of what might be possible through dividend growth investing. I have my assumptions on the right side of the table as follows:
- “401K Growth Rate” – every few years the contribution cap is raised for 401Ks, this is an adjustment upwards I am making to see how much more I might be able to contribute per year.
- “Dividend Yield” – I am making (perhaps boldly) that on average I will invest in dividend stocks paying an average of 3% yield.
- “Dividend Growth Rate” – This is my presumed annual average dividend growth rate in perpetuity.
- “EPS Growth Rate” – I set this to be equal to the dividend growth rate, higher than the dividend growth and overall payout ratios will drop, low than the dividend growth rate and eventually the dividend will be unpayable.
With those assumptions I will explain what each column means:
- “Year” – starting at 2015 and roughly my initial starting balance
- “Contribution” – This is the contribution amount from the prior year plus an additional amount based on my “401k Growth Rate” assumption. In plain english, this is the max amount that could be contributed per year, in theory.
- “Income Purchasing” – Equal to the “Contribution” x “Dividend Yield”
- “Total Income” – This is the sum of last years “Total Income” x (1 + “Dividend Growth Rate”) + this years “Income Purchasing”. Don’t let the math overwhelm, what this is saying is that what I expect to earn this year is what I earned last year, grow that amount by my assumed dividend growth rate and then add on top what I think this years income generation will be.
- “Portfolio Value” – This is my estimation of what a portfolio like this could be worth with the major caveat that overall the market will expand and contract multiples based on overall sentiment, this is not meant to be predictive. This is last years “Portfolio Value” x (1 * “EPS Growth Rate”) + this years “Contribution Amount” + this years “Total Income”. In other words, take what we had, multiple it by that earnings growth rate and add back in both what I contributed that year along with the income generated during the year.
- “Inorganic Growth” – “Income Purchasing” / (“Total Income) – “Income Purchasing”). What I wanted to know was how much of an impact does my work contribution matter? It makes sense that as time goes on, it is a smaller and smaller impact as the portfolio has grown and runs itself.
- “YoY Income Growth” – (This years “Total Income” – last years “Total Income”) / last years “Total Income” as a percent. Here I want to see how much overall income compounding is happening and how it lowers over time, eventually it would reach but not touch, the “Dividend Growth Rate”.
- “YoY Portfolio Performance” – (Take this years “Portfolio Value” – last years “Portfolio Value”) / last years “Portfolio Value” expressed as a percent. Given a steady market multiple (which will not happen!) gives a theoretical look at how the overall portfolio value should increase over time.
What this doesn’t include currently is any sort of employer match – I can adjust for that right now by making the max contribution per year to include the match I receive.