Determining Yearly Rates for Stock Prices and Dividends


There is a lot of information available to help you evaluate a stock you might be considering. The long-term yearly rate by which the stock price and dividends has increased (or decreased) is an important statistic that is not easy to find, without paying for the analysis. An Excel spreadsheet can be downloaded that you can use to generate this information. The resulting yearly rate for both the stock and dividends can be used in FinanceBase-Lifetime Finances to help project the long-term cash flow.

Selecting a Stock

This post does not provide advise on how to select a stock for investment. You can find a lot of advice on the web by doing a simple search. Many give rules and strategies for evaluating and selecting stocks. The analysis presented here deals with the change is stock prices and dividends over a 5 year period. You can use the spreadsheet to enter any company’s data and thus evaluate the company’s performance.

If you are looking for some advice on selecting a dividend stock, I find the 6 basic metrics given in a July 2014 post called Dividend Investing to Retire on Passive Income that is one of the many posts on Jim Yih’s retirehappy.ca website to be very useful. The emphasis on a positive dividend growth and a dividend yield of over 2.5% fits nicely with what you can obtain from using the spreadsheet.

Time Frame

Most companies that are worth-while as an investment have been in business for a number of years. Trying to determine the optimum time to buy a stock and to time the business cycle properly takes a lot of skill and luck. It is not something most people are good at and instead use brokers, analysts and mutual funds to provide the expertise.

However, there is one statistic that you can easily do yourself and that is a long-term indicator of how the stock has performed and just might perform in the future. This is the change is stock price over time and the rate at which it changes. Any number of years can be analyzed, but 5 years seems to be a reasonable time frame as it often covers ups and downs in the market.

Sources of Data

Finding a suitable source for 5 years of stock market prices for a company depends on what you may already have found or what you may be familiar with. I have found that Yahoo! Finances provides an easy way to obtain data. The address for Canada is ca.finance.yahoo.com and for the USA it is finance.yahoo.com. Use any other source that you like, as long as the data can be copied and pasted into the spreadsheet in the correct format, as explained below.

Obtaining 5 Years of Stock Prices, by Month

In the example spreadsheet, BCE Inc. will be used as it has a well behaved 5-year history and illustrates how to obtain the yearly rate. To use Yahoo! Finance, click on the Canada link above. Near the top-left of the window is an entry box where you can “Enter Symbol“. Enter the stock market symbol BCE and select from the drop-down that appears the BCE.TO symbol which will provide access to the Toronto Stock Exchange (TSX) values for the company. Then click on the Look Up button.

When the BCE page appears, examine any other data on the page desired to see how it has performed over the past few days and year. While the chart to the far right shows the stock prices, it is not what we want. Instead, along the left is a list of items that can be clicked for more details. Click on the Historical Prices which is a few items down from the top.

Once the Historical Prices page opens, Set Data Range to any five year period. The data that we will use is  Feb 2, 2010 to Jan 2, 2015. Then click on the Monthly radio button to the right of the dates. Finally, click on the Get Prices button.

The table below will fill with 60 months of data, plus the Dividends. The columns are Date, Open, High, Low, Close, Avg Vol and Adj Close. Yahoo provides monthly data at the start of a working day each month (not the end), so the dates used above are 1 working day into the month. Thus the dates in the table go from Feb 2, 2010 to Jan 2, 2015, but some months are on the first, second or third of the month. The Dividends are given as an amount for the date of payout.

Scroll to  the bottom of the table and click on the “Download to Spreadsheet” link. The data that appears is comma delimited and only has a header and 60 months of stock prices (no dividends). Select everything except the header and copy it. Notice that each line has the columns separated by commas.

Loading Data into the Spreadsheet

The remainder of this post explains how to use the Excel spreadsheet.

Please click on Download Spreadsheet now and open it if you want to use it and to follow along and make the changes indicated below.

It does not have any data in it. Enter the Company name and the Symbol into the cells just above the empty chart. When we are finished, it will look like this.

First, make sure that the 60 months of data are in the clipboard as indicated above.

– Then click on the cell just below the Date header.

– Paste the data.

– Click on the small Paste Options box that appears and select the Use Text Import Wizard radio button.

– On the Text Import Wizard window that opens, select the Delimited radio button and click on the Next button.

– Only select the Comma check box as a delimiter and click on the Finish button. There may be an alert that asks if you want to replace the content of the selected cells. Click on OK.

– The columns will now be properly filled and the chart will display the stock prices in blue,  a dotted trend line and a red line at the bottom.

Completing the Stock Price Chart

The blue line on the chart is the closing stock prices as loaded into the table below. The prices are continually increasing over the 5 years with minor ups and downs.

The dotted black line is a trend line that is generated by Excel that fits a straight line (using the method of least squares) to the Date and Close columns. The resulting equation is shown in on the graph and is y = 0.0115 x – 432.23. This is the y=mx+b equation of a straight line where x values are the Dates, y values are the Close stock prices, m is the slope of the line and b is the intercept.

