{"text_format":"1","workbook":"xlsx","n_sheets":16,"sheets":["Exercise 1","Exercise 2","Exercise 3","Exercise 4","Exercise 6","Exercise 7","Exercise 9 template","Exercise 9","Regression","Exercise 10 template","Exercise 10","Exercise 12 template","Exercise 12","Exercise 14 template","Exercise 14 answer","Sheet1"],"not_rendered":{"charts":4,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Exercise 1","index":0,"n_rows":6,"n_cols":3,"rows_kept":6,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["ABC CORP."]
["Current stock price, P0",50]
["Current dividend, D0",3]
["Dividend growth rate, g",0.05]
[]
["Cost of equity from Gordon model",{"$f":"B3*(1+B4)/B2+B4","v":0.113},{"$f":"getformula(B6)","v":"<--  =B3*(1+B4)/B2+B4"}]

{"sheet":"Exercise 2","index":1,"n_rows":5,"n_cols":3,"rows_kept":5,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["UNHEARDOF, INC."]
["Current dividend, D0",5]
["Anticipated dividend growth rate, g",0.15]
["Cost of equity, rE",0.25]
["Gordon model stock price",{"$f":"B2*(1+B3)/(B4-B3)","v":57.5},{"$f":"getformula(B5)","v":"<--  =B2*(1+B3)/(B4-B3)"}]

{"sheet":"Exercise 3","index":2,"n_rows":8,"n_cols":5,"rows_kept":8,"cut":false,"hidden":false,"merged":[[0,0,0,2],[2,4,7,4]]}
["DISMAL.COM"]
["Dividend, starting in 3 years",15]
["Growth rate",0.2,null,null,"Explanation:  At the end of 2 years, the next dividend will be 15, and this dividend will grow at 20% per year.Thus the Gordon model applies, and the stock price at the end of 2 years will be 15/(0.35-0.20).  Today's stock price is the price 2 years from now discounted at the cost of equity."]
["Cost of equity",0.35]
["Value of Dismal.com 3 years from today",15]
[]
["Value of Dismal.com stock 2 years from today",{"$f":"B5/(B4-B3)","v":100.00000000000003}]
["Today's stock price",{"$f":"B7/(1+B4)^2","v":54.86968449931414},{"$f":"getformula(B8)","v":"<--  =B7/(1+B4)^2"}]

{"sheet":"Exercise 4","index":3,"n_rows":23,"n_cols":5,"rows_kept":23,"cut":false,"hidden":false,"merged":[[0,0,0,4]]}
["CHRYSLER CORPORATION (C)"]
["Year","Year-end stock price","Dividend per share","Growth rate"]
[1986,null,0.4]
[1987,null,0.5,{"$f":"C4/C3-1","v":0.25},{"$f":"getformula(D4)","v":"<--  =C4/C3-1"}]
[1988,null,0.5,{"$f":"C5/C4-1","v":0},{"$f":"getformula(D5)","v":"<--  =C5/C4-1"}]
[1989,null,0.6,{"$f":"C6/C5-1","v":0.19999999999999996},{"$f":"getformula(D6)","v":"<--  =C6/C5-1"}]
[1990,null,0.6,0,{"$f":"getformula(D7)","v":"<--  =C7/C6-1"}]
[1991,null,0.3,-0.5,{"$f":"getformula(D8)","v":"<--  =C8/C7-1"}]
[1992,null,0.3,0]
[1993,null,0.33,0.10000000000000009]
[1994,null,0.45,0.36363636363636354]
[1995,null,1,1.2222222222222223]
[1996,35,1.4,0.39999999999999991]
[]
["Compound growth, 10 yr.",{"$f":"(C13/C3)^(1/10)-1","v":0.13346158167069744},{"$f":"getformula(B15)","v":"<--  =(C13/C3)^(1/10)-1"}]
["Compound growth, 5 yr.",{"$f":"(C13/C8)^(1/5)-1","v":0.36082210785873881},{"$f":"getformula(B16)","v":"<--  =(C13/C8)^(1/5)-1"}]
[]
["Gordon model"]
["P0",{"$f":"B13","v":35},"<-- Ending 1996 share price"]
["D0",{"$f":"C13","v":1.4},"<-- 1996 dividend"]
["rE"]
["     10-yr. growth",{"$f":"B20*(1+B15)/B19+B15","v":0.17880004493752533},{"$f":"getformula(B22)","v":"<--  =B20*(1+B15)/B19+B15"}]
["      5-yr. growth",{"$f":"B20*(1+B16)/B19+B16","v":0.41525499217308837},{"$f":"getformula(B23)","v":"<--  =B20*(1+B16)/B19+B16"}]

{"sheet":"Exercise 6","index":4,"n_rows":23,"n_cols":6,"rows_kept":23,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["ABC Corp (\"supernormal\" growth)"]
["Current dividend",3,null,"Dividend valuation"]
["Growth rate g1, years 1-10 (\"supernormal\")",0.15,null,"PV of years 1-10",{"$f":"NPV(B5,B8:B17)","v":34.794551567746453},{"$f":"getformula(E3)","v":"<--  =NPV(B5,B8:B17)"}]
["Growth rate g2, years 11 - ¥",0.1,null,"PV of years 11 - ¥",{"$f":"(B18/(B5-B4))/((1+B5)^10)","v":214.92261746676652},{"$f":"getformula(E4)","v":"<--  =(B18/(B5-B4))/((1+B5)^10)"}]
["Cost of equity",0.12,null,"Share value",{"$f":"SUM(E3:E4)","v":249.71716903451298},{"$f":"getformula(E5)","v":"<--  =SUM(E3:E4)"}]
[]
["Year"]
[1,{"$f":"$B$2*(1+$B$3)^A8","v":3.4499999999999997},{"$f":"getformula(B8)","v":"<--  =$B$2*(1+$B$3)^A8"}]
[2,{"$f":"$B$2*(1+$B$3)^A9","v":3.9674999999999994},{"$f":"getformula(B9)","v":"<--  =$B$2*(1+$B$3)^A9"}]
[3,4.5626249999999988,{"$f":"getformula(B10)","v":"<--  =$B$2*(1+$B$3)^A10"}]
[4,5.2470187499999987,{"$f":"getformula(B11)","v":"<--  =$B$2*(1+$B$3)^A11"}]
[5,6.0340715624999977]
[6,6.9391822968749972]
[7,7.9800596414062444]
[8,9.1770685876171818]
[9,10.553628875759758,{"$f":"getformula(B16)","v":"<--  =$B$2*(1+$B$3)^A16"}]
[10,{"$f":"$B$2*(1+$B$3)^A17","v":12.136673207123721},{"$f":"getformula(B17)","v":"<--  =$B$2*(1+$B$3)^A17"}]
[11,{"$f":"B17*(1+$B$4)","v":13.350340527836094},{"$f":"getformula(B18)","v":"<--  =B17*(1+$B$4)"}]
[12,{"$f":"B18*(1+$B$4)","v":14.685374580619705}]
[13,16.153912038681675]
[14,17.769303242549842]
[15,19.54623356680483]
[16,21.500856923485316]

{"sheet":"Exercise 7","index":5,"n_rows":18,"n_cols":3,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["bequity",1.5]
["bdebt",0.4]
["Risk-free, rf",0.06]
["E(rm)",0.15]
["Corporate tax rate TC",0.4]
[]
["Capital structure"]
["Percentage of equity",0.4]
["Percentage of debt",0.6]
[]
["Cost of equity"]
["Classic CAPM",{"$f":"B3+B1*(B4-B3)","v":0.19500000000000001},{"$f":"getformula(B12)","v":"<--  =B3+B1*(B4-B3)"}]
[]
["Cost of debt"]
["Classic CAPM",{"$f":"B3+B2*(B4-B3)","v":9.6000000000000002E-2},{"$f":"getformula(B15)","v":"<--  =B3+B2*(B4-B3)"}]
[]
["Weighted average cost of capital (WACC)"]
["Classic CAPM",{"$f":"B9*B15*(1-B5)+B8*B12","v":0.11256000000000002},{"$f":"getformula(B18)","v":"<--  =B9*B15*(1-B5)+B8*B12"}]

{"sheet":"Exercise 9 template","index":6,"n_rows":63,"n_cols":3,"rows_kept":63,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["CISCO (CSCO) AND S&P 500 PRICES\nJuly 2002 - June 2007"]
["Date","S&P 500","CSCO"]
["2002-07-03",911.62,13.19]
["2002-08-01",916.07,13.82]
["2002-09-03",815.28,10.48]
["2002-10-01",885.76,11.18]
["2002-11-01",936.31,14.92]
["2002-12-02",879.82,13.1]
["2003-01-02",855.7,13.37]
["2003-02-03",841.15,13.98]
["2003-03-03",848.18,12.98]
["2003-04-01",916.92,15]
["2003-05-01",963.59,16.41]
["2003-06-02",974.5,16.79]
["2003-07-01",990.31,19.489999999999998]
["2003-08-01",1008.01,19.14]
["2003-09-02",995.97,19.59]
["2003-10-01",1050.71,20.93]
["2003-11-03",1058.2,22.7]
["2003-12-01",1111.92,24.23]
["2004-01-02",1131.1300000000001,25.71]
["2004-02-02",1144.94,23.16]
["2004-03-01",1126.21,23.57]
["2004-04-01",1107.3,20.91]
["2004-05-03",1120.68,22.37]
["2004-06-01",1140.8399999999999,23.7]
["2004-07-01",1101.72,20.92]
["2004-08-02",1104.24,18.760000000000002]
["2004-09-01",1114.58,18.100000000000001]
["2004-10-01",1130.2,19.21]
["2004-11-01",1173.82,18.75]
["2004-12-01",1211.92,19.32]
["2005-01-03",1181.27,18.04]
["2005-02-01",1203.5999999999999,17.420000000000002]
["2005-03-01",1180.5899999999999,17.89]
["2005-04-01",1156.8499999999999,17.27]
["2005-05-02",1191.5,19.399999999999999]
["2005-06-01",1191.33,19.079999999999998]
["2005-07-01",1234.18,19.149999999999999]
["2005-08-01",1220.33,17.62]
["2005-09-01",1228.81,17.920000000000002]
["2005-10-03",1207.01,17.45]
["2005-11-01",1249.48,17.54]
["2005-12-01",1248.29,17.12]
["2006-01-03",1280.08,18.57]
["2006-02-01",1280.6600000000001,20.239999999999998]
["2006-03-01",1294.8699999999999,21.67]
["2006-04-03",1310.6099999999999,20.95]
["2006-05-01",1270.0899999999999,19.68]
["2006-06-01",1270.2,19.53]
["2006-07-03",1276.6600000000001,17.88]
["2006-08-01",1303.82,21.99]
["2006-09-01",1335.85,22.98]
["2006-10-02",1377.94,24.13]
["2006-11-01",1400.63,26.91]
["2006-12-01",1418.3,27.33]
["2007-01-03",1438.24,26.62]
["2007-02-01",1406.82,25.94]
["2007-03-01",1420.86,25.53]
["2007-04-02",1482.37,26.74]
["2007-05-01",1530.62,26.92]
["2007-06-01",1502.97,27.85]
["2007-07-02",1519.43,27.89]

{"sheet":"Exercise 9","index":7,"n_rows":72,"n_cols":7,"rows_kept":72,"cut":false,"hidden":false,"merged":[[9,4,9,5],[0,0,0,6]]}
["CISCO (CSCO) a and b\nJuly 2002 - June 2007"]
["Alpha",{"$f":"INTERCEPT(F13:F72,E13:E72)","v":-3.2420973877597247E-3},{"$f":"getformula(B2)","v":"<--  =INTERCEPT(F13:F72,E13:E72)"}]
["Beta"]
["Using Excel's slope function",{"$f":"SLOPE(F13:F72,E13:E72)","v":1.8465364006956511},{"$f":"getformula(B4)","v":"<--  =SLOPE(F13:F72,E13:E72)"}]
["Using Cov/Var",{"$f":"_xlfn.COVARIANCE.S(F13:F72,E13:E72)/_xlfn.VAR.S(E13:E72)","v":1.8465364006956502},{"$f":"getformula(B5)","v":"<--  =COVARIANCE.S(F13:F72,E13:E72)/VAR.S(E13:E72)"}]
["R-squared",{"$f":"RSQ(F13:F72,E13:E72)","v":0.44604975960715848},{"$f":"getformula(B6)","v":"<--  =RSQ(F13:F72,E13:E72)"}]
["t-statistic for alpha",{"$f":"tintercept(F13:F72,E13:E72)","v":-0.37108992903232468},{"$f":"getformula(B7)","v":"<--  =tintercept(F13:F72,E13:E72)"}]
["t-statistic for beta",{"$f":"tslope(F13:F72,E13:E72)","v":6.8339260348395143},{"$f":"getformula(B8)","v":"<--  =tslope(F13:F72,E13:E72)"}]
[]
[null,null,null,null,"Returns"]
["Date","S&P 500","CSCO",null,"S&P 500","CSCO"]
["2002-07-03",911.62,13.19]
["2002-08-01",916.07,13.82,null,{"$f":"LN(B13/B12)","v":4.8695443903156652E-3},{"$f":"LN(C13/C12)","v":4.6657851610300713E-2},{"$f":"getformula(F13)","v":"<--  =LN(C13/C12)"}]
["2002-09-03",815.28,10.48,null,{"$f":"LN(B14/B13)","v":-0.11656116844786664},{"$f":"LN(C14/C13)","v":-0.27664813944662769}]
["2002-10-01",885.76,11.18,null,8.2914421028757151E-2,6.4657788834056915E-2]
["2002-11-01",936.31,14.92,null,5.550058467611222E-2,0.28857612704866176]
["2002-12-02",879.82,13.1,null,-6.2229277129875436E-2,-0.13009036456850889]
["2003-01-02",855.7,13.37,null,-2.7797493671422965E-2,2.0401160906745996E-2]
["2003-02-03",841.15,13.98,null,-1.7149844258839787E-2,4.4614345691812426E-2]
["2003-03-03",848.18,12.98,null,8.3228742528296627E-3,-7.4218025529720219E-2]
["2003-04-01",916.92,15,null,7.7927350029476733E-2,0.14464048982626604]
["2003-05-01",963.59,16.41,null,4.9645665489287727E-2,8.9840703999789537E-2]
["2003-06-02",974.5,16.79,null,1.125862601085219E-2,2.2892565987449841E-2]
["2003-07-01",990.31,19.489999999999998,null,1.6093506478773681E-2,0.14911804243001994]
["2003-08-01",1008.01,19.14,null,1.7715343790636197E-2,-1.8121127494661146E-2]
["2003-09-02",995.97,19.59,null,-1.2016232567985653E-2,2.3238845931641137E-2]
["2003-10-01",1050.71,20.93,null,5.3504268464946513E-2,6.616430450145902E-2]
["2003-11-03",1058.2,22.7,null,7.1032253560451564E-3,8.1181388029448548E-2]
["2003-12-01",1111.92,24.23,null,4.9518899306471208E-2,6.5226610341077462E-2]
["2004-01-02",1131.1300000000001,25.71,null,1.7128882262967212E-2,5.928848644936386E-2]
["2004-02-02",1144.94,23.16,null,1.2135100829125884E-2,-0.10445336857300365]
["2004-03-01",1126.21,23.57,null,-1.6494220669989047E-2,1.7548064243245242E-2]
["2004-04-01",1107.3,20.91,null,-1.6933393494544095E-2,-0.11974720350749753]
["2004-05-03",1120.68,22.37,null,1.2011024205564368E-2,6.7493262061496029E-2]
["2004-06-01",1140.8399999999999,23.7,null,1.7829189249312503E-2,5.7754272639047269E-2]
["2004-07-01",1101.72,20.92,null,-3.4892238215330364E-2,-0.12476940894436327]
["2004-08-02",1104.24,18.760000000000002,null,2.284720571713859E-3,-0.1089786956186435]
["2004-09-01",1114.58,18.100000000000001,null,9.3203368022064838E-3,-3.5815005306298608E-2]
["2004-10-01",1130.2,19.21,null,1.391695587821374E-2,5.9519038508685083E-2]
["2004-11-01",1173.82,18.75,null,3.7868779461133012E-2,-2.4237224364045545E-2]
["2004-12-01",1211.92,19.32,null,3.1942491193192112E-2,2.9947076367952099E-2]
["2005-01-03",1181.27,18.04,null,-2.5615747968515911E-2,-6.854931414989443E-2]
["2005-02-01",1203.5999999999999,17.420000000000002,null,1.8726934874337724E-2,-3.4972543210120687E-2]
["2005-03-01",1180.5899999999999,17.89,null,-1.930275225452871E-2,2.6622926096270923E-2]
["2005-04-01",1156.8499999999999,17.27,null,-2.031351934767037E-2,-3.5271005362040593E-2]
["2005-05-02",1191.5,19.399999999999999,null,2.9512223385105795E-2,0.11630217391069511]
["2005-06-01",1191.33,19.079999999999998,null,-1.4268747689802155E-4,-1.6632400049142033E-2]
["2005-07-01",1234.18,19.149999999999999,null,3.5336451864729147E-2,3.6620496065146203E-3]
["2005-08-01",1220.33,17.62,null,-1.1285467972359155E-2,-8.3268095118621446E-2]
["2005-09-01",1228.81,17.920000000000002,null,6.9249074268589216E-3,1.6882787038750936E-2]
["2005-10-03",1207.01,17.45,null,-1.7899994313773929E-2,-2.6577758898348389E-2]
["2005-11-01",1249.48,17.54,null,3.4581237676988605E-2,5.1443382956007132E-3]
["2005-12-01",1248.29,17.12,null,-9.528500142413687E-4,-2.4236616230440881E-2]
["2006-01-03",1280.08,18.57,null,2.5147961230518261E-2,8.1300004651018276E-2]
["2006-02-01",1280.6600000000001,20.239999999999998,null,4.529940641520396E-4,8.6113469054650296E-2]
["2006-03-01",1294.8699999999999,21.67,null,1.1034733969458945E-2,6.8267971129003083E-2]
["2006-04-03",1310.6099999999999,20.95,null,1.2082373675144375E-2,-3.3790169180121059E-2]
["2006-05-01",1270.0899999999999,19.68,null,-3.1404913585891703E-2,-6.2535754744039371E-2]
["2006-06-01",1270.2,19.53,null,8.6604285391806066E-5,-7.6511467355197503E-3]
["2006-07-03",1276.6600000000001,17.88,null,5.072924191958309E-3,-8.8268975143219602E-2]
["2006-08-01",1303.82,21.99,null,2.105112458799209E-2,0.20690503482130165]
["2006-09-01",1335.85,22.98,null,2.4269376195304011E-2,4.4036467853939901E-2]
["2006-10-02",1377.94,24.13,null,3.1021836917226073E-2,4.8831607216330751E-2]
["2006-11-01",1400.63,26.91,null,1.6332505122359679E-2,0.10904208510187403]
["2006-12-01",1418.3,27.33,null,1.2536835916847028E-2,1.548703520116212E-2]
["2007-01-03",1438.24,26.62,null,1.3961172524527271E-2,-2.6322186973010985E-2]
["2007-02-01",1406.82,25.94,null,-2.2088305664389823E-2,-2.5876634078667714E-2]
["2007-03-01",1420.86,25.53,null,9.9304839152859412E-3,-1.5931947634905349E-2]
["2007-04-02",1482.37,26.74,null,4.2379836237605301E-2,4.630634042040456E-2]
["2007-05-01",1530.62,26.92,null,3.2030723748061214E-2,6.7089331027300608E-3]
["2007-06-01",1502.97,27.85,null,-1.8229731587722845E-2,3.3963461596765852E-2]
["2007-07-02",1519.43,27.89,null,1.0892114036310005E-2,1.4352352661045887E-3]

{"sheet":"Regression","index":8,"n_rows":84,"n_cols":9,"rows_kept":84,"cut":false,"hidden":false,"merged":[]}
["SUMMARY OUTPUT"]
[]
["Regression Statistics"]
["Multiple R",0.66786956781033113]
["R Square",0.44604975960715842]
["Adjusted R Square",0.43649889339348874]
["Standard Error",6.5285632521320858E-2]
["Observations",60]
[]
["ANOVA"]
[null,"df","SS","MS","F","Significance F"]
["Regression",1,0.19905623264601391,0.19905623264601391,46.702545049657324,5.5684558625220151E-9]
["Residual",58,0.247208401195119,4.2622138137089483E-3]
["Total",59,0.44626463384113291]
[]
[null,"Coefficients","Standard Error","t Stat","P-value","Lower 95%","Upper 95%","Lower 95.0%","Upper 95.0%"]
["Intercept",-3.2420973877597212E-3,8.7366892338307314E-3,-0.37108992903232468,0.71192260598423385,-2.0730480980662099E-2,1.4246286205142693E-2,-2.0730480980662137E-2,1.4246286205142693E-2]
["X Variable 1",1.8465364006956499,0.27020140271960291,6.8339260348395143,5.5684558625220151E-9,1.3056695286312534,2.3874032727600478,1.3056695286312534,2.3874032727600478]
[]
[]
[]
["RESIDUAL OUTPUT"]
[]
["Observation","Predicted Y","Residuals"]
[1,5.7496935837614629E-3,4.090815802653925E-2]
[2,-0.2184765378343628,-5.8171601612264895E-2]
[3,0.14986239918444527,-8.5204610350388357E-2]
[4,9.9241752476572714E-2,0.18933437457208904]
[5,-0.11815072279705208,-1.1939641771456816E-2]
[6,-5.4571181300149207E-2,7.4972342206895207E-2]
[7,-3.4909909077968711E-2,7.9524254769781144E-2]
[8,1.2126392878502866E-2,-8.6344418408223086E-2]
[9,0.14065359105142036,3.9868987748456763E-3]
[10,8.8430431074969909E-2,1.4102729248196277E-3]
[11,1.7547365363097714E-2,5.3452006243521269E-3]
[12,2.6475148140127164E-2,0.12264289428989278]
[13,2.9469929772487684E-2,-4.759105726714883E-2]
[14,-2.5430508223769806E-2,4.8669354155410946E-2]
[15,9.5555481925356414E-2,-2.9391177423897394E-2]
[16,9.8742667945219825E-3,7.1307121234926565E-2]
[17,8.8196352704021971E-2,-2.2969742362944509E-2]
[18,2.8387007214039327E-2,3.0901479235324533E-2]
[19,1.9165808019333194E-2,-0.12361917659233684]
[20,-3.3699276256001102E-2,5.1247340499246341E-2]
[21,-3.4510224862738317E-2,-8.5236978644759209E-2]
[22,1.8936696017451446E-2,4.8556566044044583E-2]
[23,2.9680149555987377E-2,2.8074123083059892E-2]
[24,-6.7671885354111089E-2,-5.7097523590252178E-2]
[25,9.7672231332809716E-4,-0.1099554179319716]
[26,1.396824378425785E-2,-4.978324909055646E-2]
[27,2.2456068228237258E-2,3.7062970280447825E-2]
[28,6.6683982337138209E-2,-9.0921206701183754E-2]
[29,5.5740875329369763E-2,-2.5793798961417664E-2]
[30,-5.0542508442670012E-2,-1.8006805707224419E-2]
[31,3.1337869531161716E-2,-6.631041274128241E-2]
[32,-3.8885332059357021E-2,6.5508258155627941E-2]
[33,-4.0751750289468425E-2,5.4807449274278316E-3]
[34,5.1253297358299546E-2,6.5048876552395565E-2]
[35,-3.5055750077753376E-3,-1.3126825041366695E-2]
[36,6.2007947251892355E-2,-5.8345897645377731E-2]
[37,-2.4081124797605838E-2,-5.9186970321015608E-2]
[38,9.5449962473829319E-3,7.3377907913680044E-3]
[39,-3.6295088460388446E-2,9.7173295620400571E-3]
[40,6.0613416763907642E-2,-5.5469078468306926E-2]
[41,-5.0015696234597777E-3,-1.9235046606981103E-2]
[42,4.3194528427675231E-2,3.8105476223343045E-2]
[43,-2.4056273590039195E-3,8.8519096413654214E-2]
[44,1.7133940558839031E-2,5.1134030570164052E-2]
[45,1.9068445410201251E-2,-5.285861459032231E-2]
[46,-6.1232413484810122E-2,-1.3033412592292487E-3]
[47,-3.0821794223275167E-3,-4.5689673131922336E-3]
[48,6.1252417906608667E-3,-9.4394216933880476E-2]
[49,3.5629570439546905E-2,0.17127546438175476]
[50,4.1572189179045656E-2,2.464278674894245E-3]
[51,5.4040853696342372E-2,-5.2092464800116214E-3]
[52,2.6916467835225596E-2,8.2125617266648437E-2]
[53,1.9907626482246948E-2,-4.4205912810848288E-3]
[54,2.2537715875171879E-2,-4.8859902848182868E-2]
[55,-4.4028957826747456E-2,1.8152323748079742E-2]
[56,1.5094902638338433E-2,-3.1026850273243783E-2]
[57,7.5013812880499076E-2,-2.8707472460094516E-2]
[58,5.5903799953661931E-2,-4.919486685093187E-2]
[59,-3.6903960339401271E-2,7.086742193616713E-2]
[60,1.6870587660814729E-2,-1.5435352394710141E-2]

{"sheet":"Exercise 10 template","index":9,"n_rows":13,"n_cols":6,"rows_kept":13,"cut":false,"hidden":false,"merged":[[0,0,0,5]]}
["NORMAL AMERICA, INC."]
["Year","Dec. 31 stock price","Dec. 15 dividend per share",null,null,"S&P 500 return"]
[1986,33]
[1987,{"$f":"30+11/16","v":30.6875},2.5,null,1987,4.7E-2]
[1988,35.380000000000003,2.5,null,1988,0.16200000000000001]
[1989,42.25,3,null,1989,0.314]
[1990,34.375,3,null,1990,-3.3000000000000002E-2]
[1991,36.25,1.6,null,1991,0.30199999999999999]
[1992,32.25,1.4,null,1992,7.3999999999999996E-2]
[1993,43,0.8,null,1993,9.9000000000000005E-2]
[1994,42.125,0.8,null,1994,1.2E-2]
[1995,52.875,1.1000000000000001,null,1995,0.374]
[1996,55.75,1.6,null,1996,0.22900000000000001]

{"sheet":"Exercise 10","index":10,"n_rows":31,"n_cols":7,"rows_kept":31,"cut":false,"hidden":false,"merged":[[0,0,0,6]]}
["NORMAL AMERICA, INC."]
["Year","Dec. 31 stock price","Dec. 15 dividend per share",null,null,null,"S&P 500 return"]
[1986,33]
[1987,{"$f":"30+11/16","v":30.6875},2.5,{"$f":"LN((B4+C4)/B3)","v":5.6657375356772999E-3},{"$f":"getformula(D4)","v":"<--  =LN((B4+C4)/B3)"},1987,4.7E-2]
[1988,35.380000000000003,2.5,{"$f":"LN((B5+C5)/B4)","v":0.2105678627631383},null,1988,0.16200000000000001]
[1989,42.25,3,0.24605598141429583,null,1989,0.314]
[1990,34.375,3,-0.12260232209233239,null,1990,-3.3000000000000002E-2]
[1991,36.25,1.6,9.6301423896722443E-2,null,1991,0.30199999999999999]
[1992,32.25,1.4,-7.4426325209946978E-2,null,1992,7.3999999999999996E-2]
[1993,43,0.8,0.30611577414061891,null,1993,9.9000000000000005E-2]
[1994,42.125,0.8,-1.7457089100232873E-3,null,1994,1.2E-2]
[1995,52.875,1.1000000000000001,0.24787958772834529,null,1995,0.374]
[1996,55.75,1.6,8.124220620893767E-2,null,1996,0.22900000000000001]
[]
[null,null,"Beta",{"$f":"SLOPE(D4:D13,G4:G13)","v":0.69679101657092901},{"$f":"getformula(D15)","v":"<--  =SLOPE(D4:D13,G4:G13)"}]
[null,null,"Significant?",{"$f":"tslope(D4:D13,G4:G13)","v":2.4878730184979174},{"$f":"getformula(D16)","v":"<--  =tslope(D4:D13,G4:G13)"}]
[]
["Cost of equity"]
["Risk-free rate, rf",5.5E-2]
["Expected market return, E(rM)",0.13]
["Corporate tax rate, TC",0.35]
[]
["rE, classic CAPM",{"$f":"B19+D15*(B20-B19)","v":0.10725932624281968},{"$f":"getformula(B23)","v":"<--  =B19+D15*(B20-B19)"}]
[]
[]
["Part c"]
["Cost of debt, rD",0.08]
["Equity weight, E/(E+D)",{"$f":"1/3","v":0.33333333333333331}]
["Debt weight, D/(E+D)",{"$f":"1-B28","v":0.66666666666666674}]
["WACC"]
["Classic CAPM",{"$f":"B29*B27*(1-B21)+B28*B23","v":7.0419775414273231E-2},{"$f":"getformula(B31)","v":"<--  =B29*B27*(1-B21)+B28*B23"}]

{"sheet":"Exercise 12 template","index":11,"n_rows":82,"n_cols":4,"rows_kept":82,"cut":false,"hidden":false,"merged":[[0,0,0,3]]}
["RETURN DATA FOR VANGUARD INDEX 500 FUND\nDecember 2000 - June 2007"]
["Date","Vanguard\nIndex 500","Return"]
["2000-12-01",110.75]
["2001-01-02",114.68,{"$f":"LN(B4/B3)","v":3.4870232089924166E-2},{"$f":"getformula(C4)","v":"<--  =LN(B4/B3)"}]
["2001-02-01",104.2,-9.5833511695902623E-2,{"$f":"getformula(C5)","v":"<--  =LN(B5/B4)"}]
["2001-03-01",97.32,-6.8307611403944771E-2,{"$f":"getformula(C6)","v":"<--  =LN(B6/B5)"}]
["2001-04-02",104.88,7.4812321540122628E-2,{"$f":"getformula(C7)","v":"<--  =LN(B7/B6)"}]
["2001-05-01",105.57,6.5574005461590396E-3]
["2001-06-01",102.98,-2.4839445383608074E-2]
["2001-07-02",101.96,-9.9542151100807559E-3]
["2001-08-01",95.57,-6.4721616229826404E-2]
["2001-09-04",87.83,-8.4455835349062217E-2]
["2001-10-01",89.51,1.8947222953821671E-2]
["2001-11-01",96.36,7.3740826863822898E-2]
["2001-12-03",96.84,4.968954323187144E-3]
["2002-01-02",95.42,-1.4771931981896795E-2]
["2002-02-01",93.58,-1.9471514649890758E-2]
["2002-03-01",97.09,3.6821697943983413E-2]
["2002-04-01",91.19,-6.2693141436479485E-2]
["2002-05-01",90.51,-7.4849001064946899E-3]
["2002-06-03",83.77,-7.7385393673361363E-2]
["2002-07-01",77.3,-8.0380992572341894E-2]
["2002-08-01",77.819999999999993,6.7045119630312379E-3]
["2002-09-03",69.349999999999994,-0.11523232079556688]
["2002-10-01",75.44,8.4171491564361448E-2]
["2002-11-01",80.09,5.9813364010388982E-2]
["2002-12-02",75.17,-6.3398787146213387E-2]
["2003-01-02",73.19,-2.6693415576241775E-2]
["2003-02-03",72.09,-1.5143461196648443E-2]
["2003-03-03",72.78,9.5258537675264043E-3]
["2003-04-01",78.8,7.9471804679819569E-2]
["2003-05-01",82.94,5.1204457954401907E-2]
["2003-06-02",83.99,1.2580289319300395E-2]
["2003-07-01",85.47,1.7467693040390999E-2]
["2003-08-01",87.12,{"$f":"LN(B35/B34)","v":1.9121041446778377E-2}]
["2003-09-02",86.19,-1.0732316965397278E-2]
["2003-10-01",91.05,5.4854644514309076E-2]
["2003-11-03",91.85,8.7480054875176214E-3]
["2003-12-01",96.66,5.1042854755803287E-2]
["2004-01-02",98.42,1.8044369020894194E-2]
["2004-02-02",99.78,1.3723726995059245E-2]
["2004-03-01",98.27,-1.5248970058555855E-2]
["2004-04-01",96.73,-1.5795200179879766E-2]
["2004-05-03",98.05,1.3553960447899503E-2]
["2004-06-01",99.95,1.9192508304053887E-2]
["2004-07-01",96.64,-3.3677326759904307E-2]
["2004-08-02",97.03,4.0274748507033841E-3]
["2004-09-01",98.07,1.0661300367020714E-2]
["2004-10-01",99.57,1.5179404995764015E-2]
["2004-11-01",103.59,3.9579885670017877E-2]
["2004-12-01",107.12,3.3508901312906521E-2]
["2005-01-03",104.51,-2.4666940776504765E-2]
["2005-02-01",106.69,2.0644672596957535E-2]
["2005-03-01",104.82,-1.7682839829349692E-2]
["2005-04-01",102.83,-1.9167454132920925E-2]
["2005-05-02",106.09,3.1210651230080877E-2]
["2005-06-01",106.24,1.4128952569434159E-3]
["2005-07-01",110.19,3.6505462771561942E-2]
["2005-08-01",109.18,-9.2082521460740592E-3]
["2005-09-01",110.05,7.9369116188589533E-3]
["2005-10-03",108.22,-1.6768615757881473E-2]
["2005-11-01",112.3,3.7007669529808281E-2]
["2005-12-01",112.33,2.6710590908047172E-4]
["2006-01-03",115.31,2.6183186129547051E-2]
["2006-02-01",115.63,2.7712843578455693E-3]
["2006-03-01",117.06,1.2291185722204589E-2]
["2006-04-03",118.63,{"$f":"LN(B67/B66)","v":1.3322781809937021E-2}]
["2006-05-01",115.21,-2.9252855623049721E-2]
["2006-06-01",115.37,1.3878048892362099E-3]
["2006-07-03",116.07,6.0491024413327739E-3]
["2006-08-01",118.82,2.3416285547064063E-2]
["2006-09-01",121.88,2.5427211189912972E-2]
["2006-10-02",125.84,3.1974304632514106E-2]
["2006-11-01",128.22999999999999,1.8814267721001861E-2]
["2006-12-01",130.02000000000001,1.3862758305298437E-2]
["2007-01-03",131.97999999999999,1.49621111789527E-2]
["2007-02-01",129.38999999999999,-1.9819296627414103E-2]
["2007-03-01",130.84,1.1144103342321882E-2]
["2007-04-02",136.62,4.3228146633520176E-2]
["2007-05-01",141.38,3.4247951439104578E-2]
["2007-06-01",138.43,-2.1086518043164283E-2]
["2007-07-02",140.44,1.4415568600587911E-2]

{"sheet":"Exercise 12","index":12,"n_rows":82,"n_cols":8,"rows_kept":82,"cut":false,"hidden":false,"merged":[[0,0,0,3]]}
["RETURN DATA FOR VANGUARD INDEX 500 FUND\nNovember 2000 - June 2007"]
["Date","Vanguard\nIndex 500","Return"]
["2000-12-01",110.75,null,null,null,"E(rM)?"]
["2001-01-02",114.68,{"$f":"LN(B4/B3)","v":3.4870232089924166E-2},{"$f":"getformula(C4)","v":"<--  =LN(B4/B3)"},null,"Using all the data",{"$f":"AVERAGE(C4:C82)*12","v":3.6076700107592882E-2},{"$f":"getformula(G4)","v":"<--  =AVERAGE(C4:C82)*12"}]
["2001-02-01",104.2,{"$f":"LN(B5/B4)","v":-9.5833511695902623E-2},{"$f":"getformula(C5)","v":"<--  =LN(B5/B4)"},null,"Using last two years",{"$f":"12*AVERAGE(C59:C82)","v":0.12128710140027296},{"$f":"getformula(G5)","v":"<--  =12*AVERAGE(C59:C82)"}]
["2001-03-01",97.32,-6.8307611403944771E-2,{"$f":"getformula(C6)","v":"<--  =LN(B6/B5)"}]
["2001-04-02",104.88,7.4812321540122628E-2,{"$f":"getformula(C7)","v":"<--  =LN(B7/B6)"},null,"Just for kicks:  The standard deviation of the returns"]
["2001-05-01",105.57,6.5574005461590396E-3,null,null,"Using all the data",{"$f":"_xlfn.STDEV.S(C4:C82)*SQRT(12)","v":0.13673062224130283},{"$f":"getformula(G8)","v":"<--  =STDEV.S(C4:C82)*SQRT(12)"}]
["2001-06-01",102.98,-2.4839445383608074E-2,null,null,"Using last two years",{"$f":"SQRT(12)*_xlfn.STDEV.S(C59:C82)","v":6.6441948683360064E-2},{"$f":"getformula(G9)","v":"<--  =SQRT(12)*STDEV.S(C59:C82)"}]
["2001-07-02",101.96,-9.9542151100807559E-3]
["2001-08-01",95.57,-6.4721616229826404E-2]
["2001-09-04",87.83,-8.4455835349062217E-2]
["2001-10-01",89.51,1.8947222953821671E-2]
["2001-11-01",96.36,7.3740826863822898E-2]
["2001-12-03",96.84,4.968954323187144E-3]
["2002-01-02",95.42,-1.4771931981896795E-2]
["2002-02-01",93.58,-1.9471514649890758E-2]
["2002-03-01",97.09,3.6821697943983413E-2]
["2002-04-01",91.19,-6.2693141436479485E-2]
["2002-05-01",90.51,-7.4849001064946899E-3]
["2002-06-03",83.77,-7.7385393673361363E-2]
["2002-07-01",77.3,-8.0380992572341894E-2]
["2002-08-01",77.819999999999993,6.7045119630312379E-3]
["2002-09-03",69.349999999999994,-0.11523232079556688]
["2002-10-01",75.44,8.4171491564361448E-2]
["2002-11-01",80.09,5.9813364010388982E-2]
["2002-12-02",75.17,-6.3398787146213387E-2]
["2003-01-02",73.19,-2.6693415576241775E-2]
["2003-02-03",72.09,-1.5143461196648443E-2]
["2003-03-03",72.78,9.5258537675264043E-3]
["2003-04-01",78.8,7.9471804679819569E-2]
["2003-05-01",82.94,5.1204457954401907E-2]
["2003-06-02",83.99,1.2580289319300395E-2]
["2003-07-01",85.47,1.7467693040390999E-2]
["2003-08-01",87.12,1.9121041446778377E-2]
["2003-09-02",86.19,-1.0732316965397278E-2]
["2003-10-01",91.05,5.4854644514309076E-2]
["2003-11-03",91.85,8.7480054875176214E-3]
["2003-12-01",96.66,5.1042854755803287E-2]
["2004-01-02",98.42,1.8044369020894194E-2]
["2004-02-02",99.78,1.3723726995059245E-2]
["2004-03-01",98.27,-1.5248970058555855E-2]
["2004-04-01",96.73,-1.5795200179879766E-2]
["2004-05-03",98.05,1.3553960447899503E-2]
["2004-06-01",99.95,1.9192508304053887E-2]
["2004-07-01",96.64,-3.3677326759904307E-2]
["2004-08-02",97.03,4.0274748507033841E-3]
["2004-09-01",98.07,1.0661300367020714E-2]
["2004-10-01",99.57,1.5179404995764015E-2]
["2004-11-01",103.59,3.9579885670017877E-2]
["2004-12-01",107.12,3.3508901312906521E-2]
["2005-01-03",104.51,-2.4666940776504765E-2]
["2005-02-01",106.69,2.0644672596957535E-2]
["2005-03-01",104.82,-1.7682839829349692E-2]
["2005-04-01",102.83,-1.9167454132920925E-2]
["2005-05-02",106.09,3.1210651230080877E-2]
["2005-06-01",106.24,1.4128952569434159E-3]
["2005-07-01",110.19,3.6505462771561942E-2]
["2005-08-01",109.18,-9.2082521460740592E-3]
["2005-09-01",110.05,7.9369116188589533E-3]
["2005-10-03",108.22,-1.6768615757881473E-2]
["2005-11-01",112.3,3.7007669529808281E-2]
["2005-12-01",112.33,2.6710590908047172E-4]
["2006-01-03",115.31,2.6183186129547051E-2]
["2006-02-01",115.63,2.7712843578455693E-3]
["2006-03-01",117.06,1.2291185722204589E-2]
["2006-04-03",118.63,1.3322781809937021E-2]
["2006-05-01",115.21,-2.9252855623049721E-2]
["2006-06-01",115.37,{"$f":"LN(B69/B68)","v":1.3878048892362099E-3}]
["2006-07-03",116.07,6.0491024413327739E-3]
["2006-08-01",118.82,2.3416285547064063E-2]
["2006-09-01",121.88,2.5427211189912972E-2]
["2006-10-02",125.84,3.1974304632514106E-2]
["2006-11-01",128.22999999999999,1.8814267721001861E-2]
["2006-12-01",130.02000000000001,1.3862758305298437E-2]
["2007-01-03",131.97999999999999,1.49621111789527E-2]
["2007-02-01",129.38999999999999,-1.9819296627414103E-2]
["2007-03-01",130.84,1.1144103342321882E-2]
["2007-04-02",136.62,4.3228146633520176E-2]
["2007-05-01",141.38,3.4247951439104578E-2]
["2007-06-01",138.43,-2.1086518043164283E-2]
["2007-07-02",140.44,1.4415568600587911E-2]

{"sheet":"Exercise 14 template","index":13,"n_rows":52,"n_cols":7,"rows_kept":52,"cut":false,"hidden":false,"merged":[[0,0,0,6],[2,4,2,6]]}
["COMPUTING THE GORDON DIVIDEND MODEL rE FOR INTEL"]
["S0, current stock price",24.23]
["Current dividend, Div0",{"$f":"B52","v":0.22500000000000001},null,null,"Computing the growth rates"]
["Cost of equity rE",null,null,null,"Quarterly"]
["10-year",{"$f":"B3*(1+F8)/B2+F8","v":0.26111245581146592},{"$f":"getformula(B5)","v":"<--  =B3*(1+F8)/B2+F8"},null,"10-year",{"$f":"(B52/B12)^(1/(COUNT(B12:B52)-1))-1","v":6.2377374814064801E-2},{"$f":"getformula(F5)","v":"<--  =(B52/B12)^(1/(COUNT(B12:B52)-1))-1"}]
["5-year",{"$f":"B3*(1+F9)/B2+F9","v":0.11136871391481692},{"$f":"getformula(B6)","v":"<--  =B3*(1+F9)/B2+F9"},null,"5-year",{"$f":"(B52/B33)^(1/(COUNT(B33:B52)-1))-1","v":2.528587137759164E-2},{"$f":"getformula(F6)","v":"<--  =(B52/B33)^(1/(COUNT(B33:B52)-1))-1"}]
[null,null,null,null,"Annualized"]
[null,null,null,null,"10-year",{"$f":"F5*4","v":0.2495094992562592},{"$f":"getformula(F8)","v":"<--  =F5*4"}]
[null,null,null,null,"5-year",{"$f":"F6*4","v":0.10114348551036656},{"$f":"getformula(F9)","v":"<--  =F6*4"}]
[]
["Date","Dividend per share"]
["2003-05-05",0.02]
["2003-08-05",0.02,{"$f":"LN(B13/B12)","v":0},{"$f":"getformula(C13)","v":"<--  =LN(B13/B12)"}]
["2003-11-05",0.02,{"$f":"LN(B14/B13)","v":0}]
["2004-02-04",0.04,0.69314718055994529]
["2004-05-05",0.04,0]
["2004-08-04",0.04,0]
["2004-11-03",0.04,0]
["2005-02-03",0.08,0.69314718055994529]
["2005-05-04",0.08,0]
["2005-08-03",0.08,0]
["2005-11-03",0.08,0]
["2006-02-03",0.1,0.22314355131420976]
["2006-05-03",0.1,0]
["2006-08-03",0.1,0]
["2006-11-03",0.1,0]
["2007-02-05",0.113,0.12221763272424911]
["2007-05-03",0.113,0]
["2007-08-03",0.113,0]
["2007-11-05",0.113,0]
["2008-02-05",0.128,0.12464244520727659]
["2008-05-05",0.14000000000000001,8.9612158689687138E-2]
["2008-08-05",0.14000000000000001,0]
["2008-11-05",0.14000000000000001,0]
["2009-02-04",0.14000000000000001,0]
["2009-05-05",0.14000000000000001,0]
["2009-08-05",0.14000000000000001,0]
["2009-11-04",0.14000000000000001,0]
["2010-02-03",0.158,0.12095261041766249]
["2010-05-05",0.158,0]
["2010-08-04",0.158,0]
["2010-11-03",0.158,0]
["2011-02-03",0.18099999999999999,0.13590199823885885]
["2011-05-04",0.18099999999999999,0]
["2011-08-03",0.21,0.14861049945164287]
["2011-11-03",0.21,0]
["2012-02-03",0.21,0]
["2012-05-03",0.21,0]
["2012-08-03",0.22500000000000001,6.8992871486951421E-2]
["2012-11-05",0.22500000000000001,0]
["2013-02-05",0.22500000000000001,0]
["2013-05-03",0.22500000000000001,0]

{"sheet":"Exercise 14 answer","index":14,"n_rows":52,"n_cols":7,"rows_kept":52,"cut":false,"hidden":false,"merged":[[0,0,0,6],[2,4,2,6]]}
["COMPUTING THE GORDON DIVIDEND MODEL rE FOR INTEL"]
["S0, current stock price",24.23]
["Current dividend, Div0",null,{"$f":"getformula(B3)","v":"<--  "},null,"Computing the growth rates"]
["Cost of equity rE",null,null,null,"Quarterly"]
["10-year",null,{"$f":"getformula(B5)","v":"<--  "},null,"10-year",null,{"$f":"getformula(F5)","v":"<--  "}]
["5-year",null,{"$f":"getformula(B6)","v":"<--  "},null,"5-year",null,{"$f":"getformula(F6)","v":"<--  "}]
[null,null,null,null,"Annualized"]
[null,null,null,null,"10-year",null,{"$f":"getformula(F8)","v":"<--  "}]
[null,null,null,null,"5-year",null,{"$f":"getformula(F9)","v":"<--  "}]
[]
["Date","Dividend per share"]
["2003-05-05",0.02]
["2003-08-05",0.02]
["2003-11-05",0.02]
["2004-02-04",0.04]
["2004-05-05",0.04]
["2004-08-04",0.04]
["2004-11-03",0.04]
["2005-02-03",0.08]
["2005-05-04",0.08]
["2005-08-03",0.08]
["2005-11-03",0.08]
["2006-02-03",0.1]
["2006-05-03",0.1]
["2006-08-03",0.1]
["2006-11-03",0.1]
["2007-02-05",0.113]
["2007-05-03",0.113]
["2007-08-03",0.113]
["2007-11-05",0.113]
["2008-02-05",0.128]
["2008-05-05",0.14000000000000001]
["2008-08-05",0.14000000000000001]
["2008-11-05",0.14000000000000001]
["2009-02-04",0.14000000000000001]
["2009-05-05",0.14000000000000001]
["2009-08-05",0.14000000000000001]
["2009-11-04",0.14000000000000001]
["2010-02-03",0.158]
["2010-05-05",0.158]
["2010-08-04",0.158]
["2010-11-03",0.158]
["2011-02-03",0.18099999999999999]
["2011-05-04",0.18099999999999999]
["2011-08-03",0.21]
["2011-11-03",0.21]
["2012-02-03",0.21]
["2012-05-03",0.21]
["2012-08-03",0.22500000000000001]
["2012-11-05",0.22500000000000001]
["2013-02-05",0.22500000000000001]
["2013-05-03",0.22500000000000001]

{"sheet":"Sheet1","index":15,"n_rows":12,"n_cols":3,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
[100]
[105,{"$f":"(A3-A2)/A2","v":0.05},{"$f":"LN(A3/A2)","v":4.8790164169432049E-2}]
[102,{"$f":"(A4-A3)/A3","v":-2.8571428571428571E-2},{"$f":"LN(A4/A3)","v":-2.8987536873252298E-2}]
[95,-6.8627450980392163E-2,-7.1095921683730218E-2]
[100,5.2631578947368418E-2,5.1293294387550481E-2]
[104,0.04,3.9220713153281329E-2]
[105,9.6153846153846159E-3,9.5694510161506725E-3]
[90,-0.14285714285714285,-0.15415067982725836]
[100,0.1111111111111111,0.10536051565782635]
[]
[null,{"$f":"SUM(B3:B10)","v":2.3302052264900563E-2},{"$f":"SUM(C3:C10)","v":0}]