Finance Questions - Investment Return and risk

Instructions

Due date:		Sunday, June 23 by 11:59 pm EST
Problem Set:		List of questions is outlined in tab titled "Problem"
Submission guidelines:		Submissions through Dropbox on CourseLink only
		Submit single Excel file using this file as a template
		Student Answer sheet must be filled with your final answers and will be used for grading.
		However, your calculations and rough work must be shown in tab "Calculations" (no specific formatting guidelines are required)
		Failing to show your work in "Claculations" tab will result in zero marks even if the answer is correct under the "Student Answer sheet".
Notes:		Section H of Problem 1 will require MS Excel’s Solver. Completing this section will provide you with necessary tools required for PR2

Problem

Below is actual price and dividend data for three companies for each of seven months.
	Security A		Security B		Security C
Time	Price	Dividend	Price	Dividend	Price	Dividend
1	57 3/4		333		106 3/4
2	59 7/8		368		108 1/4
3	59 3/8	0.725	368 1/2	1.35	124	0.4
4	55 1/2		382 1/4		122 1/4
5	56 1/4		386		135 1/2
6	59	0.725	397 3/4	1.35	141 3/4	0.42
7	60 1/4		392		165 3/4
A dividend entry on the same line as a price indicates that the return between that time period and the previous period consisted of a capital gain (or loss) and the receipt of the dividend.
Securities A, B and C can be referred to Securities 1, 2 and 3 respectively.
	A.      (3pts) Compute the rate of return for each company for each month (use simple return formula).
	B.      (3pts) Compute the average rate of return for each company.
	C.      (6pts) Compute the standard deviation of the rate of return for each company (use population variance formula. i.e. divide by N, not (N-1) ).
	D.      (6pts) Compute the covariance and correlation coefficients between all possible pairs of securities.
	E.       (8pts) Compute the average return and standard deviation for the following portfolios:
	i.            ½ A + ½ B
	ii.            ½ A + ½ C
	iii.            ½ B + ½ C
	iv.            1/3 A + 1/3 B + 1/3 C
	F.       (15pts) Assuming short selling is not allowed:
	i.            (2) For securities 1 and 2 find the composition, standard deviation, and expected return of that portfolio that has minimum risk.
	ii.            (2) On the same graph plot the expected return and standard deviation for all possible combinations of securities 1 and 2
	iii.            (1) Assuming that investors prefer more to less and are risk avoiders, indicate those sections of the diagram in part ii) that are efficient.
	iv.            (10) Repeat steps i), ii) and iii) for all other possible pairwise combinations of securities in Problem 1.
	G.     (15pts) Assuming short selling is allowed:
	i.            (2) For securities 1 and 2 find the composition, standard deviation, and expected return of that portfolio that has minimum risk.
	ii.            (2) On the same graph plot the expected return and standard deviation for all possible combinations of securities 1 and 2
	iii.            (1) Assuming that investors prefer more to less and are risk avoiders, indicate those sections of the diagram in part ii) that are efficient.
	iv.            (10) Repeat steps i), ii) and iii) for all other possible pairwise combinations of securities in Problem 1.
	H.      (14pts) Assuming short selling is not allowed, for portfolio with ALL THREE securities:
	i.            (6) Find the composition, standard deviation, and expected return of the portfolio that has minimum risk.
	ii.            (7) Construct the minimum variance portfolio frontier.
	iii.            (1) Assuming that investors prefer more to less and are risk avoiders, indicate those sections of the diagram in part ii) that are efficient.

Student Answer sheet

Q#	Your fina answers must be provided in cells highlighted in blue and your graphical answers in the aproximate space provided in shaded green area
Marks	17	in total	In addition to your score on the left, -3.5 off for 1 day late
A.		Month
	Security	2	3	4	5	6	7
1	A	3.68%	0.38%	-6.53%	1.35%	6.18%	2.12%
1	B	10.51%	0.50%	3.73%	0.98%	3.39%	-1.45%
1	C	1.41%	14.55%	-1.41%	10.84%	4.61%	16.93%
B.	Security	Average monthly return
1	A	1.20%
1	B	2.95%
1	C	7.82%
C.	Security	Standard deviation
1	A	0.15%	you reporting variance not SD, need to take sqrt of this answer
1	B	0.13%
1	C	0.46%
D.	Covariance					Correlation
	Security	A	B	C		Security	A	B	C
