UNIVERSITY OF WATERLOO

Faculty of Mathematics

ROYAL BANK OF CANADA STOCK ANALYSIS

Royal Bank of Canada

Toronto, Ontario

Prepared by
Chak Ho Tang
3B Math/Financial Analysis & Risk Management
ID: 20463299
24th August 2015

42 Markham Street, Toronto, ON

M6J 2G2
24th August 2015
Didier Baczynski
Manager, Solution Architect
Royal Bank of Canada
155 Wellington Street W, ON
M5V 3L3
Dear Mr. Baczynski:
I have prepared the enclosed report, Royal Bank of Canada Stock Analysis as my 3B work
report for RBC. This report, the third of four work reports that the Co-operative Education
Program needs that I complete as part of my BMath Co-op degree requirements, has not
received academic credit.
As a part of summer coop student group, a student challenge called Business and Financial
insight was issued by Campus Recruitment team. Even though my job title is Technical
System Analysis, I always want to do data analysis by applying the skills and theories that
learnt at campus. This report is study of the trend or pattern of Royal Bank of Canada to
determinate whether investors buy the stock.
The Faculty of Mathematics requests that you evaluate this report for command of topic and
technical content/analysis. Following your assessment, the report with your evaluation, will
be submitted to the Math Undergrad Office for evaluation on campus by qualified work
report markers. The combined marks determine whether the report will receive credit and
whether it will be considered for an award.
I would like to thank you for your assistance in preparing this report.
Yours sincerely,
Chak Ho Tang
Encl.

TABLE OF CONTENTS
EXECUTIVE SUMMARY ..................................................................................................... iii
1.0 INTRODUCTION ............................................................................................................... 1
2.0 LONG TERM VALUATION ANALYSIS ......................................................................... 2
2.1 Fundamental Analysis...................................................................................................... 2
Dividend Discount Model .................................................................................................. 2
2.2 Quantitative Analysis ....................................................................................................... 3
3.0 SHORT TERM ANALYSIS ............................................................................................... 9
3.1 Technical Analysis ........................................................................................................... 9
Relative Strength Index.................................................................................................... 10
Bollinger Band ............................................................................................................... 10
4.0 CONCLUSIONS ............................................................................................................... 11
REFERENCES ........................................................................................................................ 12
APPENDIX A - Unemployment rate and S&P500 Regression Analysis................................ 13
APPENDIX B - Royal Bank of Canada and S&P500 Regression Analysis ........................... 14
APPENDIX C - t+1 Test ......................................................................................................... 15
APPENDIX D - t+X Test ........................................................................................................ 17
APPENDIX E - Valuation ....................................................................................................... 19
APPENDIX F - Application of Dividend Discount Model ..................................................... 21
List of Figures and Tables
Table.1 Decision Logic Table .................................................................................................... 2
Figure.1 Estimated S&P500 Index and True S&P500 Index .................................................... 5
Figure.2 Estimated RY Price and True RY Price ...................................................................... 6
Figure.3 Difference of Estimated t+1 Value and True t+1 Value ............................................. 7
Figure.4 Difference of Estimating 20-Years Trend in 1995 and True Price ............................. 8
Figure.5 Estimation of 20-Year Trend for RY in 1995 and True RY from 1995 to 2015 ......... 8
Figure.6 Estimated RY Trend .................................................................................................... 9
Figure.7 Relative Strength Index & Bollinger Band for RY in 2015 ...................................... 11

ii

EXECUTIVE SUMMARY
This report assists investors who are interested in Royal Bank of Canada stock but have
limited capital, low trade frequency, and low risk tolerance to make a decision of buying the
stock as a mid-long term investment. It analyses in long-term and short-term aspects.

In long-term analysis, fundamental analysis and quantitative analysis are performed to

