Bonus Material: Free Dividend Portfolio Tracker Spreadsheet
I couldn’t stand it anymore.
The internet had failed me.
Hours spent looking for websites, trying different spreadsheets, downloading apps…
Hours more spent inputting my portfolio only to realize that the info I was getting out wasn’t what I wanted to see.
There was always something missing.
How much did I earn in dividend payments for October of 2016?
What’s my annual average return over the lifetime of my portfolio?
What percentage of my portfolio is in consumer defensive stocks?
Every site and app had a new take on the problem, but it never seemed to answer the real issue.
What I came to learn was that all these sites and apps were never developed for dividend investing.
Most are for general stock portfolio investing. They’re not going to tell you everything that a dividend investor wants to know.
Having reached the end of my rope, I decided it was time to sit down and build my own dividend portfolio tracker.
The Ultimate Dividend Portfolio Tracker
After hours and hours of tweaking and fiddling, here it is:
**NOTE** This isn’t my full portfolio. These are just a few transactions to show you what the tracker looks like and how it works.
Over the rest of this post, I’m going to walk through the entire tracker. Each formula and page link explained so that you can use this tracker to its full ability.
Once you’ve gone through the tutorial, you should understand how the spreadsheet works and can start using it for your own portfolio.
I could say just download it, update the yellow cells, and you’re all set, but that would leave a lot of you disappointed.
Part of the problem with spreadsheets I found out there is the mystery behind them.
They’ve got embedded formulas that take a Rosetta stone to figure out.
I had to decipher what they did before trusting that the calculations were right.
Hopefully, this full post will put your mind at ease.
If you are one of those people who trust I’ve done all the calculations correctly, let’s start off with somewhat of a manual on how the tracker works.
Dividend Portfolio Tracker Manual
If there is one thing to know about this dividend portfolio tracker it is to only update the yellow cells. All other cells will calculate automatically.
Adding a New Stock Buy
Everything stems from the “Transactions” tab.
If you enter things correctly here, nearly every other cell in the entire tracker will update.
When you buy a new stock, you need to include it as a transaction.
On the Transaction tab, in column A, there is a “Transaction Type.”
Here you will enter “O” for Order.
Enter the date of the transaction in column B.
The stock ticker goes into column C.
For stock purchases, you skip over column D as it will automatically calculate.
The same goes for columns E and F. These are formulas that pull the year and month from your transaction date.
In column G, enter the number of shares purchased.
Fill in the purchase price per share in column H.
If you want to factor in the transaction fee associated with a trade, put the amount in column I.
Increasing a Stock Position
Say you already have a position in JNJ but want to increase that position. You want to go and buy another 10 shares.
The process looks the exact same as if you were making a new purchase.
Enter “O” in column A.
Enter the transaction date in column B.
Stock ticker in column C.
The number of shares purchased in column G.
Price per share goes in column H.
And, if you want, the transaction cost in column I.
Receiving Dividends
Enter “D” for Dividend into column A.
Once you enter “D” the yellow boxes will change for that line. This means you no longer fill in G, H, or I, but instead override the formula in column D with your dividend payment.
Enter the date you received the dividend in column B.
Enter the ticker you received the dividend from in column C.
And finally, input the dividend amount into column D.
Again, it’s OK to overwrite the formula in column D when you’re receiving a dividend.
I set a reminder in Google Calendar for the beginning of each month to update my dividend stock tracker with all my dividend payments. This way I don’t get too far behind.
Receiving Dividends for DRIP investors
If you’re a drip investor, the steps for updating your tracker will follow the “Increasing a Stock Position.”
You will not be putting a “D” in column A, though. Make sure you still put “O” into column A.
In column G under “Shares,” you will need to know how many partial shares your dividend purchased and at what price they were at.
It’s not the most artful way to handle the process. I’m working on an update that will, hopefully, make calculating DRIP repurchases more automatic.
If anyone has a suggestion, shoot me an email.
Adding a New Year
Because the year I’m releasing this is 2017, every tab and year reference is to 2017.
But, if you use this when the calendar changes over to 2018, there are a few things we will need to change.
The first will be to add a new tab tracking dividends for 2018.
Now, we could recreate the “2017” tab in a clean sheet, but that’s just wasted time.
Instead, we’re going to copy the tab and change a couple things.
Right click on the “2017” tab and select “Duplicate.”
Then rename the tab to “2018.”
Go to cell O1 in the “2018” tab and change the year from 2017 to 2018.
Hop over to the “Total” tab and change cell L4 to 2018.
You should see all your dividend payments disappear from column L unless you’ve already entered 2018 dividend payments.
ERROR in Columns J, K, L
You should be seeing an ERROR message in columns J, K, and L.
These are reminders to update the other tabs in the workbook with your new ticker.
If you initiated a position in NKE, we need to account for it other tabs.
Updating TOTAL Tab
The ERROR in column J means the ticker on that line isn’t found in the “Total” tab.
Let’s jump over to the “Total” tab and add the ticker for our missing stock.
Once in the “Total” tab, you’re going to update column B with the missing ticker.
If NKE was the missing ticker, enter that on the next available line.
Bounce back over to the “Transactions” tab and the ERROR message under column J should be gone.
Updating INCOME SPREAD Tab
Next, we’ll jump over to the “Income Spread” tab and add the ticker for our missing stock.
Go to the empty cell in column A and enter the missing ticker.
Now, back in the “Transactions” tab, the ERROR should be gone under column K.
Updating “YEAR” Tab
Finally, we head over to the “Year” tab and add the ticker for our missing stock.
Go to the empty cell in column B and enter the missing ticker.
Now, back in the “Transactions” tab, the ERROR should be gone under column L.
The “Year” tab will change based on the year of your transaction.
If the transaction happened in 2017, you’re going to update the 2017 tab.
If the transaction happened in 2018, you’re going to update the 2018 tab.
One thing we do need to update in the ERROR check is the formula for a new year.
Updating the ERROR flag in Year Check
If you look at the formula in column ‘L’ of the “Transactions” tab, you’ll see this formula:
=if(iserror(vlookup(C23,’2017′!B:B,1,false)),”ERROR”,””)
The ‘2017’ means it’s looking at the “2017” tab to see if our ticker is missing.
But what if the transaction happened in 2018? We want that ERROR flag to be looking for the ticker symbol in the newly created 2018 tab.
The formula in column L for transactions that happened in 2018 need to change to:
=if(iserror(vlookup(C23,’2018′!B:B,1,false)),”ERROR”,””)
Now our ERROR flag in column L will be checking to make sure the ticker is in the “2018” tab.
You can then copy and paste the new formula to all the remaining blank cells in column L.
This way you don’t have to change the formula each time there is a new transaction in 2018.
Dividend Portfolio Tracker Walkthrough
Here is where we get into the details.
I’m going to walk through each tab and each column explaining what you’re looking at and how the formulas work.
This way you know that things are being accurately calculated and can sleep peacefully.
The walkthrough should also help you understand the values you’re seeing in each cell and why they’re important.
Let’s get to it!
Transactions Tab
Column A (Transaction Type): This is information you, the user, enters. It is either an “O” for Order or “D” for Dividend.
Some formulas throughout the tracker key on that symbol so it’s important to include it.
Column B (Date): Another field input by the user. The date of the transaction is important because its use when calculating the average annual return of your portfolio.
The tracker also uses the date when summing yearly and monthly dividend totals.
Column C (Ticker): Also a user input field. Hopefully, I shouldn’t have to explain why the ticker is important.
Column D (Cost Basis): If the transaction type is an Order, this field will calculate.
It multiplies the number of shares by the purchase price and adds the transaction fee. This becomes the cost basis for that transaction.
It’s a negative number because the formula for calculating your portfolio’s annual average return needs it to be.
If the transaction type is a Dividend, you overwrite the formula with the dividend payment amount.
The dividend payment will be input as a positive number.
Column E (Year): The formula extracts the year from the transaction date you entered in column B.
Column F (Month): The formula extracts the month from the transaction date you entered in column B.
Column G (Shares): If the transaction is an Order, you input the number of shares purchased.
Column H (Purchase Price): Another one you will input if the transaction type is an Order. This is the individual share price for your purchase.
Column I (Transaction Fee): Some people like to factor in the transaction costs of their orders. If you’re not one of them, leave this blank and it won’t affect the ‘Cost Basis’.
Total Tab
Column A (Name): This is the company name of your ticker. It looks up the name based on the ticker symbol you input in column B.
Column B (Ticker): You input the ticker symbols for all the holdings in your portfolio in this column.
Column C (Sector): This performs a lookup based on the ticker to see what sector the stock is in. It’s looking for the ticker in the “Sector Lookup” tab.
If ever you enter a ticker and that field comes back blank or with an error, you need to add the ticker and sector to the “Sector Lookup” tab.
Column D (Price): Price of the stock and updates in real-time.
Column E (Shares): Using an SUMIFS formula, this totals the number of shares you own for a given company.
It’s looking for the ticker in the “Transactions” tab and adding up all the Orders.
Column F (Cost Basis Total): Another SUMIFS formula adding the cost basis for each Order associated with the ticker.
It’s multiplied by -1 because the cost basis in transactions is a negative number. Here we want it to show up as a positive.
Column G (Cost Basis Equity Value): Showing how much your shares are worth at any given moment.
The formula multiplies the stock price by the number of shares you own.
Column H (Value Gain): The percent change in the value of your stocks based on the total cost basis.
Remember, the cost basis also takes into account the transaction costs if you included them.
Column I (Weight): The percent of your total portfolio invested in a stock. The calculation is based on the current ‘Equity Value.’
Column J (2017 Net Yield On Cost): Calculating the yield of total dividend payouts for a given year based on the total cost basis.
The calculation is based on received dividend payments. As you add more dividend payments throughout the present year, the yield will continue to increase.
Column K (2017 Net Yield On Value): Same as column J but the yield is based on the equity value of the stock.
As the value increases, the dividend yield decreases.
Column L (Net Dividends Received 2017): The total dividends received for the year entered in cell L4.
An SUMIFS formula that looks to the “Transactions” page and totals all the “Cost Basis” of transactions marked Dividend in a given year.
Column M (Net Dividends Received Lifetime): The total dividends received over the lifetime ownership.
An SUMIFS formula that looks to the “Transactions” page and totals all the “Cost Basis” of transactions for a given company marked Dividend.
Column N (Total Gain): The total return based on value change and dividend payments.
Adds the ‘Value Gain’ to the ‘Lifetime Dividends’ received and calculates the percentage change based on ‘Cost Basis.’
Column O (Annual Average): Calculates the internal rate of return based on irregularly spaced cash flows.
The XIRR formula looks at all the purchases and dividend payments in the “Transactions” tab. It also takes into account the date of all those transactions plus the current value of the entire portfolio.
If you look at line 4 on the “Transactions” tab, you’ll see the market value of the portfolio next to the current date. This line is necessary to calculate the internal rate of return.
Working some magic, the formula spits out the annual average return of the portfolio.
Total Tab – Sector Diversification
This is the second table on the “Total” tab
Column P (Sectors): These are static values of individuals sectors that companies operate in.
Column Q (%): Calculates the weight of the portfolio that is in an individual sector.
Divides the value invested in the sector by the total value of the portfolio.
Column S ($): Calculates the value invested in each sector.
Uses an SUMIFS statement to check the Sector in column P against the values in column C. If the values are a match, it sums the ‘Value Gain’ in column G.
There is one category that you need to manually update and that is CASH. You can add how much cash you’re holding in your account to get a full picture of how invested you are.
Income Spread Tab
Column A (Ticker): User input ticker symbol for the stock.
Column B (Shares): Total number of shares owned.
Using an SUMIFS formula based on the ‘Transactions’ tab. Totals all the shares owned for a given stock.
Column C (Company Name): Name of the company based on the ticker symbol.
Column D (Value): Current value of the portfolio’s holding in an individual stock.
Multiplies the current price by the number of shares owned.
Column E (Price): The current price of the stock.
Column F (Dividend): The current annual dividend payment for a given stock.
The formula imports data from Yahoo! Finance.
Column G (Yield): Percent dividend yield based on the current value of the stock.
Divides the annual dividend by the current price.
Column H (Annual Income): The total income a stock provides for the year.
Multiplies the total annual dividend by the number of shares owned.
Column I (Div. % of Total): Calculates how much of the portfolio’s total annual income is based on a given stock.
It divides the annual income in column H by the Total Dividend Income located in cell H1.
Column J (Value % of Total): Calculates how much of the portfolio’s total value is based on a given stock.
Divides the value from column D by the Total Portfolio Value located in cell H2.
“Year” Tab
Column A (Name): Name of the company based on the ticker symbol.
Column B (Ticker): User input ticker symbol
Columns C-N (Jan. through Dec.): The dividend payments for each stock broken out by the month received.
The formula uses many SUMIFS arguments to look at the ticker, the month, and the year.
If the three criteria match a Dividend transaction from the “Transactions” tab, it pulls the value into that cell.
Dividend payments are then summed by month in row 5.
Column O (Total): Sums the dividend payments across the entire year.
Cell O5 checks to make sure the total of column O matches the sum total of row 5, columns C through N.
If the totals match, it displays the total dividends received for the entire year in O5.
Sector Lookup
This is a data source for the “Total” tab.
There are no formulas in here. It is simply the ticker symbol of a company and what sector that company operates in.
If there is ever an error or missing value in column C (Sector) of the “Total” tab, come here to add it.
Conclusion
And there you have it. The last dividend portfolio tracker you’ll ever need.
If anyone knows of an app or software out there that does anything close to that, definitely let me know. I couldn’t find anything like it.
When you can’t find what you need, it’s time to take matters into your own hands.
Download and save your own copy here.
If you really like it, please share with other dividend investors.
Leave a comment with any errors you may have found, updates you’d like to see, or additional features that would be useful.
Cheers!
hello,
every column under the income spread tab after price is reading an error and cannot pull the dividend info from yahoo finance, how do we fix this?
Hey Mike,
That’s a frustrating one. I’m constantly working on different methods to get that to load. For some reason, Google Docs doesn’t like to pull that info in and Google Finance doesn’t provide a dividend value or yield percent.
Sometimes when the sheet loads, it works, other times it doesn’t.
Think it’s worth deleting the entire tab if it doesn’t work all the time?
Hi there,
I’m new to investing and really new to having a spreadsheet to track my investments so this question may be pretty simple. When I decide to sell a stock how do I go about reflecting it in this spreadsheet? It also seems like Yahoo no longer allows users to use the link for the dividend and yield in the Income Spread tab. Is there anyway you can provide information on how to change it or maybe update what you have posted now? Thanks!
Thanks for this great tracker!
How are you handling the sale of all or a partial position?
Hi there, this spreadsheet has been really helpful to me and I’ve made some changes I’d like to share. What is this spreadsheet licensed under? If possible, MIT would be great!
A few things – one thing to help with the sheet and the other two are questions:
1. To fix the dividend do the following
A) delete formulas in yield column
B) paste the following text into cell L1
//*[@id=”quote-summary”]/div[2]/table/tbody/tr[6]/td[2]
C) paset the following formula into the Dividend column
=split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”,A4),$L$1),”()”)
D) Send a thankyou note to Bob Lai at “https://www.tawcan.com/using-google-spreadsheet-dividend-investment/” as this is where I reference on how to modify the sheet.
2. How do you enter a sale in the spreadsheet correctly so that it will still show all the correct statistics for the portfolio?
3. DRIP – only way I can figure out how to capture is to record a dividend and then record a Buy transaction for the shares @ share price – is this the way to handle this correctly?
First of all, thank you @NickMorrow for creating this template! I am more of an investor than a Google Spread Sheets or Excel literate. This is a good start for me as an investor, a dividend investor to be more exact!
Second, @Snowhound, when you said to:
“delete formulas in yield column”
what tab are you talking about? TRANSACTION, TOTAL (Dividend Portfolio), INCOME SPREAD or YEAR (Dividend Overview) tab?
@Snowhound when you said to:
“paste the following text into cell L1
//*[@id=”quote-summary”]/div[2]/table/tbody/tr[6]/td[2]”
into cell L1, what tab are you talking about? TRANSACTION, TOTAL (Dividend Portfolio), INCOME SPREAD or YEAR (Dividend Overview) tab?
@Snowhound When you said to:
“paste the following formula into the Dividend column:
=split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”,A4),$L$1),”()”)”
Perhaps check that the formula doesn’t have any extra characters or misspellings. I know that issue is crucial when it comes to making formulas.
I did step A into the INCOME SPREAD tab.
I did step B in the TOTAL & the INCOME SPREAD tab.
I did step C in the INCOME SPREAD tab & got this:
I get an Alert message saying “That function isn’t valid”.
Once again thank you Nick Morrow for getting this started! With a few fixes this will be awesome. @Snowhound I want to thank you for wanting to resolve some of the errors, but I cannot do step D since the issues were not fixed 100%. Like I mentioned before I am NOT literate in Google Spread Sheets or Excel. One last thing, when I started working on this I opened it up in Excel. Would that be the issue why I can’t fix the errors? Thank you ahead of time everyone and anyone!
Hey. Thanks for this!
The link to Yahoo Finance in the Income Spread tab isn’t working. It says that it’impossible to retrieve the URL
Any clue on how to fix this? thanks!
Is the The Ultimate Dividend Portfolio Tracker dead. I just found this page and it seems that all the Download buttons are not working. Would it be possible to fix the buttons or email me a link to a functional tracker? If no to those, do you have a new suggestion? Thanks in advance.
~A New Dividend Investor