1	A	0.0015	0.000210	0.000712		A	0.00154	0.00018	0.00061	the order of math operation matters, need to use () where apropriate
1	B	0.0002	0.0015	-0.001993		B	0.00018	0.00145	0.00000
1	C	0.0007	-0.0020	0.0046		C	0.00061	0.00000	0.00457
E.		Avg.Ret	SD
1	i.	0.0207	0.0009	you reporting variance not SD, need to take sqrt of this answer
1	ii.	0.0451	0.0019
1	iii.	0.0538	0.0005
1	iv.	0.0399	0.0006
F.	Security pair	Weights		SD	E[r]
1	i. A&B	0.45	0.55	0.0008	0.0356
0	i. A&C	0.4	0.6	0.0007	0.0468
0	i. B&C	0.35	0.65	0.0005	0.0523
	Security pair
0	ii. A&B	graph goes somewhere here
0	iii. A&B
0	ii. A&C	graph goes somewhere here
0	iii. A&C
0	ii. B&C	graph goes somewhere here
0	iii. B&C
G.	Security pair	Weights		SD	E[r]
0	i. A&B	0.8	0.2	0.19%	3.05%
0	i. A&C	0.7	0.3	0.32%	5.65%
0	i. B&C	0.6	0.4	0.46%	6.52%
	Security pair
0	ii. A&B	graph goes somewhere here
0	iii. A&B
0	ii. A&C	graph goes somewhere here
0	iii. A&C
0	ii. B&C	graph goes somewhere here
0	iii. B&C
H.	Portfolio	Weights			SD	E[r]
0	i. A&B&C	0.4	0.35	0.25	0.24%	4.56%
0	ii. A&B&C	graph goes somewhere here
0	iii. A&B&C

0.45 0.55000000000000004 8.0000000000000004E-4 3.56E-2 1

0.4 0.6 6.9999999999999999E-4 4.6800000000000001E-2

0.35 0.65 5.0000000000000001E-4 5.2299999999999999E-2

0.2 1.89E-3

0.7 0.3 3.2000000000000002E-3 5.6500000000000002E-2

0.6 0.4 4.5999999999999999E-3 6.5199999999999994E-2

0.4 0.35 0.25 2.3500000000000001E-3 4.5600000000000002E-2

Calculations

Calculations -
A	Simple return formula for each security =				change in price divided by price of previous period
	Simple return formula for
		Security A				Security B				Security C
	Time	Price	Dividend	Return	Rate of return	Price	Dividend	Return	Rate of return	Price	Dividend	Return	Rate of return
	1	57.75				333.0				106.75
	2	59.875		2.125	3.68%	368.0		35	10.51%	108.25		1.5	1.41%
	3	59.375	0.725	0.225	0.38%	368.5	1.35	1.9	0.50%	124	0.4	15.75	14.55%
	4	55.5		-3.875	-6.53%	382.3		13.75	3.73%	122.25		-1.75	-1.41%
	5	56.25		0.75	1.35%	386.0		3.75	0.98%	135.5		13.25	10.84%
	6	59	0.725	3.475	6.18%	397.8	1.35	13.1	3.39%	141.75	0.42	6.25	4.61%
	7	60.25		1.25	2.12%	392.0		-5.75	-1.45%	165.75		24	16.93%
B, C		Security A				Security B			Security C
	Rate of return(X)	Average Rate of Return(M)	X-M	(X-M)²	Rate of return(X)	Average Rate of Return(M)	X-M	(X-M)²	Rate of return(X)	Average Rate of Return(M)	X-M	(X-M)²
	3.68%	1.20%	2.48%	0.062%	10.51%	2.95%	7.56%	0.57%	1.41%	7.82%	-6.42%	0.41%
	0.38%	1.20%	-0.82%	0.007%	0.50%	2.95%	-2.44%	0.06%	14.55%	7.82%	6.73%	0.45%
	-6.53%	1.20%	-7.72%	0.596%	3.73%	2.95%	0.79%	0.01%	-1.41%	7.82%	-9.23%	0.85%
	1.35%	1.20%	0.16%	0.000%	0.98%	2.95%	-1.96%	-0.04%	10.84%	7.82%	3.02%	0.09%
	6.18%	1.20%	4.98%	0.248%	3.39%	2.95%	0.45%	0.00%	4.61%	7.82%	-3.21%	0.10%
	2.12%	1.20%	0.92%	0.008%	-1.45%	2.95%	-4.39%	0.19%	16.93%	7.82%	9.11%	0.83%
Sum of squared deviations				0.921%				0.79%				2.74%
Standard Deviation				0.153%				0.132%				0.457%
				3.918%	need to take a square root
