Really Dumb Beta Question

Thanks for the reply.

To compound my stupidity, my follow up questions:

I actually tried the slope formula first, but it gave me a number that was 1.00xxxx. Then I tried the covariance way and I got like .98xxx. So they aren't even getting the same answer. I used the R^2 formula to, I only got 57%, so does that mean my data is wrong then?

By compounded yearly return, do you mean anything different than (end price-beg price)/beg year price? Because that's what I'm referring to when I say the ETF return -25%, but the S&P returned 2%.

So you're saying that despite having a Beta of 1, the ETF can so negatively out perform the S&P?

I'm pretty sure the discrepancy between the SLOPE function and the VAR/COVAR function is due to the fact that you use Population Variance/Covariance instead of Sample Var/Covar.

Try to play around using Var.S and Covariance.S instead of Var.P and Covariance.P. Try the other way around too, but don't mix P and S together.

No, there is no way your data is wrong lol. The Slope and the usual Variance/Covariance formulas are basically the same. It's how Excel calculate the Slope too.

Yes, I meant that. I said compounded because the end of year return is equal to the compounded daily returns throughout the year.

if you follow closely what I said in the previous post, you should get what I meant. I don't think my wording was that bad for that sentence. Basically, the idea is that the ETF could have had several bad/losing days, while for the rest of the year, it behaved almost like the S&P 500 (when S&P500 moved up 1%, it did too, etc).

Hope that helps.

Thanks for you help, I really appreciate it.

I guess I just always thought that a beta of 1, it meant the returns would be almost the same

What do you mean by population var vs sample var? What do you mean by "Try to play around using Var.S and Covariance.S instead of Var.P and Covariance.P. Try the other way around too, but don't mix P and S together."?

Thanks man!

you can also run a regression on the two different returns (just did this today in adv excel class actually). the covar method and slope method gave us different betas than the one listed on yahoo, but regression gave me almost the exact same (it was LMT with a beta of .71, we indexed it to VFINX). GOod Luck!

Your first formula =(covar(B2:B55,D2:55))/(var(D2:D55) is missing a D before the 55 on the second input under the covar function. I don't think excel would accept that error, but I think you should check if you made an error there.

Did you try running a regression on the returns using the data analysis tool pack? see what you get.

By the way, did you choose a risk free rate and subtract it from both set of returns?

Isn't the covar (a,b)/ var actually the sharpe ratio?

Would you put the ETF returns in the Y-value and S&P in the X-value? I can't remember what I have to do to run a regression in excel.

You have to use COVAR.S and VAR.S (basically these are the sample covariance and variance).

Remember degrees of freedom?

and NO, you don't have to substract the Rf because it doesn't change the Beta. Remember, Beta captures the MOVEMENT/SENSITIVITY. Substracting the return with a constant doesn't change the movement

Sort of.. I haven't taken a class in portfolio analytics in over a year. Sorry haha, but I don't how to use a COVAR.S and VAR.S. Could you explain please?

Those are functions on excel, use it like you would for any other functions.

For instance, previously you did =COVAR(D2:D55, B2:B55), now you use =Covar.S(D2:D55, B2:B55)

Y axis is the S&P and X is your ETF.

shykid - subtractingthe Rf from both sides will not change the beta, but since this is for academics I think this is how most professors like to see it done. So useless in appliction, but valuable in academia....Have you had a different experience in your studies?

Wait, should I be putting the S&P first, then the ETF when doing the COVAR? (You wrote D2:D55,B2:B55), I did (B2:B55,D2:D55) B=ETF, D=S&P.

Bubba is correct, Y axis = S&P

BubbaBaby - Hmm, I'm majoring in a quantitative field, so I skipped my finance classes when they were about the math... I think it's important for him to understand why substracting Rf doesn't change the Beta too :)

I think your better off watching how to calculate Beta on excel:

DISCLAIMER: haven't watched it.

Risk free rate isn't a constant, and the correct way is to subject the risk free rate. But the impact isn't huge if you're just calculating beta off of 1 year of returns, so probably not worth the pain in your ass...