Leave all answers to 2-decimal places.
						Show work: you answer cells should have the embedded algebra or Excel function.
The following data applies to all questions in this assignment:
		Rate of Return
	Year	Asset A	Asset B	Market
	1	20.00%	19.00%	10.00%
	2	-10.00%	15.00%	12.00%
	3	10.00%	-5.00%	8.00%
	4	-8.00%	-13.00%	-5.00%
	5	15.00%	25.00%	10.00%
1)	Calculate the expected returns for Asset A, Asset B and the Market (use AVERAGE function in Excel).
		Asset A =
		Asset B =
		Market =
2)	Calculate the standard deviation of returns, ?, for each asset (use STDEVP function).
		Asset A =
		Asset B =
		Market =
3)	Calculate the coefficient of correlation, ?, between Asset A and Asset B (use CORREL function).
		?A,B =
4)	Using the following asset weight combinations between Asset A and B [0%-100%, 25%-75%, etc.] calculate the expected returns and their corresponding portfolio standard deviation of the portfolio with these weight combos.
	Use the following formula to calculate the portfolio standard deviation:
	=	[(wA*?A)^2 + (wB*?B)^2 + (2 *wA * wB *?A * ?B *?(A,B))] ^0.5
	where	wA and wB	are the % of assets in Asset A and B respectively [i.e. A-B weight combo],
		?A and ?B	are the respective standard deviations of return [calculated from (2)] and
		?(A,B)	is the [coefficient of] correlation of returns between asset A and B
									[calculated from (3)].
				Portfolio	Portfolio
		%Asset A	%Asset B	Expected Return	Standard Deviation
		0%	100%
		25%	75%
		50%	50%
		75%	25%
		100%	0%
5)	Using Portfolio Expected Returns on the Y axis and Portfolio Standard Deviation in the X axis, draw the efficient frontier for possible portfolio combinations of Asset A and B [from previous question]. (include 100% A and 100% B as two possibilities).
	Hint: Use the Excel Chart Wizard and select the XY (scatter) plot option.
	Yes, I am helping you auto-plot the Efficient Frontier below once you calculate the Return-Stdev in Question4.
	When you calculate and fill in the portfolio's Expected Returns and Standard Deviations in (4), it will automatically plot below. It's probably a good idea to learn how to do it yourself, but for now it will save you some time ?
6)	Calculate Beta for Asset A (relative to the Market) and Asset B (relative to the Market). (use the SLOPE function).
	[PS: this function is also available on your TI BA II Plus calculator, and Excel facility may or may not be available (it is semester- and instructor-specific) during the Exam].
			Beta Asset A =
			Beta Asset B =
7)	Assume that for next year the Risk Free Rate is expected to be 1% and that the overall Market will realize a return of 6%. Using the CAPM / SML methodology, calculate the required returns for Asset A and Asset B.
			CAPM: ki = kRF + ?i (kM - kRF)
			kRF =
			kM =
			Required Return for Asset A =
			Required Return for Asset B =

Name: Group: Leave all answers to 2-decimal places. Show work: you answer cells should have the embedded algebra or Excel function. The following data applies to all questions in this assignment: Rate of Return Year Asset A Asset B Market 1 20.00% 19.00% 10.00% 2 -10.00% 15.00% 12.00% 3 10.00% -5.00% 8.00% 4 -8.00% -13.00% -5.00% 5 15.00% 25.00% 10.00% 2015 Spring2 1) Calculate the expected returns for Asset A, Asset B and the Market (use AVERAGE function in Excel). Asset A = Asset B = Market = 2) Calculate the standard deviation of returns, , for each asset (use STDEVP function). Asset A = Asset B = Market = 3) Calculate the coefficient of correlation, , between Asset A and Asset B (use CORREL function). A,B = 4) Using the following asset weight combinations between Asset A and B [0%-100%, 25%-75%, etc.] calculate the expected returns and their corresponding portfolio standard deviation of the portfolio with these weight combos. Use the following formula to calculate the portfolio standard deviation: P ( wA A ) 2 ( wB B ) 2 2 wA wB A B A,B = [(wA*A)^2 + (wB*B)^2 + (2 *wA * wB *A * B *(A,B))] ^0.5 where wA and wB are the % of assets in Asset A and B respectively [i.e. A-B weight combo], A and B are the respective standard deviations of return [calculated from (2)] and (A,B) is the [coefficient of] correlation of returns between asset A and B [calculated from (3)]. Portfolio Portfolio %Asset A %Asset B Expected Return Standard Deviation 0% 100% 25% 75% 50% 50% 75% 25% 100% 0% 5) Using Portfolio Expected Returns on the Y axis and Portfolio Standard Deviation in the X axis, draw the efficient frontier for possible portfolio combinations of Asset A and B [from previous question]. (include 100% A and 100% B as two possibilities). Hint: Use the Excel Chart Wizard and select the XY (scatter) plot option. Yes, I am helping you auto-plot the Efficient Frontier below once you calculate the Return-Stdev in Question4. When you calculate and fill in the portfolio's Expected Returns and Standard Deviations in (4), it will automatically plot below. It's probably a good idea to learn how to do it yourself, but for now it will save you some time ... 0.09 0.08 Port folio Expected Return 0.07 0.06 0.05 0.1 0.11 0.12 0.13 0.14 0.15 Portfolio Standard Deviation 6) Calculate Beta for Asset A (relative to the Market) and Asset B (relative to the Market). (use the SLOPE function). [PS: this function is also available on your TI BA II Plus calculator, and Excel facility may or may not be available (it is semester- and instructor-specific) during the Exam]. Beta Asset A = Beta Asset B = 7) Assume that for next year the Risk Free Rate is expected to be 1% and that the overall Market will realize a return of 6%. Using the CAPM / SML methodology, calculate the required returns for Asset A and Asset B. CAPM: ki = kRF + i (kM - kRF) kRF = kM = Required Return for Asset A = Required Return for Asset B =