Question

You are constructing a portfolio of two assets, Asset A and Asset B. The expected returns of the assets are 12 percent and 28 percent, respectively. The standard deviations of the assets are 12 percent and 33 percent, respectively. The correlation between the two assets is 0.06 and the risk-free rate is 5 percent. What is the optimal Sharpe ratio in a portfolio of the two assets? Can you show this in excel?

Answer 1

Formula sheet

A	B	C	D	E	F	G	H
2
3		Optimal sharpe ratio will be the maximum sharpe ratio that can be obtained using the given assets.
4
5		Risk free rate	0.05
6
7		Fund	Return	Standard Deviation
8		A	0.12	0.12
9		B	0.28	0.33
10		Correlation(A,B)	0.06
11		Cov(A,B)	=D10*E8*E9	=D10*E8*E9
12
13		Sharpe ratio divides the average portfolio's excess return by the standard deviation of return over the period.
14		Sharpe Ratio	=(rp-rf)/?p
15		Where, rp is average portfolio return, rf is risk free rate and ?p is standard deviation of return.
16
17		Finding the portfolio with higher sharpe ratio:
18
19			A	B
20		Expected Return	=D8	=D9
21		St. Deviation	=E8	=E9
22		Cov(A,B)	=D11
23		Risk Free Rate	=D5
24		Sharpe ratio table can be prepared as follow:
25		Weight of A	Weight of B	Expected Return	St. Deviation	Sharpe Ratio
26		0	=1-C26	=SUMPRODUCT(C26:D26,$D$20:$E$20)	=SQRT((C26*$D$21)^2+(D26*$E$21)^2+2*C26*D26*$D$22)	=(E26-$D$23)/F26
27		=C26+0.1	=1-C27	=SUMPRODUCT(C27:D27,$D$20:$E$20)	=SQRT((C27*$D$21)^2+(D27*$E$21)^2+2*C27*D27*$D$22)	=(E27-$D$23)/F27
28		=C27+0.1	=1-C28	=SUMPRODUCT(C28:D28,$D$20:$E$20)	=SQRT((C28*$D$21)^2+(D28*$E$21)^2+2*C28*D28*$D$22)	=(E28-$D$23)/F28
29		=C28+0.1	=1-C29	=SUMPRODUCT(C29:D29,$D$20:$E$20)	=SQRT((C29*$D$21)^2+(D29*$E$21)^2+2*C29*D29*$D$22)	=(E29-$D$23)/F29
30		=C29+0.1	=1-C30	=SUMPRODUCT(C30:D30,$D$20:$E$20)	=SQRT((C30*$D$21)^2+(D30*$E$21)^2+2*C30*D30*$D$22)	=(E30-$D$23)/F30
31		=C30+0.1	=1-C31	=SUMPRODUCT(C31:D31,$D$20:$E$20)	=SQRT((C31*$D$21)^2+(D31*$E$21)^2+2*C31*D31*$D$22)	=(E31-$D$23)/F31
32		=C31+0.1	=1-C32	=SUMPRODUCT(C32:D32,$D$20:$E$20)	=SQRT((C32*$D$21)^2+(D32*$E$21)^2+2*C32*D32*$D$22)	=(E32-$D$23)/F32
33		=C32+0.1	=1-C33	=SUMPRODUCT(C33:D33,$D$20:$E$20)	=SQRT((C33*$D$21)^2+(D33*$E$21)^2+2*C33*D33*$D$22)	=(E33-$D$23)/F33
34		=C33+0.1	=1-C34	=SUMPRODUCT(C34:D34,$D$20:$E$20)	=SQRT((C34*$D$21)^2+(D34*$E$21)^2+2*C34*D34*$D$22)	=(E34-$D$23)/F34
35		=C34+0.1	=1-C35	=SUMPRODUCT(C35:D35,$D$20:$E$20)	=SQRT((C35*$D$21)^2+(D35*$E$21)^2+2*C35*D35*$D$22)	=(E35-$D$23)/F35
36		=C35+0.1	=1-C36	=SUMPRODUCT(C36:D36,$D$20:$E$20)	=SQRT((C36*$D$21)^2+(D36*$E$21)^2+2*C36*D36*$D$22)	=(E36-$D$23)/F36
37
38		Hence optimal sharpe ratio is	=MAX(G26:G36)
39		Thus the corresponding portfolio should be chosen i.e.
40		Weight of A in the portfolio	=C33
41		Weight of B in the portfolio	=D33
42