determinate the macro-trend of the company and valuate the fair price. Dividend Discount
Model is a common method by summing all expected dividends under assumption of
constant dividend growth rate. It stated that the current price - $55 is undervalued. Statistical
model is constructed in the report by establishing a relationship between macroeconomic
indicator-unemployment rate and the stock. Linear regression assumptions and calculations
are applied to quantify the relationship. Under model estimation, it suggests that the price
tend to increase according to the trend formed by historical data. As a result, it is favourable
to long and hold.

In short-term analysis, technical analysis is performed to maximize opportunities for buy low
sell high. Several of technical indicators are introduced. Relative Strength index determinates
the stock is oversold but cannot confirm a signal for forming trend. Bollinger Band presents
the moving average and volatility within the stock graph. By observing the band pattern, it
suggests that there will be more violent fluctuation in price and more chances will be created.
Thus, it is against buying the stock right now.

This report concludes that investors should wait for opportunities to appear and buy the stock.

iii

1.0 INTRODUCTION
Given Royal Bank of Canada stock in United States stock market (NYSE:RY), the report
assists investors to make a decision of buy or not buy under several assumptions. Investment
plans are very specific to people. There are several key factors affecting individual investor to
buy or not. Thus, researcher defines the investors as people who have limited capital and low
trade frequency. The investors are willing to hold the stock within one year to three years.
Under low risk tolerance and limited research scope, no margin or derivatives are included in
this report.
The report covers:
l

2.1 Fundamental analysis includes applying common valuation method.

2.2 Quantitative analysis includes applying statistics and mathematics model.

3.1 Technical analysis includes technical indicators analysis.

The aforementioned sections influence two decisions-- long-term valuation decision and
short-term decision. Long-term valuation estimates the macro-trend of the company and
short-term information seizes the invest timing. These critiques are considered to help the
defined investors to make the final decision.

The evaluation logic is as follows:

Decision
Long-term

Short-term Favourable

Short-term Not Favourable

Buy, now

Buy, but not now

Favourable
Long-term

Not Buy (speculators are Not Buy

Not

not applicable in this

Favourable

research scope)

Table.1 Decision Logic Table

2.0 LONG TERM VALUATION ANALYSIS

Investors are more willing to put their capitals into companies with bright prospects. This
section will evaluate whether RBC is worth to invest in long term (1 to 3 years).

2.1 Fundamental Analysis

Dividend Discount Model (DDM)
Dividend Discount Model is a method to valuate the price of a stock by summing all the
expected dividends in the future under discount rate. Several versions of the formula are
developed to calculate the fair price under different dividend growth rates. In this report, it
assumes the dividend growth rate is constant. Then, the price can be calculated as:

Collected 8 years of dividend data from RBC official website, the constant growth rate for
dividend is 5.72%. The expected dividend for a RBC common shareholder in 2015 is $3. In
the market side, the risk-free rate of return is 4.88% (Damodaran, 2015). The expected return

rate under Capital Allocation Pricing Model is 8.14%. Thus, DDM valuates the stock worth
$123.761.

The current price of RBC is $55. DDM concludes the stock is strongly undervalued and
suggests it is favourable to buy. However, it tends to be an unrealistic high price. It could be
accounted by the assumptions. Constant dividend growth rate is usually not true. The
risk-free rate selected from 10 years T-bill might be to low. Therefore, it suffers a problem of
inflated price. As a result, other analysis should be taken in consideration.

2.2 Quantitative Analysis

Using historical data, this section constructs a simple statistic model to quantify the
relationship between macroeconomic indicator and a stock. With a model consumes
historical data gathered from financial website (Yahoo! Finance), investors can have
numerical evidences to visually foresee a trend for a specific stock.

Economy influences stock market. Healthy economy generally boost productivity and
provide good views to society. People expect increase in return and start to invest. Having
more capital to reflect the true value of the prosperous economy, most of the stock indices
rise. Bullish market is formed. Investors prefer bullish market; they have a higher probability
to make profits. Identify the correct economy trend could help investors decide when to

See appendix F for calculation

