How I Track Dividend Income in Excel

In the almost five years of sharing my investments on this blog, I’ve never shown exactly how I track dividend income.

This article lays out the steps (with screenshots) I take each month to import dividend data into my tracking spreadsheet, estimate forward dividend income, and easily capture dividend increases.

My forward 12-month investment income (F12MII) is a single number that represents how much I earn annually from dividend stocks, my rental property, interest on cash, and real estate crowdfunding.

I share the F12MII number on my portfolio page.

My goal is for the F12MII number to eventually cover most of our essential living expenses before I retire by age 55. That way, we can live off of a sustainable income stream and use retirement accounts for discretionary spending.

The job of tracking dividend income just got a little bit easier. My primary broker, TD Ameritrade, has a new tool to help predict annual dividend income automatically. At the end, I’ll share a few screenshots of the tool.

I’m also using the no-fee online broker M1 Finance (read my review here) to further diversify my income streams. It’s perfect for beginner to intermediate investors. 

How to Track Dividend Income with a Microsoft Excel Spreadsheet

The way I track dividend income has remained mostly the same since 2003, but I’ve adapted my spreadsheet to account for new income streams and to automate some tasks.

I pay to use Microsoft Excel because I like it and use it so much. Google Sheets is free and sufficient for most, but I still prefer Excel.

I’ve set up the spreadsheet to automatically calculate my monthly and annual dividends in the expected month of payment. All I have to do is retrieve a dataset and add or remove any stocks when my portfolio changes.

Since I’ve always had multiple brokerage accounts, I keep a consolidated portfolio view on Yahoo Finance. That is where I create the dataset for input into my spreadsheet.

I’ve been using Yahoo Finance since about the first time I tried Netscape in 1995. Here’s the step-by-step process to track dividend income using Yahoo Finance and a spreadsheet.

Step 1 – Create a Yahoo Finance Dataset

Yahoo Finance My Portfolios is a free functionality that allows you to track the stocks in your portfolio, the number of shares you own, and the cost basis for each holding. You can manually input the data tax lot by tax lot, or just copy the total shares and cost basis from your regular brokerage.

They can even link to your online broker now, but I have not tried that functionality yet.

Start by creating a custom portfolio view, or click Create New View. Pick all the fields you want to capture for your spreadsheet. I customized a portfolio view called Performance. Here’s what it looks like:

You can see all the fields I want on the top row. Most importantly, I track shares, forward dividend rate, and forward dividend yield. These fields will eventually be used to calculate F12MII.

Next, click the Add Symbol tab to input all the stocks you own. You can add multiple symbols at a time.

Once all the stocks are listed in the portfolio, enter each tax lot purchase or the total shares (required), cost basis (not required), and trade date (if you like).

Click the My Holdings tab, then check the box next to the holding you want to add shares and cost basis for.

Do that for each of your holdings and you’ll have a functioning live data source.

Once your portfolio is complete and saved, you can copy the numbers as a dataset.

Click the name of the portfolio view you’ve created (mine is Performance) so that the data is refreshed.

You have the option to export the data to a .CSV file. That’s never worked right for me. Yahoo Finance does not export my customized view. It exports a default view.

So I just use my mouse to copy the table, all the way down to the bottom of the portfolio. The copied data is highlighted.

And now the dataset is saved to my clipboard.

Seeking Alpha, The Motley Fool and some others have similar free portfolio tracking portfolio tools. Experiment to see what works for you. If you only used one online broker, you should be able to create a sufficient dataset for export within your account.

I use the same technique to update the Dividend Aristocrats Ranking list and the Debt-Free S&P 500 resource.

There are also ways to get live quote data into Excel with APIs and code. I’ve experimented with these, but never invested the time to commit to it.

Step 2 – Paste the Dataset into Excel

The next step is to get your dataset into Excel. Now that it’s already on your clipboard, open an Excel sheet and do a Home/Paste Special/As Text. Or simply right-click and paste as unformatted text.

Pasting without formatting will clean the data, removing any unwanted links or tags.

In my master spreadsheet, I have a tab called Raw, for raw data. I paste it there. The first time you paste the data, capture the field headings too. But after that, you only need the stock symbols and data.

The most important fields are the Shares and DIV/Share fields.

Step 3 –  Build Out Your Spreadsheet

Now that you’ve created a Yahoo Finance portfolio, update it every time you buy or sell a stock so the table is always ready to import into your Excel workbook.

If you build out your spreadsheet properly, pasting the data will automatically update the rest of the spreadsheet.

Of course, building out your spreadsheet takes the most time. But once the framework is set, changes are only needed when you buy a new stock or completely sell a holding.

I’ve got a bunch of different tabs along the bottom of my Excel workbook, each serving a different function. But the main two tabs are the Schedule and 2018 Received tabs. Those are where I track F12MII and income received for the year.

Here are the tabs on my spreadsheet and a brief description of each:

For the Cash, Rental, LC (LendingClub) and RE CF (PeerStreet and Fundrise) tabs, I still update them manually each month. The data all feeds into the Schedule tab. We’ll look at that next.

Step 4 – Create a Dividend Income Schedule

