{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Goal Return Optimizer"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":45,"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 Return Optimizer","index":0,"n_rows":100,"n_cols":36,"rows_kept":100,"cut":false,"hidden":false,"merged":[[4,3,4,7],[0,0,0,9],[32,0,32,2],[33,0,33,2],[53,2,53,4],[54,2,54,4],[43,3,43,7],[33,7,34,7],[45,3,45,7],[44,3,44,7],[5,3,5,7],[7,3,7,9],[6,3,6,7],[30,2,30,4],[31,2,31,4],[9,3,9,7],[8,3,8,7],[12,0,12,9],[62,0,62,7],[52,0,52,9],[53,6,53,8],[54,6,54,8],[47,7,48,7],[47,3,48,5],[47,0,47,1],[55,5,55,9]]}
["Retirement Planner (Read and enter data row by row. Fill only cells in green)",null,null,null,null,null,null,null,null,null,null,0.10521049311818602,"Year","Monthly",null,null,"Additional","Withdrawal","Corpus",null,null,null,null,null,null,null,"equity investing stops",0,null,0,null,0,null,0,null,0]
["No of years you expect to work",20,null,"Current monthly expenses that will persist in retirement",null,null,null,null,null,30000,null,{"$f":"g0-epf","v":46481858.476186574},null,"investment",null,null,"investment",null,null,null,null,null,null,null,null,null,"max equity investing years",{"$f":"B15-AB1","v":10},null,{"$f":"D15-AD1","v":12},null,{"$f":"F15-AF1","v":14},null,{"$f":"H15-AH1","v":16},null,{"$f":"J15-AJ1","v":18}]
["Annual expenses that will persist in retirement ",0,null,"factor in new expenses in retirement (eg. health) as a %",null,null,null,null,null,0,null,{"$f":"wy","v":20},1,{"$f":"IF(M3=0,0,invest*(1+increment)^(M3-1))","v":27000},{"$f":"IF(ISERROR(IF(M3=\"\",\"\",IF(M3=loany1,loansip1,IF(O2=loansip1,loansip1,0)))+IF(M3=\"\",\"\",IF(M3=loany2,loansip2,IF(P2=loansip2,loansip2,0)))),\"\",IF(M3=\"\",\"\",IF(M3=loany1,loansip1,IF(O2=loansip1,loansip1,0)))+IF(M3=\"\",\"\",IF(M3=loany2,loansip2,IF(P2=loansip2,loansip2,0))))","v":0},{"$f":"IF(M3=\"\",\"\",IF(M3=loany2,loansip2,IF(P2=loansip2,loansip2,0)))","v":0},{"$f":"IF(ISERROR(IF(M3=0,0,IF(M3=loany1,loansip1,IF(O2=loansip1,loansip1,0)))+IF(M3=0,0,IF(M3=loany2,loansip2,IF(P2=loansip2,loansip2,0)))),\"\",IF(M3=0,0,IF(M3=loany1,loansip1,IF(O2=loansip1,loansip1,0)))+IF(M3=0,0,IF(M3=loany2,loansip2,IF(P2=loansip2,loansip2,0))))","v":0},{"$f":"IF(M3=ygoal1,goal1,IF(M3=ygoal2,goal2,IF(M3=ygoal3,goal3,IF(M3=ygoal4,goal4,IF(M3=ygoal5,goal5,0)))))","v":0},{"$f":"IF(M3=0,0,((N3+Q3)*12+(S2-R2))*(1+interest))","v":358088.19977029227},null,null,null,null,null,null,null,"* that is less than ",{"$f":"wy","v":20},null," years"]
["Expected inflation after retirement",0.08,null,"Expected inflation upto retirement (do not underestimate)",null,null,null,null,null,0.09,null,{"$f":"B29","v":27000},{"$f":"IF(M3=wy,0,IF(M3=0,0,IF(M3>=wy,\"\",M3+1)))","v":2},29700.000000000004,{"$f":"IF(M4=\"\",\"\",IF(M4=loany1,loansip1,IF(O3=loansip1,loansip1,0)))","v":0},0,0,0,789659.8555952498,null,null,null,null,null,null,null,null,{"$f":"IF(B15>=AB3,1,IF(D15>=AB3,1,IF(F15>=AB3,1,IF(H15>=AB3,1,IF(J15>=AB3,1,0)))))","v":0}]
["Years after retirement you expect to live!",31,null,"Anticipated post-retirement rate of interest",null,null,null,null,null,0.08,null,null,3,32670.000000000004,0,0,0,0,1306027.0801201155,null,null,null,null,null,null,null,"* less than ",{"$f":"wy","v":20}," years",null,null,null,0]
["Your monthly PF contribution*",18000,null,"projected annual increase in monthly contribution %",null,null,null,null,null,0.05,null,null,4,35937.000000000015,0,0,0,0,1920050.227139517,null,null,null,null,null,null,null,{"$f":"CONCATENATE(AA5,AB5,AC5)","v":"* less than 20 years"},null,null,null,null,null,0.1]
["* (It is assumed your employer matches this)",null,null,"Projected EPF corpus when you retire (EPS corpus is not included)**",null,null,null,null,null,{"$f":"B8*(1+B9)^wy+((((1+IF(B9=J6,B9+0.00001%,B9))^wy)-((1+J6)^wy))/(IF(B9=J6,B9+0.00001%,B9)-J6))*12*(B6+B6*3.67%)","v":24307566.176457167},null,null,5,39530.700000000012,30000,0,30000,0,3044212.369154783,null,null,null,null,null,null,null,null,null,null,null,null,null,0.2]
["Current EPF balance",2000000,null,"** Only 3.67% of employers contributions goes to EPF. Rest goes to EPS to generate pension",null,null,null,null,null,null,null,null,6,43483.770000000011,30000,0,30000,0,4339075.8578046402,null,null,null,null,null,null,null,null,null,null,null,null,null,0.3]
["average EPF rate of interest %",0.08,null,"Estimated tax rate on monthy pension",null,null,null,null,null,{"$f":"(1+3%)*IF(expenses*(1+addexp)*12<=200000,0,IF(expenses*(1+addexp)*12<=500000,(expenses*(1+addexp)*12-200000)*10%,IF(expenses*(1+addexp)*12<=1000000,30000+(expenses*(1+addexp)*12-500000)*20%,30000+500000*20%+(expenses*(1+addexp)*12-1000000)*30%)))/(expenses*(1+addexp)*12)","v":4.5777777777777778E-2},null,null,7,47832.147000000019,30000,0,30000,0,5827843.0352772893]
["Average current expenses per month",{"$f":"J2+(B3/12)","v":30000},null,"Post-tax corpus (incl. EPF) reqd for monthly annuity (inflation-indexed) for life",null,null,null,null,null,{"$f":"PV((1+retroi)/(1+inf)-1,k,-prepen1*12,,1)","v":70789424.65264374},null,null,8,52615.36170000003,30000,0,30000,0,7536681.6505573308]
["Pre-tax monthly pension in 1st year of retirement",{"$f":"(expenses+J3*expenses)*(1+preinf)^(wy)*(1+inf)^(1)/(1-J9)","v":190294.15229205307},null,"Estimate annual increase in monthly investments you can afford (non-EPF)",null,null,null,null,null,0.1,null,null,9,57876.89787000003,30000,0,30000,0,9495089.2790304441]
[null,null,null,null,null,null,null,null,null,null,null,null,10,63664.58765700004,30000,30000,60000,3890613.6901500029,12134177.103146242]
["Goal Planner  (Fill only cells in green)",null,null,null,null,null,null,null,null,null,null,null,11,70031.046422700048,30000,30000,60000,0,10835412.907978682]
["Enter name of the goal for your reference","Goal 1",null,"Goal 2",null,"Goal 3",null,"Goal 4",null,"Goal 5",null,null,12,77034.151064970065,30000,30000,60000,4707642.5650815042,13792831.023236848]
["Years to goal (has to be before retirement)*",10,null,12,null,14,null,16,null,18,null,null,13,84737.566171467071,30000,30000,60000,0,11960631.338482639]
["Present cost",1500000,null,1500000,null,0,null,121212,null,0,null,null,14,93211.322788613776,30000,30000,60000,0,15250984.398935148]
["inflation",0.1,null,0.1,null,0.1,null,0.1,null,7.0000000000000007E-2,null,null,15,102532.45506747518,30000,30000,60000,0,19011138.885838881]
["Future Cost",{"$f":"B16*(1+B17)^ygoal1","v":3890613.6901500029},null,{"$f":"D16*(1+D17)^ygoal2","v":4707642.5650815042},null,{"$f":"F16*(1+F17)^ygoal3","v":0},null,{"$f":"H16*(1+H17)^ygoal4","v":556965.8656223315},null,{"$f":"J16*(1+J17)^ygoal5","v":0},null,null,16,112785.70057422269,30000,30000,60000,556965.8656223315,23302885.014781207]
[{"$f":"CONCATENATE(AA3,AB3,AD3)","v":"* that is less than 20 years"},"It is recommended that you use this optimizer for long-term goals only  (10 years or more away) ",null,null,null,null,null,null,null,null,null,null,17,124064.27063164496,30000,30000,60000,0,27580185.678991146]
[null,null,null,null,null,null,null,null,null,null,null,null,18,136470.69769480947,30000,30000,60000,0,33087608.334759593]
["Let us assume you can spare a reasonably fixed amount each month from your take-home pay for all your financial goals.The idea is to determine the",null,null,null,null,null,null,null,null,null,null,null,19,150117.76746429043,30000,30000,60000,0,39355464.26046627]
["rate of return required to meet all your financial goals assuming you keep investing regularly from now to retirement and withdraw the necessary amounts",null,null,null,null,null,null,null,null,null,null,null,20,165129.5442107195,30000,30000,60000,0,46481858.477076299]
["needed for other goals as and when you need them. The rate of return is the average return of equity and debt instruments. If this average return is reasonable",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["you stand a decent chance of achieving your goals. If is too high then you will need to consider:",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["1) using other assets or other means to meet some goals.",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["2) postpone some goals or reduce your expectations.",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["3) You could try decreasing inflation but this may not practical.",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["Inputs Required",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["Total amount you can spare each month",27000,null,"% by which you afford to increase the monthly investment each year",null,null,null,null,null,0.1,null,null,0,0,30000,30000,0,0,0]
["Can you increase more after a few years due to closure of loans? You can include details of two loans",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["Extra amount you can invest per month",30000,"after",null,null,5,{"$f":"CONCATENATE(\"years (before retirement) \",\"i.e. \",AB3,AD3)","v":"years (before retirement) i.e. 20 years"},null,null,null,null,null,0,0,30000,30000,0,0,0]
["Extra amount you can invest per month",30000,"after",null,null,10,{"$f":"CONCATENATE(\"years (before retirement) \",\"i.e. \",AB3,AD3)","v":"years (before retirement) i.e. 20 years"},null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,{"$f":"L1","v":0.10521049311818602},null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,{"$f":"IF(M35=0,0,invest*(1+increment)^(M35-1))","v":0},30000,{"$f":"IF(M35=\"\",\"\",IF(M35=loany2,loansip2,IF(P34=loansip2,loansip2,0)))","v":30000},{"$f":"IF(ISERROR(IF(M35=0,0,IF(M35=loany1,loansip1,IF(O34=loansip1,loansip1,0)))+IF(M35=0,0,IF(M35=loany2,loansip2,IF(P34=loansip2,loansip2,0)))),\"\",IF(M35=0,0,IF(M35=loany1,loansip1,IF(O34=loansip1,loansip1,0)))+IF(M35=0,0,IF(M35=loany2,loansip2,IF(P34=loansip2,loansip2,0))))","v":0},{"$f":"IF(M35=ygoal1,goal1,IF(M35=ygoal2,goal2,IF(M35=ygoal3,goal3,IF(M35=ygoal4,goal4,IF(M35=ygoal5,goal5,0)))))","v":0},{"$f":"IF(M35=0,0,((N35+Q35)*12+(S34-R34))*(1+interest))","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(M35=wy,0,IF(M35=0,0,IF(M35>=wy,\"\",M35+1)))","v":0},0,{"$f":"IF(M36=\"\",\"\",IF(M36=loany1,loansip1,IF(O35=loansip1,loansip1,0)))","v":30000},30000,0,0,0]
["This is average rate of return required to meet all your financial goals, for the amount you can invest. Is this reasonable?",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["Assuming you are using this only for long term goals only (10 years or more away), you will need about 70% equity exposure and  30% exposure",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["Let us assume that we expect a pre-tax return of about 8% from debt instruments. We could then calculate return needed from equities.",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["If the equity return is much more than 12% then, in my view,  it is not reasonable. I would recommend modifying goal parameters to bring this down.",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["Less then 12% equity return is a good result to have. Does not mean you are guaranteed of success. It just means you are less likely to fail!",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["Equity allocation",0.7,null,"Rest is assumed to be invested in debt instruments",null,null,null,null,null,{"$f":"1-B44","v":0.30000000000000004},null,null,0,0,30000,30000,0,0,0]
["Rest is assumed to be invested in debt instrum.",{"$f":"1-B44","v":0.30000000000000004},null,"Expected return from debt instrument (not more than 8-9%)",null,null,null,null,null,0.08,null,null,0,0,30000,30000,0,0,0]
["Tax rate applicable to debt instrument",0.2,null,"Approximate post-tax return from debt instruments",null,null,null,null,null,{"$f":"retdeb*(1-IF(tax=10%,10.3%,IF(tax=20%,20.6%,IF(tax=30%,30.9%))))","v":6.3520000000000007E-2},null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["The projected cash flow is shown in the adjoining chart",null,null,"Expected return from equity instruments",null,null,null,{"$f":"(H34-(J44*J46))/B44","v":0.12307784731169431},null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["Remember: Equity return of more than 12% is not practical irrespective of duration and perhaps expertise. For durations of about 10 years or so I would expect",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
["no more than about 10% from equity.",null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,{"$f":"IF(M67=0,0,invest*(1+increment)^(M67-1))","v":0},30000,{"$f":"IF(M67=\"\",\"\",IF(M67=loany2,loansip2,IF(P66=loansip2,loansip2,0)))","v":30000},{"$f":"IF(ISERROR(IF(M67=0,0,IF(M67=loany1,loansip1,IF(O66=loansip1,loansip1,0)))+IF(M67=0,0,IF(M67=loany2,loansip2,IF(P66=loansip2,loansip2,0)))),\"\",IF(M67=0,0,IF(M67=loany1,loansip1,IF(O66=loansip1,loansip1,0)))+IF(M67=0,0,IF(M67=loany2,loansip2,IF(P66=loansip2,loansip2,0))))","v":0},{"$f":"IF(M67=ygoal1,goal1,IF(M67=ygoal2,goal2,IF(M67=ygoal3,goal3,IF(M67=ygoal4,goal4,IF(M67=ygoal5,goal5,0)))))","v":0},{"$f":"IF(M67=0,0,((N67+Q67)*12+(S66-R66))*(1+interest))","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(M67=wy,0,IF(M67=0,0,IF(M67>=wy,\"\",M67+1)))","v":0},0,{"$f":"IF(M68=\"\",\"\",IF(M68=loany1,loansip1,IF(O67=loansip1,loansip1,0)))","v":30000},30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,{"$f":"IF(M99=0,0,invest*(1+increment)^(M99-1))","v":0},30000,30000,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0,30000,30000,0,0,0]