Question

How do you find the alpha and beta using solver on excel?

Homework). Excel Chapter 1 Finance Search Desert Page Layout formulas Da Revi e w Help er it! Solution Mutual fund Mutual fund return Market risk premium stock risk premium Mutual fund msk premium New SAPO Stock price 6.6 3.86% 00012 25 1/2009 4/2010 1/2000 1/2010 4/1/2010 111 1 $ S 1.4 115.11 1.50 8.10% 2.07% 1001 1105 - 5.99 705 3/11/2010 2000 1/200 39/1/2010 11/1/2010 /2000 2011 IN 1161.26 11055 1252643 SP 5.24 30/ 11 1935 Chapter Section 113 114 11

Answer 1

Alpha = Actual Return of Stock - Expected Return of Stock (All Returns are in excess of Risk Free Return)

Beta = Covariance (Stock Returns, MArket Returns)/Variance (Market Returns)

Beta can be calculated by Solver, throgh setting an objective of minimal root mean squared error, with Beta bieng the slope predicting Stock returns from Market Returns. This gives the value of Beta.

As Expected Stock Return = Beta * Market Return, the value of Beta calculated by solver can be used to calulcate Alpha

S shobhit gupta - 0 X & Share File Home Insert Page Layout Formulas Data Review View Beta Solver - Excel Search Define Name & Trace Precedents 5 Show Formulas Face Use in Formula o Trace Dependents Help fx 3 5 ? A É TQ @ - P Calculate Now Insert Function AutoSum Recently Financial Logical Text Date & Lookup & Math & - Used - - Time - Reference Trig Function Library More Functions Name Manager Create from Selection Defined Names Error Checking X Remove Arrows 0 Evaluate Formula Formula Auditing Watch Window Calculation Calculate Sheet Options Calculation M14 fc 1 к Square x т Solver Calculations м N O P Q R S T U V W X Y Z AA 4 т 1 Project A 0.000287281 0.000407386 0.000291156 0.986693332 0.000238573 Steps to Solve 1 Set N15 to 1 2 Open the solver, set objective as minmizing Root Mean Squared Error (RMSE) i.e. N16 3 Set variable cell as "Beta by Solver" i.e. N15 4 Run the Solver 0.0% -1.1% -0.3% 0.0% 0.5% -0.6% -0.4% -0.4% -0.2% -0.5% -1.2% -0.3% -0.6% 0.0% -0.7% -0.6% -0.9% -0.5% -0.6% 0.2% -0.9% 0.00018 0.00003 Formula Calculations 0.00007 Covariance with Market 0.00001 Variance of Google Returns 0.00005 Variance of S&P Retruns 0.00003 Beta 0.00000 Alpha 0.00002 0.00013 0.00002 0.00000 0.00000 Solver Calculations 0.00001 Beta by Solver 0.00002 RMSE 0.00001 Alpha 0.00001 0.00007 0.00000 0.00007 0.00002 0.00004 Beta Solver 0.959873102 0.011675478 0.00021555 23 Ready -- + + 100%

Excel with Formula and steps for Solver

Beta Solver - Excel shobhit gupta - 0 X TES File Home Insert Page Layout Formulas Data Review View Help Search & Ships - R fax 2 A E 19 Te 9 Define Name c Trace Precedents 5 Show Formulas calculate Now Use in Formula Insert Function AutoSum Recently Financial Logical Text Date & Lookup & Math & - Used - - Time - Reference Trig Function Library More Functions Name Manager Trace Dependents Error Checking X Remove Arrows 0 Evaluate Formula Formula Auditing Watch Window Create from Selection Calculation Calculate Sheet Options Calculation Defined Names M14 X fx Solver Calculations M 4 Formula Calculations 5 Covariance with Market 6 Variance of Google Returns 7 Variance of S&P Retruns 8 Beta 9 Alpha N Steps to Sol Set N15 to 1 Open the solver, set objective as minmizing Root Mean Squared Error (RMSE) i.e. N16 Set variable cell as "Beta by Solver" i.e. N15 Run the Solver =COVARIANCE.S(E3:E253,F3:F253) =VAR.S(13:1253) =VAR.S(H3:H253) =N5/N7 =INTERCEPT(13:1253, H3:H253) 14 Solver Calculations 15 Beta by Solver 16 RMSE 17 Alpha 0.959873101823526 =SQRT(AVERAGE(K3:K253)) =AVERAGE(13:1253)-N15*AVERAGE(H3:H253) Beta Solver Ready 0 - + 100%