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.

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 (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.

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 Empower made that obsolete by automating the process every day.

Only brokers are making our jobs easier by adding new tools. But as long as I have multiple brokerage and alternative income streams, I’ll keep building my spreadsheet.

Photo by Christopher Sardegna via Unsplash

Craig Stephens

Craig is a former IT professional who left his 19-year career to be a full-time finance writer. A DIY investor since 1995, he started Retire Before Dad in 2013 as a creative outlet to share his investment portfolios. Craig studied Finance at Michigan State University and lives in Northern Virginia with his wife and three children. Read more.