The slope can be thought of as the growth of the stock prices over the 5 year period and is 11.5%. It turns out that this is a little high because it does not take compounding into effect.

To determine the rate to be used, a column labelled By Year has been added to the table. It takes the Start Amount and the Yearly Rate that you enter just above the chart and plots the red line on the chart. Adjust these two values until the red line overlays the dotted trend line. After a bit of experimentation, the values that do this are Start Amount = 32 and Yearly Rate = 10%.

Note that the slope of 11.5% is higher than the Yearly Rate of 10% as expected. Experiment with the Yearly Rate to see what happens to the By Year line. The Start Amount and Yearly Rate can be adjusted to match any portion of the stock prices.

Quarterly Dividends

Many stocks provide dividends, usually quarterly. If the stock you have chosen has quarterly dividends, the spreadsheet has a second entry area and chart to the right of the Stock Price Chart. It looks similar to the Stock Price Chart and works the same way, except only Dividends are used.

The procedure for getting 5 years of quarterly dividends (20 values ) is to return to the Yahoo page that had the stock prices. Leave the Data Range at the same dates, select the Dividends only radio button and click on the Get Prices button. Once the table has filled with dividends, scroll to the bottom and click on Download to Spreadsheet. When the next window opens, select all of the dividends (excluding the header) and copy them.

Go to the spreadsheet and select the cell just below the Date header of the table in the Quarterly Dividends area. Paste, use the Wizard and watch the chart change. Enter the Dividend Start Amt and Dividend Yearly Rate. The values that work best are Dividend Start Amount= .44  and Dividend Yearly Rate = 8%. The slope of .0001 is not useful for this chart as the dates are every 3 months.

A fourth line has been added to the Dividends chart. It is the Yearly Yield and it is the purple dotted line. The Yearly Yield uses the closest Close date to the Dividend data and divides the Dividend amount by the Close amount and multiplies by 4 to convert to a yearly equivalent. Over the 5 years, while the Dividends and the Dividend Yearly Rate have increased, the Yield has remained almost constant, with a slight decrease in the past 2 years. The Yearly Yield is a useful metric in evaluating stocks, but is very volatile as it depends on the stock price and the company’s willingness to issue a dividend and then setting it accordingly. BCE seems to have set a Yearly Yield of between 4.8% and 5.6%.

Monthly Dividends

After releasing this post, I found some stocks that issue monthly dividends. These cannot be used in the dividends area indicated above because the formulas only supports 20 entries. Consequently, I have added a Monthly Dividends area to the right of the Quarterly Dividends. Scroll the spreadsheet to the right to see this area if your dividends are issued monthly.

The procedure for getting 5 years of monthly dividends (60 values ) is similar to that for getting quarterly dividends: Return to the Yahoo page that had the stock prices. Leave the Data Range at the same dates, select the Dividends only radio button and click on the Get Prices button. Once the table has filled with dividends, scroll to the bottom and click on Download to Spreadsheet. When the next window opens, select all of the dividends (excluding the header) and copy them.

Go to the spreadsheet and select the cell just below the Date header of the table in the Monthly Dividends area. Paste, use the Wizard and watch the chart change. Enter the Dividend Start Amount and Dividend Yearly Rate and adjust them until the line is where you want it.

Saving the Results

If you want to keep the modified spreadsheet and also keep the downloaded version that has no data in it, do a Save As and add BCE to the end of the name (before the suffix). This way you can reuse the downloaded spreadsheet for other companies.

Add to the Spreadsheet as Desired

You can make any changes you want to the spreadsheet as none of the cells are locked. If you make a mistake and overwrite some of the equations or modify the charts, just download a new version and start again. Since I first designed the spreadsheet, I added the Yield equations and chart line and a Monlty Dividends area and it was not difficult to do, if you have any knowledge of Excel. Any suggestions for additions or modifications will be gratefully accepted.

Using with FinanceBase-Lifetime Finances

The rates obtained for any company can be used in Lifetime Finances as follows:

– The Yearly Rate is what should be used on the Entry page in Lifetime Finances for the Shares or Dividend Shares %Rate value. It will be  used for the growth of the stock in future years.

– The Dividend Yearly Rate is what should be used on the Entry page in Lifetime Finances for the Dividend Payment %Rate value. It will be  used for the growth of the stock’s dividends in future years.

– The Yearly Yield  can be used on the Rates page, where the drop-down menu that shows Div Pay and Div Yield can be set to Div Yield. Then the Yearly Yield can be entered, if it does not match what is already there.

Lifetime Finances uses your assets, income & expenses during your lifetime to project whether you will outlive your money. It can be used at any age and provides a yearly cash flow (income less expenses, including taxes) from now to 45 years after retirement. If desired, changes can be made in any year. You can see what it provides by clicking on this link to FinanceBase-Lifetime Finances. It can be downloaded free in a demo mode for 31 days. After that a license is required which is a very nominal amount.