Question

Imagine, finally, that you are applying for a position as an analyst at Blackstone Asset Management. You are attending a testing session today. Some basic portfolio analysis skills will be tested. You have been given two series of prices (below) and you guess that you will have to compute the expected return and standard deviation (risk) for the two assets and portfolios containing different combinations of them. The company’s recruiters have made it clear that you can bring your spreadsheet files with you to the test and that you should prepare a basic framework with as much work pre-done as possible.

	Breville			Monadel
Date	Price	Dividend		Price	Dividend
1/12/2018	10.66			13.77
1/01/2019	10.98			14.77
1/02/2019	15.75	0.18		17.81
1/03/2019	16.28			17.35	0.25
1/04/2019	19.24			18.95
1/05/2019	16.88			19.07
1/06/2019	16.36			18.81
1/07/2019	19.23			18.93
1/08/2019	16.26			15.91
1/09/2019	16.1	0.18		15.75	0.23
1/10/2019	15.34			15.34
1/11/2019	16.98			16.02
1/12/2019	17.36			16.5
Beta	1.15			Beta	1.40

Your Mentor’s Advice: This should be relatively easy. BUT… Don’t forget that the prices must be converted to returns before you can start computing averages, covariance or standard deviation. Once you have two returns series, you can compute all these things very easily using Excel. You will have to be prepared to work out the portfolio expected return for different weights, so it might be best to set up a spreadsheet based on a calculation for the equally weighted portfolio and then change the weights once you know what they are. Given that the data set contains information about the betas, you might also expect questions based on the Capital Asset Pricing Model (CAPM).

QUESTION 1

What is the covariance (computed using the COVAR function in Excel) between the returns series for Breville and Monadel?

Select one:

a. 0.002341

b. 0.0089

c. 0.01082

d. 0.02192

e. 0.0003

QUESTION 2

What is the standard deviation and expected (average) return for an equally weighted portfolio of Breville and Monadel shares?

Select one:

a. 1.34% expected return and 8.74% standard deviation

b. 3.74% expected return and 11.74% standard deviation

c. -4.35% expected return and 20.91% standard deviation

d. 7.11% expected return and 9.04% standard deviation

e. 1.03% expected return and 3.05% standard deviation

Answer 1

A) Covariance measures the extent to which two variables move together over time. A positive covariance means that variables tends to move together. A negative covariance means that variables tends to move in opposite directions. A covariance of zero means that there is no linear relationship. (Like in our example we have to calculate covariance between the returns series for Breville and Monadel).

Breville	Monadel
Dates	Stock Price	Returns	Stock Price	Returns
01-12-2018	10.66		13.77
01-01-2019	10.98	3.0%	14.77	7.3%
01-02-2019	15.93	45.1%	17.81	20.6%
01-03-2019	16.28	2.2%	17.6	-1.2%
01-04-2019	19.24	18.2%	18.95	7.7%
01-05-2019	16.88	-12.3%	19.07	0.6%
01-06-2019	16.36	-3.1%	18.81	-1.4%
01-07-2019	19.23	17.5%	18.93	0.6%
01-08-2019	16.26	-15.4%	15.91	-16.0%
01-09-2019	16.28	0.1%	15.98	0.4%
01-10-2019	15.34	-5.8%	15.34	-4.0%
01-11-2019	16.98	10.7%	16.02	4.4%
01-12-2019	17.36	2.2%	16.5	3.0%

  Covariance formula in excel

=Covariance.P(array1,array2)

The COVARIANCE.P function uses the following arguments:

Array1 (required argument) – This is a range or array of integer values.

Array2 (required argument) – This is a second range or array of integer values.

Based on the above table we can calculate covariance in excel

For array 1 we have to take returns of Breville for array 2 we have to take returns of Monadel

Based on data covariance will be 0.010854

Based on options our nearest option is 0.01082.

B)

Breville	Monadel
Dates	Stock Price	Returns	Stock Price	Returns
01-12-2018	10.66		13.77
01-01-2019	10.98	3.0%	14.77	7.3%
01-02-2019	15.93	45.1%	17.81	20.6%
01-03-2019	16.28	2.2%	17.6	-1.2%
01-04-2019	19.24	18.2%	18.95	7.7%
01-05-2019	16.88	-12.3%	19.07	0.6%
01-06-2019	16.36	-3.1%	18.81	-1.4%
01-07-2019	19.23	17.5%	18.93	0.6%
01-08-2019	16.26	-15.4%	15.91	-16.0%
01-09-2019	16.28	0.1%	15.98	0.4%
01-10-2019	15.34	-5.8%	15.34	-4.0%
01-11-2019	16.98	10.7%	16.02	4.4%
01-12-2019	17.36	2.2%	16.5	3.0%

Based on the above table average return for Breville is 5.2% and average return for Monadel is 1.8%

So equally weighted portfolio of Breville and Monadel shares is

5.2% * 50% + 1.8% * 50%

=3.5%

The standard deviation is a measure that indicates how much the values of the set of data deviate (spread out) from the mean.

Formula for standard deviation in excel

=STDEV(number1,[number2],…)

The STDEV function uses the following arguments:

Number1 (required argument) – This is the first number argument that corresponds to a sample of a population.

Number2 (optional argument) – This is a number argument that corresponds to a sample of a population.

Based on the above table standard deviation for Breville is 16.3% and standard deviation for Monadel is 8.5%

So equally weighted portfolio of Breville and Monadel shares is

16.3% * 50% + 8.5% * 50%

=12.4%

Based on the options we can select closest answer i.e 3.74% expected return and 11.74% standard deviation for an equally weighted portfolio of Breville and Monadel shares