Dividend Stock Portfolio Tracker with Transactions Page
An international version of this dividend tracker with support for multiple currencies is now available.
To see a list of all my spreadsheets, including a 30-year dollar cost averaging model, click the Spreadsheets tab at the top of the page.
1. Dividend Portfolio Tracker with Transactions Page
For references to the importxml and importhtml formulas that I used in my spreadsheets, please see here: Investing Formulas for Google Spreadsheets
Over the last few years I’ve transitioned into a conservative and proven investing approach for the long term involving purchases of high-quality dividend paying and raising companies.
- The major November 2014 revision, which updated the color scheme and layout was based on Ryan’s excellent spreadsheet available on his site, My Dividend Growth Portfolio.
- The major December 2014 update added a transaction sheet, which gives the ability to record every dividend, stock purchase and sale, and stock split. It was inspired by Kyith NG of Investment Moats.
- The major June 2015 update created a “clean” version of the spreadsheet making it super easy for the beginning spreadsheet user to begin entering his or her stocks.
- The February 2016 update automated the DivCalendar.
There are two versions available in the templates gallery. The first is the original Google Spreadsheets version which includes the transactions page as well as the “old” version, discussed below. The original version will be left available to use but will no longer be maintained. The new version is the one that will get future updates.
If you find this useful, please remember to rate it well on Google Docs Templates. Thanks!
A template for the new Google Spreadsheet version is available here (Click “File” and then “Make a Copy,” or below, if logged into your Google account:
Orange cells – user needs to fill in appropriate data
Light green cells – these contain formulas and are automatically calculated by the spreadsheet. Do not fill in your own data!
The Portfolio page is the main page which gives a summary of your current stocks. The orangish highlighted columns (Sector, Company Name, and Ticker) are what you manually update. Everything else is automatically updated based on information provided in the Transactions sheet, which is discussed below.
Sector: Drop-down menu allows you to pick the appropriate GCIS sector. The drop down choices come from the Lists sheet.
Account: Use this drop-down menu to choose the type of account that the stock is held in. Brokerage, Roth IRA, etc. The choices can be updated on the Lists sheet. The account information is currently used to help the Summary sheet tabulate information for each separate account.
Company name: The name of the company. This data will be used in the Transactions sheet.
Ticker: The stock’s ticker symbol as used on Google Finance. It is especially important to use the Google Finance version for Canadian exchange traded stocks. If it fails for whatever reason, the formula from the ReferenceData sheet defaults to try and pull the data from Yahoo Finance instead.
Cost Basis: Cost basis information is calculated from purchases net sales and includes reinvested dividends.
Unrealized Gain/Loss & %: These two columns show the current unrealized gain/loss in dollar value and percentage. Conditional formatting gives a green background for gains and a red background for losses.
XIRR: The XIRR function gives a dollar-weighted annualized return that takes into account the timing and amounts of cash flows into and out of your investment accounts. Please see Calculating Your Annualized Return – XIRR Function
Realized Gain/Loss: Money loss or gained in stock sales.
Dividends Collected: Total dividends collected throughout the entire period stock is held.
Total Gain/Loss: Unrealized gain/loss + Realized gain/loss + dividends collected
Annual Dividend: Forward 12-month dividends for the stock calculated by taking the current annual yield * the market value of the position
Dividend Yield: current dividend yield
Finally, a pie chart shows the sector diversification of your portfolio. Note that there is also a cash section, which is manually edited under the market value column.
At the top of this sheet is a portfolio summary section. This provides a breakdown of the performance of each separate account. It is dependent on picking an account for each stock from the drop-down menu on the Portfolio page. See revision history for further details.
This sheet also records the amount of dividends received each month and allows me at a glance to see how that has (hopefully) increased from year to year. The numbers on this page are based on the entries on the transactions page so make sure you enter and date those correctly. If you desire to update past years by hand, just replace the formula with whatever value you want.
The Summary sheet also calculates annual dividends as well as total realized gain/losses.
The DivCalendar became automated in version 3.5 released on 2/4/2016. See the blog post discussing that here: Automated Dividend Calendar
The estimated monthly dividends became automated in version 4.1 released on 9/17/2016, blog post here: Automatic Estimation of Monthly Dividends for Google Sheets
I got this inspiration from Hello Sucker’s Calendar page. At a glance it gives me a head’s up on the months and days that the companies I own payout. It also allows me to record the dividends as they come in.
The automated version is not pre-highlighted with the expected dividend paying months, so that will still need to be done manually, if desired. However, any transactions that are classified as “Div” will show up on the DivCalendar and be highlighted a slightly darker color of green, as indicated above.
What I’ve done on my own personal DivCalendar is to pre-highlight the months in orange that I expect each stock to pay and then to have conditional formatting change the highlight to green as it gets automatically filled in. The conditional formatting for that is already in place.
I also tabulate each month’s total dividend distributions, which is noted along the top and at the bottom of the DivCalendar table.
As of version 4.1, the template also includes automation of the estimated monthly dividend payout as calculated on the DivPayoutCalc sheet, described below.
In the future, this calendar will be very helpful to gauge the amount of dividend income coming in and to adjust expenditures accordingly.
If everything is working correctly, the total value on the DivCalendar sheet should match the monthly totals on the Summary sheet.
Div Increases: This column is used to track the effect of an increased dividend each time the dividend is raised. It needs to be manually updated and is based on the current amount of shares held at the time you updated it. You can think of it like the amount extra each year that you will be receiving even if you did not add additional capital or reinvest the dividends. To calculate Div Increases I just make note of how much the annual dividend changes after a company increases its dividend.
This sheet is used to calculate the estimated monthly dividends. You’ll have to type in the company name (matching what is on the Portfolio sheet) and then type in the months that the stock pays. A more detailed explanation of this is available here: Automatic Estimation of Monthly Dividends for Google Sheets
Each stock purchase or sale, dividend, and split are recorded on this sheet. It is from this sheet that all the data on the Portfolio page is generated. As long as you keep the transaction sheet up-to-date, nearly everything else is automated. If you still want to try the old layout, please see the bottom of this page.
The inspiration and the general formulas came from the wonderful spreadsheet created here. (Thanks, Kyith!) The formulas and layout have been fine-tuned to fit within my dividend focused Google Spreadsheets portfolio.
Note: Do note delete Row 2. This row contains the formulas, which are pre-populated for the first 100 entries. Thereafter, new transactions will have to be created by dragging the formula down from the entry above it. I tend to drag down from the very top.
New rows are able to be created in order to add additional entries. However, before the formulas will work correctly, you will again have to drag the formulas down from the top.
Manually edited cells are again highlighted in orange. The light green cells contain formulas and should not be edited. If these are edited accidentally, just drag the formulas down again from row 2.
Step 1: Enter the date. This is not technically needed but will be helpful for future versions of this spreadsheet, which will use the date data to tabulate annual dividends and calculate growth rate.
Step 2: Choose the type of transaction: Buy, Sell, Div, or Split.
Step 3: Choose the stock. This drop down menu is created from the companies entered into the Portfolio sheet. The data is obtained from the ReferenceData sheet’s ArrayFormula generated list. As additional companies are entered, the drop down menu will need to be updated via the data validation section.
“Buy” transaction: Enter the # of shares, the per share price you paid, and any fees. Stock split ratio should be 1.0.
“Sell” transaction: Enter the # of shares, the per share price you sold the shares at, and any fees. Stock split ratio should be 1.0.
“Div” transaction: There are a number of ways to record dividends. You basically just want the “gain/loss from sale” column to reflect the amount of the dividend. What is easiest is to just enter 1 as transacted shares and then the dividend amount in the “transacted price/share” column.
Note: With dividend reinvestment you technically receive cash and then buy additional shares. So, if you are reinvesting dividends, an additional transaction must be entered with the partial shares purchased. This allows the cost basis to be calculated correctly. If entered correctly, the dividend amount should be at positive cash flow while the subsequent dividend reinvestment will be the same amount only at a negative cash flow.
“Split” transaction: Just enter the split ratio. Ensure that “transacted shares,” “transacted price,” and “fees” are either 0.0 or blank. Apple’s recent 1 to 7 split would be entered as 7/1 = 7.0.
Notes: Enter any notes that you may want to keep for a particular transaction
The data in this sheet is automatically updated. Other than dragging down the formulas as more stocks are purchased, you will not need to manually edit it.
This sheet provides a basic watchlist of potential dividend stocks. I’m sure it is not nearly as sophisticated as what is available at any of the discount brokerage sites, but it works fine for its purpose. Basically, you just update the light blue sections of company name, symbol, date added to the watchlist, and its price and PE at the time it was added. The other fields then give the current price and PE and the 52 week high and low. It also calculates the % off the 52 week high and % from the 52 week low.
It also shows the annual dividend yield, payout ratio (based on EPS), 5-year dividend growth rate, and calculates the Chowder number for each stock. The Chowder Rule is described in detail here: Chowder Rule Breakdown – High Growth Vs. High Yield In Your Portfolio and by Chowder himself here. Basically, the Chowder Rule says that the combination of current yield with the 5-year compounded annual dividend growth rate should be equal to or more than 12%.
The goal of the watchlist is that you can add your top picks to this list and then buy the ones that seem to now have the best “value” as determined by change in PE, poor performance recently, greatest percentage from its 52 week high, and/or passing the Chowder Rule.
This hidden sheet provides the drop down boxes for the sector and account type information in the Portfolio sheet. If you want to add or update the sectors or add new brokerage accounts/types, this is the place to do it.
Note: The old version is still available for the time being and, for those individuals who buy-and-hold as well as reinvest their dividends, it may be perfectly adequate. However, it will not be maintained and will shortly be removed from the spreadsheet.
This is the main sheet that includes the entire dividend portfolio. Any time I buy a stock or reinvest dividends, I update this sheet to reflect the new share count. Once the received dividends are updated in the DivCalender sheet, everything else is updated automatically. Only the Ticker and Shares columns are editable.
Sector: GCIS sector data is automatically updated for US stocks.
Ticker: ticker symbol for your stock (reference this as how Google Finance sees it. Canadian stocks require the exchange. For example, TSE:EXE for the Canadian company, Extendicare.
Market Value: latest stock price * number of shares
Cost Basis: purchase price of the stock plus any reinvested dividends (this is automatically calculated by data entered into DivCalendar and into the Cost Basis & DGR sheet)
Gain/Loss: cost basis – market value
Market Weight: weighting of each stock
Yield: current dividend yield
Annual Dividend: forward 12-month dividends
Div/Sh: quarterly dividend per share
Dividends Collected: total dividends received
Payback %: dividends as a percentage of the cost basis net reinvested dividends; reference
This sheet also calculates the total portfolio value, overall cost basis, overall gain/loss, 12-month forward estimated dividends, current dividend yield, and the yield on cost (YOC). It also shows the total dividends collected for each stock and the payback percentage. (The formulas as written assumes dividend reinvestment…if you are not doing that, you’ll have to edit the formulas.) They will also have to be updated each year to reflect the new DivCalendar data.
Payback percentage is the percentage of invested capital that has been returned as dividends. The amount of invested capital does not, obviously, take into account the dividends, which, for tax purposes, are included in the cost basis. I added this to my spreadsheet after reading about it on Passive Income Pursuit’s Payback period blog post.
Finally, a pie chart shows the sector diversification of your portfolio. Note that there is a cash section, which also contributes to the total portfolio value.
See the DivCalendar information section above for further details.
DivCalendar is very important to keep up-to-date and accurate as the Income Old and Cost Basis & DGR Old sheets (discussed below) rely on its data.
Div Increases: This column is used to track the effect of an increased dividend each time the dividend is raised. It needs to be manually updated and is based on the current amount of shares held at the time you updated it.
You can think of it like the amount extra each year that you will be receiving even if you did not add additional capital or reinvest the dividends. To calculate Div Increases I just make note of how much the annual dividend changes when I plug in the new quarterly dividend amount into Portfolio.
This sheet records the amount of dividends received each month and allows me at a glance to see how that has (hopefully) increased from year to year. The numbers on this page reference the monthly totals listed on the DivCalendar Old sheet.
Cost Basis & DGR Old.
Company: the ticker symbol or name of each company you own
Capital Invested: the amount spent on each stock purchase including any fees; dividends are not included in this figure
Date Initially Purchased: date of stock purchase
Initial Dividend: annual dividend at time of purchase
Current Dividend: current annual dividend is automatically calculated from Div/Sh value in the Portfolio sheet
CAGR: compound annual growth rate
Please do not hesitate to ask me any questions or offer suggestions about this template either via the comments section below or the Contact Us link. I’ll try to answer them as quickly as I can.
*** These spreadsheets are solely for entertainment purposes. Please don’t rely on them for any official/ tax-related functions. ***