Hi, I need some help calculating covariance b/w 5 investments and market to obtain beta. Please see directions and attached spreadsheet.
COULD SOMEONE SHOW ME HOW TO CALCULATE THE COVARIANCE AND BETA FOR THE DATA ON THE ATTACHED SPREADSHEET. A CHECK POINT TO MAKE SURE WE ARE ON THE SAME PAGE IS THAT THE 10-YR BETA FOR THE MEGA MULTINATIONAL FUND SHOULD BE .8044, AND SOUNDSLEEP SHOULD BE 1.0. COULD YOU RANK THE 5 INVESTMENTS FROM BEST TO WORSE, ONE RANKING BASED ON BETA, AND THE OTHER ON STANDARD DEVIATION? ALSO, COULD SOMEONE GIVE ME DETAILS ON HOW TO PLOT A REGRESSION ANALYSIS GRAPH SO I CAN DO SO FOR THE DATA. I DO NOT HAVE THE REGRESSION KIT FOR EXCEL, BUT I DO HAVE REGULAR GRAPH FUNCTIONS SUCH AS REGRESSION, BUT NEED TO KNOW HOW THE DATA MUST BE SPELLED OUT FOR THE GRAPH TO COME OUT PROPERLY. IF YOU COULD GIVE ME AN EXAMPLE OF A GRAPH FOR ONE SET OF DATA THAT WOULD BE GREAT AND APPRECIATED. PLEASE LET ME KNOW IF THERE IS ANY INFO I LEFT OUT
Calculating the beta
1. Find the covariance between the market returns and the fund returns. Use the COVAR function of excel.
2. Find the variance of the market returns ( use VARP function of excel since the data used is for the entire population)
3. Divided covariance by variance to get the beta
Plotting the ...
The solution explains how to calculate the beta using covariance.