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. In general, such a process will involve seeking data from multiple sources. To avoid the ambiguity with respect to data sources, however, in this project we will focus on Intelligize whenever you are asked to find and download data. To be fair to everyone, I will only grade submissions that use data fromIntelligize.

Note: some of the values for dividends as reported inIntelligize may be negative. In those cases, take the absolute values of these numbers.

Note:

A. You need to submit your EXCEL file with the data (including the Excel file that I provide) and calculations, and this assignment in Word format. But I will only grade the Word document. The Excel file is for me to check your data and verify calculations. As a result, please make sure you put all your answers on this assignment. Please do not make me refer to the external Excel file for grading purpose.

B. Keep two decimal places for all your numerical answers.

In this project, we will apply the DGM to estimate the intrinsic value of the common stock of Exelon, a publicly traded utility company (ticker: EXC).

DGM formula:

V = D1/(R-g), where

V is the intrinsic value;

D1 is the expected dividend in the next period;

R is the required rate of return;

and g is the expected growth rate of dividend.

We will try to estimate each of these inputs in this project.

1. (7 points) Find or calculate the aggregate cash dividend paid by Exelon in 2023 (if necessary, use the weighted average basic shares outstanding for your calculation). Is this D₀ or D₁ based on the terminologies in our course? Note that we focus on aggregate dividend rather than per share dividend here to consider the possibility that the firm may have altered its shares outstanding during the time period as in Question 2, hence making the estimates of the growth rate of per share dividends less relevant (hint: you may need to check the "Statement of Cash Flows" to find information on dividends).

Answer:

2. (9 points) Calculate the annual growth rate of aggregate dividend for the past 5 years (that is, between 2019 and 2023). Note that g_t = D_t/D_t-1 - 1, where g_t is the growth rate of aggregate dividend at year t, D_t is the aggregate dividend at year t, and D_t-1is the aggregate dividend at year t-1. Then calculate the average annual growth rate of aggregate dividend over the past five years, g. Fill in the blanks in the following table.

Answer:

	Aggregate dividend ($million)	Growth rate	Average growth rate
2023
2022
2021
2020
2019
2018

It might be reasonable to assume that the average historical growth rate of dividend will be the growth rate of future dividends. That is, the average growth rate we just estimated may be taken as a proxy for g as in the DGM. Our next goal is to estimate R, the required rate of return of Exelon stock.

The CAPM states that: R = Rf + * (Rm - Rf), where Rf is the risk-free rate, is a measure of systematic risk, and Rm is the expected rate of return of the market. Questions 3-9 use this formula to estimate R. Specifically, Question 3 estimates . Questions 4-7 estimate the market risk premium, Rm-Rf, and Question 8 estimates the risk-free rate. Question 9 integrates all these inputs to get an estimate for R. Note that in general, we estimate the market risk premium, Rm - Rf, as a whole, rather than estimating the market return and the risk-free rate separately.

3. (3 points) Find an estimate for inIntelligize.

Answer:

Questions 4-9 are based on the worksheet titled "Price&Yield Data (1985-2024)", which lists the monthly price and yield data for S&P 500 index and 10-year Treasury bond between Jan, 1985 and Mar, 2024, respectively.

4. (7 points) As we learned in Chapter 6, one proxy for the market is the S&P 500 index. Given the price of S&P 500 index at each month between January, 1985 and Mar, 2024, calculate the monthly return. Note that the relation between price and return is the following:

Rm_t = Pm_t/Pm_t-1 - 1, where Rm_t is the monthly market return (proxied by S&P 500 index) at month t, Pm_t and Pm_t-1 are the prices at month t and t-1, respectively. Note that your calculation should start from Feb, 1985 since there's no price information before Jan, 1985 for you to calculate the return. Produce a screenshot or snippet of a portion of your output.

Answer:

5. (10 points) Use the knowledge we gained in Chapter 7 (bond valuation), 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 snippet of a portion of your output.