Calculating Beta in Excel - VAR or VARP?

pinstrypes · Post by **pinstrypes** » Sat Jun 12, 2010 7:14 pm

I am calculating the beta for some ETF's and mutual funds, with the benchmark being SPY and the time frame being six months of daily returns.

The formula I am using is Variance/Covariance= Beta

In excel, one can choose from the functions VAR, VARA, VARP, and VARPA

VARP and VARPA give a slightly lower result than VAR.

I am using this as part of my risk management and hedging.

So which is a better function to use...VAR or VARP? I understand the difference between population and sample, but is there a "correct" application?

Thanks

E

Trev · Post by **Trev** » Sat Jun 12, 2010 9:09 pm

Why not just use the SLOPE() function as it is the Beta in a univariate framework? And it should be VAR() as it accounts for the loss of one degree of freedom.

pinstrypes · Post by **pinstrypes** » Wed Jun 16, 2010 5:19 am

Found a decent article on Investopedia, of all places. Looks like my formula needed re-arranged.

http://www.investopedia.com/articles/financial-theory/09/calculating-beta.asp

aaron · Post by **aaron** » Sat Jun 19, 2010 1:28 am

You should use neither. The best general purpose formula is:

SumProduct(A1:A260,B1:B260) / SumSq(B1:B260)

Assuming you have your security returns in column A and your index returns in column B. This assumes the mean is zero, which is more accurate than estimating a mean. Because you specify the mean, you divide by N rather than N - 1.

Unless your prices are quoted in perfect synch, it's better to use three-day overlapping returns than daily returns. If you want to test this, see if you get significant Beta at lags of +1 or -1.

simcon · Post by **simcon** » Tue Apr 24, 2012 2:52 am

This page offers a couple of ways of calculating beta in Excel:

http://investexcel.net/367/calculate-stock-beta-with-excel/

The first method uses slope() and the second method uses VAR.P(). Both give the same result.

Sim

cpptrader · Post by **cpptrader** » Wed May 16, 2012 6:37 pm

"This assumes the mean is zero, which is more accurate than estimating a mean."

Beginner question here, but is that because if we assume no drift we are better off assuming zero (random walk with zero mean) since any mean we estimate from a sample is limited to that sample and not the population? I'm not sure if I have that correct, but the challenge that comes to mind is that some would argue equities are not a zero mean.

edit: f*. didn't realize this thread was 2 years old. although I'm still interested in the answer if anyone is willing to comment.

MoreLiver · Post by **MoreLiver** » Thu May 17, 2012 1:18 am

means are less stable than covariances. It is too easy to have sampled means that do not hold in the future, especially for single stocks. Just google it, there are plenty of examples and ideas on this.