Finance project

Calculating beta in excel

Step 1. Download the data for SP 500 and calculate rolling returns. Make sure that you use the

same data range that you used for other stocks. For example, if the data that you downloaded for

your stocks select your range and remember then the data for SP 500 should have the same range

as your stocks.

Step 2. Use the =slope function in excel and use stock return as the known Y's and SP 500

return as known Xs. So if you have WMT and FB in your portfolio you would calculate the

betas in the following manner: =slope(WMTreturncolumn,SPreturncolumn)

and slope(FBreturncolumn,SPreturncolumn) and you would do it for each stock in your

portfolio.

Step 3. Remember that

– A beta of 1 implies the asset has the same systematic risk as the overall

market

– A beta < 1 implies the asset has less systematic risk than the overall market

– A beta > 1 implies the asset has more systematic risk than the overall market

Step 4. Portfolio beta is simply

nnp WWW   .....2211

you just take the individual betas and do a weighted average.