You can use it to analyze the differences between the calculations in the files. Testing-Population-Mean.xlsx

Solved

Posted on 2011-10-27

Hi all

I am trying to replicate some stats in a spreadsheet, namely Alpha and Beta

I can get the beta calculation right (i think) but the alpha is not getting there...

If you look in the spreadsheet I have details the 'official' figures alongside my calculated figures...

can someone have a look and see whats going wrong?

Cheers

alphabeta.xlsx

I am trying to replicate some stats in a spreadsheet, namely Alpha and Beta

I can get the beta calculation right (i think) but the alpha is not getting there...

If you look in the spreadsheet I have details the 'official' figures alongside my calculated figures...

can someone have a look and see whats going wrong?

Cheers

alphabeta.xlsx

16 Comments

You can use it to analyze the differences between the calculations in the files. Testing-Population-Mean.xlsx

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?

It is all self-evident. Look at all the tabs. There is one unique to beta and alpha.

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

{=table(c15,c14)}

I am not sure what this means

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

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.

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

we are calculating beta:

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

http://www.investopedia.co

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

http://www.investopedia.co

Using this, and with only modifying the parentheses in your formula, I am now getting a different figure for alpha. In your spreadsheet, in order to make it easier for me to follow what you were doing, I named some cells. I named the Rf cell and the Beta cell. So I am now using formula:

=C16-(Rf+Beta*(C17-C22))

And with nothing but the addition of the parenthesis which match the article on investopedia, the calculated figure changes due to order of operations. Please check out the article and check your formula for exact order of operations processing.

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....

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)

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Extract strings / characters from the right till the first occurrence of a comma, using an excel formula | 6 | 31 | |

MS Excel Cell questions | 5 | 40 | |

Sum Per Month | 7 | 23 | |

Min Month | 11 | 29 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**18** Experts available now in Live!