Forum Discussion
Covariance and variance
Hello,
When using the Covariance and Variance formulas, they give me the completely wrong numbers when I plot my data into the functions.
3 Replies
Could you please give bit more details what exactly do you do, desirably with sample file.
Basically, I need to calculate the Beta for Facebook stocks and Shell stocks. In order to do this, I need to calculate the variance using the Facebook stock returns and the covariance using the Facebook stock returns and the S&P 500. Then I can calculate the beta by using covariance/ variance. However, the answer that I get for the beta is 0.51 whereas the correct number is 0.81 for the Facebook stock. For the shell stock, excel gives me the answer of 0.43 but it's supposed to be 1.6. I checked where the mistakes were and saw that the values given through the covariance and variance formula are completely wrong.
That's easy to check with direct calculations:
COVARIANCE.S which you use returns correct result. Didn't check VAR.S and VAR.P but I believe they shall work, these functions were tested for years by millions of users.
