You can call me a data nerd, or anything else for that matter, but it is no secret that I love spreadsheets.
Recently I was reading Alex’s dividend income report over at Snugfortune and had commented about the step-chart that is tracking the projected dividend income vs. actual received dividend income.
Not only does this chart demonstrate the projected vs. actual for current and past years, but it also provides a forecast of the projected annual dividend income (PADI) moving forward.
Naturally, I had to give this a spin within my own spreadsheet.
Setting Up the Data
At the core, the step-chart is pretty basic as you simply have a column for the year, projected annual dividend income, and then the actual dividend income received.
Take that data range and create a stacked step-chart.
Easy-peasy (now that I know which chart to use thanks to Alex).
Within Simply Safe Dividends, I already have access to a great feature that demonstrates the dividend growth potential of my portfolio as shown below. They utilize the 5-year average growth rate of all positions in my portfolio to create the forecast.
Here is an example of what that looks like:
The one thing that this is lacking is the ability to include the impact of ongoing capital investments.
While it is great to see what the organic dividend growth potential is at any given time, I am continuing to invest capital on a regular basis and that will drastically change the forecast.
Therefore, as I added this capability to my spreadsheet to forecast projected annual dividend income, I added two fields that I can easily adjust to monitor how they impact my long-term results. The two fields allow me to change the dividend growth rate as well as the dividend increase based on new capital.
For the growth rate, I have used the same 5-year average as reported by Simply Safe Dividends.
When it comes to the increase in PADI due to new capital investments, I based that on my actual results from last year–actually slightly more conservative from my actual results. There were two months that were outliers, but on average my capital investments were adding approximately $186 to my PADI, or $2,232 annually.
As I will show below, my initial forecast is using an increase in PADI of $2,000 from new capital.
Alright, enough of the background as it is time to check out the forecast!
Dividend Crystal Ball
Since I just started my portfolio in 2018, I am essentially initiating this forecast based off of my 2019 goals to collect $4,500 in dividends from my brokerage account. The 2018 numbers are included for historical reference of my starting point.
Using the average growth rate of 9.4% and $2,000 annually from new capital, here is what my forecast looks like:
If the crystal ball is correct, when I reach the age of 55 I will be collecting just shy of $48,000 per year in dividend income!
In the chart above, the purple line crossing the chart represents the organic growth of my portfolio if I were to stop making new capital investments moving forward. The blue line represents the potential growth based on my average growth rate and on-going contributions–which as you can see begins to take larger steps up as time goes on.
Lastly, the green line and shading represents my actual dividends received.
For last year, out of the $2,815 in dividends that I received, $1,945 were from my brokerage account. I’ve opted to only include my brokerage account in this chart as that will be the money available to me in early retirement.
Here is a look at the data that supports that chart, and shows the specific numbers:
In the table above, I included data out until the year I turn 65 to demonstrate how quickly that can start to grow as you age.
However, as I am planning to begin cashing out dividends to supplement, or even better fully fund, our retirement income, I know that it will not continue to grow and compound at that rate because I will no longer be reinvesting all of my dividends.
For those of you that are younger, hopefully this adds additional motivation on how important it is to start young and invest consistently.
Certainly the forecast above has a couple of key assumptions:
- Dividend Growth Rate: There is no telling how the dividend growth rate will change over time, however by using the 5-year average growth rate from my portfolio I am able to smooth out the year to year swings.
- Impact of New Capital: While I am certainly planning to continue adding new capital at the rate that I have been, I know there are a couple of significant financial events in my future this year and that may impact our savings rate. First, my wife is starting a new job that pays slightly less and has her working about half the hours. Second, our youngest son will be starting college in the fall.
Changes to one or both of these metrics will directly impact this forecast.
However, I can now quickly and easily change those numbers and see what that impact might look like. For instance, if I reduce the amount of capital that I can invest and only produce an additional $1,000 in PADI per year, my forecast changes to approximately $30,000 in PADI in the year that I turn 55.
Or if I maintain the level of capital but my growth rate drops to 6.0%, I would be forecast to collect about $38,000 when I am 55.
While these numbers may change as time progresses, I like having this forecast in my spreadsheet as it provides another way to monitor and measure the progress of my dividend portfolio.
It also shows me that if I were to delay retirement until age 59, we would be forecast to have more than enough to simply live off of the dividends produced by all of my accounts without touching the principal at all. We could actually do it sooner if I was able to access those funds at an earlier age.
What do you think about this step-chart and dividend growth forecast?
Please let me know if you notice any errors in my calculations as well, and I’ve already thought of a couple new tweaks that I might make to this chart in the future.