participate the game. There are several economy indicators: gross domestic product,
consumer price index, inflation rate, and unemployment rate etc. Due to time constraint and
research scope, the only indicator selected to implement to the model is unemployment rate2.
S&P 500 index is the benchmark stock selected to represent the US stock market. In this
section, the model approximates the benchmark index by unemployment rates.

To illustrate the relationship and establish the model, 238 months of unemployment rate and
S&P stock index3 are collected from October 1995 to July 2015. The model imposed several
assumptions: linear regression assumptions4 and time variable starts from 1995 monthly.
According to multi-linear regression, the best-fit linear equation is as follows:

It describes the negative relationship between unemployment rate and S&P 500 index as time
progress. Specifically, on average, 1-point increase in unemployment rate and 1 month
passed contribute -141.7689 points decreases and 5.4280 increases in S&P500 index
respectively56.

Unemployment rate and stock relationship is verified in other methods, see Farsio F and Fazel S, 2013 in references
Data were extracted from Yahoo! Finance, see references
4
Serial Correlations and Multi-collinearity are found in the model. Further research is needed to modify the model. Due to
time constraint, the redemption is to enforce the assumptions
5
See appendix A for complete regression analysis
6
R statistic for the equation is 0.89, meaning the correlation of the index, time, and unemployment rate is strong. R2 statistic
is 0.80, meaning 80% of the S&P 500 index variation are captured by unemployment rate and time. The null hypothesis (H0):
selected parameter equals zero, meaning it is insignificant, is rejected. Both variables are statistically significant.
3

True and estimated S&P 500 index

2500.0
2000.0
S&P500
_true

index

1500.0

S&P500
_estima
te

1000.0

0.0

1
18
35
52
69
86
103
120
137
154
171
188
205
222
239
256

500.0

time(month)

Figure.1 Estimated S&P500 Index and True S&P500 Index

To quantify the relationship between a particular stock and stock market, the beta from
Capital Allocation Pricing Model (CAPM) is considered. Beta measures the volatility of a
stock compare to the benchmark stock. If beta is very close to 1, it means the percentage
change of the stock price is similar to its own benchmark stock. This time, S&P 500 is
selected to be the benchmark stock again, and the stock is Royal Bank of Canada
(NYSE:RY).
The assumption imposed is linear regression assumptions. 238 months stock data of S&P500
and RY7 are collected and transformed into percentage change. The best-fit line linear
regression calculated is as follows:

Data were extracted from Yahoo! Finance, see references

The beta is 1.0271. It means RY is less fluctuate from the market. On average, 1% increase in
S&P 500 index contributes 1.0271% increase in RY8.

True and estimate %change of RY

0.3
0.2

RY
%change_true

0
-0.1

1
20
39
58
77
96
115
134
153
172
191
210
229
248
267

0.1

RY
%change_estim
ate

-0.2
-0.3
-0.4

Figure.2 Estimated RY Price and True RY Price

Combining 2 equations, this model estimates the trend by consuming historical price
movements and expected unemployment rates.

Before valuation, testing the accuracy of the model with historical data is necessary. Since it
is a simple setup, there are several assumptions for the model:
Linear Regression assumptions and the time unit assumptions are inherited from the best-fit
lines. For testing purposes, perfect expected unemployment rate is an additional assumption
imposed.

See appendix B for complete regression analysis

Time+1 Test:
To test how accurate the model estimates the RY stock price for t+1 at t only (at any month,
predict next months stock price), simulation of all the t+1 estimations from Oct 1995 to June
2015 were generated9. The test shows the average magnitude difference from actual stock and
the estimated model is 0.198 from 237 results. Observed in 1999 US market crash, 2001
internet bubble burst, and 2008 financial crisis, there exist huge magnitude differences. This
reflects that the model is less problematic for estimating steady trend without anticipating

20.00
15.00
10.00
5.00
0.00
-5.00
-10.00
-15.00

Difference
difference

1
20
39
58
77
96
115
134
153
172
191
210
229
248

Price difference USD

catastrophic events or rapid changes.

time (month)

Figure.3 Difference of Estimated T+1 Value and True T+1 Value

