Using Statistics Functions in Excel
Using Statistical Functions in Excel
To calculate the expected return using historical return data use the AVERAGE function in Excel. For each firm and the market index, first calculate the return for each month over the entire time period. If you already have return data proceed as follows. An approximation of the annualized expected return can be calculated using the formula: 12*AVERAGE(Cells for firm here, e.g., A2:A37), where the cells are the monthly returns.
To calculate the variance of returns using historical return data use the VAR function in Excel. Again for each firm and the market index, first calculate the return for each month. The historical annualized variance can be calculated using the formula: 12*VAR(Cells for firm’s returns here, e.g., A2:A37), where the cells are the monthly returns.
To get the standard deviation just take the square root of the variance as calculated above. You can also use the standard deviation function in Excel, STDEV, but be careful because you will need to take the calculation and multiple it by the square root of 12 to annualize the returns. The following formula calculates the yearly standard deviation: =STDEV(cells for firm here, e.g., A2:A37)*SQRT(12).
To calculate the covariance use the COVAR function. The following formula calculates the annualized covariance between two stocks =12*COVAR(cells range for the firms here, e.g., A2:B37|B2:B37).
You can calculate a firm beta using the covariance and variance functions discussed above but the easier way to do it is with regression analysis. See the handout titled “Calculating a Beta Using Excel” for instructions on how to calculate a beta using regression analysis.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- using statistics functions in excel
- excel formulas and functions whitman college
- more formulae and functions exercises
- microsoft excel spreadsheet
- introduction to excel formulae and functions
- advanced excel statistical functions formulae
- microsoft excel 2013 formulas and functions
- introduction to excel formulae functions exercises
Related searches
- treasury functions in a company
- finance functions in a company
- support functions in an organization
- statistics functions on ti 84
- ti 84 statistics functions pdf
- functions in a company
- functions in excel
- functions in microsoft word
- financial functions in healthcare
- using variables in excel formulas
- descriptive statistics in excel 365
- change text color in excel using formula