Solving problems using excel

Module 05

Problem 5-1
Rate of return and standard deviation
Porter, Inc. is evaluating a security. Calculate the investment’s expected return and its standard deviation
DATA
Probability	Return
0.10	-10.0%
0.20	5.0%
0.30	10.0%
0.40	25.0%
Expected return
Variance
Standard deviation
Problem 5-2	Holding Period Returns
From the price data tha follow, compute the holding period returns for Waters and Panner for periods 2 through 4.
DATA
	Waters
Period	Price
1	8.00
2	10.00
3	12.00
4	14.00
	Panner
Period	Price
1	26.00
2	29.00
3	30.00
4	28.00
Problem 5-3: Holding-Period Gain SAR and Return
Suppose you purchased 20 shares of Apple stock for 25.35 SAR per share on April, 1 2021 and sold 12 shares for 28.35 SAR on August 1, 2022.
Calculate the holding-period SAR gain for the shares you sold, assuming no dividends was distributed , and the holding-period rate of return.
Solution:
DATA
HOLDING PERIOD SARS GAIN AND RETURN
DATA
Purchase price	25.35
Selling price	28.32
Shares sold	12
Holding-period gain
Holding-period return
Problem 5-4
CAPITAL ASSET PRICING MODEL
Using the Capital Asset Pricing Model, estimate the appropriate required rate of return for the three stocks listed below, given that the risk-free rate is 5 percent and the expected return for the makret is 12 percent.
DATA
Stock	Beta
A	0.85
B	0.95
C	1.65
Problem 5-5: Portfolio Beta and Security Market Line
Based on the data below:
a. Calculate the expected return on your portfolio. (The expected return of a portfolio equals the weighted average of the indivdiual stocks' expected returns, where the weights are the percentage invested in each stock).
b. Calculate the portfolio beta.
DATA
	Percentage		Expected
Stock	of Portfolio	Beta	Return
1	10.0%	0.95	11.0%
2	22.0%	1.25	7.0%
3	23.0%	0.85	12.0%
4	25.0%	0.60	5.0%
5	20.0%	1.60	1.0%
Problem 5-6: Required Rate of Return CAPM
Compute an appropriate rate of return for ABC Corporation common stock, which has a beta of 1.25.The risk -free rate is 2 percent, and the market portfolio has an expected return of 13 percent.
Solution:
DATA
Risk-free rate =		2.0%
Market expected return =		13.0%
Beta =		1.25
Rate of return =
Problem 5-7: Expected Return, Standard Deviation
Below are the prices for ABC Corporation and the S&P 500 Index.
a. Calculate the monthyly holding-eriod returns for ABC and the S&P 500 Index.
b. What are the average monthly returns and stardard deviations for each?
DATA
a.
Month	ABC Corporation		S&P 500 Index
	Prices	Returns	Prices
May-21	48.55		2,025
Jun-21	48.11	-0.91%	2,024
Jul-21	48.95	1.75%	2,026
Aug-21	50.55	3.27%	2,013
Sep-21	50.82	0.53%	2,014
Oct-21	52.55	3.40%	2,018
Nov-21	53.75	2.28%	2,023
Dec-21	54.05	0.56%	2,025
Jan-22	47.95	-11.29%	1,995
Feb-22	51.33	7.05%	2,095
Mar-22	51.58	0.49%	2,096
Apr-22	52.42	1.63%	2,088
May-22	53.77	2.58%	2,105