D	Security A	Security B	Security C
	X-M (A)	X-M (B)	X-M ( C)		A*A	A*B	A*C	B*B	B*C	C*C
	2.48%	7.56%	-6.42%		0.062%	0.19%	-0.16%	0.57%	-0.49%	0.41%
	-0.82%	-2.44%	6.73%		0.007%	0.02%	-0.06%	0.06%	-0.16%	0.45%
	-7.72%	0.79%	-9.23%		0.596%	-0.06%	0.71%	0.01%	-0.07%	0.85%
	0.16%	-1.96%	3.02%		0.000%	-0.00%	0.00%	0.04%	-0.06%	0.09%
	4.98%	0.45%	-3.21%		0.248%	0.02%	-0.16%	0.00%	-0.01%	0.10%
	0.92%	-4.39%	9.11%		0.009%	-0.04%	0.08%	0.19%	-0.40%	0.83%
	SUM				0.922%	0.13%	0.43%	0.87%	-1.20%	2.74%
	Sample size (n)		n-1		6	6	6	6	6	6
	Covariance				0.002	0.0002100	0.001	0.001	-0.002	0.005
	Correlation (x,y) =		Cov x,y/ Sd x*Sd y		0.002	0.000	0.001	0.001	0.000	0.005
					1	0.141	0.269	1
E	Security A	Security B	Security C		Portfolio I	Portfolio II	Portfolio III	Portfolio IV
	Rate of Return	Rate of Return	Rate of Return		1/2 A + 1/2 B (X 1)	1/2 A + 1/2 C (X 2)	1/2 B + 1/2 C (X 3)	1/3 A + 1/3 B + 1/3 C (X 4)
	3.68%	10.51%	1.41%		0.071	0.025	0.060	0.052
	0.38%	0.50%	14.55%		0.004	0.075	0.075	0.051
	-6.53%	3.73%	-1.41%		-0.014	-0.040	0.012	-0.014
	1.35%	0.98%	10.84%		0.012	0.061	0.059	0.044
	6.18%	3.39%	4.61%		0.048	0.054	0.040	0.047
	2.12%	-1.45%	16.93%		0.003	0.095	0.077	0.059
	AVERAGE RETURN (M)				0.021	0.045	0.054	0.040
					Portfolio I	Portfolio II	Portfolio III	Portfolio IV
					(X-M)²	(X-M)²	(X-M)²	(X-M)²
					0.0025	0.0004	0.0000	0.0001
					0.0003	0.0009	0.0005	0.0001
					0.0012	0.0072	0.0018	0.0029
					0.0001	0.0003	0.0000	0.0000
					0.0007	0.0001	0.0002	0.0001
					0.0003	0.0025	0.0006	0.0004
	Sum of squared deviations				0.0051	0.0113	0.0031	0.0036
	Standard Deviation				0.0009	0.0019	0.0005	0.0006
F	Security	Mean Return	S.D.	Cov(1,2)	Cov(1,3)	Cov(2,3)
	A(1)	1.20%	0.153%	0.0002	0.0007	-0.0020
	B(2)	2.95%	0.132%
	C(3)	7.82%	0.457%
	ER(P) = X1*ER1 + X2*ER2
i	A&B	=	X1*0.012 + X2*0.0295
ii	A&C	=	X1*0.012 + X3*0.0782
iii	B&C	=	X2*0.0295 + X3*0.0782
	Var(P) = X1² + Var(1) + X2² + Var(2) + 2X1*X2*Cov(1,2)
i	A&B	=	X1² + 0.00153² + X2² + 0.0013² + 2X1*X2*0.0003
ii	A&C	=	X1² + 0.00153² + X2² + 0.00457² + 2X1*X2*0.0009
iii	B&C	=	X1² + 0.0013² + X2² + 0.00457² + 2X1*X2*-0.0024
	If short-selling in not allowed, weights shall be assumed to be non-negative
G	If short-selling is allowed, the weights shall be assumed to be negative as well.
	In that case, the formula for portfolio variance becomes -
	Var(P) = X1² + Var(1) + X2² + Var(2) + 2X1*X2*-1*Cov(1,2)
i	A&B	=	X1² + 0.00153² + X2² + 0.0013² + 2X1*X2*-1*0.0003
ii	A&C	=	X1² + 0.00153² + X2² + 0.00457² + 2X1*X2*-1*0.0009
iii	B&C	=	X1² + 0.0013² + X2² + 0.00457² + 2X1*X2*-1*-0.0024
H	Portfolio with three securities -
	Variance can be calculated as follows:
	Var(P) = X1² Var(1) + X2² Var(2) + X3²Var(3)+ 2X1*X2*Cov(1,2) + 2X1*X3*Cov(1,3) + 2X2*X3*Cov(2,3)
	Standard Deviation =		√ Var(P)
	We can modify the Markowitz problem to find the minimum-variance portfolio as follows:
	If we leave out our requirement that the expected rate of return be equal to a given level r,
	then the Markowitz problem becomes
	minimize
	1/2 * sum of X1*X2*S.D.12
	wher, X= 1
	and its solution yields the minimum-variance portfolio for n risky assets.