{"text_format":"1","workbook":"xlsx","n_sheets":31,"sheets":["NOTES","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","Retirement picture","Page 34","Page 38 (Replaced)","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":29,"comments":6,"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":"NOTES","index":0,"n_rows":22,"n_cols":15,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
["FINANCIAL MATH"]
[null,"Pn = Value at end of period n. Present is n=0."]
[null,"r = Rate of interest or growth per compounding period"]
[null,"n = Number of integer compounding periods"]
[]
[null,"Future Value:","Pn = P0 (1 + r)n"]
[null,"Present Value:","P0 = Pn / (1 + r)n = Pn (1 + r)-n"]
[null,"Rate:","r = (Pn/P0)1/n - 1"]
[null,"# of Periods:","n = ln(Pn/P0) / ln(1 + r)"]
[]
["Time Line Steps"]
[1,"Draw a line with zero at left."]
[2,"Label the scale with the compounding interval (years, days, months, etc.)."]
[3,"Add integer tick marks considering the time horizon (0, 1, 2, 3, … )."]
[4,"Show dollar amounts & unknowns above tick marks."]
[5,"Show the one-period rate (of interest, discount, COC, etc.)."]
[null,"The rate MUST be compounded for the labeled period length."]
["Time Line:","    <------------ 5-Period Annuity Due ----------------------->",null,null,null,null,"    <---- 8-Period Withdrawal Ordinary Annuity deferred 4 years from time zero ------>"]
[null,"?????","?????","?????","?????","?????","25k","25k","25k","25k","25k","25k","25k","25k"]
[null,"!","!","!","!","!","!","!","!","!","!","!","!","!"]
[null,0,{"$f":"1+B21","v":1},{"$f":"1+C21","v":2},3,4,5,6,7,8,9,10,11,12,"Years"]
[null,"               r = 12%",null,"<-- We assume that 12% is the rate compounded annually in this example"]

{"sheet":"Page 14","index":1,"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]
[]
["End of 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":2,"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]
[]
["End of 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":3,"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]
[]
["End of 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":4,"n_rows":16,"n_cols":3,"rows_kept":16,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["COMPUTING THE VALUE OF A FINITE ORDINARY 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)"}]
[]
["End of 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":5,"n_rows":145,"n_cols":4,"rows_kept":145,"cut":false,"hidden":false,"merged":[]}
["COMPUTING THE VALUE OF AN INFINITE ORDINARY ANNUITY"]
["Periodic payment at end of period, C",1000]
["Discount rate, r",0.12]
["Present value of annuity",{"$f":"B2/B3","v":8333.3333333333339},{"$f":"getformula(B4)","v":"<--  =B2/B3"}]
[null,null,{"$f":"SUM(C6:C145)","v":8333.3322610845007},{"$f":"COUNT(C6:C145)","v":140}]
[null,1,{"$f":"$B$2/(1+$B$3)^B6","v":892.85714285714278}]
[null,{"$f":"1+B6","v":2},{"$f":"$B$2/(1+$B$3)^B7","v":797.19387755102025}]
[null,{"$f":"1+B7","v":3},711.78024781341082]
[null,4,635.51807840483116]
[null,5,567.4268557185992]
[null,6,506.63112117732067]
[null,7,452.34921533689345]
[null,8,403.88322797936911]
[null,9,360.61002498157956]
[null,{"$f":"1+B14","v":10},321.97323659069599]
[null,11,287.47610409883566]
[null,12,256.675092945389]
[null,13,229.17419012981159]
[null,14,204.61981261590319]
[null,15,182.69626126419928]
[null,16,163.12166184303504]
[null,17,145.64434093128128]
[null,18,130.03959011721543]
[null,19,116.1067768903709]
[null,20,103.66676508068831]
[null,21,92.559611679185977]
[null,22,82.642510427844613]
[null,23,73.78795573914698]
[null,24,65.882103338524075]
[null,25,58.823306552253634]
[null,26,52.520809421655031]
[null,27,46.893579840763415]
[null,28,41.869267714967336]
[null,29,37.38327474550654]
[null,30,33.377923879916558]
[null,31,29.801717749925498]
[null,32,26.608676562433473]
[null,33,23.757746930744169]
[null,34,21.212274045307293]
[null,35,18.939530397595796]
[null,36,16.910294997853391]
[null,37,15.098477676654811]
[null,38,13.480783639870365]
[null,39,12.03641396416997]
[null,40,10.746798182294613]
[null,41,9.5953555199059064]
[null,42,8.5672817142016999]
[null,43,7.6493586733943735]
[null,44,6.8297845298164059]
[null,45,6.0980219016217898]
[null,46,5.4446624121623115]
[null,47,4.8613057251449217]
[null,48,4.3404515403079653]
[null,49,3.8754031609892543]
[null,50,3.4601813937404051]
[null,51,3.0894476729825042]
[null,52,2.7584354223058076]
[null,53,2.4628887699158994]
[null,54,2.1990078302820528]
[null,55,1.9633998484661186]
[null,56,1.753035578987606]
[null,57,1.5652103383817906]
[null,58,1.3975092306980275]
[null,59,1.2477760988375242]
[null,60,1.1140858025335039]
[null,61,0.9947194665477711]
[null,62,0.88814238084622421]
[null,63,0.79298426861270022]
[null,64,0.70802166840419645]
[null,65,0.63216220393231826]
[null,66,0.56443053922528408]
[null,67,0.50395583859400361]
[null,68,0.4499605701732175]
[null,69,0.4017505090832299]
[null,70,0.35870581168145521]
[null,71,0.32027304614415641]
[null,72,0.28595807691442537]
[null,73,0.2553197115307369]
[null,{"$f":"1+B78","v":74},0.22796402815244365]
[null,75,0.20353931085039609]
[null,76,0.18173152754499655]
[null,77,0.16226029245088971]
[null,78,0.14487526111686583]
[null,79,0.12935291171148733]
[null,80,0.11549367117097083]
[null,81,0.10311934925979538]
[null,82,9.2070847553388721E-2]
[null,83,8.2206113886954202E-2]
[null,84,7.3398315970494829E-2]
[null,85,6.553421068794181E-2]
[null,86,5.8512688114233748E-2]
[null,87,5.2243471530565848E-2]
[null,88,4.6645956723719503E-2]
[null,89,4.1648175646178121E-2]
[null,90,3.7185871112659037E-2]
[null,91,3.3201670636302708E-2]
[null,92,2.9644348782413128E-2]
[null,93,2.6468168555726007E-2]
[null,94,2.363229335332679E-2]
[null,95,2.1100261922613207E-2]
[null,96,1.8839519573761787E-2]
[null,97,1.6820999619430163E-2]
[null,98,1.5018749660205504E-2]
[null,99,1.3409597910897771E-2]
[null,100,1.1972855277587294E-2]
[null,101,1.0690049354988655E-2]
[null,102,9.5446869240970126E-3]
[null,103,8.5220418965151912E-3]
[null,104,7.6089659790314181E-3]
[null,105,6.7937196241351959E-3]
[null,106,6.0658210929778519E-3]
[null,107,5.4159116901587962E-3]
[null,108,4.8356354376417826E-3]
[null,109,4.3175316407515901E-3]
[null,110,3.8549389649567768E-3]
[null,111,3.4419097901399794E-3]
[null,112,3.0731337411964098E-3]
[null,113,2.7438694117825086E-3]
[null,114,2.4498834033772395E-3]
[null,115,2.187395895872535E-3]
[null,116,1.9530320498861921E-3]
[null,117,1.7437786159698141E-3]
[null,118,1.5569451928301912E-3]
[null,119,1.390129636455528E-3]
[null,120,1.2411871754067214E-3]
[null,121,1.1082028351845723E-3]
[null,122,9.8946681712908254E-4]
[null,123,8.8345251529382341E-4]
[null,124,7.8879688865519959E-4]
[null,125,7.0428293629928515E-4]
[null,126,6.2882405026721882E-4]
[null,127,5.6145004488144547E-4]
[null,128,5.0129468292986197E-4]
[null,129,4.4758453833023385E-4]
[null,130,3.9962905208056586E-4]
[null,131,3.5681165364336237E-4]
[null,132,3.1858183361014501E-4]
[null,133,2.8444806572334373E-4]
[null,134,2.5397148725298543E-4]
[null,135,2.2676025647587984E-4]
[null,136,2.02464514710607E-4]
[null,137,1.8077188813447052E-4]
[null,{"$f":"1+B142","v":138},1.6140347154863438E-4]
[null,139,1.4411024245413782E-4]
[null,140,1.2866985933405163E-4]

{"sheet":"Page 19, middle","index":6,"n_rows":23,"n_cols":5,"rows_kept":23,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["COMPUTING THE VALUE OF A FINITE GROWING ORDINARY ANNUITY"]
["First payment, end of period, C",1000]
["Growth rate of payments, g",0.06,"Try each of the 3 scenarios!"]
["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)"},"<-- Algebraic Formula"]
[]
["End of Period","Annuity\nPayment",null,"Payment  PV","When does the pattern of values in D10:D14 increase/decrease/stay same?"]
[1,{"$f":"B2","v":1000},{"$f":"getformula(B10)","v":"<--  =B2"},{"$f":"ROUND(B10*(1+$B$5)^-A10,4)","v":892.85709999999995},{"$f":"getformula(D10)","v":"<--  =ROUND(B10*(1+$B$5)^-A10,4)"}]
[2,{"$f":"$B$2*(1+$B$3)^(A11-1)","v":1060},{"$f":"getformula(B11)","v":"<--  =$B$2*(1+$B$3)^(A11-1)"},{"$f":"ROUND(B11*(1+$B$5)^-A11,4)","v":845.02549999999997},"<--  =ROUND(B11*(1+$B$5)^-A11,4)"]
[3,{"$f":"$B$2*(1+$B$3)^(A12-1)","v":1123.6000000000001},{"$f":"getformula(B12)","v":"<--  =$B$2*(1+$B$3)^(A12-1)"},799.75630000000001,"<--  =ROUND(B12*(1+$B$5)^-A12,4)"]
[4,{"$f":"$B$2*(1+$B$3)^(A13-1)","v":1191.0160000000003},{"$f":"getformula(B13)","v":"<--  =$B$2*(1+$B$3)^(A13-1)"},756.91219999999998,"<--  =ROUND(B13*(1+$B$5)^-A13,4)"]
[5,{"$f":"$B$2*(1+$B$3)^(A14-1)","v":1262.4769600000004},{"$f":"getformula(B14)","v":"<--  =$B$2*(1+$B$3)^(A14-1)"},716.36329999999998,"<--  =ROUND(B14*(1+$B$5)^-A14,4)"]
["Sum",{"$f":"SUM(B10:B14)","v":5637.0929600000018},null,"Spark Line -->"]
["Present value using Excel's NPV function",{"$f":"NPV(B5,B10:B14)","v":4010.9144710037572},{"$f":"getformula(B16)","v":"<--  =NPV(B5,B10:B14)"},{"$f":"SUM(D10:D14)","v":4010.9143999999997},"<--  =SUM(D10:D14)"]
[]
[]
["Equivalent value payment at end period 3",{"$f":"B10*(1+B5)^2+B11*(1+B5)+B12+B13*(1+B5)^-1+B14*(1+B5)^-2","v":5635.0460459183687},{"$f":"getformula(B19)","v":"<--  =B10*(1+B5)^2+B11*(1+B5)+B12+B13*(1+B5)^-1+B14*(1+B5)^-2"}]
["Equivalent value payment at end period 0",{"$f":"B19/(1+B5)^3","v":4010.9144710037576},{"$f":"getformula(B20)&\": Discount value at end of period 3\"","v":"<--  =B19/(1+B5)^3: Discount value at end of period 3"}]
["Equivalent value payment at end period 5",{"$f":"B20*(1+B5)^5","v":7068.6017600000023},{"$f":"getformula(B21)&\": Compound PV 5 periods\"","v":"<--  =B20*(1+B5)^5: Compound PV 5 periods"}]
[]
[null,null,"All 3 of these values are the same considering their timing!"]

{"sheet":"Page 19, bottom","index":7,"n_rows":147,"n_cols":5,"rows_kept":147,"cut":false,"hidden":false,"merged":[]}
["COMPUTING THE VALUE OF AN INFINITE GROWING ORDINARY ANNUITY"]
["First payment, end of period, C",1000,null,"<-- Starting at end of date 1"]
["Growth rate of payments, g",-0.03]
["Discount rate, r",0.06]
["Present value of annuity",{"$f":"B2/(B4-B3)","v":11111.111111111111},{"$f":"getformula(B5)","v":"<--  =B2/(B4-B3)"}]
[null,null,{"$f":"B5-D7","v":4.4769181138690328E-2},"<-- Difference"]
["Count:",{"$f":"COUNT(B8:B147)","v":140},"Dollars",{"$f":"SUM(D8:D147)","v":11111.066341929973},"<-- Sum"]
[null,1,{"$f":"B2","v":1000},{"$f":"C8/(1+$B$4)^B8","v":943.39622641509425}]
[null,{"$f":"1+B8","v":2},{"$f":"C8*(1+$B$3)","v":970},{"$f":"C9/(1+$B$4)^B9","v":863.2965468138126}]
[null,{"$f":"1+B9","v":3},{"$f":"C9*(1+$B$3)","v":940.9},789.9977834050926]
[null,4,912.673,722.92249990843379]
[null,5,885.29281000000003,661.54228765205733]
[null,6,858.73402569999996,605.37360285141085]
[null,7,832.97200492899992,553.97395732629093]
[null,8,807.98284478112987,506.93843264764359]
[null,9,783.74335943769597,463.89649025303237]
[null,10,760.23105865456512,424.50905240135978]
[null,11,737.42412689492812,388.46583097105554]
[null,12,715.30140308808029,355.48288305841874]
[null,13,693.84236099543784,325.30037411949638]
[null,14,673.02709016557469,297.68053103387876]
[null,15,652.83627746060745,272.40576896496447]
[null,16,633.25118913678921,249.27697726039204]
[null,17,614.25365346268552,228.11195088922665]
[null,18,595.82604385880495,208.74395505900929]
[null,19,577.9512625430408,191.02041170494243]
[null,20,560.61272466674961,174.80169750357942]
[null,21,543.79434292674716,159.96004394195472]
[null,22,527.48051263894479,146.3785307770718]
[null,23,511.6560972597764,133.95016495637699]
[null,24,496.3064143419831,122.57703774309971]
[null,25,481.41722191172357,112.16955340642143]
[null,26,466.97470525437183,102.64572340021581]
[null,27,452.96546409674067,93.930520470008801]
[null,28,439.37650017383845,85.955287599913703]
[null,29,426.19520516862326,78.657197143317248]
[null,30,413.40934901356457,71.978755876431819]
[null,31,401.00706854315763,65.867352075602682]
[null,32,388.97685648686291,60.274841050315686]
[null,33,377.30755079225702,55.157165866798309]
[null,34,365.98832426848929,50.474010274334297]
[null,35,355.00867454043458,46.188481100098358]
[null,36,344.35841430422153,42.266817610467356]
[null,37,334.02766187509485,38.678125549201255]
[null,38,324.00683201884198,35.394133757287932]
[null,39,314.28662705827674,32.38897145714084]
[null,40,304.85802824652842,29.638964446628886]
[null,41,295.71228739913255,27.122448597386814]
[null,42,286.84091877715855,24.81959918817472]
[null,43,278.23569121384378,22.71227472880139]
[null,44,269.88862047742845,20.783874044280513]
[null,45,261.7919618631056,19.019205493351038]
[null,46,253.93820300721242,17.404367291085382]
[null,47,246.32005691699604,15.926637992785674]
[null,48,238.93045520948615,14.574376276417082]
[null,49,231.76254155320157,13.336929234079781]
[null,50,224.80966530660552,12.204548450054141]
[null,51,218.06537534740735,11.168313204294824]
[null,52,211.52341408698513,10.220060196382999]
[null,53,205.17771166437558,9.3523192363127432]
[null,54,199.02238031444432,8.5582543954937371]
[null,55,193.051708905011,7.831610154366909]
[null,56,187.26015763786066,7.1666621223923617]
[null,57,181.64235290872483,6.5581719421892357]
[null,58,176.19308232146309,6.0013460225693951]
[null,59,170.9072898518192,5.4917977753701059]
[null,60,165.78007115626463,5.0255130585933987]
[null,61,160.80666902157668,4.5988185536184867]
[null,62,155.98246895092939,4.2083528273678601]
[null,63,151.30299488240149,3.8510398514592672]
[null,64,146.76390503592944,3.5240647697315945]
[null,65,142.36098788485154,3.2248517232449494]
[null,66,138.09015824830598,{"$f":"C73/(1+$B$4)^B73","v":2.9510435580637742}]
[null,{"$f":"1+B73","v":67},{"$f":"C73*(1+$B$3)","v":133.9474535008568},2.7004832559640191]
[null,68,129.92902989583109,2.4711969417783948]
[null,69,126.03115899895616,2.261378333514191]
[null,70,122.25022422898748,2.0693745127441181]
[null,71,118.58271750211784,1.8936729031715041]
[null,72,115.02523597705431,1.7328893547890178]
[null,73,111.57447889774268,1.5857572397597619]
[null,74,108.2272445308104,1.4511174741197819]
[null,75,104.98042719488608,1.3279093866945171]
[null,76,101.83101437903949,1.2151623632959259]
[null,77,98.776083947668297,1.1119882003745736]
[null,78,95.812801429238249,1.0175741078899401]
[null,79,92.938417386361095,0.93117630627664283]
[null,80,90.150264864770264,0.85211416706447529]
[null,81,87.445756918827158,0.77976485099296311]
[null,82,84.822384211262346,0.71355840138035309]
[null,83,82.277712684924467,0.65297325409334184]
[null,84,79.809381304376728,0.59753212874579398]
[null,85,77.415099865245423,0.54679826875794335]
[null,86,75.09264686928806,0.50037200065585374]
[null,87,72.839867463209416,0.45788758550582837]
[null,88,70.654671439313134,0.4190103376798619]
[null,89,68.535031296133738,0.38343398825421326]
[null,90,66.478980357249725,0.35087827227036489]
[null,91,64.484610946532229,0.32108672085118289]
[null,92,62.550072618136262,0.29382464077891268]
[null,93,60.673570439592176,0.26887726561843894]
[null,94,58.853363326404406,0.24604806382064692]
[null,95,57.087762426612272,0.22515719047738439]
[null,96,55.375129553813906,0.20604007053119142]
[null,97,53.713875667199488,0.1885461022785431]
[null,98,52.102459397183502,0.17253747095300639]
[null,99,50.539385615267996,0.15788806304190206]
[null,100,49.023204046809951,0.14448247278362733]
[null,101,47.552507925405649,0.13221509301897971]
[null,102,46.125932687643477,0.12098928323434932]
[null,103,44.742154707014173,0.11071660824275362]
[null,104,43.399890065803746,0.10131614150516134]
[null,105,42.097893363829634,9.2713827603779719E-2]
[null,106,40.834956562914741,8.4841898844968219E-2]
[null,107,39.609907866027299,7.763834139586713E-2]
[null,108,38.42161063004648,7.1046406749048219E-2]
[null,109,37.268962311145081,6.5014164666581845E-2]
[null,110,36.15089344181073,5.9494094081683387E-2]
[null,111,35.06636663855641,5.4442708735125356E-2]
[null,112,34.014375639399717,4.9820214597237365E-2]
[null,113,32.993944370217726,4.5590196376717206E-2]
[null,114,32.004126039111192,4.1719330646618577E-2]
[null,115,31.044002257937855,3.8177123327566051E-2]
[null,116,30.112682190199717,3.4935669460131193E-2]
[null,117,29.209301724493724,3.196943337389363E-2]
[null,118,28.333022672758911,2.9255047521393227E-2]
[null,119,27.483031992576144,2.6771128392218329E-2]
[null,120,26.658541032798858,2.4498108057029983E-2]
[null,121,25.858784801814892,2.241808001445196E-2]
[null,122,25.083021257760443,2.0514658126432453E-2]
[null,123,24.330530620027631,1.8772847530791958E-2]
[null,124,23.6006147014268,1.7178926514026603E-2]
[null,125,22.892596260383996,1.5720338413779057E-2]
[null,126,22.205818372572477,1.4385592699401591E-2]
[null,127,21.539643821395302,1.3164174451339189E-2]
[null,128,20.893454506753443,1.2046461526225488E-2]
[null,129,20.266650871550841,1.1023648755130872E-2]
[null,130,19.658651345404316,{"$f":"C137/(1+$B$4)^B137","v":1.0087678577808437E-2}]
[null,{"$f":"1+B137","v":131},{"$f":"C137*(1+$B$3)","v":19.068891805042185},9.2311775664850777E-3]
[null,132,18.496825050890919,8.4473983391420062E-3]
[null,133,17.941920299364192,7.7301664046865518E-3]
[null,134,17.403662690383264,7.0738315212697678E-3]
[null,135,16.881552809671767,6.4732231845581831E-3]
[null,136,16.375106225381614,5.9236098953032437E-3]
[null,137,15.883853038620165,5.4206618853246662E-3]
[null,138,15.407337447461559,4.9604170082687971E-3]
[null,139,14.945117324037712,4.5392495264346541E-3]
[null,140,14.496763804316581,4.1538415477751075E-3]

{"sheet":"Page 21, top","index":8,"n_rows":10,"n_cols":3,"rows_kept":10,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["INTERNAL RATE OF RETURN"]
["End of 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":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.12]
[]
["End of 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":10,"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]
[]
["End of 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":11,"n_rows":20,"n_cols":10,"rows_kept":20,"cut":false,"hidden":false,"merged":[[0,0,0,2],[12,3,12,4]]}
["INTERNAL RATE OF RETURN",null,null,"Loan Principal",{"$f":"-B3","v":800},"End of Year","Pmt","Interest","Principal Reduction","Ending Balance"]
["End of Year","Cash flow",null,"Interest Rate",{"$f":"B10","v":0.22160309795512223},0,null,null,null,{"$f":"E1","v":800}]
[0,-800,null,"Term",{"$f":"A8","v":5},{"$f":"1+F2","v":1},{"$f":"B4","v":200},{"$f":"$E$2*J2","v":177.28247836409778},{"$f":"G3-H3","v":22.717521635902216},{"$f":"J2-I3","v":777.28247836409776}]
[1,200,null,null,null,{"$f":"1+F3","v":2},{"$f":"B5","v":250},{"$f":"$E$2*J3","v":172.24820519171934},{"$f":"G4-H4","v":77.751794808280664},{"$f":"J3-I4","v":699.53068355581706}]
[2,250,null,null,null,{"$f":"1+F4","v":3},300,155.01816659063334,{"$f":"G5-H5","v":144.98183340936666},{"$f":"J4-I5","v":554.54885014645038}]
[3,300,null,null,null,{"$f":"1+F5","v":4},350,122.88974315990424,{"$f":"G6-H6","v":227.11025684009576},{"$f":"J5-I6","v":327.43859330635462}]
[4,350,null,null,null,{"$f":"1+F6","v":5},400,72.561406666755531,{"$f":"G7-H7","v":327.43859333324446},{"$f":"J6-I7","v":-2.6889836135524092E-8}]
[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":12,"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,{"$f":"-B6","v":-1000}]
[1,{"$f":"C6","v":300}]
[2,{"$f":"C7","v":200}]
[3,150]
[4,600]
[5,900]
[]
["IRR",{"$f":"IRR(B17:B22)","v":0.24436074261538088},{"$f":"getformula(B24)","v":"<--  =IRR(B17:B22)"}]

{"sheet":"Page 26, bottom","index":13,"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":14,"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,{"$f":"-145","v":-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":15,"n_rows":24,"n_cols":7,"rows_kept":24,"cut":false,"hidden":false,"merged":[[0,0,0,10]]}
["BOND CASH FLOWS:  NPV CROSSES X-AXIS ONLY ONCE, SO THERE IS ONLY ONE IRR"]
["End of Year","\"Promised\" Cash flow",null,null,"Data table:  Effect of"]
[0,-800,"<--Bond Price",null,"discount rate on NPV"]
[1,100,"<-- Interest","Rate:",{"$f":"B13","v":0.14364208444928206},{"$f":"NPV(E4,B4:B11)+B3","v":7.6784090197179466E-10},{"$f":"getformula(F4)&\", table header\"","v":"<--  =NPV(E4,B4:B11)+B3, table header"}]
[2,100,"<-- Interest",null,0,1000]
[3,100,"<-- Interest",null,0.02,786.03851523955382]
[4,100,"<-- Interest",null,0.04,603.96469249702341]
[5,100,"<-- Interest",null,0.06,448.39175243878162]
[6,100,"<-- Interest",null,0.08,314.93277887450563]
[7,100,"<-- Interest",null,0.1,199.99999999999955]
[8,1100,"<-- Interest + Principal",null,0.12,100.64720466322774]
[null,null,null,null,0.14000000000000001,14.445444243074121]
["IRR=YTM",{"$f":"IRR(B3:B11)","v":0.14364208444928206},{"$f":"getformula(B13)","v":"<--  =IRR(B3:B11)"},null,0.16,-60.615453700742364]
["NPV@IRR",{"$f":"NPV(B13,B4:B11)+B3","v":7.6784090197179466E-10},{"$f":"getformula(B14)","v":"<--  =NPV(B13,B4:B11)+B3"},null,0.18,-126.20526056422]
[null,null,null,null,0.2,-183.71598031931103]
["Yield to Maturity is the rate of return promised based on"]
["market price & holding the bond to maturity."]
[]
["Expected return is less than the YTM if there is"]
["a probability of default as with corporate debt."]
[]
["Investors' discount rate (their required rate or cost of"]
["capital) depends on substitute investment rates which"]
["depend on things like risk, liquidity and tax status."]

{"sheet":"Page 29","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,"End of 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":17,"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,"End of 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":18,"n_rows":17,"n_cols":9,"rows_kept":17,"cut":false,"hidden":false,"merged":[[0,0,0,4]]}
["SIMPLE FUTURE VALUE",null,null,null,null,"Using End of Year Notation"]
["Interest",0.1,null,null,null,"Interest",{"$f":"B2","v":0.1}]
[]
["Year","Account\nbalance, beginning of year","Interest\nearned during year","Total in\naccount, end year",null,"Year End","Account\nbalance After Interest","Interest\n",{"$f":"RIGHT(getformula(G6),LEN(getformula(G6))-5)&\" = Previous Balance + Interest\"","v":"=G5+H6 = Previous Balance + Interest"}]
[1,1000,{"$f":"$B$2*B5","v":100},{"$f":"C5+B5","v":1100},{"$f":"getformula(D5)","v":"<--  =C5+B5"},0,{"$f":"B5","v":1000},0,{"$f":"getformula(H5)","v":"<--  0"}]
[2,{"$f":"D5","v":1100},{"$f":"$B$2*B6","v":110},{"$f":"C6+B6","v":1210},{"$f":"getformula(D6)","v":"<--  =C6+B6"},{"$f":"1+F5","v":1},{"$f":"G5+H6","v":1100},{"$f":"G5*$G$2","v":100},"<--  =G5*$G$2"]
[3,{"$f":"D6","v":1210},121,1331,null,{"$f":"1+F6","v":2},{"$f":"G6+H7","v":1210},{"$f":"G6*$G$2","v":110}]
[4,1331,133.1,1464.1,null,3,1331,121]
[5,1464.1,146.41,1610.51,null,4,1464.1,133.1]
[6,1610.51,161.05100000000002,1771.5609999999999,null,5,1610.51,146.41]
[7,1771.5609999999999,177.15610000000001,1948.7170999999998,null,6,1771.5609999999999,161.05100000000002]
[8,1948.7170999999998,194.87171000000001,2143.5888099999997,null,7,1948.7170999999998,177.15610000000001]
[9,2143.5888099999997,214.358881,2357.9476909999998,null,8,2143.5888099999997,194.87171000000001]
[10,2357.9476909999998,235.7947691,2593.7424600999998,null,9,2357.9476909999998,214.358881]
[11,2593.7424600999998,null,null,null,10,2593.7424600999998,235.7947691]
[]
["A simpler way",null,{"$f":"B5*(1+B2)^10","v":2593.7424601000021},{"$f":"getformula(C17)","v":"<--  =B5*(1+B2)^10"},null,"A simpler way",null,{"$f":"G5*(1+G2)^10","v":2593.7424601000021},{"$f":"getformula(H17)","v":"<--  =G5*(1+G2)^10"}]

{"sheet":"Page 32","index":19,"n_rows":20,"n_cols":5,"rows_kept":20,"cut":false,"hidden":false,"merged":[[0,0,0,5]]}
["FUTURE VALUE WITH ANNUAL DEPOSITS: ANNUITY DUE"]
["Interest",0.1]
["Annual deposit",1000,"<-- Made today and at beginning of each of next 9 years"]
["Number of deposits",10]
[]
["Table below uses End of Year framework"]
["END of Year","Deposit","Interest\nearned","Account\nBalance"]
[0,1000,null,{"$f":"B8","v":1000},{"$f":"getformula(D8)","v":"<--  =B8"}]
[1,1000,{"$f":"D8*$B$2","v":100},{"$f":"B9+C9+D8","v":2100},{"$f":"getformula(D9)","v":"<--  =B9+C9+D8"}]
[2,1000,210,{"$f":"B10+C10+D9","v":3310},{"$f":"getformula(D10)","v":"<--  =B10+C10+D9"}]
[3,1000,331,4641]
[4,1000,464.1,6105.1]
[5,1000,610.5100000000001,7715.6100000000006]
[6,1000,771.56100000000015,9487.1710000000003]
[7,1000,948.71710000000007,11435.8881]
[8,1000,1143.58881,13579.476910000001]
[9,1000,1357.9476910000003,15937.424601000002]
[10,0,1593.7424601000002,17531.167061100001,"<-- Ending Balance = FV(10)"]
[]
["Future value",{"$f":"FV(B2,B4,-B3,,1)","v":17531.167061100023},{"$f":"getformula(B20)","v":"<--  =FV(B2,B4,-B3,,1)"}]

{"sheet":"Retirement picture","index":20,"n_rows":19,"n_cols":15,"rows_kept":19,"cut":false,"hidden":false,"merged":[[0,0,0,5]]}
["A RETIREMENT PROBLEM"]
["You wish to make five equal annual payments over the next five"]
["years in your retirement account. The deposits will be made at the"]
["beginning of each year and earn 8% APR. You plan on withdrawing"]
["eight $30,000 amounts starting at the end of year 5. What deposit"]
["amount for each of the first five years will leave a balance of zero in"]
["the account after the last $30,000 withdrawal?"]
[]
["Start the solution to this problem with a time line."]
["Dollar amounts above the line plot directly above the end"]
["of period number below the line."]
["Time Line:","    <----- 5-Period Annuity Due ----------------------->",null,null,null,null,"    <------- 8-Period Withdrawal Ordinary Annuity deferred 4 years from time zero --------->"]
[null,"?????","?????","?????","?????","?????",{"$f":"-$B$16","v":-30000},-30000,-30000,-30000,-30000,-30000,-30000,-30000]
[null,"!","!","!","!","!","!","!","!","!","!","!","!","!"]
[null,0,{"$f":"1+B15","v":1},{"$f":"1+C15","v":2},3,4,5,6,7,8,9,10,11,12,"Years"]
["Annual withdrawal",30000,"<-- Input"]
["Number of withdrawals",8,"<-- Input"]
["Number of equal deposits",5,"<-- Input"]
["Interest rate",0.08,"<-- Input"]

{"sheet":"Page 34","index":21,"n_rows":23,"n_cols":19,"rows_kept":23,"cut":false,"hidden":false,"merged":[[0,0,0,5],[6,0,6,5]]}
["A RETIREMENT PROBLEM: SOLVER SOLUTION"]
["Interest",0.08]
["Annual deposit",48000,"<-- Use SOLVER to set ending balance to zero while changing this cell!"]
["Annual retirement withdrawal",30000]
[]
[]
["Note:  This problem has 5 deposits and 8 annual withdrawals, all made at the beginning of the year.  The end of year 12 is the last year of the retirement plan; if the annual deposit is correctly computed, the balance at the end of year 12 after the withdrawal should be zero."]
[]
["Table below uses End of Year framework",null,null,null,null,null,"Time Line:"]
["END of Year","Deposit","Interest\nearned","Account\nBalance",null,null,{"$f":"$B$11","v":48000},{"$f":"$B$12","v":48000},{"$f":"$B$13","v":48000},{"$f":"$B$14","v":48000},{"$f":"$B$15","v":48000},{"$f":"$B$16","v":-30000},{"$f":"$B$17","v":-30000},{"$f":"$B$18","v":-30000},{"$f":"$B$19","v":-30000},{"$f":"$B$20","v":-30000},{"$f":"$B$21","v":-30000},{"$f":"$B$22","v":-30000},{"$f":"$B$23","v":-30000}]
[0,{"$f":"$B$3","v":48000},null,{"$f":"B11","v":48000},{"$f":"getformula(D11)","v":"<--  =B11"},null,"!","!","!","!","!","!","!","!","!","!","!","!","!"]
[1,{"$f":"$B$3","v":48000},{"$f":"D11*$B$2","v":3840},{"$f":"D11+C12+B12","v":99840},{"$f":"getformula(D12)","v":"<--  =D11+C12+B12"},null,0,{"$f":"1+G12","v":1},{"$f":"1+H12","v":2},3,4,5,6,7,8,9,10,11,12]
[2,48000,{"$f":"D12*$B$2","v":7987.2},{"$f":"D12+C13+B13","v":155827.20000000001},{"$f":"getformula(D13)","v":"<--  =D12+C13+B13"}]
[3,48000,12466.176000000001,216293.37600000002]
[4,48000,17303.470080000003,281596.84608000005]
[5,{"$f":"-$B$4","v":-30000},22527.747686400005,274124.59376640007]
[6,{"$f":"-$B$4","v":-30000},21929.967501312007,266054.5612677121]
[7,-30000,21284.364901416968,257338.92616912909]
[8,-30000,20587.114093530326,247926.04026265943]
[9,-30000,19834.083221012756,237760.12348367216]
[10,-30000,19020.809878693773,226780.93336236593]
[11,-30000,18142.474668989275,214923.40803135521]
[12,-30000,17193.872642508417,202117.28067386363,"<-- Ending Balance = FV(12)"]

{"sheet":"Page 38 (Replaced)","index":22,"n_rows":25,"n_cols":15,"rows_kept":25,"cut":false,"hidden":false,"merged":[[0,0,0,5]]}
["A RETIREMENT PROBLEM: FORMULA SOLUTION"]
["Time Line:","    <----- 5-Period Annuity Due ----------------------->",null,null,null,null,"    <--- 8-Period Withdrawal Annuity deferred 4 years from time zero ---------------->"]
[null,{"$f":"$B$18","v":29386.551000693766},{"$f":"$B$18","v":29386.551000693766},{"$f":"$B$18","v":29386.551000693766},{"$f":"$B$18","v":29386.551000693766},{"$f":"$B$18","v":29386.551000693766},{"$f":"-$B$13","v":-30000},-30000,-30000,-30000,-30000,-30000,-30000,-30000]
[null,"!","!","!","!","!","!","!","!","!","!","!","!","!"]
[null,0,{"$f":"1+B5","v":1},{"$f":"1+C5","v":2},3,4,5,6,7,8,9,10,11,12,"Years"]
[null,null,null,null,"FV(4) =",{"$f":"PV(B16,B14,-B13)","v":172399.16831175907},{"$f":"getformula(F6)&\" = Value of withdrawal stream at end of year \"&FIXED(B15,0)-1","v":"<--  =PV(B16,B14,-B13) = Value of withdrawal stream at end of year 4"}]
[null,null,null,null,"PV(0) =",{"$f":"F6/(1+B16)^(B15-1)","v":126718.53530642323},{"$f":"getformula(F7)&\" = Value of withdrawal stream at time zero\"","v":"<--  =F6/(1+B16)^(B15-1) = Value of withdrawal stream at time zero"}]
[]
[]
[]
[]
[]
["Annual withdrawal",30000]
["Number of withdrawals",8]
["Number of equal deposits",5]
["Interest rate",0.08]
["PV of Withdrawals",{"$f":"PV(B16,B14,-B13)/(1+B16)^(B15-1)","v":126718.53530642323},{"$f":"getformula(B17)","v":"<--  =PV(B16,B14,-B13)/(1+B16)^(B15-1)"}]
["Annual deposit needed",{"$f":"PMT(B16,B15,-B17,,1)","v":29386.551000693766},{"$f":"getformula(B18)","v":"<--  =PMT(B16,B15,-B17,,1)"}]
["Answer in One Formula",{"$f":"PMT(B16,B15,-PV(B16,B14,-B13)/(1+B16)^(B15-1),,1)","v":29386.551000693766},{"$f":"getformula(B19)","v":"<--  =PMT(B16,B15,-PV(B16,B14,-B13)/(1+B16)^(B15-1),,1)"}]
[]
["Calculating the amount of the annual payment that leaves"]
["the ending balance zero is easily solved. First calculate the"]
["present value of the withdrawal deferred annuity. Use that"]
["present value to calculate the payment for an annuity due"]
["using the PMT function."]

{"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":12,"n_cols":5,"rows_kept":12,"cut":false,"hidden":false,"merged":[[0,0,0,3]]}
["CONTINUOUS DISCOUNTING"]
["APR -->",0.08,null,"Equivalent APR for Annual Compounding:"]
[null,null,null,{"$f":"EXP(B2)-1","v":8.3287067674958637E-2},{"$f":"getformula(D3)","v":"<--  =EXP(B2)-1"}]
["End of 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 (Continuous)",null,{"$f":"SUM(C5:C9)","v":1124.3483885991679},{"$f":"getformula(C11)","v":"<--  =SUM(C5:C9)"}]
["Present value (Discrete)",null,{"$f":"NPV(D3,B5:B9)","v":1124.3483885991675},{"$f":"getformula(C12)","v":"<--  =NPV(D3,B5:B9)"}]

{"sheet":"Page 41","index":25,"n_rows":26,"n_cols":15,"rows_kept":26,"cut":false,"hidden":false,"merged":[[0,0,0,2],[1,5,1,9],[1,10,1,13],[8,0,8,2],[0,5,0,13]]}
["CALCULATING RETURNS FROM PRICES",null,null,null,null,"REPLACE VALUES IN TABLE BELOW WITH FORMULAS!"]
["Initial deposit",1000,null,null,null,"Discrete: Monthly Compounding",null,null,null,null,"Continuous Compounding & Discounting"]
["End-of-year value",1200,null,null,null,"Value or",0.01,"Disc Rate =",1.0000000000000009E-2,"<-- Monthly",null,"Disc Rate =",9.950330853168092E-3,9.950330853168092E-3,"<-- Monthly"]
["Number of compounding periods",2,null,null,"Month","Price","ROR",null,"PV(Col F)","FV","ROR",null,"PV","FV"]
["Implied APR with compounding",{"$f":"((B3/B2)^(1/B4)-1)*B4","v":0.1908902300206643},{"$f":"getformula(B5)","v":"<--  =((B3/B2)^(1/B4)-1)*B4"},null,0,100,null,100,100,100,null,100,100,100]
["1-Year HPR (Holding Period Return)",{"$f":"B3/B2-1","v":0.19999999999999996},{"$f":"getformula(B6)","v":"<--  =B3/B2-1"},null,1,101,1.0000000000000009E-2,null,100,101,9.950330853168092E-3,null,100,101]
["Continuous rate of return",{"$f":"LN(B3/B2)","v":0.18232155679395459},{"$f":"getformula(B7)","v":"<--  =LN(B3/B2)"},null,2,102.01,1.0000000000000009E-2,null,100,102.01,9.950330853168092E-3,null,100,102.01]
["EAR (Effective Annual Rate)",{"$f":"B3/B2-1","v":0.19999999999999996},{"$f":"getformula(B8)","v":"<--  =B3/B2-1"},null,3,103.0301,1.0000000000000009E-2,null,100.00000000000001,103.03009999999999,9.950330853168092E-3,null,100,103.03010000000002]
["Implied annual interest rate with n compounding periods",null,null,null,4,104.060401,1.0000000000000009E-2,null,100,104.060401,9.950330853168092E-3,null,99.999999999999986,104.060401]
["Number of compounding periods","APR",null,null,5,105.10100500999999,1.0000000000000009E-2,null,100,105.10100500999999,9.950330853168092E-3,null,99.999999999999986,105.10100501000001]
[null,{"$f":"B5","v":0.1908902300206643},{"$f":"getformula(B11)&\", data table formula cell\"","v":"<--  =B5, data table formula cell"},null,6,106.1520150601,1.0000000000000009E-2,null,99.999999999999986,106.15201506010001,9.950330853168092E-3,null,100,106.15201506010001]
[1,0.19999999999999996,null,null,7,107.213535210701,1.0000000000000009E-2,null,100.00000000000001,107.21353521070098,9.950330853168092E-3,null,99.999999999999986,107.21353521070101]
[2,0.1908902300206643,null,null,8,108.28567056280801,1.0000000000000009E-2,null,99.999999999999986,108.28567056280802,9.950330853168092E-3,null,99.999999999999986,108.28567056280802]
[4,0.18654055756842247,null,null,9,109.36852726843608,1.0000000000000009E-2,null,99.999999999999972,109.36852726843611,9.950330853168092E-3,null,99.999999999999986,109.36852726843611]
[8,0.1844150017637034,null,null,10,110.46221254112045,1.0000000000000009E-2,null,99.999999999999972,110.46221254112048,9.950330853168092E-3,null,100,110.46221254112045]
[20,0.18315511655226047,null,null,11,111.56683466653166,1.0000000000000009E-2,null,100,111.56683466653166,9.950330853168092E-3,null,100,111.56683466653166]
[1000,0.18233817837920441,null,null,12,112.68250301319698,1.0000000000000009E-2,112.68250301319698,100,112.68250301319698,9.950330853168092E-3,112.68250301319698,99.999999999999986,112.68250301319698]
[null,null,null,null,null,"Annual",0.12000000000000011,0.12682503013196977,null,"Continuous-->",0.11940397023801713,0.11940397023801703,"<-- Annual"]
["APR (Annual Percentage Rate) is a \"Nominal\" or \"Stated\" rate for one year.",null,null,null,null,"Discrete","APR","HPR/EAR",null,null,null,null,0.12682503013196977,"<-- EAR"]
["           If the APR is 12% the monthly rate is 1%."]
[null,null,null,null,null,"EAR =",0.12682503013196977]
["HPR (Holding Period Return) is the rate earned over some arbitrary period.",null,null,null,null,"Monthly ROR =",1.0000000000000009E-2]
["          With HPR the starting and ending values ignore elapsed time.",null,null,null,null,"APR =",0.12]
[null,"HPR = Ending Value/Starting Value -1 =",{"$f":"B3/B2-1","v":0.19999999999999996}]
[]
["EAR is the one-year HPR (same as IRR using one-year periods) = ",null,null,{"$f":"C24","v":0.19999999999999996}]

{"sheet":"Page 42","index":26,"n_rows":10,"n_cols":3,"rows_kept":10,"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)"}]
["Wrong -->",{"$f":"IRR(B3:B7,0.15)","v":7.9789912969554511E-2},{"$f":"getformula(B10)","v":"<--  =IRR(B3:B7,0.15)"}]

{"sheet":"Page 43","index":27,"n_rows":13,"n_cols":7,"rows_kept":13,"cut":false,"hidden":false,"merged":[[0,0,0,2],[12,0,12,2]]}
["USING XNPV TO COMPUTE THE NET PRESENT VALUE",null,null,"Daily rate",{"$f":"RATE(365,0,-1,1+B2)","v":3.1053775566129563E-4},{"$f":"getformula(E1)","v":"<--  =RATE(365,0,-1,1+B2)"}]
["Annual discount rate",0.12,"<-- EAR!","Daily rate",{"$f":"(1+B2)^(1/365)-1","v":3.1053775565537123E-4},{"$f":"getformula(E2)","v":"<--  =(1+B2)^(1/365)-1"},"Using"]
[null,null,null,"Table below uses the daily rate (above):",null,null,"PV"]
["Date","Cash flow",null,"Day","PV",null,"Function"]
["2014-01-01",-1000,null,{"$f":"A5-$A$5","v":0},{"$f":"B5*(1+$E$2)^-D5","v":-1000},{"$f":"getformula(E5)","v":"<--  =B5*(1+$E$2)^-D5"},{"$f":"PV($E$2,D5,0,-B5)","v":-1000}]
["2015-03-03",100,null,{"$f":"A6-$A$5","v":426},{"$f":"B6*(1+$E$2)^-D6","v":87.610568724514195},{"$f":"getformula(E6)","v":"<--  =B6*(1+$E$2)^-D6"},{"$f":"PV($E$2,D6,0,-B6)","v":87.610568724514195}]
["2015-07-04",195,null,549,164.43918343416277,"<--  =B7*(1+$E$2)^-D7",164.43918343416277]
["2016-10-12",350,null,1015,255.38858869355298,"<--  =B8*(1+$E$2)^-D8",255.38858869355298]
["2017-12-25",800,null,1454,509.36248976993613,"<--  =B9*(1+$E$2)^-D9",509.36248976993613]
[null,null,null,null,{"$f":"SUM(E5:E9)","v":16.800830622166018},{"$f":"getformula(E10)","v":"<--  =SUM(E5:E9)"}]
["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]