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.
22 thoughts on “Forecasting Projected Annual Dividend Income”
Spreadsheets is the key to keep me focused on long run. I believe that you can tapping on dividends from Roth IRA without taxes? If yeah, then why not include amount from Roth?
Thanks Flyer. The ability to withdraw dividends from the Roth without taxes depends primarily on your age; before the age of 59.5 you’re only able to withdraw contributions without incurring any penalty or tax. As my target is to retire at 52, but am contemplating a push to 55 to open my 401(k) earlier, I would not be able to withdraw dividends.
Also, the IRA mentioned here that holds my DGI stocks is a rollover IRA, which is a Traditional IRA. So that money will be taxed.
The step-chart, here we go 🙂
Nice edition! I also like that you added the organic growth visualization.
I couldn’t agree more with you how imprtant it is to start investing at a young age.
This is basically what this step-chart is visualizing. Make use of the power of compounding and the small steps in the beginning will convert into big steps in the end.
Thanks for featuring me in your post.
Thanks again for the inspiration and help Alex!
I may have to mock up this chart for my younger son; we just opened him a custodial Roth IRA and I have been encouraging him to pick some DGI stocks. So far he picked one DGI and one that does not pay a dividend, BAC and AMD respectively. He may be blown away by what he sees since he is starting at 17.
I have played with my dividend forecasts like this for years DD. It’s really motivating to know your dividend income will almost certainly grow while the market does whatever it is going to do. Tom
I can imagine Tom, and I am sure your forecasts are looking great! It has certainly motivated me as the more we can fund our retirement income from dividends alone, the less risk we face from sequence of returns risk (at least in my opinion) and the more conservative we can be if we do have to withdraw any capital.
Nice work DivvyDad – I love a good spreadsheet and some fancy charts especially! It’s great fun building forecast models to see what the future might hold!
Thanks Frankie, now if only I had your amazing illustration capabilities to go along with it then it could get really fun!
I love excel too, nice analysis!
Wow. First of all I love it. The spreadsheet is great and the numbers are definitely motivating.
This may be slightly unrelated but not really. When I got out of credit card debt it was because I had a spreadsheet that allowed me to graphically visualize my payments all the way to zero. It gave me great motivation to go forward. Your spreadsheet does the same thing but I’m reverse. It allows you to vizualize the income you’ll have by your target date. Like I said, I Love it and may look to do something similar. I’m afraid of how little the numbers will be when I do the calculations though.
Thanks for the motivation and good luck with achieving your goals throughout the years DD.
Thanks DP, and I think your anecdote is spot on actually. We did the same thing years ago when we were eliminating our non-mortgage debt, and I think the act of putting it down on paper (or electronically in a file) gives that structure to things that adds the layer of motivation / commitment that can be lacking otherwise. The great thing is that it can work in many scenarios, as you have mentioned.
I hope you do create something similar, and even if they are little numbers, remember that little numbers become big numbers over time!
Very nice work DivvyDad! I just discovered your blog and there is so much to see. I also love graphs and spreadsheets and I should make a similar graph like you, to see, where I’m and where I want to be going. Nice idea, thank you!
And btw. nice cost base on your three top holdings – that’s also very motivating for me to see. Just buy and hold and let them grow.
Thanks for stopping by DivRider, I will have to stop by your site and check things out as well. I’ll keep an eye out for something similar mapping out where you are and where you’re going.
I definitely lucked into those three holdings, at least from a dividend perspective, as I bought them long before I was ever even contemplating a dividend growth strategy. Fortunately I now have a very appealing cost basis. Time in the market is definitely one of our best friends.
The chart looks great and sounds easy to create. I’ll have to give it a go.
Looks like you will be living comfortably in the future, DivvyDad! It’s inspiring to see how the numbers can grow over the years.
One suggestion for possible enhancement is to add an inflation adjustment factor so that those future dollars can be adjusted to reflect today’s purchasing power.
Thanks for sharing your numbers.
Thanks ED, and it was really easy to create once Alex had shared the right chart to use. Give me a shout if you have any questions if you do give it a go.
The numbers were really encouraging and it surprised my wife as well to see what is possible to achieve. Thanks for the suggestion, I think that would make a nice addition to this to include inflation.
The chart is great. It really helps better visualize the progress being made and it inspires us to make every dollar count. As a side note, I just started a free trial of SSD. I really love the features of it. I may eventually get a subscription. The fact that it weights the 5 year dividend growth rates in your portfolio is so fantastic, not to mention the other data. So much relevant information in one place blows my mind.
Thanks Kody, happy to hear that you like the chart.
Also, as you noted, I have found SSD to be a fantastic resource as it really pulls so much information together into one place for easy access. While it is true that you can compile much of that information via free sources, there isn’t one that has it all pulled together and that can save so much time. In addition, their analysis and research notes are quite thorough and helpful.
I actually just dicked around with a calculator with our current dividends a 8% over dividend growth rate and a extra 1000 to forward dividends each year based on new capital.
It was pretty cool to see and really shows ya a) start asap b) get dividend growth stocks.
love the step chart! keep it up and you will surpass those numbers
Thanks PCI, it is fun to screw around with the numbers and projections isn’t it? I haven’t looked at taking my own scenario back to see what it would be had I started earlier, partly because that is water under the bridge at this point and partly because I don’t want to cry, lol.
However, I continue to show this to my youngest son who is very interested in investing and showing him what can be accomplished even with the small amounts he has to invest now while he is still in high school.
Looks like a very fun chart to create, being a data nerd is something I can heavily relate to haha! Nice work and I can’t wait to see this same chart in five years time!
Most definitely DI, we share that data nerd tendency! I’m looking forward to seeing this chart in five years as well, and will be challenging myself to get that green step moving higher and faster than the blue projection!