Time+X Test:
To realise the relationship between wider predict interval and accuracy, after X month
parameter (t+X) and the corresponding unemployment rate pairs are inputted to estimate the
RY stock after X months10. It is discovered that the model is more accurate after t = 190 (July
2011) and the prediction interval should be limited to 10, meaning it is more accurate within

9

See appendix C for partial test data

See appendix D for partial test data

10

10 months. Predictions beyond 10 months suffer great undervalue problem.

Difference

0.00
-20.00

difference
1
20
39
58
77
96
115
134
153
172
191
210
229
248

price difference USD

20.00

-40.00
-60.00
-80.00

time(month)

Figure.4 Difference of Estimating 20-Year Trend in 1995 and True Prices

Adjusted Model:
To increase accuracy, adapting the observations from test results above created the adjusted
model. It limits the prediction interval as t+10 and it calculates the prediction data based on
previous estimation. It has a more accurate description of data even estimating 20 years after.

Adjusted model
80
70
RY_estim
ate

price USD

60
50
40

RY_TRUE

30
20

1
14
27
40
53
66
79
92
105
118
131
144
157
170
183
196
209
222
235
248

10

time(month)

Figure.5 Estimation of 20-Year Trend for RY in 1995 and True RY from 1995 to 2015

As a result, the adjusted model could have a better approximation of the trend. This simple
statistic model reveals that RY would drop then increase in the future11. It is a signal of
favourable to buy. However, due to assumptions, properties and model limitations, the model
is not an efficient design and does not give precise predictions.

80
70
60
50
40
30
20
10
0

RY|t
Predict
1
15
29
43
57
71
85
99
113
127
141
155
169
183
197
211
225
239

Price USD

Estimation

Time(month)

Figure.6 Estimated RY Trend

3.0 SHORT TERM ANALYSIS

Investors perform profit maximizing by buy low sell high. Having long-term vision,
short-term analysis helps investors to buy at an attractive price. This section is a
micromanagement stage to identify whether the investors trade right now.

3.1 Technical Analysis

Technical indicators are studied to understand the current market status. There are two
common technical indicators:

11

See appendix E for valuation data

Relative Strength Index (RSI)

Relative strength index determinates whether a stock is overbought or oversold. If RSI is over
50, it is over bought; below 50, it is over sold. People believe the index move pass to 50
eventually. For RY, it is oversold at this price. However, RSI at this point does not confirm
there exists a signal (divergence) of trend forming. It gives no conclusion.

Bollinger Band (BB)

One standard deviation is added and subtracted from the moving average to form the upper
and lower bound of the band respectively. People believe the bandwidth follows expansion
and contraction cycle. Investor could predict when the stock has a large or small volatility by
observing the cycle. For RY, the band starts expanding meaning there might be large price
movement for a period of time in the future. This indicates investor could have more chances
to seize a better price to buy. Therefore, it prefers not buying now and waits for opportunities
(not favourable).

10

Figure.7 Relative Strength Index and Bollinger Band for RY in 2015 (Trading view, 2015)

4.0 CONCLUSIONS
For long-term decision, dividend discount model and statistic model both valuate the fair
stock price for Royal Bank of Canada is higher than current price, it is favourable to long.
For short-term decision, Relative Strength Index states it is over-sold and Bollinger Band
suggests investors not to buy now.
By the decision table, the investor should wait for an opportunity and long the stock.

11

REFERENCES
Damodaran, Aswath. Annual Returns on Stock, T.Bonds and T.Bills: 1928 - Current. (2015,
January 5). Retrieved August 24, 2015, from
http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/histretSP.html
Civilian Unemployment Rate. (2015, August 7). Retrieved August 24, 2015, from
https://research.stlouisfed.org/fred2/series/UNRATE#
Dividend History For Common Shares Royal Bank of Canada. (n.d.). Retrieved August 24,
2015.
Farsio, F., & Fazel, S. (2013). The Stock Market/Unemployment Relationship in USA, China
and Japan. Retrieved August 24, 2015, from
http://ccsenet.org/journal/index.php/ijef/article/viewFile/25036/15607
GSPC: Summary for S&P 500- Yahoo! Canada Finance. (n.d.). Retrieved August 24, 2015.
Investopedia - Educating the world about finance. (n.d.). Retrieved August 24, 2015.
RY:

