Date	CAT MOT
01-Dec-05	52.09	21.51
01-Nov-05	52.1	22.9
03-Oct-05	47.42	21.06
01-Sep-05	52.74	20.94
01-Aug-05	49.81	20.76
01-Jul-05	48.39	20.1
01-Jun-05	42.58	17.33
02-May-05	42.04	16.45
01-Apr-05	39.33	14.52
01-Mar-05	40.66	14.17
01-Feb-05	42.26	14.79
03-Jan-05	39.62	14.86
01-Dec-04	43.17	16.24

Above are the closing prices of two stocks (CAT and MOT)

 

Use Excel to calculate the following and show the formula as well.  

a) The monthly return of each stock

b) the average monthly return and volatility for each stock

c) the covariance between the stocks

d) the correlation between these two stocks.

e) calculate the volatility of a portfolio that is 60^ invested in CAT and the remaining in MOT

Answer rating: 100% (QA)

To calculate the requested values in Excel follow these steps a Monthly return of each stock In Exce... View the full answer