{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Goal-based investing","Typical Goal Planner"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":54,"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":"Goal-based investing","index":0,"n_rows":50,"n_cols":23,"rows_kept":50,"cut":false,"hidden":false,"merged":[[2,2,2,11],[10,9,10,11],[3,6,3,11],[0,9,0,10],[4,10,4,11]]}
["Steps","Goal Planner (enable macros). When you enter data in few cells the file will appear unresponsive as it is",null,null,null,null,null,null,null,"Fill only green cells",null,null,null,null,null,null,null,null,1,"beginning of month"]
[null,"busy calculating the rate of return required for accomplishing the goal via a macro and Excel's 'goal seek' function",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,"end of the month"]
["1a","Name of the goal (for reference)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(typeg=1,(((12*((1+ByChangingCell)^(yearsg)-(1+incg+0.00001%)^(yearsg)))*(1+ByChangingCell))*sip/(ByChangingCell-incg+0.00001%))+fvcurr+(((12*((1+ByChangingCell)^(yearsg-yearsinc)-(1+0.00001%)^(yearsg-yearsinc)))*(1+ByChangingCell))*sipinc/(ByChangingCell+0.00001%)),(((12*((1+ByChangingCell)^(yearsg)-(1+incg+0.00001%)^(yearsg))))*sip/(ByChangingCell-incg+0.00001%))+fvcurr+(((12*((1+ByChangingCell)^(yearsg-yearsinc)-(1+0.00001%)^(yearsg-yearsinc))))*sipinc/(ByChangingCell+0.00001%)))","v":14122927.696020849},0,"Net rate of return",null,6.8000000000000005E-2]
["1b","Years to goal",12,"Identify the goal and its time-frame",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0.1]
["1c","Present cost",4500000,"Find out as accurately as possible how much it will cost today ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0.2,"Yes"]
["1d","Amt invested so far",2500000,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(typeg=1,(((12*((1+ByChangingCell)^(yearsg)-(1+incg+0.00001%)^(yearsg)))*(1+ByChangingCell))*sip/(ByChangingCell-incg+0.00001%))+fvcurr,(((12*((1+ByChangingCell)^(yearsg)-(1+incg+0.00001%)^(yearsg))))*sip/(ByChangingCell-incg+0.00001%))+fvcurr)","v":14122927.696020849},0.3,"No"]
["1e","RoI of current invest.",0.1,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,8.532231954965197E-2]
["1f","Future value of curr. Inv.",{"$f":"C6*(1+C7)^C4","v":7846070.9418025063},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"initial mon. invest. reqd.",null,{"$f":"IF(typeg=1,(gcorpus-fvcurr)*(retg-incg+0.00001%)/((12*((1+retg)^(yearsg)-(1+incg+0.00001%)^(yearsg)))*(1+retg)),(gcorpus-fvcurr)*(retg-incg+0.00001%)/((12*((1+retg)^(yearsg)-(1+incg+0.00001%)^(yearsg)))))","v":29586.759018830333}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"postpone invest. (years)",null,0]
[2,"Expected inflation",0.1,"Assume a reasonable inflation rate (not the historical average). The higher the safer.",null,null,null,null,null,null,null,null,null,null,null,null,null,null," if postponed, pm invest.",null,{"$f":"IF(typeg=1,(gcorpus-fvcurr)*(retg-incg+0.00001%)/((12*((1+retg)^(yearsg-yearsp)-(1+incg+0.00001%)^(yearsg-yearsp)))*(1+retg)),\n(gcorpus-fvcurr)*(retg-incg+0.00001%)/((12*((1+retg)^(yearsg-yearsp)-(1+incg+0.00001%)^(yearsg-yearsp)))))","v":29586.759018830333}]
[3,"Future Cost or Goal Target",{"$f":"C5*(1+C10)^C4","v":14122927.695244512},"determine how much the goal would cost at the time of need",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(typeg=1,(((12*((1+ByChangingCell)^(yearsg)-(1+incg+0.00001%)^(yearsg)))*(1+ByChangingCell))*sip/(ByChangingCell-incg+0.00001%))+fvcurr+(((12*((1+ByChangingCell)^(yearsg-yearsinc)-(1+incg+0.00001%)^(yearsg-yearsinc)))*(1+ByChangingCell))*sipinc/(ByChangingCell-incg+0.00001%)),(((12*((1+ByChangingCell)^(yearsg)-(1+incg+0.00001%)^(yearsg))))*sip/(ByChangingCell-incg+0.00001%))+fvcurr+(((12*((1+ByChangingCell)^(yearsg-yearsinc)-(1+incg+0.00001%)^(yearsg-yearsinc))))*sipinc/(ByChangingCell-incg+0.00001%)))","v":14122927.696020849}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"C47*equityalloc+C25*debtalloc","v":8.3999999999999991E-2}]
[4,"How much can you invest per month",28000,"Taking into account: expenses, loans, investment towards retirement (always the no. 1 goal)."]
["5a","Annual inc. in monthly invest. %"]
["5b","If you have loans or other commitments at present and can contribute more towards this goal after a few years then"]
["5c","Expected  additional mon. invest.",null,"years from now this increase is likely to occur",null,null,null,null,null,"must be less than 'years to goal'"]
[null,"If not applicable ensure this is 'zero'"]
["5d","When investment are made",0,"Choose '0' if SIP occurs at month beginning and '1' if SIP occurs at month end**"]
[null,null,null,"** 1st 10 days can be taken as 'month beginning' and last 10 days as 'month end' approx."]
[null,null,null,"If the SIP occurs in the middle 10 days then it does not matter which option you choose!"]
[]
[6,"Average rate of interest required ",7.7346645632463115E-2,null,"This is an output. It is quite natural for it to flicker if cell entries are chaned"]
[null,null,null,null,"If it doesn't change then save your work and reopen the file and try again"]
[]
[7,"Post-tax return from  fixed income",0.06,"If you have no taxable fixed income, use about 7-8%"]
[null,"If you have some taxable fixed income, use proportionately lower.  Pessimism is key to a realistic plan"]
[]
[]
["8a","Allocation to equity",0.6,"Equity","50-70% for goals 15 or more years away; "]
["8b","Allocation to debt (output)",{"$f":"1-equityalloc","v":0.4},"Allocation","40-50% for goals about 10 years away;"]
[null,null,null,null,"20-30% for goals 7 years or so away and 0-10% for lower durations"]
[]
[9,"Expected return from equity",{"$f":"(ByChangingCell-debtalloc*C25)/equityalloc","v":8.8911076054105198E-2},null,"Anything above 12% is impractical irrespective of duration"]
[null,"If equity return is too small then for reasonable inflation rate",null,null,"if you don't have the expertise to manage equity"]
[null,"you can afford to decrease your montly invesment",null,null,"12% is okay only for periods well above 10 years"]
[null,"If its too high then you will have to increase your monthly",null,null,"9-10% for periods less than 10 years is what I would use."]
[null,"investment or decrease your goal amount"]
[]
[10,"If equity return is anything more than 12%, then irrespective of duration and perhaps expertise, it is"]
[null,"best to increase investment in one of the following ways:"]
[null,"Initial monthly investment could be increased and/or"]
[null,"% by which investment will increase annually (from year 2) can be increased and/or"]
[null,"Consider additional investment a few years down the line."]
[11,"If equity returns are unreasonable depsite increase investment as much as possible"]
[null,"consider postponent"]
[12,"if postponement is not possible the estimate the shortfall"]
["12a","Expected return from equity",0.1,"Statuatory warning: garbage in, garbage out!"]
["12b","Expected corpus ",{"$f":"IF(typeg=1,(((12*((1+estimate)^(yearsg)-(1+incg+0.00001%)^(yearsg)))*(1+estimate))*sip/(estimate-incg+0.00001%))+fvcurr+(((12*((1+estimate)^(yearsg-yearsinc)-(1+0.00001%)^(yearsg-yearsinc)))*(1+estimate))*sipinc/(estimate+0.00001%)),(((12*((1+estimate)^(yearsg)-(1+incg+0.00001%)^(yearsg))))*sip/(estimate-incg+0.00001%))+fvcurr+(((12*((1+estimate)^(yearsg-yearsinc)-(1+0.00001%)^(yearsg-yearsinc))))*sipinc/(estimate+0.00001%)))","v":14375646.350118592},"All inputs except equity returns are the same. You can change them to see the effect on expected corpus"]
["12c","Deviation from expected corpus",{"$f":"(C48-gcorpus)/gcorpus","v":1.7894211478486612E-2},"Shortfall only if negative"]
[null,"Expected corpus falls short by",{"$f":"C48/gcorpus","v":1.0178942114784866}]

{"sheet":"Typical Goal Planner","index":1,"n_rows":17,"n_cols":13,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["This is an example of a typical goal planner where the average rate of return is an input. "]
["Goal Planner (annual compounding)"]
["Years to goal",3,"Fill only green cells"]
["Present cost",43691.697,null,null,null,null,null,null,null,null,null,1,"beginning of month"]
["inflation",0,null,null,null,null,null,null,null,null,null,0,"end of the month"]
["Net rate of return",0.1]
["Futire Cost",{"$f":"B4*(1+B5)^B3","v":43691.697}]
["Amt invested so far",0]
["RoI of current invest.",0]
["Future value of curr. Inv.",{"$f":"B8*(1+B9)^B3","v":0}]
["Annual inc. in monthly invest. %",0]
["When investment are made",1,"Choose '0' if SIP occurs at month beginning and '1' if SIP occurs at month end**"]
["initial mon. invest. reqd.",{"$f":"IF(typeg=1,(gcorpus-fvcurr)*(retg-incg+0.00001%)/((12*((1+retg)^(yearsg)-(1+incg+0.00001%)^(yearsg)))*(1+retg)),(gcorpus-fvcurr)*(retg-incg+0.00001%)/((12*((1+retg)^(yearsg)-(1+incg+0.00001%)^(yearsg)))))","v":999.99497142501593}]
["postpone invest. (years)",0]
[" if postponed, pm invest.",{"$f":"IF(typeg=1,(gcorpus-fvcurr)*(retg-incg+0.00001%)/((12*((1+retg)^(yearsg-yearsp)-(1+incg+0.00001%)^(yearsg-yearsp)))*(1+retg)),\n(gcorpus-fvcurr)*(retg-incg+0.00001%)/((12*((1+retg)^(yearsg-yearsp)-(1+incg+0.00001%)^(yearsg-yearsp)))))","v":999.99497142501593}]
["** 1st 10 days can be taken as 'month beginning' and last 10 days as 'month end' approximately."]
["If the SIP occurs in the middle 10 days then it does not matter which option you choose!"]