Stock

chart

TradingView.

(n.d.).

Retrieved

August

24,

2015,

from

https://www.tradingview.com/chart/9MEDv98D/
Royal Bank of Canada Yahoo! Finance. (n.d.). Retrieved August 24, 2015.
United States Unemployment Rate Forecast. Trading Economics (n.d.). Retrieved August
24, 2015, from http://www.tradingeconomics.com/united-states/unemployment-rate/forecast

12

Appendix A - Unemployment rate and S&P 500 index regression analysis

Linear Regression
Regression Statistics
R
0.89194
R Square
0.79556
Adjusted R Square
0.79382
S
149.90892
Total number of observations
238

S&P500 = 1443.0967 - 141.7689 * Un rate + 5.4280 * t

ANOVA
d.f.
Regression
Residual
Total

SS

2. 20,550,801.13991
235. 5,281,080.53104
237. 25,831,881.67095
Coefficients

Intercept
Un rate
t

1,443.09666
-141.76885
5.42798
1.97011

T (5%)
LCL - Lower value of a reliable interval (LCL)
UCL - Upper value of a reliable interval (UCL)

Standard Error

35.80733
7.20107
0.17967

MS

10,275,400.56995
22,472.68311

LCL

1,372.55227
-155.95575
5.07401

457.2396

UCL

1,513.64104
-127.58195
5.78195

p-level

0.E+0

t Stat

H0 (5%) rejected?
40.30171 Yes
-19.68719 Yes
30.2106 Yes

Appendix B - Royal Bank of Canada stock and S&P 500 index regression analysis
Linear Regression
Regression Statistics
R
R Square
Adjusted R Square
S
Total number of observations

0.66553
0.44293
0.44057
0.05125
238

RY%change = 0.0075 + 1.0271 * S&P500%change

ANOVA
d.f.
Regression
Residual
Total

1.
236.
237.

Coefficients
Intercept
0.00753
S&P500%change
1.02709
T (5%)
1.97007
LCL - Lower value of a reliable interval (LCL)
UCL - Upper value of a reliable interval (UCL)

SS
0.49277
0.61975
1.11252

MS

Standard Error
0.00335
0.07498

LCL

0.49277
0.00263

0.00094
0.87937

F
187.64364

UCL
0.01413
1.1748

p-level
0.E+0

t Stat
H0 (5%) rejected?
2.25188 Yes
13.69831 Yes

Appendix C - t+1 Test

Year
1995-10-01
1995-11-01
1995-12-01
1996-01-01
1996-02-01
1996-03-01
1996-04-01
1996-05-01
1996-06-01
1996-07-01
1996-08-01
1996-09-01
1996-10-01
1996-11-01
1996-12-01
1997-01-01
1997-02-01
1997-03-01
1997-04-01
1997-05-01
1997-06-01
1997-07-01
1997-08-01
1997-09-01
1997-10-01
1997-11-01

t
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

Un rate t+1
S&P|t
5.6 581.5
5.6 605.4
5.6 615.9
5.5 636.0
5.5 640.4
5.6 645.5
5.6 654.2
5.3 669.1
5.5 670.6
5.1 640.0
5.2 652.0
5.2 687.3
5.4 705.3
5.4 757.0
5.3 740.7
5.2 786.2
5.2 790.8
5.1 757.1
4.9 801.3
5 848.3
4.9 885.1
4.8 954.3
4.9 899.5
4.7 947.3
4.6 914.6
4.7 955.4

RY|t
2.62
2.678
2.678
2.809
2.678
2.736
2.78
2.794
2.809
2.877
2.965
3.363
3.936
4.428
4.175
4.473
4.864
4.698
4.851
5.308
5.521
5.894
5.764
6.032
6.633
6.61

