Beta Calculation on Excel

[freedx10]

youve explained it really well, thanks

[RaChoo19]

This is really helpful. Thank you so much.

[rlacjfdn]

U R GOD 

[Dfrogman]

I LOVE YOU <3

[MrDevilzpride]

Thanx Thanx Thanx Dude :)



[MrDevilzpride]

Thanx Thanx Thanx Dude

[arsalanoneseven]

Thumbs up if Nauman Amin sent you here :P

[pdaves]

To answer a couple of questions. If you use the formulas Covar/Var you'll get a slightly different answer because the var and covar functions use "n-1" weighting. That is, they are calculating the "unbiased estimate of the variance of the sample" rather than the sample variance. The regression coefficient is calculated using the sample variance. If the sample is very large you'll get almost exactly the same values.

[smonkey555059]

This was more helpful than my actual professor. Thank you so much for the upload

[pinglet]

Hi, thanks for the upload :) but for some reason when I use the two methods for caluculating the beata using the same data sets they end up being slightly different values. Is there any reason that you know of why this could be happening to me?

[ravs77gmail]

Awesome.... best explaination of Beta I have ever seen. It really helped me with my MBA project. One small suggestion...if we can also include what does Beta indicates and how to describe the value which we derive,,

[chrissizkool]

Hey, very good video. Help me out a lot. I am just wondering, how would you put it in the CAPM model? I tried putting it into the CAPM model and get a expected return very low, like 1-3 percent. Is this right?

[ruma066]

thank you, this is really helpful. Btw, by any chance that if you know the reason why when using "slope" function I have 1 result, then I've tried to use the formula " Covar(x,y)/var(x)" I've got another different result for beta? Thank you very much.

[mkr00a]

@ruma066

I found this on a forum over at mackb.com

XL calculates COVAR as the covariance of of a population, just as it does STDEVP and VARP. As far as a workaround, just multiply the population Covariance by n / (n-1). Depending on your circumstances, then, the XL formula for sample covariance might be something like: COVAR(arr1, arr2) * (COUNT(arr1)/COUNT(arr1)-1)) / VAR(arr1)

[plscreateaccount]

I LOVE YOU!!!! THANK YOU SO MUCH! IT REALLY HELPS!!

[toseeornot2see]

Thank you so much. This was great help. I have an assignment due tomorrow and this saved me a lot of time!

[josepv2005]

thanks!

[josepv2005]

thanks!



[goochi555]

SO helpful

[Mendojua]

Thank you so so much

[pdaves]

@YourWorstNightmareDK

You can just leave the observations blank, with no value. Excel deletes from its regression observations in which any of the right or left hand variables are missing.

[MrKaryerist]

Thank you! It had helped me a lot!

[Dietrich123]

Thank you.

[jeevs182]

thank you so much.

[tr1sha0312]

Can u plz explain how to replace the beta in the CAPM formula?? what is the expected return for the market to be replaced in the CAPM formula??

[problemchild786]

i love you man! this helped me a lot with my school project

[Patricklaurence]

Thanks! Greatly helped for my school work!

[TCfromUB]

thank you.

[pdaves]

Actually, R2 isn't correlation at all. It is a measure of goodness of fit. An R2 (read that, R-squared) of 0.23 is pretty good for weekly data and an individual stock on the left hand side. A portfolio on the left hand side would give an even higher R2. There is a reasonable question as to whether beta estimates are themselves all that useful, since the CAPM doesn't do a particularly good job of explaining stock returns. As far as a beta calculation goes, though, this is a pretty good one.

[santos3012]

Thanks so much didnt understand some of the things my teacher had on his excel example it results it was the regression thank you very much for the help

[carvajal185]

Great Value. Keep it up!!!

[McTheflame]

thanks man!!! finnly finished my paper!

[alaser66]

hey ,, it's actually like ur answering my assignment (: so thank u was really useful info. im just wondering if there are any differences (as i never done finance), between calculating beta and doing regression? because the question i got is .. estimate beta coefficients for A company and the lecturer told us to do regression first..

[pdaves]

@alaser66

This is the same as regression. The =SLOPE function is just an easier way of doing regression.

[AJones413]

thanks, helped me with my chem project

[malkidash]

two questions.

1.why didnt you include any riskfree rates in your calculation ? ( R-Rf=a+b(Rm-Rf)+e)

2.when I calculate the beta with the two ways, you explained very well, I attain a beta from the slope calculation that is slightly different from the regression calculation. Do you know any other reason than just me doing it wrong haha ?

very good explanation ;). thanks for that!

[pdaves]

@malkidash

