Finance Excel homework

FIN360 – Fall 2015

In-class Excel Activity

Calculate summary statistics for Microsoft:

· Arithmetic and geometric average return.

· Range, standard deviation.

· 5%, 2.5% and 0.5% VAR

Estimate today’s expected return for Microsoft according to the Capital Asset Pricing Model:

· Plot security characteristic line.

· Run regression of Microsoft excess returns on market excess returns: identify alpha, beta, systematic risk, idiosyncratic risk, “goodness of fit” measure (R2) and standard deviation of the model residuals.

· Calculate the Treynor Information Ratio.

· Write-out the model’s CAPM equation and determine the expected return for today. According to the model, is MSFT over or undervalued? What would be your recommendation for a potential investor?

Estimate today’s expected return for Microsoft according to the Fama- French Three Factor Model

· Run regression of Microsoft excess returns on market excess returns: identify alpha, beta, factor loads (i.e. coefficients for SMB and HML factors), systematic risk, idiosyncratic risk, “goodness of fit” measure (R2) and standard deviation of the model residuals. Compare these results for those you obtained for the CAPM.

· Calculate the Treynor Information Ratio.

· Write-out the model’s equation and determine the expected return for today. According to the model, is MSFT over or undervalued? What would be your recommendation for a potential investor?

Estimate the Security Market Line for Google According to the Fama-French Five Factor Model

· Run regression of Microsoft excess returns on market excess returns: identify alpha, beta, factor loads, systematic risk, idiosyncratic risk, “goodness of fit” measure (R2) and standard deviation of the model residuals. Compare these results for those you obtained for the CAPM and the Fama-French three factor model.

· Calculate the Treynor Information Ratio.

· Write-out the model’s equation and determine the expected return for today. According to the model, is MSFT over or undervalued? What would be your recommendation for a potential investor?