S&P|t+X
660.047
665.475
670.903
690.508
695.936
687.187
692.615
740.574
717.648
779.783
771.034
776.462
753.537
758.965
778.57
798.174
803.602
823.207
856.989
848.24
867.845
887.45
878.701
912.483
932.088
923.339

RY_estimate
RY_true
difference
%diff
3.00617
2.67784
0.33
11.57%
2.97349
2.67784
0.30
10.47%
2.94562
2.80882
0.14
4.76%
3.07926
2.67784
0.40
13.97%
2.93845
2.73606
0.20
7.14%
2.93966
2.77972
0.16
5.59%
2.96983
2.79427
0.18
6.09%
3.12453
2.80882
0.32
10.65%
3.03394
2.87667
0.16
5.32%
3.55059
2.96518
0.59
18.02%
3.54907
3.36349
0.19
5.37%
3.84094
3.93639 -0.10
-2.45%
4.24505
4.42844 -0.18
-4.23%
4.47355
4.17496
0.30
6.91%
4.42718
4.47257 -0.05
-1.02%
4.57698
4.86410 -0.29
-6.08%
4.9821
4.69845
0.28
5.86%
5.15871
4.85142
0.31
6.14%
5.2369
5.30766 -0.07
-1.34%
5.34736
5.52058 -0.17
-3.19%
5.45054
5.89423 -0.44
-7.83%
5.51167
5.76393 -0.25
-4.48%
5.66964
6.03220 -0.36
-6.20%
5.84842
6.63287 -0.78
-12.59%
6.81393
6.60970
0.20
3.04%
6.43004
6.54793 -0.12
-1.82%

result
%
mean error0.19843
0.67%
high
16.6435
51.23%
low
-9.5758 -31.53%

1997-12-01
1998-01-01
2014-01-01
2014-02-01
2014-03-01
2014-04-01
2014-05-01
2014-06-01
2014-07-01
2014-08-01
2014-09-01
2014-10-01
2014-11-01
2014-12-01
2015-01-01
2015-02-01
2015-03-01
2015-04-01
2015-05-01
2015-06-01
2015-07-01

27
28
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238

4.6
4.6
6.7
6.6
6.2
6.3
6.1
6.2
6.1
5.9
5.7
5.8
5.6
5.7
5.5
5.5
5.4
5.5
5.3
5.3

970.4
980.3
1782.6
1859.4
1872.3
1883.9
1923.6
1960.2
1930.7
2003.4
1972.3
2018.1
2067.6
2058.9
1995.0
2104.5
2067.9
2085.5
2107.4
2063.1

6.548
6.508
58.42
61.4
62.3
63.68
65.6
68.09
70.99
71.43
68.72
69.1
71.02
67.06
55.49
61.4
59.18
65.81
62.99
60.53

942.944
948.372
1692.83
1712.44
1774.57
1765.82
1799.61
1790.86
1810.46
1844.24
1878.03
1869.28
1903.06
1894.31
1928.09
1933.52
1953.12
1944.38
1978.16
1983.59

6.40538
6.33796
55.8143
56.8437
59.4014
60.0275
61.7245
62.5214
66.9528
66.1036
65.8393
64.3557
65.7177
62.0261
53.9814
56.7067
56.2339
61.7008
59.4689
58.5759

6.50809
7.27879
61.39937
62.29619
63.67616
65.60169
68.08963
70.98826
71.43067
68.71848
69.10185
71.02432
67.06287
55.48827
61.40088
59.18488
65.80976
62.98849
60.53349

-0.10
-0.94
-5.59
-5.45
-4.27
-5.57
-6.37
-8.47
-4.48
-2.61
-3.26
-6.67
-1.35
6.54
-7.42
-2.48
-9.58
-1.29
-1.06

