[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

Excel statistics Alpha and Beta problem

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
0
cycledude
Asked:
cycledude
  • 9
  • 7
1 Solution
 
Felicia KingCommented:
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
0
 
cycledudeAuthor Commented:
@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?
0
 
Felicia KingCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
cycledudeAuthor Commented:
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


0
 
cycledudeAuthor Commented:
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

0
 
Felicia KingCommented:
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.
0
 
Felicia KingCommented:
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.
0
 
cycledudeAuthor Commented:
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)

0
 
cycledudeAuthor Commented:
thanks

that would be great if you could help ....
0
 
Felicia KingCommented:
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
0
 
cycledudeAuthor Commented:
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
0
 
Felicia KingCommented:
Ok, maybe I have it. It looks like your formula doesn't have the parentheses in the correct places in order to calculate alpha properly. Look at the following article.
http://www.investopedia.com/articles/financial-theory/08/deeper-look-at-alpha.asp#axzz1bzIOdSov

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.
0
 
cycledudeAuthor Commented:
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....
0
 
cycledudeAuthor Commented:
thank you
0
 
Felicia KingCommented:
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.
0
 
cycledudeAuthor Commented:
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)

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now