Financial Management Fall 2019: Stock Analysis Assignment

This assignment solution delves into the intricacies of stock analysis, providing detailed calculations and explanations for various financial metrics. It begins by calculating expected returns and standard deviations for individual securities and portfolios, including covariance analysis. The solution then explores holding period returns, effective annual rates, dividend yields, and capital gains yields for Microsoft stock. Further analysis includes the calculation of arithmetic and geometric means, and standard deviations for multiple stocks, along with holding period returns incorporating dividends and stock splits. Regression analysis is performed to determine the beta of stocks and assess systematic risk. The assignment also covers portfolio beta, expected returns, and standard deviation calculations using CAPM and other financial models. The document includes numerous figures, tables, and Excel formulas to support the calculations and analysis, providing a comprehensive guide to stock valuation and portfolio management.

Running head: STOCK ANALYSIS
Stock Analysis
Name of the Student:
Name of the University:
Author Note:

1STOCK ANALYSIS
Table of Contents
Answer to Question 1:................................................................................................................2
Part a).....................................................................................................................................2
Part b).....................................................................................................................................2
Part c).....................................................................................................................................3
Part d).....................................................................................................................................4
Part e).....................................................................................................................................5
Answer to Question 2:................................................................................................................6
Part a (i))................................................................................................................................6
Part a (ii))...............................................................................................................................6
Part b).....................................................................................................................................7
Part c).....................................................................................................................................8
Answer to Question 3:................................................................................................................8
Part a, b and c)........................................................................................................................8
Part d)...................................................................................................................................10
Part e)...................................................................................................................................10
Answer to Question 4:..............................................................................................................11
Answer to Question 5:..............................................................................................................13
Part A)..................................................................................................................................13
Part B)..................................................................................................................................14
Answer to Question 6:..............................................................................................................14
Part a & b)............................................................................................................................14

2STOCK ANALYSIS
Part c)...................................................................................................................................15
Answer to Question 7:..............................................................................................................16
Part a)...................................................................................................................................16
Part b)...................................................................................................................................17
Part c)...................................................................................................................................19
Answer to Question 8:..............................................................................................................20
Part a)...................................................................................................................................20
Part b)...................................................................................................................................20
Part c)...................................................................................................................................21
Answer to Question 9:..............................................................................................................22
Part a)...................................................................................................................................22
Part b)...................................................................................................................................22
Part c)...................................................................................................................................22
References and Bibliographies:................................................................................................23

3STOCK ANALYSIS
Answer to Question 1:
Part a)
The expected return for the two securities A and B are calculated in the table below,
Figure 1: Expected Return
Source: By the Author
The expected return on security A is 7.70% and in security B is 13.90% which is
provided in the figure above.
Part b)
The standard deviation of security A is given in the figure below,
Figure 2: Standard Deviation of Security A
Source: By the Author
The standard deviation of security A is calculated at 27.44% and the corresponding
formula are given below the calculation for your reference. The standard deviation of security
B is highlighted in the figure below,

4STOCK ANALYSIS
Figure 3: Standard Deviation of Security B
Source: By the Author
The standard deviation of security B is calculated at 21.43%. The method to calculate
the standard deviation is given in the following points,
 The probability is considered to be x and the expected return is taken as the average
return.
 The expected average return is subtracted from the probability and the results are
squared.
 The squared results are multiplied with the probability of the individual security and
the state of economy.
 The square root of the above results sum gives the standard deviation of each security.
Part c)
The expected return of the portfolio consisting of the two securities A and B with the
weights of 60% and 40% respectively is given in the figure below,

5STOCK ANALYSIS
Figure 4: Expected return in boom cycle
Source: By the Author
The expected portfolio return in the boom cycle is coming at 3.29% and the respective
formula are given in the table along with the calculation.
Part d)
The expected or forecasted return from a portfolio is calculated by multiplying the
expected return of the individual security with their weights in the portfolio. The calculation
for the same is given in the figure below,
Figure 5: Portfolio Return
Source: By the Author
The total portfolio return with the weights of 60% in security A and 40% in security B
generates 10.18%.