-1.59%
-13.84%
-9.54%
-9.16%
-6.95%
-8.88%
-9.81%
-12.70%
-6.47%
-3.88%
-4.84%
-9.86%
-2.03%
11.14%
-12.88%
-4.28%
-15.72%
-2.07%
-1.77%

Appendix D - t+X test

t+X
Un rate t+1 RY|t+x
RY_TRUE
difference %diff
191
9 42.9435535
43.59490
-0.65 -1.51%
192
9 43.1424274
39.00507
4.14 10.08%
193
8.8 44.3806815
42.20719
2.17
5.02%
194
8.6 45.619848
39.67925
5.94 13.95%
195
8.5 46.3393938
43.96726
2.37
5.25%
196
8.3 47.5799546
45.63264
1.95
4.18%
197
8.3 47.7793661
48.98734
-1.21 -2.50%
198
8.2 48.499784
50.58191
-2.08 -4.20%
199
8.2 48.699296
50.88054
-2.18 -4.38%
200
8.2 48.8988296
43.78666
5.11 11.04%
201
8.2 49.0983847
45.08046
4.02
8.54%
202
8.2 49.2979611
45.50506
3.79
8.01%
203
8 50.5405242
49.84820
0.69
1.38%
204
7.8 51.7838938
51.09418
0.69
1.34%
205
7.8 51.9837504
51.28078
0.70
1.36%
206
7.7 52.705762
52.94428
-0.24 -0.45%
207
7.9 51.8614743
54.22113
-2.36 -4.45%
208
8 51.5393741
56.59592
-5.06 -9.36%
209
7.7 53.30567
56.39616
-3.09 -5.64%
210
7.5 54.5507683
54.75267
-0.20 -0.37%
211
7.6 54.228237
55.41724
-1.19 -2.17%
212
7.5 54.951049
54.50891
0.44
0.81%
213
7.5 55.1512183
53.49965
1.65
3.04%
214
7.3 56.3974229
57.72471
-1.33 -2.33%
215
7.2 57.1209786
57.20592
-0.08 -0.15%
216
7.2 57.3213522
59.48489
-2.16 -3.70%

result
%
mean error 0.99943407
high
13.3676935
low
-6.4178637

1.86%
20.93%
-9.48%

217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238

7.2
7
6.7
6.6
6.7
6.6
6.2
6.3
6.1
6.2
6.1
5.9
5.7
5.8
5.6
5.7
5.5
5.5
5.4
5.5
5.3
5.3

57.5217442
58.7693186
60.5416488
61.2665514
60.9430278
61.6680561
63.9674287
63.6435357
64.8944106
64.5703943
65.2965233
66.5482368
67.8005718
67.4761799
68.7289685
68.4044599
69.6576962
69.8591199
70.5867507
70.2620132
71.516128
71.7176915

62.83824
62.22089
62.88501
58.41625
61.39937
62.29619
63.67616
65.60169
68.08963
70.98826
71.43067
68.71848
69.10185
71.02432
67.06287
55.48827
61.40088
59.18488
65.80976
62.98849
60.53349
58.35000

-5.32
-3.45
-2.34
2.85
-0.46
-0.63
0.29
-1.96
-3.20
-6.42
-6.13
-2.17
-1.30
-3.55
1.67
12.92
8.26
10.67
4.78
7.27
10.98
13.37

-8.84%
-5.71%
-3.80%
4.76%
-0.75%
-1.01%
0.46%
-3.03%
-4.81%
-9.48%
-8.98%
-3.21%
-1.90%
-5.12%
2.45%
20.93%
12.62%
16.58%
7.01%
10.93%
16.67%
20.63%

Appendix E - Valuation
Year
2012-01-01
2012-02-01
2012-03-01
2012-04-01
2012-05-01
2012-06-01
2012-07-01
2012-08-01
2012-09-01
2012-10-01
2012-11-01
2012-12-01
2013-01-01
2013-02-01
2013-03-01
2013-04-01
2013-05-01
2013-06-01
2013-07-01
2013-08-01
2013-09-01
2013-10-01
2013-11-01
2013-12-01
2014-01-01
2014-02-01

