Many people take a set-and-forget mentality when it comes to investing, but some cases require closer attention. For example, retirees may want to project how much they can expect to earn from their portfolio if they rely on it to cover retirement expenses. And they may factor the timing of those cash flows into their monthly budgets.
Let’s take a look at what’s involved in tracking a dividend portfolio, how to build your own spreadsheet, where to download done-for-you spreadsheets, and some popular alternatives to make your life easier.
How to Track Your Dividends
Many brokers and apps provide tools to track a stock portfolio, but they typically focus on measuring capital gains rather than income. While they may include some dividend-focused tools, they’re usually designed to compare yields or project dividend income.
Many income investors want more dividend-focused capabilities like:
- Calculating how much annual income they generate from a dividend portfolio for retirement planning or tax purposes.
- Estimating how much annual income they can expect given annual contributions, dividend growth, and price appreciation.
- Calculating their dividend portfolio’s yield for comparison with other fixed-income options like bonds or covered calls.
- Setting up alerts when dividends are announced or paid.
As a result, many income investors end up building their own spreadsheets or using off-the-shelf tools to help them get the information they need.
Building a Spreadsheet
The simplest option is creating a spreadsheet.
While Microsoft Excel is the most popular spreadsheet application, Google Sheets provides built-in access to real-time financial data, making it easier to keep updated. You can also access Google Sheets from any device in the cloud.
Here’s how you can create Google Sheet to track your dividends:
|Column Name||Google Sheets Function*|
|# of Shares||(User Input)|
|Cost per Share||(User Input)|
|Last Price||=GOOGLEFINANCE([Symbol], “price”)|
|Total Cost||=SUM([# of Shares]*[Cost per Share])|
|Total Value||=SUM([# of Shares]*[Last Price])|
|Gain/Loss||=[Total Value]-[Total Cost]|
|Annual Income||Annual Income – =GOOGLEFINANCE([Symbol], “price”)*GOOGLEFINANCE([Symbol], “yieldpct”)/100|
The biggest advantage of building your own spreadsheet is that you can customize it to your specific requirements. For example, you can easily extend our example above to include the average yield or even calculate your own “risk-adjusted” yields.
The GOOGLEFINANCE Google Sheet function offers several attributes, including everything from price-earnings ratios to Morningstar ratings. There are also dividend-focused options like “incomedividend” and “incomedividenddate” to see the latest dividend amount or distribution date, respectively. You can browse all these options in the documentation.
If you prefer to use Microsoft Excel, Microsoft 365 offers Stock data types that provide similar functionality. However, the service only provides access to a limited set of price data and Microsoft 365 requires a subscription to access.
5 Downloadable Spreadsheets
Many people have built dividend tracking spreadsheets that you can copy or download rather than building your own spreadsheet from scratch.
Some of the most popular ones include:
- Dividend Stock Portfolio Tracker – TwoInvesting.com provides a free Google Sheet that you can copy and customize for your own purposes. You can input transactions on the Transactions tab and then see a detailed portfolio summary. In addition, you can use the dividend payout calculator to see distribution dates.
- Dividend Tracker – FamilyFinance sells a comprehensive Microsoft Excel-based dividend tracker via Etsy. After entering your portfolio details, the spreadsheet automatically creates your monthly dividend payment graph and shows you your income levels. You can also see how much of your expenses you’re covering with dividends.
- Portfolio Tracker – DividendEarner.com shows you how to build a comprehensive Google Sheet for tracking your dividend portfolio. In addition to the standard tracking features, the spreadsheet includes a sector diversification tool and a way to track all transactions, including purchases, sales, dividends, and DRIPs.
- Dividend Yield Spreadsheet – Genymoney.ca offers a simple, free spreadsheet to track your dividend stocks. You can see your capital gain or loss, adjusted cost basis, and other metrics. While it’s not as fully featured as some of the other options on our list, it may be a perfect fit for someone looking for a basic tracking tool.
- Dividend Tracking Spreadsheet – WealthCapitalist provides a detailed tutorial for creating a comprehensive dividend tracking spreadsheet in Google Sheets, but if you want a done-for-you version, you can download a template at the end of the post.
While these are more comprehensive alternatives to a DIY approach, you may find them more difficult to customize or adapt to your specific requirements. And some options may rely on external services (e.g., data providers) or require data subscriptions. And finally, not everyone enjoys spending time building a spreadsheet to meet your needs.
Alternatives to Consider
Spreadsheets are an excellent way to organize information and provide limitless customizations, but they can be difficult to create and become unwieldy over time. You also must remember to update spreadsheets with new portfolio positions to keep them up-to-date or configure complex workflows to automatically sync data from your broker.
TrackYourDividends.com eliminates the need to constantly update spreadsheets with new positions by connecting directly to your brokerage account. You can also easily configure dividend alerts and see upcoming dividend payments in one place. And best of all, the basic features are free for life – saving you time and money.
If you’re interested in more advanced capabilities, you can upgrade to the premium level for $9.99 per month or $99.99 annually. Rather than simply tracking your dividend payments or average yield, you can evaluate the fundamental and technical strength of each position and access ideas lists to help you find the next opportunities.
If you’re new to dividend investing, TrackYourDividends.com also offers a comprehensive dividend investing course to help you get started. The course covers everything from common dividend calculations, tax implications of dividends, and dividend portfolio construction.
The Bottom Line
Retirees and other income-focused investors often take an active approach when monitoring their portfolios. If your broker or other tools fall short, you can easily create your spreadsheet to track your positions, dividend yield, and income over time. But it’s easier to use off-the-shelf tools like TrackYourDividends.com that import transactions and offer more insights.