- You have been provided with monthly share price data for ANZ and BHP from June 2017
to June 2019. This Excel file is available on Moodle under the ‘Individual Assignment’
folder. For each stock, calculate the following:
a) Monthly stock return (holding period yield);
b) Arithmetic and geometric averages of monthly return;
c) Standard deviation of monthly return; and
d) Coefficient of variation. - Based on the calculated monthly returns, calculate the following:
a) Covariance between the two stocks
b) Correlation coefficient between the two stocks.
Note: Do not use Excel built-in functions for the above calculations. The only function
allowed is the ‘average’. - Create a series of portfolios (at least 20) with different weights assigned to ANZ and BHP
(e.g. from 0% to 100%), and calculate the expected return and standard deviation of those
portfolios. Plot the risk and return of these portfolios using the XY scatter graph with risk
on X axis and return on Y axis. - Use Excel form ‘control’ to so that the plot visibly responds to changes in the value of the
correlation coefficient.
Sample Solution