The regression where you subtract off the risk free rate from the stock on the LHS, and from Rm on the RHS is the preferred method in finance. However, IF the risk free rate is uncorrelated with the market and with the stock, then it won't affect the expected value of the slope coefficient. So, since daily risk free data is a bit of a pain to download and put in the spreadsheet, I didn't use it. If you do subtract, then theory says you should run the regression without intercept.

[jamran08]

thanks.it was really helpfull

[pdaves]

Adjusted Close already has dividends built in. Find any company with dividends and look at the actual close on the ex-dividend date, and the adjusted close. Calculate the return correctly using the actual prices and the actual dividend. Then calculate the return using the Adjusted Close. You'll get the same answer.

[yunh789]

Adjusted Close price don't need to consider the dividends?

[Scud89]

Not the most difficult thing to calculate, but an extremely helpful video!

[pdaves]

You are welcome. It's not so difficult once you see how.

[enigmana]

THANK YOU SO MUCH! helped alot for my assignment :)

[mattnymet]

Thank you. Really helped with my assignment

[MrsSheryay]

you've been such a great help

thanks

[wannwanna]

am now doin my term paper and this video of urs is really useful ^^

thank you so much^^

[tiagetahun]

Thank you, thank you, thank you!

[ryanglambert]

Of great help!  Thanks

[WoodesRogers]

Hero x2. Thank you very much!!!

[fox5091]

You sir are a HERO!!! Your tutorial helped me out on my HW!! My professor is not as clear as you

[spyali1]

Your tutorial helped me a lot on my project. Thank you sir! =)

[t1n26]

I missed the part where he explained why weekly data is better than daily data.. Can someone help me please?

[z3186041]

The reason why weekly data is better than daily data is that it is less volatile. He calls is "noise". Noise is the up and down movements that occur on a daily basis that are above or below the fundamental (intrinsic) value.

With weekly data the movements are smoothed and so you get a less volatile reading.

[kennyrogers71385]

Really great stuff. Helped a lot. A pimp at heart you are professor. A pimp. A pimp of whoring out your beautiful pieces of knowledge.

[James2689uk]

you sound like mr garrison off south park ahha

[xjoncamposx]

Haha agreed.

[fahdcheetah]

thank you sooo much , i really learnt a lot , thank you once again

[annous89]

amazing video! keep it up.

[hamzaata]

THANKS A LOT

[shishead]

Many thanks from Greece for the video and the smooth explanation

[slipknotpsychoman]

Very well explained. What may have been helpful, and what was helpful for me when learning this was to see the Beta calculation using the covariance/VARP market and finding that you still get the slope value and the same value in the summary output.

[DennisVdBroeck]

wow, really practical vid. exactly what I needed. I was lost but now I see the light ;).

[haroldbingo]

This is an excellent tutorial - easy to follow, and very useful - thanks!!!

[krazykmac]

4:00 AM and i was soo lost, thank you so much!

[froggy575]

great! exactly what i needed!

[RefAmen]

thanks for the helpful tutorial. Very easy to follow. Although it was a pain to figure out how to install the 'add - in' data analysis.

[Arseheat]

guys do you know how to find slope of graph with excel 2007?

[pdaves]

The easiest way is with the =SLOPE function. Just enter that in a cell and click on the function help to see where to put the X-variables and the Y-Variables.

[carebare24]

thanks man.have an assignment on finance assignment due fri and this helped me out

[sza107]

pdaves, Good video and very helo ful1! Just so I have right, you took weekly data to calcate this? Why Weekly over month? please explain..

So 1.79 (slope) was the Beta? and you used regression also? In other works you used two approaches as a check to see if 1.79 would be the correct beta? your input will be appreciated.

[brzezik9]

that's a great video! it was very difficult to find a practical way of calculating Beta! Thank you very much pdaves! :)

[tougefreak]

so the beta 1.79 is actually for the S&P but not GM? so I want to know the beta for GM I need to switch it from Y to X right?

[pdaves]

Actually, the beta is for GM. If you run the regression with S&P on the right and on the left, you'll get a slope coefficient (and beta) of 1.0. Just remember the stock you want to know about goes on the left hand side of the regression(Y-variable), and the market index you use goes on the right hand side (X-variable). The intuition is that the market return in some fashion determines the systematic portion of the stock return.

[800382914]

finding the return on GM , why you are using the discrete arithmetic way of finding rate of return, right ?

can we use ln(B4/B5) continuous compunding return ?

[pdaves]

You are correct. That is the conventional way to do it. You could use continuous compounding as well. For high-frequency data, like this, it would be about the same as arithmetic compounding.

[sctrojnvirus]

Awesome, thanks.

[BkDs]

Thanks for the tutorial!!