Excel statistics Alpha and Beta problem

Rather than analyze your file, I thought I would post a file that is known good and accurate in alpha and beta calculations.
You can use it to analyze the differences between the calculations in the files. Testing-Population-Mean.xlsx

@locojalapeno

Thanks, I am non too familiar with excel (especially 2007+), I use it for mostly basic stuff... yours is a bit ahead of me here!

where are your formulas and calculations?

All you need to do is to select on the actual cells where the calculations occur.
It is all self-evident. Look at all the tabs. There is one unique to beta and alpha.

I am looking at the beta vs alpha tab, but I am not seeing any formulas anywhere

all I can see is a table, in each cell is a reference to

{=table(c15,c14)}

I am not sure what this means

ah, just noticed, the hidden cells

I am not sure if this will make any difference but I am trying to replicate Financial stats, beta and alpha

in particular, the alpha is known as 'jensens alpha' and may be different

The level of significance, alpha, is always decided upon at 1%, 5%, or 10% in calculations with beta and its complement Power. The Power tab in the spreadsheet shows the calculation for beta in detail as well as power. Beta is the probability of Type 2 error, so that is what it is listed as in the spreadsheet. Look for the formula cell to the right of that. Keep in mind that excel has a plethora of built-in functions for statistics. These are used in the calculations.

Beta is not an easy calculation because you have to know whether you are dealing with the rejection region to the right, left, or both sides. The direction of the rejection region dictates the direction of the Z transform calculation from the critical value. The critical value could be meu naught a.k.a. the population mean for the null hypothesis. The critical value could also be p naught a.k.a. the population proportion for the null hypothesis.

This is why the calculation for beta is so complicated. The formula accomodates for these factors.

Yes, alpha and beta are different in corporate financial managment than in statistics. I assumed you were looking for statistics calculations because you mentioned stats in your question. Do you have access to an advanced corporate financial management textbook? If not, I could break out my text and see about giving you more info.

Hi again

I have a feeling that the financial calculations are different to yours..

we are calculating beta:

beta=COVAR(B8:B19,C8:C19)/VARP(C8:C19)

thanks

that would be great if you could help ....

I just looked in my advanced corporate financial management textbook. It doesn't go into enough detail to be useful for you. In that section, it focuses on WACC and NPV calcs associated with cash flow estimation and risk analysis. However, I found an article that would indicate your calculations look correct.
http://www.investopedia.com/articles/financial-theory/09/calculating-beta.asp#axzz1bzECbv3L

Hi Again

Thanks for the link, yes I am using the same beta calculation as that... it's the alpha which is throwing me... do you have any ideas on what it going wrong?

Thanks again

Thanks for the additional link... (I did a search on that website for alpha and there were TONS of results!)

I never noticed that extra set of parenthesis in the calculations I was working from but, I have put them in, thanks

Still got a different figure for the alpha to the 'proper' one though....

thank you

I became rather interested in your project. Would you mind posting your final version of the corrected spreadsheet so I could review it and learn from your work? I love finance and statistics.

It turned out I was using the wrong version of Alpha

I was using Jensens Alpha, when I should have been using Standard Alpha

alpha = (((sum of the rate of return of fund) – (ß)*(rate of return of the market))/(# of observations))

which gave me a more accurate figure (however it was not 100%, but I guess this is due to decimal places in the performance data)