My name is Stefan. Welcome to my personal finance blog! The millennial generation has the highest debt burden of any generation and this is why I decided to begin a blog. I hope to use this blog to educate and motivate my readers, and provide high-quality financial advice. #PersonalFinance Blogger | Dividend Investor | MBA | Tax Accountant | Caribbean Born | Millennial Advocate
Tracking your portfolio is one of the most exciting parts of investing. Everybody wants to know how much money they have, how their portfolio is doing against the market, and more. I have found it best to build your own spreadsheet to track all of this data. However, I found it incredibly hard to find a good guide to building a dividend portfolio.
Before Yahoo Finance made their API changes, it was very easy to make a portfolio in excel. Unfortunately, they switched their coding which broke many people’s personal spreadsheets coding. That is why I will teach you how to make your very own dividend portfolio on Google Sheets. You can find a free, simple to use template at the end of this post. You will learn how to build a dividend portfolio, a dividend tracker, and have access to a few criteria for spotting undervalued stocks.
Create Google Account
First, you need to create a Google account. Once you have a Google account created, access you’re Google Drive and open Google Sheets. If you cannot find this, simply type in Google Sheets on any web browser and it will take you to it.
Create your portfolio
Everybody will likely customize their portfolio to their needs but after many changes I have settled on the below headings. I refer to the Portfolio tab as the lead page because it consolidates all the information into one easy to read page. Any of the below headings that are highlighted green populate automatically in the portfolio template.
Sector: What sector does the stock belong to
Ticker: Stock ticker symbol
Quantity: Number of shares you own
Price paid: Average cost you paid per share
Cost basis: Total value of shares you bought
Current Price: Current price of stock via Google Finance
Current Value: Current value of your holing
$ Change: Difference between Current Value and Cost Basis
% Change: Percentage points your stock is up or down
Overall weight: Percentage of your portfolio in one stock
Yield: Current dividend yield
Annual Div/Sh: Annual dividend per share
Annual Income: How many dividends you receive per year
Yield on cost (YOC): What is your dividend yield on the price you paid for the stock
Portfolio Tab – This was the clearest I could get the picture.
Some other information I add on my personal portfolio, which is included in the template, is a pie chart of sector allocation as well as a chart showing the allocation per sector. This will let you know if you are overexposed or underexposed to certain sectors.
Unfortunately Google Finance does not automatically pull the dividend yield and annual dividend per share. Thankfully, there is a formula that can. Here is the secret formula for a dividend portfolio; you must type into your spreadsheet if you do not use the template:
=split(ImportXML(concatenate(ʺhttp://finance.google.com/finance?q=ʺ,(insert ticker symbol here)), ʺ//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*ʺ),ʺ/ʺ)
This formula will pull the per period dividend (that could be monthly, quarterly, or annually) for the stock. You will manually need to update the number of times a company pays out a dividend in the “Stock Data” tab.
One quick note: Sometimes this formula will return “#NA.” There are two ways to fix it. The first one is to give the sheet some time to populate. The second option is to change the ticker symbol and change it back to what you want. Sadly not everything will work properly instantly.
Stock Data Tab
The stock data tab is where most of the information for the Dividend Portfolio is pulled from. This helps make the Portfolio tab very clean. There are multiple uses for this tab:
Spot Undervalued Stock
A dividend portfolio on Google Sheets has options to many criteria thanks to Google Finance. Some of my favorites for getting a quick glimpse of value are the % off 52 week high vs % off 52 week low. Through conditional formatting, you can set your own personal criteria. I personally have a condition for the cell to turn green if a stock is 10% off its high and 7% off its low. You can adjust as you want.
Determine an approximate payout ratio
Payout ratio is simply calculated as dividend per share dividend by earnings per share. Simply put, how much of earnings are management paying out as a dividend. The stock data tab will automatically calculate the payout ratio for you. If you have a criteria on an appropriate payout ratio, you can create conditional formatting in this column to identify stocks that fall in your range.
For example, one of my four parameters is a payout ratio less than 70%. If you right click and go down to conditional formatting, you can create a criteria to highlight the cell green if it is less than 70% and red for greater than 70%. This makes sorting through information very easy.
This feature does not work properly for REITs as they use AFFO rather than EPS.
Track day-to-day performance
One final feature is the ability to track the one day change of your portfolio vs. the market. While most dividend investors will not use this feature, I have found it very useful for seeing if apps like personal capital accurately track my portfolio. I have found using this feature is more accurate than most apps that I use.
Stock Data Tab – Unfortunately the Image is not very clear.
There is no way to accurately pull the dividends you will receive from the stock in the appropriate month. You will need to manually make all entries on this tab.
I currently have this tab setup to be split between taxable, and retirement accounts but you can adjust as needed. This sheet keeps all of your dividend payments neat and easy to follow as well as allows you to see when stocks have increased their dividends.
That is all I have for a guide on how to build a dividend portfolio on Google Sheets. There are many other features such as charts, data validation, and other techniques you can use to make your personal portfolio as crazy or simplistic as you want.
Just like that half of the year is done! June was a terrific month for me with both my savings and dividends. Let’s jump right into my June savings rate, stock purchases, and dividends received.
After a very expensive month in May, purchased a new suit as well as paid for a vacation, I was back on track in June. In fact, I was well above my 55% goal. This month I managed to save a whopping 61% of my salary!
There were two reasons for this, I lowered my food expenses as well as my entertainment expenses. Despite spending a fair amount of money in Miami this month during my vacation I came in $250 under budget despite doing everything I wanted. With such a high savings rate, I was able to put almost $2,400 to work in the market.
June was a terrible month for most of the retail sector. Thanks to Amazon and their recent purchase of Whole Foods a large portion of the grocery stocks as well as REITs with these tenants had major drops in value. As I am trying to max out my ROTH IRA for the year as well as use some of my HSA funds, I took the opportunity to add a couple of REITs due to the tax advantages they have.
I purchased 21 shares of DEA, a government REIT, in my HSA. The best part was I got in right before the ex-dividend date so I recently received my first dividend check of $5.25 from them! This purchase added $21 of dividend income annually.
My second purchase was one that I was eyeing for a while. Once again, another REIT called KIM. I purchased 52 shares in my ROTH IRA. They have a wide array of tenants and will help diversify my REIT portfolio. This purchase added $56.16 in forward income.
With over $3,000 left for my ROTH IRA for the year, I will continue looking at REITs and Canadian stocks due to the tax synergies these companies get in tax-deferred accounts.
June Dividend Income
Before the month even began I knew that this was going to be huge for my portfolio. Since I began working I have regularly pumped money into the market. June was my first triple digit income month and I could not be more pumped! I am finally out of the double digits and am on my way to the four-digit club. However, that is a long way from now. Here were all the stocks I received dividends from this month:
Any ticker with an R next to it means retirement account. These range from 401(k), ROTH IRA, and my HSA. All stocks in my retirement accounts use a DRIP and select taxable stocks.
Compared to March 2017, I increased my dividend income by almost 20%! If we were going all the way back to June 2016, when I received $28.93 in dividends, this was a 255% increase. Not bad for one year’s work!
The dividends received this month would have paid for 8% of my necessary expenses. With the upcoming bank dividend increases – BAC is raising their dividend 60%! – I expect September to be even larger. However, I am most interest in December where my 401k funds will pay dividends and capital distributions. Considering 50% of my investments are in my 401k I am expecting to be close to or over $200.
How was the month of June for you? Did you have any new purchases that you made?
Identifying an undervalued dividend stock to add to your portfolio is an exciting task for dividend investors. As an accountant, I naturally try to set parameters to mitigate risk. When looking for a new stock for a portfolio I consider two factors; is the stock over/undervalued and is the dividend safe. However, there is no golden rule to figuring out these two answers.
This is why I set some simple dividend stock parameters to help identify undervalued, and safe, dividend growth stocks. Once the stock passes all parameters I put on my hard hat and perform in-depth analysis. My four metrics are as follows:
Parameter #1: Price to earnings (P/E) Ratio
The price to earnings ratio is widely used by many investors to determine how a stock is valued. While this metric certainly has many flaws, it is a quick way to glance at a stock and figure out if it may potentially be undervalued. I often see many investors put a set number on this parameter. Some may say a P/E less than 20, or a P/E less than the S&P 500 shows signs of potential undervaluation. However, I highly disagree with this method.
The simple reason is that every industry has a different P/E. Autos and banks often have a low P/E due to consumer sentiment, technology often has a P/E higher than the S&P due to their high growth and consumer stocks are valued around the S&P 500 P/E. Example, if I am screening a consumer stock, I know that the S&P typically has a P/E ratio of 18-19 (higher now that we are in an overvalued market). With this in mind, I will look for stocks somewhere in the 15-16 P/E range for potential undervaluation. The key word here is potential. Each stock has a historical P/E that I would further look into if it passes all parameters. This metric is simply to identify companies to consider.
Parameter # 2: Current & Historical Yield
It is important for my portfolio to include companies with dividends. The second parameter I look for is the yield of the company. Most commonly, I look for companies with a starting yield of 3.0% or greater as I would like my portfolio to have an average yield of greater than 3.0%. Should a company have a yield below 3.0% I would refer to parameter #3. Companies with a yield over 5% are often a red flag to me. These companies usually have a high payout ratio (Parameter #4).
However, there are times where companies are unfairly punished in the market which causes their price to drop and their yield to rise above this threshold. One example is a REIT I recently bought that had a 3.2% historical yield but is now yielding 5.3%. This is often a sign of undervaluation which would lead me to do more research on the company and the industry they operate in.
Parameter #3: Dividend Growth Greater than 7%
Unlike the P/E ratio that varies per industry, I apply the 7% dividend growth to most companies. The only industries that I give exception to are utilities and telecom. These sectors are usually mature with little room for growth so I look for growth greater than inflation as they typically yield 4%+. You can easily find the 3, 5 and 10-year dividend growth on most financial websites.
If a stock has a yield lower than 3% I look for dividend growth above 10% due to parameter #2. Lowe’s is a perfect example of this as they currently have a yield of 1.75% but have been growing their dividend by an average of 21.12% over the past 5 years. I believe it is important to have these companies in your portfolio as your yield on cost will likely be higher on these stocks in the future than a company yielding 4% with 3% dividend growth. Of course, maintaining those aggressive growth rates will not last forever but it usually signals a low payout ratio.
Parameter #4: Payout Ratio
Last but not least is what I believe to be the most important parameter, the payout ratio. The payout ratio is the proportion of earnings paid out as a dividend to shareholders. If a company has a high payout ratio they often cannot sustain their dividend. There is a high correlation between payout ratio and dividend cuts which make this a crucial metric.
Through research, reading, and talking to other investors I believe a payout ratio less than 70% is healthy for a company. However, it is important to remember that each industry and company are different. Utilities, REITs, and telecoms often have payouts higher than this benchmark. I would compare these companies to their historic average and the industry.
If a payout ratio spikes above 70%, eg. 67% the year before and 77% this year, it is often a warning sign that requires more research. The energy industry is currently an example of this as many energy companies have seen their top line reduced due to lower oil and gas prices. If a company has a payout ratio greater than 100% it is a major red flag that their dividend is unsustainable. I would urge you to stay away from these companies.
While these four parameters are great for identifying undervalued dividend paying stocks, in-depth research should always be done. You should understand why is the company down, how is the industry performing, how the company will grow in the future and more. Historical performance does not dictate future growth but these parameters help weed out some of the dangerous investments.