While past performance is not a predictor of future returns, companies that have consistently meet their long-term yearly growth rate for stock price and dividends deserve close scrutiny. However, it is not always obvious which company’s combined yearly stock growth rate plus yearly dividend growth rate is better than that of another company. An Excel spreadsheet can be downloaded that takes the yearly stock price and dividends rates and projects the stock prices and dividends 5 years into the future, determines a final value using taxes and charts them so you can easily compare companies.
Selecting a Stock
It is never easy to decide what stock you should buy. There is a lot of information available for helping make the choice and as many opinions as there are sources. There are also many metrics and rules that can be found that you might find useful.
In the end, what really counts is the stock price and, for dividend-issuing companies, the dividends paid and your opinion as to whether both will increase or stay the same and not decrease in the future.
The Excel spreadsheet that you can download can be used to compare the project yearly growth of stock prices and dividends using historical yearly rates for any number of companies on one chart over a 5 year period. Hopefully, this will be useful to you as one of the criteria you use to decide on a company.
Downloading the Spreadsheet
The remainder of this post explains how to use the Excel spreadsheet.
Please click on Download Telecoms Spreadsheet now and open it if you want to use it and to follow along and make the changes indicated below.
It is loaded with the 4 major Telecom companies in Canada to illustrate how to use it and interpret the results. There is also an empty spreadsheet which you can use by clicking on Download Empty Spreadsheet if you want to start from scratch. It also has room for 4 companies and others can be added easily as described later.
The spreadsheet has 4 areas, each of which are explained below: Comparison Chart, Company Summary, Entry area, GIC area and Company area.
Because the spreadsheet has been split into 2 panes and then frozen, the top pane will not scroll so that you can always see the effect of any changes in the bottom pane. Any scrolling will only move the bottom pane. Just scroll down to see each company and to make any changes. Enter data only in the cells that are outlined.
Company Comparison Chart
Each item on the chart is explained below. For comparison, the value of a Guaranteed Investment Certificate (GIC) at 3% is included to set the baseline for the lowest possible expected return over 5 years.
To make it easy to see what has been entered (as described below), a summary of the 4 companies information is shown to the right of the chart. Do not make any changes to the these values. They will be updated automatically when a change is made to any of the data in a Company area. A description for each line is given in the Company area below.
Just below the chart is a number of values that you can change if you want. The first one is the Starting Value of the investment at the beginning of Year 1. An initial value of $10,000 is used, but can be changed as desired on the spreadsheet. The second value to enter is whether the dividends (or interest for the GIC) are to be Reinvested. The third value, Date of Stock Prices, is optional and is used for historical purposes.
After that are the tax values needed to determine what taxes are applied to the dividends and the stocks. They are set for Canada and Ontario for 2014, but any other province can be used. Refer to Canadian Marginal Tax Rates – 2014 for your province for the Marginal Tax Rate. In Canada, the Capital Gains is reduced by 50% (as entered in the Capital Gain Rate) before the Marginal Tax Rate is applied.
In Canada, dividends are taxed at a lower rate than interest investments. There are many good explanations of how this is done, with a good one being the August 2014 article from trader ii: Dividend Tax Credit and Dividend Income from Canadian Corporations.
For 2014 income taxes, the Federal Dividend Gross-up Rate is 138% and the Federal Dividend Tax Credit Rate is 15.0198% for eligible dividend tax credits, for which most large public Canadian corporation qualify. For most privately held corporations that are not eligible for these preferred rates, the Federal Dividend Gross-up Rate is 18% and the corresponding Federal Dividend Tax Credit Rate is 11.0169%. For the provinces, see the tables below. These rates come from an examination of the 2014 Federal and Provincial tax forms as found at the Canada Revenue Agency’s General Returns, Forms and Schedule page.
Eligible Dividends Tax Credits by Province
Other than Eligible Dividends Tax Credits by Province
Using the eligible dividend tax credits for the Federal and Ontario taxes, the Effective Dividend Tax Rate is less than one-third of the normal Marginal Tax Rate. The formula used is as follows:
Effective Dividend Tax Rate = Federal Dividend Gross-up Rate x ( Marginal Tax Rate – Federal Dividend Tax Credit Rate – Provincial Dividend Tax Credit Rate)
For other countries, use the appropriate Marginal and Capital Gains Rate and clear or change the other rates, as appropriate. To tax the Dividends at the Marginal Rate, change the Gross-up to 100% and set the Federal and Provincial Dividend Tax Credits to 0%.
If the stocks are held in a Canadian Tax Free Savings Account or a USA Roth account, change the all of the Rates to 0%. The Return will increase by the Capital Gains and Div Taxes.
5 Year Return is an Important Value
Unlike a GIC, when you invest in stocks, you incur a tax liability if the stock appreciates. To compare stocks to each other and to a GIC, the return is what you would receive if you sold the stock after 5 years. You would then select another stock and start again with no tax liability. This is what happens with a 5-year GIC as you can then select another investment vehicle with no tax liability. However, there is no requirement to sell a stock at any time, which is not true with a long-term GIC which matures in 5 years.
The Return is not a normal metric used to compare stocks, but it is a useful one as it helps you determine your net worth and gives you information on the tax liability that it has incurred.
The Return is shown on the graph after the 5 years (i.e. after the x-axis value of 6 which is the start of year 6). It always is less than the start of year 6 values for Stocks no matter what the value of Reinvest. However, for the GIC, it is more when Reinvest is set to No.
Return after 5 years as indicated below is calculated as follows:
– If Reinvest is Yes, then Return = Value after Taxes – Total Div Taxes (or Total Interest Taxes for GIC)
– If Reinvest is No, then Return = Value after Taxes +Total Dividends after Taxes (or Total Interest after taxes for GIC)
The GIC Area
The GIC area is in the bottom pane and should show if you have enough screen height. If not, you will have to Unfreeze the panes using the Window menu.The only value you should change in the GIC area of the spreadsheet is the Interest rate. It is initially set to 3% so that the chart line will be below those for the stocks. After the stocks have been entered, it is instructive to change the interest rate to see what value is needed to move the chart line to match any of the stocks.
You can change the text GIC to whatever you want. For example, in the USA it is CD for Certificate of Deposit.
Next to this text is the variable used in the Legend of the Chart. Do not change it.
For details, see Explanation of Variables in the GIC Area at the end of the post.
Scroll down to get to each Company area. There are many variables that you can enter in the company area. Those in the table are used for the chart, while the others can be left empty if you do not have the values.
Enter anything you want for the Company Name, but the shorter the better as it appears in the legend. Next to this text is the variable used in the Legend of the Chart. Do not change it.
On the second line of the Company area, If you have them, enter the Price/Earnings and Earnings/Share values. The Payout Ratio will be calculated by dividing the Earnings/Share by the Dividends/Share (see below). The Payout Ratio is a good indication of how much room the company has to distribute dividends before reaching 100%.
The table used for the chart requires that you enter the Stock Price and the expected Stock Price Yearly Increase. If this is a Dividend issuing stock, enter the Dividends/Share and the expected Dividends Yearly Increase.
The yearly increases can be your best guess, values obtained from analysis by other or by using the spreadsheet that is described in the Determining Yearly Rates for Stock Prices and Dividends post.
Use the Stock Price and Dividends/Share for the latest date for the stock market. There are many sources for this, but obviously the key resource is the stock market listings. The Stocks used here came from S&P/TSX 60 best dividend yielding stocks.
All of the other values in the table are calculated from these 4 values. For details, see Explanation of Variables in the Company Area at the end of the post.
Creating Additional Company Areas
If you want to have more than 4 companies, just select all 13 of the rows for any of the existing companies, starting at the row that has the Company name, and paste below that last company. Then make the changes needed for this new company.
To include the new company on the chart, select the chart, under the Chart menu, select the Source Data item. In the window that appears, click on the Add button. Then move the cursor the Name field and select the cell on the spreadsheet that is next to the new company’s Name. After that, move to the Y Values and select all of the Start of Year cells from 1 to Return. Finally, click on the OK button. The new company should appear on the chart and the legend.
To add the new company to the Summary is a little more difficult as 2 hidden columns must be exposed to make room for the new company. To expose columns K and L, selecting columns J and M. Then move the cursor to the edge of column M until it becomes a cross with left and right arrows and double-click. This will show 2 columns with numbers in them. Now insert a new column by selecting column K, and from the Insert menu select Columns. Finally, select the cell at row 5 and column J and all of the column down to row 18, copy it and paste it into column K starting at row 5. This will then show all the values for the new company. You may have to widen each of column J and K to show some values. You can also hid columns L and M, if you want.
Ordering the Legend to Match the Lines
It is easier to determine which line on the chart applies to which company if the order in the legend matches the top to bottom order of the lines on the chart. To get this to occur, click on the top-most line. A Series equation will appear in the equation line just above the A-J column names. Change the last value in the equation (the series number) from whatever it is to 1. The legend will then have this company at the top. For example, after a new company has been added, the equation will look like this:
Change the 6 to 1.
Select to the next line and change the series number to 2. Repeat for each line, making the series number the same as the top-to-bottom order of the lines.
Deleting a Company
This is a two stage process. 1) First select the company’s line on the Chart. Delete the equation. Then select all 13 rows for the company and delete them. 2) To remove the company from the summary, select the company column starting at row 5 down to row 18. From the Edit menu select Delete and then the Shift cells left radio button.
The Equivalent GIC Interest Rate
It is instructive to see what GIC interest rate it takes to match the Return for any of the dividend-issuing companies. This can be done by going to the GIC area and entering, for example, 12% into the Interest variable. This will move the GIC line on the chart up to the Rogers line. To match the performance of Telus would require a 19% interest rate.
Deciding On Which Company
As shown in the chart, Telus generates superior value per year and has significantly greater Return than any of the other companies. Whether Telus or any of the other companies can continue to grow at the past rates used is where your judgement come into play.
However, by playing with the Yearly Increases, you can determine if the down-side is acceptable. For example, if Telus does not have any growth (change the Stock and Dividends Yearly Increase to 0), and the Dividends/Share does not change and Reinvest is Yes, the Return is just a few hundred dollars more than investing in a 3% GIC. Using yearly increases equal to that of the lowest performing stock (Rogers), it almost exactly matches the Rogers line on the chart.
You can try other combinations of changes, such as skipping dividends some or all years, reducing any of the Yearly Increases and decreasing the stock prices in some years. In fact, any of the Yearly Increases can be negative and the table and chart will show the decreasing values.
Explanation of Values in the GIC Area
The Start of Year column is the start of the year, so there are 6 years showing with year 6 being the same as the end of 5 years. (These are used because the chart would not look right if years 0 to 5 were used.)
The first row of the Value column is set to the Starting Value. You enter the Interest rate value. The Interest Paid is calculated by multiplying the Value by the Interest rate. The Interest Taxes uses the Marginal Tax Rate.
The Value in subsequent rows is calculated by adding the Value from the row above and the Interest Paid, but only if the Reinvest is equal to Yes. The Interest Taxes are NOT subtracted from the Interest Paid.
The Return Value shown after the Start of Year 6 is calculated as given above. The last 2 rows are used to obtain the values for the Return and are useful totals in themselves. On the second last row, there is always 0 Taxes On Final Value. The Totals for the Interest Paid and Interest Taxes are the sum of the rows above. The last line has the Value After Taxes which is always equal to the Value at the start of year 6. The Total Interest After Taxes is the Total Interest Paid less the Total Interest Taxes.
Explanation of Values in the Company Area
For any year, the # Shares column takes the Value and divides it by the Price. It is rounded to 2 decimal places to ensure there is no error in the calculations. In real-life, only integer values will be available. The End of Yr Div Paid is the Dividends/Share times the # Shares. The Div Taxes is the Effective Dividend Tax Rate calculated in the Entry area times the Div Paid value. The Yield is the Dividends/Year divided by the Stock Price. Many analysts use the Yield as a method of ranking companies.
For years other than year 1, the first 5 columns after the year column use values from the year above. For example, the Stock Value for the start of year 2 is the value for year 1 times one plus the Stock Yearly Increase from year 1 plus the Div Paid from year 1 if Reinvest is Yes. The Stock Price in year 2 is that of year 1 times one plus the Stock Yearly Increase from year 1. Each year’s Stock Yearly Increase and Dividends Yearly Increase is the same as that of the year above. This was done to simplify creating new Company areas and to permit changing the increases in any year, if desired. The Dividends/Share in year 2 is that of year 1 times one plus the entered Dividends/Share from year 1. The # Shares for year 2 is a little different in that it is the # Shares from year 1 plus, if Reinvest is Yes, the Div Paid of year 1 divided by the Stock Price of year 2.
This continues for each year. Check the equations used, if you are interested.
The Return Value shown after the Start of Year 6 is calculated as given above. The last 2 rows are used to obtain the values for the Return and are useful totals in themselves. On the second last row, Capital Gains Taxes is the Value at the start of year 6 minus the Value at the start of year 1 times the Marginal Tax Rate times the Capital Gains Rate. The Totals for the End of Yr Div Paid and Div Taxes are the sum of the rows above. The last line has the Value After Taxes which is equal to the Value at the start of year 6 less the Capital Gains Taxes. The Total Dividends After Taxes is the Total End of Yr Div Paid less the Total Div Taxes.