Imagine the odd looks and blank stares that I receive when answering people about my hobbies with the following one-word reply…
I’ll admit that I am a bit of a spreadsheet nut, and despite great tools such as Mint.com and Personal Capital, I continue to track our family finances with spreadsheets. We have spreadsheets that track our net worth, budget, and a handful of other relevant information dating back to at least 2003.
Tracking the DGI Portfolio
obsession love, it was only natural that I would look to track my dividend portfolio with a spreadsheet.
Being a fan of the mantra “work smarter, not harder”, I usually try not to reinvent the wheel when it comes to my spreadsheets. Therefore I leveraged the great work from the team over at Two Investing when they shared their dividend portfolio spreadsheet.
With a solid foundation, I will then usually tweak the spreadsheet to fit my needs–which I have done slightly with my DGI spreadsheet.
All in all, I’ve been pretty happy with my spreadsheet.
However, there has been one gap that I had identified not only in my spreadsheet but also in how I was monitoring the progress of my DGI portfolio overall.
Gap in Tracking
As part of my monthly dividend income reports, I have been mentioning the amount of annual forward dividend income (AFDI) that has been added by new stock purchases–which for me is one of the exciting parts about those monthly reports.
However, I wasn’t recording or tracking that anywhere in my spreadsheet.
Nor was I tracking the annual forward dividend income generated by my reinvested dividends.
Likewise for dividend raises.
In all honesty, tracking dividend raises was a glaring gap in my process as it seemed I was not yet in-tune with the best process to monitor those raises before they arrive in the form of an increased dividend payment.
Work Smarter, Not Harder
When reading and commenting on blog posts across the DGI community, one of the things that I always keep an eye on is how other people are tracking and monitoring their progress.
Through that process, I was drawn to how Engineering Dividends is tracking his AFDI when he shared his recent dividend income report.
Check out his post and then scroll down to the comments.
You will notice that I shared the fact that tracking my dividend raises was a gap in my process, and something that I needed to improve upon. ED shared his recommendation on the importance of tracking where the AFDI is being generated. Being new to this space, I know that there is a lot to learn and therefore I definitely heeded his advice.
Seeing as how ED had already implemented a nice process for his portfolio, I reached out to him and asked a few questions. We exchanged a couple of emails and I asked if he was okay with me mimicking his process.
New and Improved Tracking
The spreadsheet that I am using has a fair amount of automation built-in so all I typically have to do is enter in transactions, such as a new buy or dividend, and everything else is updated accordingly.
The first step was to convert my dividend payouts into the equivalent forward dividend income. The tab that summarizes my dividend payouts is automated but it records the full dollar amount paid out. I decided to copy that tab and adjust the formula to calculate the forward dividend income generated by that dividend payout.
This new tab looks like this:
As an example, the $1.72 in forward dividend income from Cracker Barrel was from the $50.00 dividend payment in August.
The next step was to improve (or maybe I should say start) tracking the dividend raises.
Tracking the dividend raises is a more manual process, as it relies on monitoring the news / notifications on each holding to see when they have announced a dividend raise. I am currently doing that via SeekingAlpha, and have also leveraged the dividend history available on the Nasdaq.com website.
Here you will see that I have utilized the same format that ED has used, as why fix something that isn’t broken.
You’ll notice an extra section of information off to the right in the screen shot above.
I’ve added that to record what month each holding has typically announced their dividend raises, and then I have two columns that show the annual and periodic dividend payments for the month of July.
As my spreadsheet automatically updates the dividend forecast when there is an increase, I am going to manually do a cross-check every month just to confirm that I have not missed any updates that were announced. This probably isn’t necessary but will be an additional sanity check just to be sure.
The last step is to track the annual forward dividend income from the new capital invested in the portfolio.
As I am already calculating this as part of my monthly recap, I am going to simply add that value into my cumulative view of the AFDI on an annual basis.
Below is what this looks like–which is just like ED’s report with one addition.
The top section of the table is formatted just like ED has done. On a monthly basis it records the amount of forward dividend income received from reinvested dividends, dividend raises, and investment of capital.
The addition that I made is in the bottom section of the table.
To truly appreciate the value of dividend growth investing it helps to see how much new capital would have to be invested to obtain the same amount of forward dividend income received from the reinvested dividends and dividend raises.
With a quick calculation that references the current average dividend yield of my entire portfolio, I am calculating that on a monthly basis.
As an example, for the month of July, I received an additional $3.78 in forward dividend income from the dividends that were reinvested and $4.33 from dividend raises. That equates to requiring a capital investment of $294.92 combined.
That might not seem like a lot, but that is nearly $300 in value that I received for doing absolutely nothing.
Talk about passive income!
The best part is that as I continue feeding the dividend machine, this number will continue to grow. Over time, the goal is to see that level of equivalent capital exceed the amount of capital I am investing.
At that point my money is working harder for me than vice-versa.
While I had been pretty happy with my portfolio spreadsheet before making these changes, now I feel as though I have addressed the one gap that I knew was there.
With the additions to track the annual forward dividend income generated from reinvested dividends, dividend raises, and new capital I will be able to monitor this over time and include this in future monthly dividend income reports as another metric.
I’d like to thank Engineering Dividends again for the time to help answer my questions and willingness to share his approach.
That level of transparency and camaraderie is something that is truly special about the DGI community!
What do you think of these additions to my tracking?
Are you tracking your annual forward dividend income from each source?
17 thoughts on “Tracking Annual Forward Dividend Income”
Great way on how to track dividends. I have been trying to find something similar! I am glad to have stumbled upon this post! Also, congrats on the amazing months Dividends received.! keep up the amazing job.
Thanks for stopping by Dr. D!
I am happy to hear that you like this approach, and I owe a nod to Engineering Dividends as I’ve simply implemented his approach to the tracking of the AFDI.
Appreciate the feedback as well, trying my best to build this dividend machine!
A couple thoughts DD….
1) I’m a spreadsheet guy too. I think it’s good to put your hands on the data. It’s the accountant in me, I guess.
2) I focus more on tracking forward income. It’s nice to know where I’ve been, but I like to concentrate on where I’m going. I do track it by each dividend stock holding, however, I don’t track it by month. I just track my annual forward income.
You’re right Tom, and that is the same rationale for why I do my own taxes too. I think having my sleeves rolled up and hands on the data helps me understand things much better. Maybe I went into the wrong field, haha.
I have been tracking the forward income on each holding in my portfolio, but was missing the piece of how that changes over time. You’re right that it is largely a look back at where I’ve been on a month to month basis, but I also like to think of it as a record of what I did that worked (or didn’t work) to get me to where I am going.
Hey DivvyDad! Wow, you’ve taken the spreadsheets to another level. I like the pulldown so you can select your stock when adding an entry from another dividend payout.
It will be terrific to see the tracking take place over the coming months/years. I think the numbers will be revealing and give you a good sense of how much your portfolio is working for you.
In the Step 3 table, I see you’ve got the reinvested dividends carried forward from Step 1, and that you calculate the equivalent capital for those reinvested dividends. It looks like you are using an average portfolio yield of 2.75% if I’m not mistaken. I was wondering why you didn’t choose to list the actual capital used to generate those reinvested dividends instead of calculating the equivalent capital. The actual number should be available in Step 1, and would just be the dividends collected for the month, assuming you reinvest all paid dividends. On an annual basis, the equivalent capital number would probably closely track the actual number, but on a monthly basis there’s likely more variance depending on the yields of the stocks getting the reinvested dividends that particular month. Let me know what you think about this.
It’s awesome that you’re tracking this from the get-go for your portfolio, as doing it at a later time would make it more difficult… as I’m finding out.
Excellent work! Thanks for sharing the details.
Thanks ED, I appreciate your feedback and help with these additions. The drop downs are great, and I have my spreadsheet setup where every tab that references the stocks in my portfolio has the drop downs that read from one master list. If I add a new holding, I just need to add it to that list and it is available everywhere else.
I am really happy to have this all setup too, and am excited to have these metrics to track as I move forward on this journey.
Re: Step 3, first, you are correct with the yield. My total portfolio currently has an average yield of 2.75% (with a YoC of 3.99%) that you can see on my Portfolio page. The calculations are set to automatically reference that average yield–which now that I think about in hindsight I may need to change otherwise it will skew the numbers as the yield changes. Going to have to look at that now.
To your questions about the equivalent capital, what I am trying to capture / represent there is how much I would have had to invest in new capital to equal the amount of forward dividends received from the reinvested dividends and raises at the average yield. If I am following you though, as an example, the $3.78 that I have listed for July reinvested dividends was earned from the $108.75 in July dividends, as opposed to the $137.36 shown in the bottom section. The reason for the difference is because the calculation in this table uses the average yield, which in some cases will be higher / lower than the yield of the stocks that actually paid the dividend.
I might have to think on this a bit more too, as my intent was to highlight how these little increases to the forward dividend income add up to where they would require a substantial capital investment to equal what is done passively.
Maybe I should drop that line and only track the equivalent capital from raises, as that does not involve receiving dividends or adding new capital.
I’ve just tinkered with the spreadsheet, and I removed the auto-calculation with the lookup of the current average yield. As that will change on a month to month basis, I don’t want the old calculations to adjust when the average yield changes in the future.
Also, I decided to add an additional line to the bottom of the table to show the actual capital of the reinvested dividends (what I truly received in dividend dollars) and then kept the line that shows what the equivalent capital would need to be based on the average yield of the portfolio. This will let me compare what I received to what I would need to contribute, and whether a particular month received dividends from higher or lower yielding stocks.
I follow you. These sound like good adjustments, DivvyDad.
Thanks again ED!
I think the changes to your spreadsheets are amazing. Like Engineering Dividends said, you’ve taken them to the next level. On that note, I also get inspired when it comes to ED’s reports. I am tracking forward dividend income. I am tracking individually, but not as detailed as you and ED are. I do track dividend raises and how much income is added each month, but not the specific amount that is reinvested. Thanks for sharing the steps you took to make the changes. The charts look great!
Appreciate you stopping by and for the feedback RTC! Adding these features to my spreadsheet has made the process of tracking dividends even more exciting. I’m looking forward to the coming months and reviewing these new metrics.
I just focus on tracking forward income too. That looks like a great spreadsheet by the way. Super colour coded/ multi-coloured. They look great!
Thanks GYM, appreciate you stopping by and for the feedback on the spreadsheet!
I have a personal question to ask you about DGI Tools (and portfolio tracking) is there a way to get in touch with you by email?
I’ve sent you an email Michael and look forward to hearing back from you!
I too am just starting out on my DGI plan and have just last night started populating the spreadsheet provided by twoinvesting.com with my limited holdings.
My question is are you able to supply the formulas for the AFDI as I am NOT a spreadsheet guy 🙁
Any help would be awesome.
Do you mean the formulas that I have in the table that I shared here in this post? You’ll note that this is not part of the original spreadsheet and is something I have added; but the original does calculate the forward income on a total basis and you can see that on the Portfolio tab.
For my table here, I actually duplicated the DivCalendar tab and then made a change to the formula that they use. Essentially I changed it to just retrieve the buy transactions that are less than 5 shares, as the dividend reinvestments are fractional shares that are often less than a single share. Eventually this could become an issue once you are producing large enough dividends that buy more than 5 shares, but I figured that would take quite some time.
Here is the formula I used on the new tab I created:
For the dividend raises, I am not using much of a formula as the table I use to track my raises simply records the old dividend amount, new dividend amount, and then calculates that based on the number of shares that I own at the time.
Let me know if the above helps, and I will shoot you an email as well so you can contact me off the blog too.