6STOCK ANALYSIS
Part e)
To calculate the standard deviation of the portfolio the covariance between the two
stocks A and B is calculated and provided in the table below,
Figure 6: Calculation of Covariance
Source: By the Author
The Covariance between the two stocks is at 0.0043 which is calculated by
multiplying the difference of expected return and average return of the two stock and dividing
by three. We are assuming the data to be a population data so N-1 is not incorporated in the
formula.
Figure 7: Calculation of standard deviation of Portfolio
Source: By the Author
The standard deviation of the portfolio is at 19.12%, and the excel formula for the
same is highlighted in the table below the calculation. To calculate the standard deviation of
the portfolio the following formula has been used,
S . d= √ weigh tA2∗VarianceA + √ weight B2∗varianceB+¿ ¿2 *weight A* Weight B* Cov
(A,B)*S.D A*S.D B

7STOCK ANALYSIS
Answer to Question 2:
Part a (i))
The holding period return for the Microsoft stock is calculated and provided in the
table below,
Figure 8: Holding Period Return of Microsoft
Source: By the Author
The holding period return for the Microsoft stock is at 18.04%, and has been
calculated by using adding the sale price and the dividend less the purchase price. The results
derived is then divided by the purchase price of the stock to get the holding period return.
Part a (ii))
The effective annual rate of return of the holding period return of the Microsoft stock
is provided in the figure below,

8STOCK ANALYSIS
Figure 9: Effective Annual Rate
Source: By the Author
The effective annual return for the Microsoft stock is at 92.17% and the excel formula
is shown in the figure above. The mathematical formula used to calculate the effective annual
rate is ((1+ holding period return/ time)^time)-1.
Part b)
The dividend yield of the Microsoft stock is calculated by dividing the dividend
received with the current stock price of the stock and the following results were calculated in
the figure below,
Figure 10: Dividend Yield
Source: By the Author

9STOCK ANALYSIS
The dividend yeild of the Microsoft stock is at 0.37% which is by dividing the
dividend received of $0.46 with the stock price of $126.02.
Part c)
The capital gains yeild of the Microsoft stock is the return which the stock provided
by holding the stock for a certain period of time. Thus the capital gain yeild is calculated by
dividing the result of sale price less the purchase price with the purchase price. The following
figure shows the calculation of capital gains yeild.
Figure 11: Capital Gains yield
Source: By the Author
The capital gain yeild of the Microsoft stock over the holding period is at 17.61%.
The sale price is $126.02 and the purchase price is $107.15.
Answer to Question 3:
Part a, b and c)
The arithmetic mean. Geometric mean and standard deviation of the three stocks is
calculated and highlighted in the table below,

10STOCK ANALYSIS
Figure 12: Results of V stock
Source: By the Author
Figure 13: Results of IBM stock
Source: By the Author
Figure 14: Results of Netflix stock
Source: By the Author
The arithmetic mean of the three stocks have been calculated in the table above and
are 1.22%, 0.06% and 2.7% for each of the stock respectively. The geometric mean of the
three stocks are 101.1%, 99.92% and 101.86% respectively. The standard deviation of the
three stocks are as follows 4.89%, 5.32% and 13.15% respectively. The excel formula for all
the calculation made are shown in the figure above.

11STOCK ANALYSIS
Part d)
The holding period return for each of the stock incorporating dividend and stock split
is highlighted in the table below,
Figure 15: Holding Period Return of Stock
Source: By the Author
The holding period return for the V stock over the three year horizon incorporating
dividend and stock split is at 40.08%. The holding period return for the IBM stock
incorporating dividend is at -5.16%. The holding period return for the Netflix stock is at
135.38% because there was no dividend payment by the stock but the stock split of the stock
has been incorporated in the calculation.
Part e)
The calculation of geometric mean and the holding period return of the three stock is
given in the figure below,