FIN 505 – Essentials of Finance

Empirical Project

Dr. Dong Chen

Fall 2020

This empirical project examines your familiarity with the inputs and processes in applying the Dividend Growth Model (DGM) to estimate the intrinsic value of a stock. You will find an accompanying EXCEL data together with this assignment document.

Note: You need to submit both your EXCEL calculation sheet and this assignment.

Questions 1 & 2 are based on the worksheet titled “Income Statement”.

1. (3 points) Find the latest cash dividend per share. How much is this amount for Microsoft? Is this D0 or D1 based on the terminologies in our course?

2. (10 points) Calculate the growth rate of dividend/share for the past 5 years. Note that gt = Dt/Dt-1 – 1, where gt is the growth rate of dividend/share at year t, Dt is the dividend/share at year t, and Dt-1 is the dividend/share at year t-1. Then calculate the average growth rate of dividend/share over the past five years, g. Fill in the blanks in the following table.

Dividend/Share

Growth rate

Average growth rate

2019 (6/30/2020)

2018

2017

2016

2015

The CAPM states that: R = Rf + ? * (Rm – Rf). Questions 3-9 revolve around this formula and ask you to find/calculate the various inputs in this formula.

3. (5 points) Go to finance.yahoo.com, put in the ticker of Microsoft, MSFT, and find the ? of this stock.

Questions 4-9 are based on the worksheet titled “Price&Yield Data (1962-2020)”, which lists the price and yield data for S&P 500 index and 10-year Treasury bond, respectively.

4. (10 points) Given the price of S&P 500 index at each month between January, 1962 and June, 2020, calculate the monthly return. Note that the relation between price and return is the following:

Rmt = Pmt/Pmt-1 – 1, where Rmt is the monthly market return (proxied by S&P 500 index) at month t, Pmt and Pmt-1 are the price at month t and t-1, respectively. Produce a screenshot or snip of a portion of your output.

5. (20 points) Use the knowledge we gained in Session 5, calculate the price of the 10-year Treasury bond for each month. Assume the bond is a zero-coupon bond with a par-value of $1,000. Produce a screenshot or snip of a portion of your output.

6. (10 points) Calculate the monthly return of the Treasury bond given the prices you just calculated, following a similar step as in Q4. Namely, the relation between price and return is the following:

Rft = Pft/Pft-1 – 1, where Rft is the monthly return of the risk-free asset (proxied by the 10-year Treasury bond) at month t, Pft and Pft-1 are its price at month t and t-1, respectively. Produce a screenshot or snip of a portion of your output.

7. (10 points) Calculate the difference between Rm and Rf for each month. Produce a screenshot or snip of a portion of your output. Then, calculate the average value of Rm – Rf over the entire sample period (1962-2020).

8. (6 points) What value from the data sheet can you use to proxy for Rf in the CAPM formula above? Justify your choice.

9. (6 points) Given the inputs of the CAPM equation, calculate the expected rate of return of Microsoft, R. Show the steps of your calculation briefly. As we explained in the class, this expected rate of return is also the cost of equity for Microsoft, and can be used as the discount rate in the DGM valuation process.

10. (10 points) Given the values of the variables we have found so far, and the DGM valuation model, find the intrinsic value of Microsoft stock on June 30, 2020. You can assume that the average dividend growth in the past five years as we found in Q2 will also be applicable for future dividend growth.

11. (10 points) Compare the value you find in Q10 with the actual price of Microsoft stock as found in worksheet titled “Price&Yield Data (2015-2020)”. Based on the difference between the two values, should you buy or sell the stock? Discuss briefly what may drive the discrepancy between the two values.

12 (extra credit). (20 points) One fact about ? Is that it is time-varying and different estimation inputs and time periods can produce very different estimate of this variable. The ? you found in Q3 is valid for the date when you checked the Yahoo! Site, and is based on the past 5 years’ monthly stock return data. This extra credit exercise asks you to estimate a presumably more accurate value of ? based on our date in consideration (June 30, 2020). Use the data in the worksheet titled “Price&Yield Data (2015-2020)” to conduct such an estimation. Produce a screenshot or snip of your output.