I’ve seen dozens of dividend bloggers create a similar schedule for tracking dividends. Everyone has their own version. Whatever works for you is fine.

The Schedule tab is where I combine all of the dividend data that comes from the Raw data tab, which was retrieved from Yahoo Finance. I first use the Input Data tab to clean up the Raw data, making it easier for the Schedule tab to digest.

I use the Schedule tab to track dividend income increases and scheduled payments.

Each year I start with a fresh page noting the beginning dividend amount (Start Div). That’s a static number.

Then the current dividend amount (Curr Div) is linked to the Input Data tab. Using these two columns, I can identify when a company increases their dividend automatically. When Yahoo Finance updates the dividend, it’s captured in my spreadsheet.

The Div Δ column calculates the percentage increase/decrease. Conditional formatting turns the cell green or red to highlight an increase or decrease.

By tracking and highlighting increases this way, I don’t have to watch the news releases for dividend increases. They just show up here the last day of the month.

I also add the dividend increase date from the previous year so I know when to expect the next increase for a company. The final two columns are Per Div (dividend amount per period), and # of Shares (linked to Input Data).

I then multiply # of Shares by Per Div in the month when the dividend payment is expected. The dividend payment date is usually paid quarterly.

You can find the month of expected dividend payment on the Yahoo Finance Statistics page. Here’s an example for ABT, the next expected payment is on August 15th.

I copy the same formula to the three other months the dividends are expected. For ABT, that’s November, February, and May. Do this for all holdings. It’s only required the first month a new dividend stock is purchased. After that, it updates when I dump a new dataset into the spreadsheet.

The expected forward dividend income is then totaled in column “T“.

Lastly, at the bottom, I link to the Cash (Capital One 360 accounts), Rental, LC, and RE CF tabs. The estimated forward monthly income amounts from each tab are added, then everything is summed at the end, giving me a grand total at the very bottom.

That’s the F12MII number I update readers with every quarter. These values are carried over to the Received tab.

Step 5 – Create a Dividends Received Sheet and Chart

Inspired by other dividend bloggers, I started to track dividend income received in 2015. This tab simply contains the dividends I receive each month plus other income streams. I still gather this manually, but the data is easily retrievable from my online brokers.

Here’s a look at the bottom part of the tab showing income received for stocks and other income streams:

Once the numbers for the month are complete, I copy rows 54-59 for the latest month over to another table which I use to create the chart I share each quarter.

You may notice a recent downward trend. This last quarter was disappointing. I’ll fill you in on what happened in the next quarterly update in early July.

If you’ve read this far and would like to check out my spreadsheet, here’s it is. I’ve sanitized it and added dummy data to show how it works.

Track Dividend Income with Google Sheets

If you prefer the free Google Sheets over Microsoft Excel, all of what I’ve done in Excel can be replicated in Sheets.

Google Sheets is also cool because certain fields can update with live data using the =GoogleFinance function. However, data options are limited.

I used to use Google Sheets to embed spreadsheets on this website. Just copy the Excel data into a Google Sheet and paste the embedded code into a WordPress installation.

The portfolio table, Dividend Aristocrats Ranking tool, and the Debt-Free S&P 500 List are no longer using Google Sheets. I now use a WordPress plugin called Tables by Supsystic.

Live data from Yahoo Finance used to integrate well with Google Sheets, but that functionality no longer works. There’s another way to do it now.

If you’re looking for a spreadsheet to track dividend income for your own portfolio but don’t want to create your own, I recommend checking out this one from TwoInvesting.com. The curators of the blog and spreadsheet have built an awesome free tool with live data integration. They explain how it works better than me.

It takes some techie stuff to get it set up. But the instructions are good, it works well, and they update the tool when needed. Check it out.

The Income Estimator by TD Ameritrade

After all these years being a TD Ameritrade customer and tracking dividend income in my own spreadsheet, TD Ameritrade built a new Income Estimator tool to do the job for me.

If you have a TD Ameritrade account, just go to the Stock Profile page of any stock. You’ll find a link to it near the top on the right side.

Click the Income Estimator link. A new browser will open. You can choose your holdings, a watch list, or any individual stock. For this example, I’m using my actual portfolio.

The tool creates this view of forward 12-month dividend income:

Further down, each holding is listed with helpful data. The quantity field can be modified to simulate a purchase or sale.

This tool was launched toward the beginning of January. I’m a big fan of this and plan to use it more frequently. Hopefully, I can find a way to integrate this into my spreadsheet. Or at the very least, include in my quarterly income updates.

Conclusion

Fellow spreadsheet wizards may find article really interesting. The rest probably didn’t make it this far.

Spending the time to track dividend income is a long-time hobby of mine. I like measuring my progress and estimating forward income. It’s one of the foundations of my retirement plan. My way isn’t the fanciest, but it gets the job done.

But lately, I’ve been questioning whether it’s necessary every single month. I’m considering only updating my spreadsheet every quarter instead of monthly starting next year.

I used to track my net worth every month until Personal Capital made that obsolete by automating the process every day.

Tools like the TD Ameritrade Income Estimator and may end up doing the same. But as long as I have multiple brokerage and alternative income streams, I’ll keep building my spreadsheet.

Photo by Christopher Sardegna via Unsplash