{"text_format":"1","workbook":"xlsx","n_sheets":31,"sheets":["Page 14","Page 15","Page 16","Page 18, top","Page 18, middle","Page 19, middle","Page 19, bottom","Page 21, top","Page 21, bottom","Page22","Page23","Pages24-26","Page 26, bottom","Pages 27-28","Page 28, bottom","Page 29","Page 30","Page 31","Page 32","Page 34","Retirement picture","Page 37","Page 38","Page 39","Page 40","Page 41","Page 42","Page 43","Page 44, top","Page 44, bottom","Page 45"],"not_rendered":{"charts":5,"images":3,"macros":1,"external_links":0,"defined_names":73,"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":"Page 14","index":0,"n_rows":14,"n_cols":4,"rows_kept":14,"cut":false,"hidden":false,"merged":[[0,0,0,3]]}
["COMPUTING THE PRESENT VALUE"]
["Discount rate",0.1]
[]
["Year","Cash flow","Present\nvalue"]
[1,100,{"$f":"B5/(1+$B$2)^A5","v":90.909090909090907},{"$f":"getformula(C5)","v":"<--  =B5/(1+$B$2)^A5"}]
[2,100,{"$f":"B6/(1+$B$2)^A6","v":82.644628099173545},{"$f":"getformula(C6)","v":"<--  =B6/(1+$B$2)^A6"}]
[3,100,{"$f":"B7/(1+$B$2)^A7","v":75.131480090157751},{"$f":"getformula(C7)","v":"<--  =B7/(1+$B$2)^A7"}]
[4,100,{"$f":"B8/(1+$B$2)^A8","v":68.301345536507057},{"$f":"getformula(C8)","v":"<--  =B8/(1+$B$2)^A8"}]
[5,100,{"$f":"B9/(1+$B$2)^A9","v":62.092132305915499},{"$f":"getformula(C9)","v":"<--  =B9/(1+$B$2)^A9"}]
[]
["Net present value"]
[{"$f":"\"     Summing cells \"&CHAR(COLUMN(C5)+64)&ROW(C5)&\":\"&CHAR(COLUMN(C9)+64)&ROW(C9)","v":"     Summing cells C5:C9"},{"$f":"SUM(C5:C9)","v":379.07867694084473},{"$f":"getformula(B12)","v":"<--  =SUM(C5:C9)"}]
["     Using Excel's NPV function",{"$f":"NPV(B2,B5:B9)","v":379.07867694084473},{"$f":"getformula(B13)","v":"<--  =NPV(B2,B5:B9)"}]
["     Using Excel's PV function",{"$f":"PV(B2,5,-100)","v":379.07867694084507},{"$f":"getformula(B14)","v":"<--  =PV(B2,5,-100)"}]

{"sheet":"Page 15","index":1,"n_rows":13,"n_cols":4,"rows_kept":13,"cut":false,"hidden":false,"merged":[[0,0,0,3]]}
["COMPUTING THE PRESENT VALUE\nIn this example the cash flows are not equal\nEither discount each cash flow separately or use Excel's NPV function\nExcel's PV doesn't work for this case"]
["Discount rate",0.1]
[]
["Year","Cash\nflow","Present\nvalue","Present value\nof each cash flow"]
[1,100,{"$f":"B5/(1+$B$2)^A5","v":90.909090909090907},{"$f":"getformula(C5)","v":"<--  =B5/(1+$B$2)^A5"}]
[2,200,{"$f":"B6/(1+$B$2)^A6","v":165.28925619834709},{"$f":"getformula(C6)","v":"<--  =B6/(1+$B$2)^A6"}]
[3,300,{"$f":"B7/(1+$B$2)^A7","v":225.39444027047327},{"$f":"getformula(C7)","v":"<--  =B7/(1+$B$2)^A7"}]
[4,400,{"$f":"B8/(1+$B$2)^A8","v":273.20538214602823},{"$f":"getformula(C8)","v":"<--  =B8/(1+$B$2)^A8"}]
[5,500,{"$f":"B9/(1+$B$2)^A9","v":310.46066152957746},{"$f":"getformula(C9)","v":"<--  =B9/(1+$B$2)^A9"}]
[]
["Net present value"]
[{"$f":"\"     Summing cells \"&CHAR(COLUMN(C5)+64)&ROW(C5)&\":\"&CHAR(COLUMN(C9)+64)&ROW(C9)","v":"     Summing cells C5:C9"},{"$f":"SUM(C5:C9)","v":1065.258831053517},{"$f":"getformula(B12)","v":"<--  =SUM(C5:C9)"}]
["     Using Excel's NPV function",{"$f":"NPV(B2,B5:B9)","v":1065.2588310535168},{"$f":"getformula(B13)","v":"<--  =NPV(B2,B5:B9)"}]

{"sheet":"Page 16","index":2,"n_rows":14,"n_cols":4,"rows_kept":14,"cut":false,"hidden":false,"merged":[[0,0,0,3]]}
["COMPUTING THE NET PRESENT VALUE"]
["Discount rate",0.1]
[]
["Year","Cash flow","Present\nvalue"]
[0,-250,{"$f":"B5/(1+$B$2)^A5","v":-250},{"$f":"getformula(C5)","v":"<--  =B5/(1+$B$2)^A5"}]
[1,100,90.909090909090907,"<--  =B6/(1+$B$2)^A6"]
[2,100,82.644628099173545,"<--  =B7/(1+$B$2)^A7"]
[3,100,75.131480090157751,"<--  =B8/(1+$B$2)^A8"]
[4,100,68.301345536507057,"<--  =B9/(1+$B$2)^A9"]
[5,100,62.092132305915499,"<--  =B10/(1+$B$2)^A10"]
[]
["Net present value"]
[{"$f":"\"     Summing cells \"&CHAR(COLUMN(C5)+64)&ROW(C5)&\":\"&CHAR(COLUMN(C10)+64)&ROW(C10)","v":"     Summing cells C5:C10"},{"$f":"SUM(C5:C10)","v":129.07867694084476},{"$f":"getformula(B13)","v":"<--  =SUM(C5:C10)"}]
["     Using Excel's NPV function",{"$f":"B5+NPV(B2,B6:B10)","v":129.07867694084473},{"$f":"getformula(B14)","v":"<--  =B5+NPV(B2,B6:B10)"}]

{"sheet":"Page 18, top","index":3,"n_rows":16,"n_cols":3,"rows_kept":16,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["COMPUTING THE VALUE OF A FINITE ANNUITY"]
["Periodic payment, C",1000]
["Number of future periods paid, n",5]
["Discount rate, r",0.12]
["Present value of annuity"]
["     Using formula",{"$f":"B2*(1-1/(1+B4)^B3)/B4","v":3604.7762023450068},{"$f":"getformula(B6)","v":"<--  =B2*(1-1/(1+B4)^B3)/B4"}]
["     Using Excel's PV function",{"$f":"PV(B4,B3,-B2)","v":3604.7762023450064},{"$f":"getformula(B7)","v":"<--  =PV(B4,B3,-B2)"}]
[]
["Period","Annuity\npayment"]
[1,{"$f":"B2","v":1000},{"$f":"getformula(B10)","v":"<--  =B2"}]
[2,{"$f":"B10","v":1000}]
[3,{"$f":"B11","v":1000}]
[4,{"$f":"B12","v":1000}]
[5,{"$f":"B13","v":1000}]
[]
["Present value using Excel's NPV function",{"$f":"NPV(B4,B10:B14)","v":3604.7762023450041},{"$f":"getformula(B16)","v":"<--  =NPV(B4,B10:B14)"}]

{"sheet":"Page 18, middle","index":4,"n_rows":4,"n_cols":3,"rows_kept":4,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["COMPUTING THE VALUE OF AN INFINITE ANNUITY"]
["Periodic payment, C",1000]
["Discount rate, r",0.12]
["Present value of annuity",{"$f":"B2/B3","v":8333.3333333333339},{"$f":"getformula(B4)","v":"<--  =B2/B3"}]

{"sheet":"Page 19, middle","index":5,"n_rows":16,"n_cols":3,"rows_kept":16,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["COMPUTING THE VALUE OF A FINITE GROWING ANNUITY"]
["First payment, C",1000]
["Growth rate of payments, g",0.06]
["Number of future periods paid, n",5]
["Discount rate, r",0.12]
["Present value of annuity"]
["     Using formula",{"$f":"B2*(1-((1+B3)/(1+B5))^B4)/(B5-B3)","v":4010.914471003759},{"$f":"getformula(B7)","v":"<--  =B2*(1-((1+B3)/(1+B5))^B4)/(B5-B3)"}]
[]
["Period","Annuity\npayment"]
[1,{"$f":"B2","v":1000},{"$f":"getformula(B10)","v":"<--  =B2"}]
[2,{"$f":"$B$2*(1+$B$3)^(A11-1)","v":1060},{"$f":"getformula(B11)","v":"<--  =$B$2*(1+$B$3)^(A11-1)"}]
[3,{"$f":"$B$2*(1+$B$3)^(A12-1)","v":1123.6000000000001},{"$f":"getformula(B12)","v":"<--  =$B$2*(1+$B$3)^(A12-1)"}]
[4,{"$f":"$B$2*(1+$B$3)^(A13-1)","v":1191.0160000000003},{"$f":"getformula(B13)","v":"<--  =$B$2*(1+$B$3)^(A13-1)"}]
[5,{"$f":"$B$2*(1+$B$3)^(A14-1)","v":1262.4769600000004},{"$f":"getformula(B14)","v":"<--  =$B$2*(1+$B$3)^(A14-1)"}]
[]
["Present value using Excel's NPV function",{"$f":"NPV(B5,B10:B14)","v":4010.9144710037572},{"$f":"getformula(B16)","v":"<--  =NPV(B5,B10:B14)"}]

{"sheet":"Page 19, bottom","index":6,"n_rows":5,"n_cols":3,"rows_kept":5,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["COMPUTING THE VALUE OF AN INFINITE GROWING ANNUITY"]
["Periodic payment, C",1000,"<-- Starting at date 1"]
["Growth rate of payments, g",0.06]
["Discount rate, r",0.12]
["Present value of annuity",{"$f":"B2/(B4-B3)","v":16666.666666666668},{"$f":"getformula(B5)","v":"<--  =B2/(B4-B3)"}]

{"sheet":"Page 21, top","index":7,"n_rows":10,"n_cols":3,"rows_kept":10,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["INTERNAL RATE OF RETURN"]
["Year","Cash flow"]
[0,-800]
[1,200]
[2,250]
[3,300]
[4,350]
[5,400]
[]
["Internal rate of return",{"$f":"IRR(B3:B8)","v":0.22160309795512223},{"$f":"getformula(B10)","v":"<--  =IRR(B3:B8)"}]

{"sheet":"Page 21, bottom","index":8,"n_rows":12,"n_cols":3,"rows_kept":12,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["INTERNAL RATE OF RETURN"]
["Discount rate",0.12]
[]
["Year","Cash\n flow"]
[0,-800]
[1,200]
[2,250]
[3,300]
[4,350]
[5,400]
[]
["Net present value (NPV)",{"$f":"B5+NPV(B2,B6:B10)","v":240.80604203233725},{"$f":"getformula(B12)","v":"<--  =B5+NPV(B2,B6:B10)"}]

{"sheet":"Page22","index":9,"n_rows":12,"n_cols":3,"rows_kept":12,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["INTERNAL RATE OF RETURN"]
["Discount rate",0.22160309793523875]
[]
["Year","Cash flow"]
[0,-800]
[1,200]
[2,250]
[3,300]
[4,350]
[5,400]
[]
["Net present value (NPV)",{"$f":"B5+NPV(B2,B6:B10)","v":4.82358473163913E-8},{"$f":"getformula(B12)","v":"<--  =B5+NPV(B2,B6:B10)"}]

{"sheet":"Page23","index":10,"n_rows":20,"n_cols":6,"rows_kept":20,"cut":false,"hidden":false,"merged":[[0,0,0,2],[12,3,12,4]]}
["INTERNAL RATE OF RETURN"]
["Year","Cash flow"]
[0,-800]
[1,200]
[2,250]
[3,300]
[4,350]
[5,400]
[]
["Internal rate of return",{"$f":"IRR(B3:B8)","v":0.22160309795512223},{"$f":"getformula(B10)","v":"<--  =IRR(B3:B8)"}]
[]
["USING THE IRR IN A LOAN TABLE"]
[null,null,null,"Division of cash flow between investment income and return of principal"]
["Year","Investment at beginning of year","Cash flow\nat end of year","Income","Return of\nprincipal"]
[1,{"$f":"-B3","v":800},{"$f":"B4","v":200},{"$f":"$B$10*B15","v":177.28247836409778},{"$f":"C15-D15","v":22.717521635902216},{"$f":"getformula(E15)","v":"<--  =C15-D15"}]
[2,{"$f":"B15-E15","v":777.28247836409776},{"$f":"B5","v":250},{"$f":"$B$10*B16","v":172.24820519171934},{"$f":"C16-D16","v":77.751794808280664}]
[3,{"$f":"B16-E16","v":699.53068355581706},{"$f":"B6","v":300},{"$f":"$B$10*B17","v":155.01816659063334},{"$f":"C17-D17","v":144.98183340936666}]
[4,{"$f":"B17-E17","v":554.54885014645038},{"$f":"B7","v":350},{"$f":"$B$10*B18","v":122.88974315990424},{"$f":"C18-D18","v":227.11025684009576}]
[5,{"$f":"B18-E18","v":327.43859330635462},{"$f":"B8","v":400},{"$f":"$B$10*B19","v":72.561406666755531},{"$f":"C19-D19","v":327.43859333324446}]
[6,{"$f":"B19-E19","v":-2.6889836135524092E-8}]

{"sheet":"Pages24-26","index":11,"n_rows":24,"n_cols":6,"rows_kept":24,"cut":false,"hidden":false,"merged":[[0,0,0,5],[3,3,3,4],[14,0,14,3]]}
["USING A LOAN TABLE TO FIND THE IRR"]
["IRR?",0.15]
[]
[null,null,null,"Division of cash flow between investment income and return of principal"]
["Year","Principal\nat beginning of year","Cash flow\nat end of year","Income","Principal"]
[1,1000,300,{"$f":"$B$2*B6","v":150},{"$f":"C6-D6","v":150},{"$f":"getformula(E6)","v":"<--  =C6-D6"}]
[2,{"$f":"B6-E6","v":850},200,{"$f":"$B$2*B7","v":127.5},{"$f":"C7-D7","v":72.5}]
[3,{"$f":"B7-E7","v":777.5},150,{"$f":"$B$2*B8","v":116.625},{"$f":"C8-D8","v":33.375}]
[4,{"$f":"B8-E8","v":744.125},600,{"$f":"$B$2*B9","v":111.61874999999999},{"$f":"C9-D9","v":488.38125000000002}]
[5,{"$f":"B9-E9","v":255.74374999999998},900,{"$f":"$B$2*B10","v":38.361562499999998},{"$f":"C10-D10","v":861.63843750000001}]
[6,{"$f":"B10-E10","v":-605.89468750000003}]
[]
[]
[]
["Direct calculation of IRR"]
["Year","Cash flow"]
[0,-1000]
[1,{"$f":"C6","v":300}]
[2,{"$f":"C7","v":200}]
[3,{"$f":"C8","v":150}]
[4,{"$f":"C9","v":600}]
[5,{"$f":"C10","v":900}]
[]
["IRR",{"$f":"IRR(B17:B22)","v":0.24436074261538088},{"$f":"getformula(B24)","v":"<--  =IRR(B17:B22)"}]

{"sheet":"Page 26, bottom","index":12,"n_rows":5,"n_cols":3,"rows_kept":5,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["USING EXCEL'S RATE FUNCTION TO COMPUTE THE IRR"]
["Initial investment",1000]
["Periodic cash flow",100]
["Number of payments",30]
["IRR",{"$f":"RATE(B4,B3,-B2)","v":9.3073397717987436E-2},{"$f":"getformula(B5)","v":"<--  =RATE(B4,B3,-B2)"}]

{"sheet":"Pages 27-28","index":13,"n_rows":31,"n_cols":9,"rows_kept":31,"cut":false,"hidden":false,"merged":[[0,0,0,8],[20,6,22,8]]}
["MULTIPLE INTERNAL RATES OF RETURN"]
["Discount rate",0.06]
["NPV",{"$f":"NPV(B2,B7:B11)+B6","v":-3.9854362682000044},{"$f":"getformula(B3)","v":"<--  =NPV(B2,B7:B11)+B6"},null,null,null,"DATA TABLE"]
[null,null,null,null,null,null,"Discount\nrate","NPV"]
["Year","Cash flow",null,null,null,null,null,{"$f":"B3","v":-3.9854362682000044},{"$f":"\"Table header, \"&getformula(H5)","v":"Table header, <--  =B3"}]
[0,-145,null,null,null,null,0,-20]
[1,100,null,null,null,null,0.03,-10.507575424608092]
[2,100,null,null,null,null,0.06,-3.9854362682000044]
[3,100,null,null,null,null,0.09,0.24085647329212634]
[4,100,null,null,null,null,0.12,2.6925493400257778]
[5,-275,null,null,null,null,0.15,3.7742340642815009]
[null,null,null,null,null,null,0.18,3.8011460077677839]
[null,null,null,null,null,null,0.21,3.0197000689104811]
[null,null,null,null,null,null,0.24,1.6230390485557962]
[null,null,null,null,null,null,0.27,-0.237122901511583]
[null,null,null,null,null,null,0.3,-2.4414276595572915]
[null,null,null,null,null,null,0.33,-4.895886047904952]
[null,null,null,null,null,null,0.36,-7.5264699851816204]
[null,null,null,null,null,null,0.39,-10.274907341794687]
[]
[null,null,null,null,null,null,"Note:  For a discussion of how to create data tables in Excel, see Chapter 31. "]
[]
[]
[]
[]
[]
[]
[]
["Identifying the two IRRs"]
["First IRR",{"$f":"IRR(B6:B11,0)","v":8.7828273849489102E-2},{"$f":"getformula(B30)","v":"<--  =IRR(B6:B11,0)"}]
["Second IRR",{"$f":"IRR(B6:B11,0.3)","v":0.266495007167735},{"$f":"getformula(B31)","v":"<--  =IRR(B6:B11,0.3)"}]

{"sheet":"Page 28, bottom","index":14,"n_rows":15,"n_cols":7,"rows_kept":15,"cut":false,"hidden":false,"merged":[[0,0,0,10]]}
["BOND CASH FLOWS:  NPV CROSSES X-AXIS ONLY ONCE, SO THERE IS ONLY ONE IRR"]
["Year","Cash flow",null,null,"Data table:  Effect of"]
[0,-800,null,null,"discount rate on NPV"]
[1,100,null,null,null,{"$f":"NPV(E4,B4:B11)+B3","v":1000},{"$f":"getformula(F4)&\", table header\"","v":"<--  =NPV(E4,B4:B11)+B3, table header"}]
[2,100,null,null,0,1000]
[3,100,null,null,0.02,786.03851523955382]
[4,100,null,null,0.04,603.96469249702341]
[5,100,null,null,0.06,448.39175243878162]
[6,100,null,null,0.08,314.93277887450563]
[7,100,null,null,0.1,200]
[8,1100,null,null,0.12,100.64720466322774]
[null,null,null,null,0.14000000000000001,14.445444243074121]
["IRR",{"$f":"IRR(B3:B11)","v":0.14364208444928206},{"$f":"getformula(B13)","v":"<--  =IRR(B3:B11)"},null,0.16,-60.615453700742364]
[null,null,null,null,0.18,-126.20526056422]
[null,null,null,null,0.2,-183.71598031931103]

{"sheet":"Page 29","index":15,"n_rows":15,"n_cols":6,"rows_kept":15,"cut":false,"hidden":false,"merged":[[0,0,0,6]]}
["FLAT PAYMENT SCHEDULES"]
["Loan principal",10000]
["Interest rate",7.0000000000000007E-2]
["Loan term",6,"<-- Number of years over which loan is repaid"]
["Annual payment",{"$f":"PMT(B3,B4,-B2)","v":2097.9579975832812},{"$f":"getformula(B5)","v":"<--  =PMT(B3,B4,-B2)"}]
[]
[null,null,null,null,"Split payment into:"]
[null,"Year","Principal\nat beginning of year","Payment at\nend of year","Interest","Return of\nprincipal"]
[null,1,{"$f":"B2","v":10000},{"$f":"$B$5","v":2097.9579975832812},{"$f":"$B$3*C9","v":700.00000000000011},{"$f":"D9-E9","v":1397.9579975832812}]
[null,2,{"$f":"C9-F9","v":8602.0420024167179},2097.9579975832812,602.14294016917029,1495.8150574141109]
[null,3,7106.226945002607,2097.9579975832812,497.43588615018251,1600.5221114330986]
[null,4,5505.7048335695081,2097.9579975832812,385.39933834986562,1712.5586592334157]
[null,5,3793.1461743360924,2097.9579975832812,265.52023220352652,1832.4377653797546]
[null,6,1960.7084089563377,2097.9579975832812,137.24958862694365,1960.7084089563375]
[null,7,0]

{"sheet":"Page 30","index":16,"n_rows":15,"n_cols":6,"rows_kept":15,"cut":false,"hidden":false,"merged":[[0,0,0,6]]}
["FLAT PAYMENT SCHEDULES"]
["Loan principal",10000]
["Interest rate",7.0000000000000007E-2]
["Loan term",6,"<-- Number of years over which loan is repaid"]
["Annual payment",{"$f":"PMT(B3,B4,-B2)","v":2097.9579975832812},{"$f":"getformula(B5)","v":"<--  =PMT(B3,B4,-B2)"}]
[]
[null,null,null,null,"Split payment into:"]
[null,"Year","Principal\nat beginning of year","Payment at\nend of year","Interest","Return of\nprincipal"]
[null,1,{"$f":"B2","v":10000},{"$f":"$B$5","v":2097.9579975832812},{"$f":"$B$3*C9","v":700.00000000000011},{"$f":"D9-E9","v":1397.9579975832812}]
[null,2,{"$f":"C9-F9","v":8602.0420024167179},2097.9579975832812,602.14294016917029,1495.8150574141109]
[null,3,7106.226945002607,2097.9579975832812,497.43588615018251,1600.5221114330986]
[null,4,5505.7048335695081,2097.9579975832812,385.39933834986562,1712.5586592334157]
[null,5,3793.1461743360924,2097.9579975832812,265.52023220352652,1832.4377653797546]
[null,6,1960.7084089563377,2097.9579975832812,137.24958862694365,1960.7084089563375]
[null,7,0]

{"sheet":"Page 31","index":17,"n_rows":17,"n_cols":5,"rows_kept":17,"cut":false,"hidden":false,"merged":[[0,0,0,4]]}
["SIMPLE FUTURE VALUE"]
["Interest",0.1]
[]
["Year","Account\nbalance, beginning of year","Interest\nearned during year","Total in\naccount, end year"]
[1,1000,{"$f":"$B$2*B5","v":100},{"$f":"C5+B5","v":1100},{"$f":"getformula(D5)","v":"<--  =C5+B5"}]
[2,{"$f":"D5","v":1100},{"$f":"$B$2*B6","v":110},{"$f":"C6+B6","v":1210},{"$f":"getformula(D6)","v":"<--  =C6+B6"}]
[3,{"$f":"D6","v":1210},121,1331]
[4,1331,133.1,1464.1]
[5,1464.1,146.41,1610.51]
[6,1610.51,161.05100000000002,1771.5609999999999]
[7,1771.5609999999999,177.15610000000001,1948.7170999999998]
[8,1948.7170999999998,194.87171000000001,2143.5888099999997]
[9,2143.5888099999997,214.358881,2357.9476909999998]
[10,2357.9476909999998,235.7947691,2593.7424600999998]
[11,2593.7424600999998]
[]
["A simpler way",null,{"$f":"B5*(1+B2)^10","v":2593.7424601000021},{"$f":"getformula(C17)","v":"<--  =B5*(1+B2)^10"}]

{"sheet":"Page 32","index":18,"n_rows":18,"n_cols":6,"rows_kept":18,"cut":false,"hidden":false,"merged":[[0,0,0,5]]}
["FUTURE VALUE WITH ANNUAL DEPOSITS"]
["Interest",0.1]
["Annual deposit",1000,"<-- Made today and at beginning of each of next 9 years"]
["Number of deposits",10]
[]
["Year","Account\nbalance, beginning of year","Deposit at\nbeginning of year","Interest\nearned during year","Total in\naccount, end year"]
[1,0,1000,{"$f":"$B$2*(C7+B7)","v":100},{"$f":"D7+C7+B7","v":1100},{"$f":"getformula(E7)","v":"<--  =D7+C7+B7"}]
[2,{"$f":"E7","v":1100},1000,{"$f":"$B$2*(C8+B8)","v":210},{"$f":"D8+C8+B8","v":2310},{"$f":"getformula(E8)","v":"<--  =D8+C8+B8"}]
[3,{"$f":"E8","v":2310},{"$f":"C8","v":1000},{"$f":"$B$2*(C9+B9)","v":331},{"$f":"D9+C9+B9","v":3641}]
[4,3641,{"$f":"C9","v":1000},464.1,5105.1000000000004]
[5,5105.1000000000004,1000,610.5100000000001,6715.6100000000006]
[6,6715.6100000000006,1000,771.56100000000015,8487.1710000000003]
[7,8487.1710000000003,1000,948.71710000000007,10435.8881]
[8,10435.8881,1000,1143.58881,12579.476910000001]
[9,12579.476910000001,1000,1357.9476910000003,14937.424601000002]
[10,14937.424601000002,1000,1593.7424601000002,17531.167061100001]
[]
["Future value",{"$f":"FV(B2,B4,-B3,,1)","v":17531.167061100023},{"$f":"getformula(B18)","v":"<--  =FV(B2,B4,-B3,,1)"}]

{"sheet":"Page 34","index":19,"n_rows":21,"n_cols":6,"rows_kept":21,"cut":false,"hidden":false,"merged":[[0,0,0,5],[20,0,20,5]]}
["A RETIREMENT PROBLEM"]
["Interest",0.08]
["Annual deposit",48000]
["Annual retirement withdrawal",30000]
[null,null,null,null,null,{"$f":"ggetformula(D7)","v":"=$B$2*(C7+B7)"}]
["Year","Account\nbalance, beginning of year","Deposit at\nbeginning of year","Interest\nearned during year","Total in\naccount, end year"]
[1,0,{"$f":"$B$3","v":48000},{"$f":"$B$2*(C7+B7)","v":3840},{"$f":"D7+C7+B7","v":51840},{"$f":"getformula(E7)","v":"<--  =D7+C7+B7"}]
[2,{"$f":"E7","v":51840},{"$f":"$B$3","v":48000},7987.2,107827.2]
[3,107827.2,{"$f":"$B$3","v":48000},12466.176000000001,168293.37599999999]
[4,168293.37599999999,{"$f":"$B$3","v":48000},17303.470079999999,233596.84607999999]
[5,233596.84607999999,{"$f":"$B$3","v":48000},22527.747686399998,304124.59376640001]
[6,304124.59376640001,{"$f":"-$B$4","v":-30000},21929.967501312,296054.56126771204]
[7,296054.56126771204,-30000,21284.364901416964,287338.92616912897]
[8,287338.92616912897,-30000,20587.114093530319,277926.04026265931]
[9,277926.04026265931,-30000,19834.083221012745,267760.12348367204]
[10,267760.12348367204,-30000,19020.809878693763,256780.93336236582]
[11,256780.93336236582,-30000,18142.474668989267,244923.40803135509]
[12,244923.40803135509,-30000,17193.87264250841,232117.28067386351]
[13,232117.28067386351,-30000,16169.382453909082,218286.6631277726]
[]
["Note:  This problem has 5 deposits and 8 annual withdrawals, all made at the beginning of the year.  The beginning of year 13 is the last year of the retirement plan; if the annual deposit is correctly computed, the balance at the beginning of year 13 after the withdrawal should be zero."]

{"sheet":"Retirement picture","index":20,"n_rows":11,"n_cols":15,"rows_kept":11,"cut":false,"hidden":false,"merged":[[7,6,7,14],[7,1,7,5]]}
[]
["Annual deposit",29386.551000608499]
["Annual withdrawal",30000]
["Interest rate",0.08]
[]
[null,0,1,2,3,4,5,6,7,8,9,10,11,12,13]
[]
[null,"Deposits made",null,null,null,null,"Withdrawals at beginning of year"]
[]
["Future value of deposits",null,null,null,null,{"$f":"FV(B4,5,-B2,,1)","v":186191.10177615963},{"$f":"getformula(F10)","v":"<--  =FV(B4,5,-B2,,1)"}]
["Present value of withdrawals\nto end of year 3",null,null,null,null,{"$f":"PV(B4,8,-B3,,1)","v":186191.10177669983},{"$f":"getformula(F11)","v":"<--  =PV(B4,8,-B3,,1)"}]

{"sheet":"Page 37","index":21,"n_rows":21,"n_cols":6,"rows_kept":21,"cut":false,"hidden":false,"merged":[[0,0,0,5],[20,0,20,5]]}
["A RETIREMENT PROBLEM"]
["Interest",0.08]
["Annual deposit",29386.551000608495]
["Annual retirement withdrawal",30000]
[null,null,null,null,null,{"$f":"ggetformula(D7)","v":"=$B$2*(C7+B7)"}]
["Year","Account\nbalance, beginning of year","Deposit at\nbeginning of year","Interest\nearned during year","Total in\naccount, end year"]
[1,0,{"$f":"$B$3","v":29386.551000608495},{"$f":"$B$2*(C7+B7)","v":2350.9240800486796},{"$f":"D7+C7+B7","v":31737.475080657176},{"$f":"getformula(E7)","v":"<--  =D7+C7+B7"}]
[2,{"$f":"E7","v":31737.475080657176},{"$f":"$B$3","v":29386.551000608495},4889.9220865012539,66013.948167766925]
[3,66013.948167766925,{"$f":"$B$3","v":29386.551000608495},7632.0399334700342,103032.53910184545]
[4,103032.53910184545,{"$f":"$B$3","v":29386.551000608495},10593.527208196316,143012.61731065027]
[5,143012.61731065027,{"$f":"$B$3","v":29386.551000608495},13791.933464900703,186191.10177615948]
[6,186191.10177615948,{"$f":"-$B$4","v":-30000},12495.288142092759,168686.38991825224]
[7,168686.38991825224,-30000,11094.911193460181,149781.30111171241]
[8,149781.30111171241,-30000,9582.5040889369939,129363.8052006494]
[9,129363.8052006494,-30000,7949.1044160519523,107312.90961670135]
[10,107312.90961670135,-30000,6185.0327693361078,83497.942386037452]
[11,83497.942386037452,-30000,4279.8353908829959,57777.777776920448]
[12,57777.777776920448,-30000,2222.2222221536358,29999.999999074083]
[13,29999.999999074083,-30000,-7.4073323048651215E-8,-9.999894245993346E-7]
[]
["Note:  This problem has 5 deposits and 8 annual withdrawals, all made at the beginning of the year.  The beginning of year 13 is the last year of the retirement plan; if the annual deposit is correctly computed, the balance at the beginning of year 13 after the withdrawal should be zero."]

{"sheet":"Page 38","index":22,"n_rows":9,"n_cols":3,"rows_kept":9,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["A RETIREMENT PROBLEM"]
["Interest",0.08]
["Annual retirement withdrawal",30000]
["Years of withdrawal",8]
["Years of deposit",5]
["Present value of withdrawals",{"$f":"-PV(B2,B4,B3)/(1+B2)^B5","v":117331.97713557708},{"$f":"getformula(B6)","v":"<--  =-PV(B2,B4,B3)/(1+B2)^B5"}]
["Annual deposit",{"$f":"PMT(B2,B5,-B6)","v":29386.551000693773},{"$f":"getformula(B7)","v":"<--  =PMT(B2,B5,-B6)"}]
[]
[null,{"$f":"PMT(B2,B5,PV(B2,B4,B3)/(1+B2)^B5)","v":29386.551000693773},{"$f":"getformula(B9)","v":"<--  =PMT(B2,B5,PV(B2,B4,B3)/(1+B2)^B5)"}]

{"sheet":"Page 39","index":23,"n_rows":33,"n_cols":3,"rows_kept":33,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["MULTIPLE COMPOUNDING PERIODS"]
["Initial deposit",1000]
["Interest rate",0.05]
["Number of compounding periods per year",2]
["Interest per compounding period",{"$f":"B3/B4","v":2.5000000000000001E-2},{"$f":"getformula(B5)","v":"<--  =B3/B4"}]
["Accretion in one year",{"$f":"B2*(1+B5)^B4","v":1050.625},{"$f":"getformula(B6)","v":"<--  =B2*(1+B5)^B4"}]
["Continuous compounding with Exp",{"$f":"B2*EXP(B3)","v":1051.2710963760242},{"$f":"getformula(B7)","v":"<--  =B2*EXP(B3)"}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Compounding periods per year","End-year\naccretion"]
[1,{"$f":"$B$2*(1+$B$3/A25)^A25","v":1050},{"$f":"getformula(B25)","v":"<--  =$B$2*(1+$B$3/A25)^A25"}]
[2,{"$f":"$B$2*(1+$B$3/A26)^A26","v":1050.625},{"$f":"getformula(B26)","v":"<--  =$B$2*(1+$B$3/A26)^A26"}]
[10,1051.1401320407892]
[20,1051.2055032813846]
[50,1051.244832434744]
[100,1051.2579599480462]
[150,1051.2623377663551]
[300,1051.2667165755583]
[800,1051.2694538347121]

{"sheet":"Page 40","index":24,"n_rows":11,"n_cols":4,"rows_kept":11,"cut":false,"hidden":false,"merged":[[0,0,0,3]]}
["CONTINUOUS DISCOUNTING"]
["Interest",0.08]
[]
["Year","Cash flow","Continously\ndiscounted PV"]
[1,100,{"$f":"B5*EXP(-$B$2*A5)","v":92.311634638663577},{"$f":"getformula(C5)","v":"<--  =B5*EXP(-$B$2*A5)"}]
[2,200,{"$f":"B6*EXP(-$B$2*A6)","v":170.42875779324226},{"$f":"getformula(C6)","v":"<--  =B6*EXP(-$B$2*A6)"}]
[3,300,{"$f":"B7*EXP(-$B$2*A7)","v":235.98835831996604}]
[4,400,{"$f":"B8*EXP(-$B$2*A8)","v":290.45961482947638}]
[5,500,{"$f":"B9*EXP(-$B$2*A9)","v":335.16002301781964}]
[]
["Present value",null,{"$f":"SUM(C5:C9)","v":1124.3483885991679},{"$f":"getformula(C11)","v":"<--  =SUM(C5:C9)"}]

{"sheet":"Page 41","index":25,"n_rows":17,"n_cols":3,"rows_kept":17,"cut":false,"hidden":false,"merged":[[0,0,0,2],[8,0,8,2]]}
["CALCULATING RETURNS FROM PRICES"]
["Initial deposit",1000]
["End-of-year value",1200]
["Number of compounding periods",2]
["Implied annual interest rate",{"$f":"((B3/B2)^(1/B4)-1)*B4","v":0.1908902300206643},{"$f":"getformula(B5)","v":"<--  =((B3/B2)^(1/B4)-1)*B4"}]
[]
["Continuous return",{"$f":"LN(B3/B2)","v":0.18232155679395459},{"$f":"getformula(B7)","v":"<--  =LN(B3/B2)"}]
[]
["Implied annual interest rate with n compounding periods"]
["Number of compounding periods","Rate"]
[null,{"$f":"B5","v":0.1908902300206643},{"$f":"getformula(B11)&\", data table header\"","v":"<--  =B5, data table header"}]
[1,0.19999999999999996]
[2,0.1908902300206643]
[4,0.18654055756842247]
[8,0.1844150017637034]
[20,0.18315511655226047]
[1000,0.18233817837920441]

{"sheet":"Page 42","index":26,"n_rows":9,"n_cols":3,"rows_kept":9,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["USING XIRR TO COMPUTE THE ANNUALIZED INTERNAL RATE OF RETURN"]
["Date","Cash flow"]
["2014-01-01",-1000]
["2014-03-03",150]
["2014-07-04",100]
["2014-10-12",50]
["2014-12-25",1000]
[]
["IRR",{"$f":"XIRR(B3:B7,A3:A7)","v":0.37188269495964044},{"$f":"getformula(B9)","v":"<--  =XIRR(B3:B7,A3:A7)"}]

{"sheet":"Page 43","index":27,"n_rows":13,"n_cols":3,"rows_kept":13,"cut":false,"hidden":false,"merged":[[0,0,0,2],[12,0,12,2]]}
["USING XNPV TO COMPUTE THE NET PRESENT VALUE"]
["Annual discount rate",0.12]
[]
["Date","Cash flow"]
["2014-01-01",-1000]
["2015-03-03",100]
["2015-07-04",195]
["2016-10-12",350]
["2017-12-25",800]
[]
["Net present value",{"$f":"XNPV(B2,B5:B9,A5:A9)","v":16.800830622140609},{"$f":"getformula(B11)","v":"<--  =XNPV(B2,B5:B9,A5:A9)"}]
[]
["Note that XNPV has a different syntax from  NPV !     \nXNPV requires all the cash flows, including the initial cash flow, whereas NPV assumes that the first cash flow occurs one period hence."]

{"sheet":"Page 44, top","index":28,"n_rows":13,"n_cols":3,"rows_kept":13,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["PROBLEM WITH XNPV\nXNPV does not work with zero or negative discount rates"]
["Discount rate",-0.03]
["Net present value",{"$f":"XNPV(B2,B7:B13,A7:A13)","v":{"$error":"#NUM!"}},{"$f":"getformula(B3)","v":"<--  =XNPV(B2,B7:B13,A7:A13)"}]
[null,{"$f":"nXNPV(B2,B7:B13,A7:A13)","v":-194.87376610952469},{"$f":"getformula(B4)","v":"<--  =nXNPV(B2,B7:B13,A7:A13)"}]
[]
["Date","Cash flow"]
["2014-06-30",-500]
["2015-02-14",100]
["2016-02-14",300]
["2017-02-14",400]
["2018-02-14",600]
["2019-02-14",800]
["2020-02-14",-1800]

{"sheet":"Page 44, bottom","index":29,"n_rows":28,"n_cols":6,"rows_kept":28,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["PROBLEMS WITH XIRR"]
["Discount rate",0.22]
["Net present value",{"$f":"XNPV(B2,B9:B15,A9:A15)","v":64.961862683133518},{"$f":"getformula(B3)","v":"<--  =XNPV(B2,B9:B15,A9:A15)"}]
["IRR",{"$f":"XIRR(B9:B15,A9:A15)","v":{"$error":"#NUM!"}},{"$f":"getformula(B4)&\" , no Guess\"","v":"<--  =XIRR(B9:B15,A9:A15) , no Guess"}]
[null,{"$f":"XIRR(B9:B15,A9:A15,35%)","v":{"$error":"#NUM!"}},{"$f":"getformula(B5)&\", Guess = 35%\"","v":"<--  =XIRR(B9:B15,A9:A15,35%), Guess = 35%"}]
[null,{"$f":"XIRR(B9:B15,A9:A15,5%)","v":{"$error":"#NUM!"}},{"$f":"getformula(B6)&\" , Guess = 5%\"","v":"<--  =XIRR(B9:B15,A9:A15,5%) , Guess = 5%"}]
[]
["Date","Cash flow",null,"Data table:  XNPV as function of discount rate"]
["2014-06-30",-500]
["2015-02-14",100,null,"Rate",{"$f":"B3","v":64.961862683133518},{"$f":"getformula(E10)&\", data table header\"","v":"<--  =B3, data table header"}]
["2016-02-14",300,null,1E-3,-97.366433866481884]
["2017-02-14",400,null,2.5000000000000001E-2,-42.752588461809637]
["2018-02-14",600,null,4.9000000000000002E-2,-2.310474758705368]
["2019-02-14",800,null,7.2999999999999995E-2,26.837075854900831]
["2020-02-14",-1800,null,9.7000000000000003E-2,46.983277802916746]
[null,null,null,0.121,59.961258361210639]
[null,null,null,0.14499999999999999,67.240176253293043]
[null,null,null,0.16900000000000001,70.00016374391646]
[null,null,null,0.193,69.19102187217868]
[null,null,null,0.217,65.578387895134142]
[null,null,null,0.24099999999999999,59.78019616595418]
[null,null,null,0.26500000000000001,52.295583830152623]
[null,null,null,0.28899999999999998,43.527890164868666]
[null,null,null,0.313,33.8030192950215]
[null,null,null,0.33700000000000002,23.384148582167313]
[null,null,null,0.36099999999999999,12.483545956100727]
[null,null,null,0.38500000000000001,1.2720917685055042]
[null,null,null,0.40899999999999997,-10.113028226149225]

{"sheet":"Page 45","index":30,"n_rows":14,"n_cols":3,"rows_kept":14,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["NXIRR FIXES THE XIRR BUG"]
["Discount rate",-0.03]
["IRR",{"$f":"nXIRR(B8:B14,A8:A14)","v":5.063148498535152E-2},{"$f":"getformula(B3)&\" , no Guess\"","v":"<--  =nXIRR(B8:B14,A8:A14) , no Guess"}]
[null,{"$f":"nXIRR(B8:B14,A8:A14,35%)","v":0.38769500732421891},{"$f":"getformula(B4)&\", Guess = 35%\"","v":"<--  =nXIRR(B8:B14,A8:A14,35%), Guess = 35%"}]
[null,{"$f":"nXIRR(B8:B14,A8:A14,5%)","v":5.0631484985351562E-2},{"$f":"getformula(B5)&\" , Guess = 5%\"","v":"<--  =nXIRR(B8:B14,A8:A14,5%) , Guess = 5%"}]
[]
["Date","Cash flow"]
["2014-06-30",-500]
["2015-02-14",100]
["2016-02-14",300]
["2017-02-14",400]
["2018-02-14",600]
["2019-02-14",800]
["2020-02-14",-1800]