Un rate
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221

S&P500
8.3
8.3
8.2
8.2
8.2
8.2
8.2
8.0
7.8
7.8
7.7
7.9
8.0
7.7
7.5
7.6
7.5
7.5
7.3
7.2
7.2
7.2
7.0
6.7
6.6
6.7

1312.410034
1365.680054
1408.469971
1397.910034
1310.329956
1362.160034
1379.319946
1406.579956
1440.670044
1412.160034
1416.180054
1426.189941
1498.109985
1514.680054
1569.189941
1597.569946
1630.73999
1606.280029
1685.72998
1632.969971
1681.550049
1756.540039
1805.810059
1848.359985
1782.589966
1859.449951

RY|t
45.632641
48.987343
50.581913
50.880539
43.786659
45.08046
45.505062
49.848198
51.094181
51.280781
52.944283
54.22113
56.595921
56.396156
54.75267
55.417236
54.508907
53.499653
57.724712
57.205921
59.484894
62.838238
62.22089
62.88501
58.416252
61.399368

Predict
46.62009565
48.24475478
48.98906627
49.86495557
46.04912118
45.74383647
45.77006074
50.37931794
51.62348029
53.09079488
55.41229563
55.43711303
54.6721431
55.71319511
53.37902182
52.72777685
51.45716709
51.48133715
54.05424767
56.05695104
56.7596023
57.53213848
56.57202862
57.50792055
56.11060237
56.17536124

2014-03-01
2014-04-01
2014-05-01
2014-06-01
2014-07-01
2014-08-01
2014-09-01
2014-10-01
2014-11-01
2014-12-01
2015-01-01
2015-02-01
2015-03-01
2015-04-01
2015-05-01
2015-06-01
2015-07-01

222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238

6.6
6.2
6.3
6.1
6.2
6.1
5.9
5.7
5.8
5.6
5.7
5.5
5.5
5.4
5.5
5.3
5.3

Aug-15
Sep-15
Oct-15
Nov-15
Dec-15
Jan-16
Feb-16
Mar-16
Apr-16
May-16
Jun-16

239
240
241
242
243
244
245
246
247
248
249

5.2
5.2
5.1
5.1
5.1
5
5
5
4.9
4.9
4.9

1872.339966
1883.949951
1923.569946
1960.22998
1930.670044
2003.369995
1972.290039
2018.050049
2067.560059
2058.899902
1994.98999
2104.5
2067.889893
2085.51001
2107.389893
2063.110107
2103.840088

62.296192
63.676155
65.601685
68.08963
70.988258
71.430672
68.718483
69.101845
71.024315
67.062874
55.488274
61.400879
59.184879
65.809761
62.988491
60.533493
58.349998
2003.19042 Estimated Price

2008.61842
2028.22331
2033.65131
2039.07931
2058.6842
2064.1122
2069.5402
2089.14509
2094.57309
2100.00109
2119.60598

57.26617609
60.33297532
60.53474859
62.8351242
66.20822884
64.86025621
64.62318906
64.66513226
64.51983541
62.32036643
53.01017995
56.54317456
55.65426213
61.98595999
58.42600246
58.41125306
55.34055812
55.34055812
55.91235668
56.89808529
57.48400748
58.0755384
59.09063865
59.69671764
60.30858468
61.35388221
61.98073377
62.61355797
63.68990084

Appendix F- Application of Dividend Discount Model

2007
2008
2009
2010
2011
2012
2013
2014
2015_expected
risk-free rate
10-year T-Bill

$1.82
$2
$2
$2
$2.08
$2.28
$2.53
$2.84
$3

4.88%

Market average return

S&P500
7.60%
Beta RY

1.2

constant growth rate

($2.84/$1.82)^(1/8)-1 =

5.72%

expected dividend
$2.84*5.72%

$3

Discount rate
4.88+1.2(7.6-4.88)

8.14%

DDM
3/(8.14%-5.72%)

123.966942