{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["2YrValue"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":65,"comments":1,"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":"2YrValue","index":0,"n_rows":96,"n_cols":10,"rows_kept":96,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,"_","___",null,"___"]
["Inputs",0,1,2,3,4,null,"Click here"]
["Horizon year ",2,null,null,null,null,null,1]
["Book value per share: b0",10,null,null,null,null,null,{"$f":"H13+1","v":2}]
["Cost of equity or discount rate: re",8.0000273417028089E-2,null,null,null,null,null,{"$f":"H14+1","v":3}]
["Perpetual growth in residual earnings: g",2.2093150312694078E-2,null,null,null,null,null,{"$f":"H15+1","v":4}]
["Earnings per share: et","NA",{"$f":"B14*14%","v":1.4000000000000001},{"$f":"C17*(1+7%)","v":1.4980000000000002},null,null,null,{"$f":"H16+1","v":5}]
["Dividend payout as % of earnings [payout]","NA",0.6,{"$f":"C18","v":0.6},{"$f":"D18","v":0.6},{"$f":"E18","v":0.6},null,{"$f":"H17+1","v":6}]
[]
["Financial statements",{"$f":"B12","v":0},{"$f":"C12","v":1},{"$f":"D12","v":2},{"$f":"E12","v":3},{"$f":"F12","v":4}]
["Earnings per share: et",{"$f":"B17","v":"NA"},{"$f":"C17","v":1.4000000000000001},{"$f":"D17","v":1.4980000000000002},{"$f":"E25+E24","v":1.5603673458265397},{"$f":"F25+F24","v":1.6250492838134529},null,{"$f":"H18+1","v":7},null,{"$f":"J24+1","v":15}]
["Dividends per share: dt",{"$f":"B18","v":"NA"},{"$f":"C21*C18","v":0.84000000000000008},{"$f":"D21*D18","v":0.89880000000000015},{"$f":"E21*E18","v":0.93622040749592372},{"$f":"F21*F18","v":0.97502957028807169},null,{"$f":"H21+1","v":8},null,{"$f":"J21+1","v":16}]
["Ending book value per share: bt",{"$f":"B14","v":10},{"$f":"B23+C21-C22","v":10.56},{"$f":"C23+D21-D22","v":11.1592},{"$f":"D23+E21-E22","v":11.783346938330617},{"$f":"E23+F21-F22","v":12.433366651855998},null,{"$f":"H22+1","v":9},{"$f":"H23+1","v":10},{"$f":"J22+1","v":17}]
["Charge for equity: cost of equity*previous ending book value = re * bt-1",null,{"$f":"$B$15*B23","v":0.80000273417028089},{"$f":"$B$15*C23","v":0.84480288728381669},{"$f":"$B$15*D23","v":0.89273905111529983},{"$f":"$B$15*E23","v":0.94267097683415013},null,{"$f":"I23+1","v":11},null,{"$f":"J25+1","v":14}]
["Residual earnings per share: rest = et -  re * bt-1","NA",{"$f":"C21-($B$15*B23)","v":0.59999726582971924},{"$f":"D21-($B$15*C23)","v":0.65319711271618353},{"$f":"D25*(1+$B$16)","v":0.66762829471123986},{"$f":"E25*(1+$B$16)","v":0.6823783069793028},null,{"$f":"H24+1","v":12},null,{"$f":"H25+1","v":13}]
["Growth rates and returns",{"$f":"B12","v":0},{"$f":"C12","v":1},{"$f":"D12","v":2},{"$f":"E12","v":3},{"$f":"F12","v":4}]
["Earnings growth rate",null,null,{"$f":"D21/C21-1","v":7.0000000000000062E-2},4.163374220730276E-2,4.1453019482826203E-2,null,{"$f":"J23+1","v":18}]
["Dividends growth rate",null,null,7.0000000000000062E-2,4.1633742207302538E-2,4.1453019482826203E-2,null,{"$f":"H27+1","v":19}]
["Book value growth rate",null,{"$f":"C23/B23-1","v":5.600000000000005E-2},5.674242424242415E-2,5.5931154413453976E-2,5.5164268431314722E-2,null,{"$f":"H28+1","v":20}]
["Residual earnings growth rate",null,null,{"$f":"IF(AND(C25=0,D25=0),0,D25/C25-1)","v":8.8666815527727039E-2},{"$f":"IF(AND(D25=0,E25=0),0,E25/D25-1)","v":2.2093150312693988E-2},{"$f":"IF(AND(E25=0,F25=0),0,F25/E25-1)","v":2.2093150312693988E-2},null,{"$f":"H29+1","v":21}]
["Forward return on equity = Forthcoming eps/current bps [ROE]","NA",{"$f":"C21/B23","v":0.14000000000000001},{"$f":"D21/C23","v":0.14185606060606062},{"$f":"E21/D23","v":0.139827886033635},{"$f":"F21/E23","v":0.13791067107828692},null,{"$f":"H30+1","v":22}]
["Intermediate computations: You may skip these"]
["Terminal value computation",{"$f":"B12","v":0},{"$f":"C12","v":1},{"$f":"D12","v":2},{"$f":"E12","v":3},{"$f":"F12","v":4}]
["Book value of equity at the end of the year",null,null,{"$f":"D23","v":11.1592},null,null,null,{"$f":"H31+1","v":23}]
["+ PV of residual earnings at the end of the year",null,null,{"$f":"E25/(B15-F30)","v":11.529294824547598},null,null,null,{"$f":"H34+1","v":24}]
["= Terminal value per share",null,null,{"$f":"SUM(D34:D35)","v":22.688494824547597},null,null,null,{"$f":"H35+1","v":25}]
["Alternate computation of terminal value: Theoretical, skip"]
["DPS for perpetual growth = EPS - perpetual growth rate in bt * bt",null,null,null,{"$f":"E21-B16*D23","v":1.313825462857124},null,null,{"$f":"H36+1","v":26}]
["Terminal value = PV of future DPS growing at the perpetual growth rate = dt/(re- g) ",null,null,{"$f":"E38/(B15-B16)","v":22.688494824547618},null,null,null,{"$f":"H38+1","v":27}]
["Capitalized change in dividends: Change in dividends/re = (dt - dt-1)/re",null,0,{"$f":"(D22-C22)/$B$15","v":0.73499748798964071},null,null,null,{"$f":"H39+1","v":28}]
["End of intermediate computation"]
[]
[]
["Valuation",{"$f":"B12","v":0},{"$f":"C12","v":1}]
["Baseline: Discounted dividends Model",null,"% of Value"]
["Anchor: None",0,{"$f":"B46/$B$49","v":0},null,null,null,null,{"$f":"H40+1","v":29}]
["+ Until horizon: PV of dividends until the horizon",{"$f":"NPV(B15,C22:D22)","v":1.548353322395811},{"$f":"B47/$B$49","v":7.3730861798651959E-2},null,null,null,null,{"$f":"H46+1","v":30}]
["+ Beyond the horizon: PV of terminal value",{"$f":"D36/(1+B15)^B13","v":19.451717538353574},{"$f":"B48/$B$49","v":0.92626913820134804},null,null,null,null,{"$f":"H47+1","v":31}]
["= Total equity value",{"$f":"SUM(B46:B48)","v":21.000070860749386},{"$f":"SUM(C47:C48)","v":1},null,null,null,null,{"$f":"H48+1","v":32}]
["Connecting to valuation anchors",{"$f":"B12","v":0},{"$f":"C12","v":1}]
["I: Dividend yield: Dividend growth model"]
["Anchor: Capitalized forthcoming dividends",{"$f":"C22/B15","v":10.499964114137711},{"$f":"B52/$B$55","v":0.49999660400017437},null,null,null,null,{"$f":"H49+1","v":33}]
["+ Until horizon: PV of capitalized change in dividends until the horizon",{"$f":"NPV(B15,C40:D40)","v":0.63014156021956347},{"$f":"B53/$B$55","v":3.0006639710789859E-2},null,null,null,null,{"$f":"H52+1","v":34}]
["+ Beyond the horizon: PV of capitalized change in dividends beyond the horizon",{"$f":"B49-SUM(B52:B53)","v":9.8699651863921112},{"$f":"B54/$B$55","v":0.46999675628903581},null,null,null,null,{"$f":"H53+1","v":35}]
["= Total equity value",{"$f":"SUM(B52:B54)","v":21.000070860749386},{"$f":"SUM(C52:C54)","v":1},null,null,null,null,{"$f":"H54+1","v":36}]
["II: Price to book: Book value growth model [RIV]",null,"% of Value"]
["Anchor: Current book value",{"$f":"B14","v":10},{"$f":"B57/$B$61","v":0.47618886937618404},null,null,null,null,{"$f":"H55+1","v":37}]
["+ Until horizon: PV of residual change in book value for the first 2 years",{"$f":"NPV(B15,C25:D25)","v":1.1155638417619183},{"$f":"B58/$B$61","v":5.3121908452556016E-2},null,null,null,null,{"$f":"H57+1","v":38}]
["[Note: Residual change in book value = residual earnings]"]
["+ Beyond the horizon: PV of residual change in book value beyond 2 years",{"$f":"D35/(1+B15)^B13","v":9.8845070189874704},{"$f":"B60/$B$61","v":0.47068922217125991},null,null,null,null,{"$f":"H58+1","v":39}]
["= Total equity value",{"$f":"B57+B58+B60","v":21.000070860749389},{"$f":"SUM(C57:C60)","v":1},null,null,null,null,{"$f":"H60+1","v":40}]
["Intermediate computations: You may skip these",{"$f":"B12","v":0},{"$f":"C12","v":1},{"$f":"D12","v":2},{"$f":"E12","v":3},{"$f":"F12","v":4}]
["Change in earnings [et - et-1]",null,null,{"$f":"D21-C21","v":9.8000000000000087E-2},{"$f":"E21-D21","v":6.2367345826539466E-2},{"$f":"F21-E21","v":6.468193798691324E-2},null,{"$f":"H61+1","v":41}]
["Reinvestment at the end of the previous year: et-1 - dt-1",null,{"$f":"C21-C22","v":0.56000000000000005},{"$f":"D21-D22","v":0.59920000000000007},{"$f":"E21-E22","v":0.62414693833061596},{"$f":"F21-F22","v":0.65001971352538124},null,{"$f":"H63+1","v":42}]
["Change in earnings if reinvestment yields cost of equity [=re*(et-1 - dt-1)]",null,null,{"$f":"C64*$B$15","v":4.4800153113535732E-2},{"$f":"D64*$B$15","v":4.7936163831483233E-2},{"$f":"E64*$B$15","v":4.9931925718850245E-2},null,{"$f":"H64+1","v":43}]
["Residual change in earnings [=(et - et-1) - re*(et-1 - dt-1)]"]
["[i.e., actual change in earnings - change in earnings from normal return on reinvestment]",null,null,{"$f":"D63-D65","v":5.3199846886464355E-2},{"$f":"E63-E65","v":1.4431181995056233E-2},{"$f":"F63-F65","v":1.4750012268062995E-2},null,{"$f":"H65+1","v":44}]
["Not used: Just for checking"]
["Change in residual earnings ['(et - re*bt-1)- (et-1 - re*bt-2)]","NA","NA",{"$f":"D25-C25","v":5.3199846886464286E-2},{"$f":"E25-D25","v":1.443118199505633E-2},{"$f":"F25-E25","v":1.475001226806294E-2},null,{"$f":"H67+1","v":45}]
["Check: Residual change in earnings = Change in residual earnings",null,null,{"$f":"D67-D69","v":6.9388939039072284E-17},{"$f":"E67-E69","v":-9.7144514654701197E-17},{"$f":"F67-F69","v":5.5511151231257827E-17},null,{"$f":"H69+1","v":46}]
["Growth rate of residual change in earnings",null,null,null,{"$f":"IF(AND(D69=0,E69=0),0,E69/D69-1)","v":-0.72873639982734462},{"$f":"IF(AND(E69=0,F69=0),0,F69/E69-1)","v":2.2093150312693099E-2},null,47]
["Value added each year: Capitalized forthcoming residual change in earnings",null,{"$f":"D67/$B$15","v":0.66499581331606739},{"$f":"E67/$B$15","v":0.18038915841985798},{"$f":"F67/$B$15","v":0.18437452321161005},null,null,48]
["PV of value added each year based on earnings forecasts until the horizon",{"$f":"NPV(B15,C72)","v":0.61573670829922822},null,null,null,null,null,49]
["Capitalized value of value added per year beyond the horizon",null,{"$f":"D72/(B15-B16)","v":3.1151462678406983},null,null,null,null,50]
["PV of value added each year based on earnings forecasts beyond the horizon",{"$f":"NPV(B15,C74)","v":2.8843939622206229},null,null,null,null,null,51]
["End of intermediate computation"]
["III. Price to forward earnings: Earning growth Model [AEG]",null,"% of Value"]
["Anchor: Capitalized value of forthcoming earnings for Y1",{"$f":"C21/B15","v":17.49994019022952},{"$f":"B78/$B$81","v":0.83332767333362456},null,null,null,null,{"$f":"H75+1","v":52}]
["+ Until horizon: PV of value based on earnings forecasts until the horizon",{"$f":"B73","v":0.61573670829922822},{"$f":"B79/$B$81","v":2.9320696695842297E-2},null,null,null,null,{"$f":"H78+1","v":53}]
["+ Beyond the horizon: PV of value based on earnings forecasts beyond the horizon",{"$f":"C74/(1+B15)","v":2.8843939622206229},{"$f":"B80/$B$81","v":0.13735162997053313},null,null,null,null,{"$f":"H79+1","v":54}]
["= Total Equity Value",{"$f":"SUM(B78:B80)","v":21.000070860749371},{"$f":"SUM(C78:C80)","v":1},null,null,null,null,{"$f":"H80+1","v":55}]
[]
["Valuation ratios",0]
["Dividend yield = Forthcoming dividends/Current stock price",{"$f":"C22/B49","v":3.9999865027599474E-2},null,null,null,null,null,{"$f":"H81+1","v":56}]
["P/B ratio = Current stock price/current book value",{"$f":"B61/B14","v":2.1000070860749389},null,null,null,null,null,{"$f":"H84+1","v":57}]
["Forward P/E ratio = Current stock price/Forthcoming earnings",{"$f":"B61/C17","v":15.00005061482099},null,null,null,null,null,{"$f":"H85+1","v":58}]
["Forward ROE = Forthcoming earnings/Current book value",{"$f":"C31","v":0.14000000000000001},null,null,null,null,null,{"$f":"H86+1","v":59}]
["Check: P/B - P/E * ROE = 0",{"$f":"B85-B86*B87","v":0},null,null,null,null,null,{"$f":"H87+1","v":60}]
[]
["Reverse engineering: Inferring cost of equity OR growth",{"$f":"B83","v":0}]
["Current stock price",{"$f":"C17*15","v":21.000000000000004},null,null,null,null,null,{"$f":"H88+1","v":61}]
["Difference = Current stock price - Computed present value",{"$f":"B91-B61","v":-7.0860749385559529E-5},null,null,null,null,null,{"$f":"H91+1","v":62}]
["Choose one of the following from the drop down box on the right and then CLICK INFER"]
["Infer cost of equity",null,2,null,null,null,null,{"$f":"H92+1","v":63}]
["Infer growth rate",null,null,null,null,null,null,{"$f":"H94+1","v":64}]
[{"$f":"IF(C94=1,\"Inferred cost of equity\", \"Inferred growth rate\")&\" that sets Price = Present value \"","v":"Inferred growth rate that sets Price = Present value "},{"$f":"IF(C94=1,B15,B16)","v":2.2093150312694078E-2},null,null,null,null,null,{"$f":"H95+1","v":65}]