{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Read me first","Rebalancing Illustration","Annual Rebalancing Lumpsum","Annual Rebalancing SIP"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":148,"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":"Read me first","index":0,"n_rows":11,"n_cols":1,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["Read me first"]
["1. Equity returns assumed are historical sensex returns computed for each financial year"]
["2. Debt returns are historcial FD returns from RBI website"]
["3. The last few debt returns (in red) have been added by hand as I could not get hold of actual data"]
["4. Rebalancing a porfolio may involve transaction costs especially in SIP mode if the holding period of equity is less than an year"]
["5. Debt instruments are taxable* so the final corpus will be lower in both cases and this tax may impact the benefit of rebalancing"]
["6. The rebalancing shown here is done during the growth phase of the portfolio when the goal if far away"]
["7. Close to goal (2-3 year) the equity component will be gradually or abruptly decreased and pushed to debt"]
["8. The above step does not constitute rebalancing is the sense that it is usually used"]
["9. The tool is only an indicator you would need to make a suitable choice depending on your personal situation."]
["* If the direct tax code comes to effect long term equity investments will also become taxable "]

{"sheet":"Rebalancing Illustration","index":1,"n_rows":27,"n_cols":15,"rows_kept":27,"cut":false,"hidden":false,"merged":[[3,11,3,12],[3,13,3,14],[2,11,2,14],[11,11,11,14],[12,11,12,12],[12,13,12,14]]}
["This is an illustration of portfolio rebalancing as found at"]
["http://www.jagoinvestor.com/2008/07/portfolio-rebalancing-today-i-am-going.html"]
["An investment of 1 lakhs is considered. Of this 30% is invested in equity and 70% in debt instruments",null,null,null,null,null,null,null,null,null,null,"Asset Allocation"]
["No Rebalancing. No futher changes are then made. The money is allowed to grow.",null,null,null,null,null,null,null,null,null,null,"Before investment",null,"After Investment."]
["Year","Sensex","investment","Total Equity",null,"FD","investment","Total FD","Total Corpus","Equity","Debt","Equity","Debt","Equity","Debt"]
[null,"return ",null,null,null,"return"]
[1,0.3,30000,{"$f":"IF(A7=0,0,(C7+D6)*(1+B7))","v":39000},null,0.09,70000,{"$f":"IF(A7=0,0,(G7+H6)*(1+F7))","v":76300},{"$f":"D7+H7","v":115300},{"$f":"IF(A7=0,0,D7/(D7+H7))","v":0.33824804856895058},{"$f":"IF(A7=0,0,H7/(D7+H7))","v":0.66175195143104948},{"$f":"C7/(C7+G7)","v":0.3},{"$f":"G7/(C7+G7)","v":0.7},{"$f":"D7/I7","v":0.33824804856895058},{"$f":"H7/I7","v":0.66175195143104948}]
[2,-0.35,{"$f":"IF(A8=0,0,(D7))","v":39000},{"$f":"IF(A8=0,0,(C8)*(1+B8))","v":25350},null,0.09,{"$f":"IF(A8=0,0,(H7) )","v":76300},{"$f":"IF(A8=0,0,(G8)*(1+F8))","v":83167},{"$f":"D8+H8","v":108517},{"$f":"IF(A8=0,0,D8/(D8+H8))","v":0.2336039514546108},{"$f":"IF(A8=0,0,H8/(D8+H8))","v":0.76639604854538923},{"$f":"C8/(C8+G8)","v":0.33824804856895058},{"$f":"G8/(C8+G8)","v":0.66175195143104948},{"$f":"D8/I8","v":0.2336039514546108},{"$f":"H8/I8","v":0.76639604854538923}]
[3,0.4,{"$f":"IF(A9=0,0,(D8))","v":25350},{"$f":"IF(A9=0,0,(C9)*(1+B9))","v":35490},null,0.09,{"$f":"IF(A9=0,0,(H8) )","v":83167},{"$f":"IF(A9=0,0,(G9)*(1+F9))","v":90652.030000000013},{"$f":"D9+H9","v":126142.03000000001},{"$f":"IF(A9=0,0,D9/(D9+H9))","v":0.28134952323186807},{"$f":"IF(A9=0,0,H9/(D9+H9))","v":0.71865047676813198},0.2336039514546108,0.76639604854538923,0.28134952323186807,0.71865047676813198]
[4,0.6,35490,56784,null,0.09,{"$f":"IF(A10=0,0,(H9) )","v":90652.030000000013},{"$f":"IF(A10=0,0,(G10)*(1+F10))","v":98810.712700000018},{"$f":"D10+H10","v":155594.71270000003},{"$f":"IF(A10=0,0,D10/(D10+H10))","v":0.36494813361354012},{"$f":"IF(A10=0,0,H10/(D10+H10))","v":0.63505186638645983},0.28134952323186807,0.71865047676813198,0.36494813361354012,0.63505186638645983]
[5,-0.3,56784,39748.799999999996,null,0.09,{"$f":"IF(A11=0,0,(H10) )","v":98810.712700000018},{"$f":"IF(A11=0,0,(G11)*(1+F11))","v":107703.67684300002},{"$f":"D11+H11","v":147452.47684300001},{"$f":"IF(A11=0,0,D11/(D11+H11))","v":0.26957024290831355},{"$f":"IF(A11=0,0,H11/(D11+H11))","v":0.73042975709168645},0.36494813361354012,0.63505186638645983,0.26957024290831355,0.73042975709168645]
[null,null,null,null,null,null,null,null,null,null,null,"Asset Allocation"]
["Rebalancing with 30% equity and 70% debt",null,null,null,null,null,null,null,null,null,null,"Before investment",null,"After Investment."]
["Each year 30% of total corpus earned is invested in equity and 70% in debt irrespective of returns earned the previous year",null,null,null,null,null,null,null,null,null,null,"Equity","Debt","Equity","Debt"]
["Year","Sensex","Investment","Total Equity",null,"FD","Investment","Total","Total Corpus","Equity","Debt"]
[null,"return ",null,null,null,"return"]
[1,0.3,30000,{"$f":"IF(A17=0,0,(C17)*(1+B17))","v":39000},null,0.09,70000,{"$f":"IF(A17=0,0,(G17)*(1+F17))","v":76300},{"$f":"D17+H17","v":115300},{"$f":"IF(A17=0,0,D17/(D17+H17))","v":0.33824804856895058},{"$f":"IF(A17=0,0,H17/(D17+H17))","v":0.66175195143104948},{"$f":"C17/(C17+G17)","v":0.3},{"$f":"G17/(C17+G17)","v":0.7},{"$f":"D17/I17","v":0.33824804856895058},{"$f":"H17/I17","v":0.66175195143104948}]
[2,-0.35,{"$f":"IF(A18=0,0,(30%*(D17+H17)))","v":34590},{"$f":"IF(A18=0,0,(C18)*(1+B18))","v":22483.5},null,0.09,{"$f":"IF(A18=0,0,(70%*(H17+D17)))","v":80710},{"$f":"IF(A18=0,0,(G18)*(1+F18))","v":87973.900000000009},{"$f":"D18+H18","v":110457.40000000001},{"$f":"IF(A18=0,0,D18/(D18+H18))","v":0.20354906054279748},{"$f":"IF(A18=0,0,H18/(D18+H18))","v":0.79645093945720247},{"$f":"C18/(C18+G18)","v":0.3},{"$f":"G18/(C18+G18)","v":0.7},{"$f":"D18/I18","v":0.20354906054279748},{"$f":"H18/I18","v":0.79645093945720247}]
[3,0.4,{"$f":"IF(A19=0,0,(30%*(D18+H18)))","v":33137.22},{"$f":"IF(A19=0,0,(C19)*(1+B19))","v":46392.108},null,0.09,{"$f":"IF(A19=0,0,(70%*(H18+D18)))","v":77320.180000000008},{"$f":"IF(A19=0,0,(G19)*(1+F19))","v":84278.996200000009},{"$f":"D19+H19","v":130671.1042},{"$f":"IF(A19=0,0,D19/(D19+H19))","v":0.35502958579881655},{"$f":"IF(A19=0,0,H19/(D19+H19))","v":0.64497041420118351},{"$f":"C19/(C19+G19)","v":0.3},{"$f":"G19/(C19+G19)","v":0.70000000000000007},0.35502958579881655,{"$f":"H19/I19","v":0.64497041420118351}]
[4,0.6,{"$f":"IF(A20=0,0,(30%*(D19+H19)))","v":39201.331259999999},{"$f":"IF(A20=0,0,(C20)*(1+B20))","v":62722.130016000003},null,0.09,{"$f":"IF(A20=0,0,(70%*(H19+D19)))","v":91469.772939999995},{"$f":"IF(A20=0,0,(G20)*(1+F20))","v":99702.052504599997},{"$f":"D20+H20","v":162424.18252060001},{"$f":"IF(A20=0,0,D20/(D20+H20))","v":0.38616251005631536},{"$f":"IF(A20=0,0,H20/(D20+H20))","v":0.61383748994368459},{"$f":"C20/(C20+G20)","v":0.3},{"$f":"G20/(C20+G20)","v":0.7},0.38616251005631536,{"$f":"H20/I20","v":0.61383748994368459}]
[5,-0.3,{"$f":"IF(A21=0,0,(30%*(D20+H20)))","v":48727.254756180002},{"$f":"IF(A21=0,0,(C21)*(1+B21))","v":34109.078329326003},null,0.09,{"$f":"IF(A21=0,0,(70%*(H20+D20)))","v":113696.92776442},{"$f":"IF(A21=0,0,(G21)*(1+F21))","v":123929.65126321782},{"$f":"D21+H21","v":158038.72959254382},{"$f":"IF(A21=0,0,D21/(D21+H21))","v":0.21582733812949639},{"$f":"IF(A21=0,0,H21/(D21+H21))","v":0.78417266187050361},{"$f":"C21/(C21+G21)","v":0.3},{"$f":"G21/(C21+G21)","v":0.7},0.21582733812949639,{"$f":"H21/I21","v":0.78417266187050361}]
[]
["In this case the rebalanced porfolio has done better. "]
["Is rebalancing crucial? It can be most times. The most important thing is what is your expected corpus for a goal. If this amount"]
["is lower then the corpus achieved with no rebalancing than your goal can be achieved."]
["However there is no way of knowing this.  Have a look at the sample data. It seems advantageous more often to rebalance"]
["In this example the rebalanced portfolio is 7% higher. A difference of greater than 5% can be taken as significant."]

{"sheet":"Annual Rebalancing Lumpsum","index":2,"n_rows":38,"n_cols":38,"rows_kept":38,"cut":false,"hidden":false,"merged":[[19,0,19,2],[20,0,20,2],[17,0,17,2],[1,23,1,24],[2,23,2,24],[1,9,1,10],[0,13,0,24],[0,26,0,37],[1,36,1,37],[2,36,2,37],[18,0,18,2]]}
["Enter data only in green cells",null,null,null,null,null,null,null,null,null,null,null,null,"No Rebalancing",null,null,null,null,null,null,null,null,null,null,null,null,"Annual Rebalancing"]
["Enter no of years (max 32)",null,null,25,null,null,null,null,null,"Annual CAGR",null,null,null,"Year of","Year","Sensex","investment","Total Corpus",null,"FD","Monthly","Total",null,"Asset Allocation",null,null,"Year of","Year","Sensex","Investment","Total",null,"FD","Monthly","Total",null,"Asset Allocation"]
["Choose a starting year between",null,null,1980,"and",{"$f":"2011-time1+1","v":1987},null,null,null,"No","With",null,null,"return",null,"return ",null,null,null,"return",null,null,null,"After Investment",null,null,"return",null,"return ",null,null,null,"return",null,null,null,"After investment"]
["Starting year",null,1987,null,null,null,null,null,null,"Rebalancing","Rebalancing",null,null,null,null,null,null,null,null,null,null,null,null,"Equity","Debt",null,null,null,null,null,null,null,null,null,null,null,"Equity","Debt"]
[null,null,null,null,null,null,null,null,null,{"$f":"IF(O5=0,\"\",((R5+V5)/cont1)^(1/O5)-1)","v":""},{"$f":"IF(O5=0,\"\",((AE5+AI5)/cont1)^(1/O5)-1)","v":""},null,null,1980,{"$f":"IF(T3=start-1+time1,0,IF(O3<>0,O3+1,IF(N5=start,1,0)))","v":0},0.34899276658629547,{"$f":"IF(T3=start-1+time1,0,IF(N5=start,cont1*eqper,0))","v":0},{"$f":"IF(O5=0,0,(Q5+R3)*(1+P5))","v":0},{"$f":"IF(O5=time1,R5,0)","v":0},7.4999999999999997E-2,{"$f":"IF(T3=start-1+time1,0,IF(N5=start,cont1*bondper,0))","v":0},{"$f":"IF(O5=0,0,(U5+V3)*(1+T5))","v":0},{"$f":"IF(O5=time1,V5,0)","v":0},{"$f":"IF(O5=0,0,R5/(R5+V5))","v":0},{"$f":"IF(O5=0,0,V5/(R5+V5))","v":0},null,1980,{"$f":"IF(AG3=start-1+time1,0,IF(AB3<>0,AB3+1,IF(AA5=start,1,0)))","v":0},0.34899276658629547,{"$f":"IF(AG3=start-1+time1,0,IF(AA5=start,cont1*eqper,0))","v":0},{"$f":"IF(AB5=0,0,(AD5+eqper*(AE3+AI3))*(1+AC5))","v":0},{"$f":"IF(AB5=time1,AE5,0)","v":0},7.4999999999999997E-2,{"$f":"IF(AG3=start-1+time1,0,IF(AA5=start,cont1*bondper,0))","v":0},{"$f":"IF(AB5=0,0,(AH5+bondper*(AE3+AI3))*(1+AG5))","v":0},{"$f":"IF(AB5=time1,AI5,0)","v":0},{"$f":"IF(AB5=0,0,AE5/(AE5+AI5))","v":0},{"$f":"IF(AB5=0,0,AI5/(AE5+AI5))","v":0}]
["Lumpsum investment",null,null,100,null,null,null,null,null,"","",null,null,{"$f":"N5+1","v":1981},{"$f":"IF(N5=start-1+time1,0,IF(O5<>0,O5+1,IF(N6=start,1,0)))","v":0},0.25524677121771222,{"$f":"IF(T5=start-1+time1,0,IF(N6=start,cont1*eqper,0))","v":0},{"$f":"IF(O6=0,0,(Q6+R5)*(1+P6))","v":0},0,0.08,{"$f":"IF(T5=start-1+time1,0,IF(N6=start,cont1*bondper,0))","v":0},{"$f":"IF(O6=0,0,(U6+V5)*(1+T6))","v":0},0,0,0,null,{"$f":"AA5+1","v":1981},{"$f":"IF(AA5=start-1+time1,0,IF(AB5<>0,AB5+1,IF(AA6=start,1,0)))","v":0},0.25524677121771222,{"$f":"IF(AG5=start-1+time1,0,IF(AA6=start,cont1*eqper,0))","v":0},{"$f":"IF(AB6=0,0,(AD6+eqper*(AE5+AI5))*(1+AC6))","v":0},0,0.08,{"$f":"IF(AG5=start-1+time1,0,IF(AA6=start,cont1*bondper,0))","v":0},{"$f":"IF(AB6=0,0,(AH6+bondper*(AE5+AI5))*(1+AG6))","v":0},0,0,0]
[null,null,null,null,null,null,null,null,null,"","",null,null,{"$f":"N6+1","v":1982},0,-2.8478250884203839E-2,0,0,0,0.08,0,0,0,0,0,null,{"$f":"AA6+1","v":1982},0,-2.8478250884203839E-2,0,0,0,0.08,0,0,0,0,0]
[null,null,null,null,null,null,null,null,null,"","",null,null,1983,0,0.15989787716892828,0,0,0,0.08,0,0,0,0,0,null,1983,0,0.15989787716892828,0,0,0,0.08,0,0,0,0,0]
["% in equities",0.7,"% in FDs",{"$f":"100%-eqper","v":0.30000000000000004},null,null,null,null,null,"","",null,null,1984,0,0.44238372803978315,0,0,0,0.08,0,0,0,0,0,null,1984,0,0.44238372803978315,0,0,0,0.08,0,0,0,0,0]
[null,null,null,null,null,null,null,null,null,"","",null,null,1985,0,0.62242129655796075,0,0,0,8.5000000000000006E-2,0,0,0,0,0,null,1985,0,0.62242129655796075,0,0,0,8.5000000000000006E-2,0,0,0,0,0]
["Total must be 100%",null,null,null,null,null,null,null,null,"","",null,null,1986,0,-0.11104143805194128,0,0,0,8.5000000000000006E-2,0,0,0,0,0,null,1986,0,-0.11104143805194128,0,0,0,8.5000000000000006E-2,0,0,0,0,0]
[null,null,null,null,null,null,null,null,null,-0.12660333881965669,-0.12660333881965669,null,null,1987,1,-0.21943334117093818,70,54.639666118034327,0,0.09,30.000000000000004,32.700000000000003,0,0.62559966790108967,0.37440033209891038,null,1987,1,-0.21943334117093818,70,54.639666118034327,0,0.09,30.000000000000004,32.700000000000003,0,0.62559966790108967,0.37440033209891038]
[null,"Equity corpus","Debt Corpus","Total corpus","% Equity","%Debt","CAGR",null,null,0.15550425828562653,0.17505797212149043,null,null,1988,2,0.79129954564851768,0,97.876009091621597,0,0.09,0,35.643000000000008,0,0.73304924712599129,0.26695075287400877,null,1988,2,0.79129954564851768,0,109.51605296402968,0,0.09,0,28.560070820597232,0,0.79315706410511899,0.20684293589488092]
["no rebalancing",{"$f":"MAX(S2:S57)","v":2387.1267340700674},{"$f":"MAX(W2:W57)","v":247.39399919743246},{"$f":"B14+C14","v":2634.5207332675},{"$f":"B14/D14","v":0.9060952544144153},{"$f":"C14/D14","v":9.3904745585584634E-2},{"$f":"(D14/cont1)^(1/time1)-1","v":0.13979845730760765},null,null,0.13439070686789822,0.14710024137852784,null,null,1989,3,9.4520739910313803E-2,0,107.12732189043028,0,0.09,0,38.850870000000015,0,0.73385839695040833,0.26614160304959161,null,1989,3,9.4520739910313803E-2,0,105.78902681808857,0,0.09,0,45.150892477573016,0,0.70086844694059158,0.29913155305940842]
["annual rebalancing",{"$f":"MAX(AF3:AF58)","v":2185.8160302788833},{"$f":"MAX(AJ3:AJ58)","v":1161.7526780909513},{"$f":"B15+C15","v":3347.5687083698349},{"$f":"B15/D15","v":0.65295628580042198},{"$f":"C15/D15","v":0.34704371419957797},{"$f":"(D15/cont1)^(1/time1)-1","v":0.15077171825283542},null,null,0.19297085272376746,0.19999507521759363,null,null,1990,4,0.49538441841111336,0,160.19652794106122,0,0.09,0,42.347448300000018,0,0.79092220323748619,0.20907779676251376,null,1990,4,0.49538441841111336,0,157.99924240167431,0,0.09,0,49.357353609681347,0,0.76196873135890819,0.23803126864109181]
["The % equity and debt shown here represents the final asset allocation",null,null,null,null,null,null,null,null,0.44735634812099323,0.43201251150717113,null,null,1991,5,2.6687586153753942,0,587.72239203699337,0,0.12,0,47.429142096000021,0,0.925326257519408,7.4673742480591887E-2,null,1991,5,2.6687586153753942,0,532.5189086501033,0,0.12,0,69.671816259815515,0,0.88430274101242978,0.1156972589875702]
["% difference bet the two corpuses",null,{"$f":"(D15-D14)/D14","v":0.27065567034576626},null,null,null,null,null,null,0.24108722292400264,0.27265299667956411,null,null,1992,6,-0.4677899649941657,0,312.79175483972091,0,0.11,0,52.64634772656003,0,0.85593634775122729,0.14406365224877271,null,1992,6,-0.4677899649941657,0,224.34436274914762,0,0.11,0,200.52951139500303,0,0.52802578930290345,0.4719742106970965]
["+ve means rebalanced portfolio is higher",null,null,null,null,null,null,null,null,0.28427063791361995,0.30163829029997324,null,null,1993,7,0.65707382526792135,0,518.31902970452222,0,0.1,0,57.91098249921604,0,0.89950023207270846,0.10049976792729148,null,1993,7,0.65707382526792135,0,492.83316311911437,0,0.1,0,140.20837846756976,0,0.77851630697703489,0.22148369302296511]
["Click on the macro to find out the % difference",null,null,null,null,null,null,null,null,0.22633979654332825,0.24924885303200517,null,null,1994,8,-0.13708160116856616,0,447.2670271964887,0,0.11,0,64.281190574129809,0,0.87433991881689266,0.12566008118310737,null,1994,8,-0.13708160116856616,0,382.38423542183483,0,0.11,0,210.80283334836588,0,0.64462672157469025,0.35537327842530969]
["between the two corpuses for all possible ",null,null,null,null,null,null,null,null,0.20452430068669569,0.22648367085282461,null,null,1995,9,3.2398434816741109E-2,0,461.75777882279175,0,0.12,0,71.994933443025388,0,0.8651155642143683,0.13488443578563175,null,1995,9,3.2398434816741109E-2,0,428.68378094632016,0,0.12,0,199.3108551067875,0,0.68262331608524696,0.31737668391475299]
["investment durations between",null,null,null,null,null,null,null,null,0.1840457186274651,0.20581778010924912,null,null,1996,10,-1.6990384986678749E-3,0,460.97323457951245,0,0.12,0,80.634325456188449,0,0.85112038419317249,0.14887961580682757,null,1996,10,-1.6990384986678749E-3,0,438.84935429264755,0,0.12,0,211.00619771384424,0,0.675302923761531,0.32469707623846905]
[{"$f":"D3","v":1980},"and",{"$f":"F3","v":1987},null,null,null,null,null,null,0.18100894925133049,0.20004111070399455,null,null,1997,11,0.15824974932235217,0,533.92213339603416,0,0.11,0,89.504101256369182,0,0.85643193006423135,0.14356806993576871,null,1997,11,0.15824974932235217,0,526.88652114508045,0,0.11,0,216.40189881816181,0,0.70885877809200426,0.29114122190799568]
["Average % difference bet the two corpuses",null,null,{"$f":"AVERAGE(B26:B48)","v":0.14664428892898157},null,null,null,null,null,0.16293327939584157,0.18233848838020661,null,null,1998,12,-3.9249887611585633E-2,0,512.96574966690173,0,0.105,0,98.902031888287951,0,0.83836045160458061,0.16163954839541933,null,1998,12,-3.9249887611585633E-2,0,499.88010311168432,0,0.105,0,246.40011121781484,0,0.6698289643934956,0.33017103560650446]
[null,null,null,null,null,null,null,null,null,0.17269866456982119,0.18825305060737274,null,null,1999,13,0.33725494390314326,0,685.96598479504655,0,8.5000000000000006E-2,0,107.30870459879242,0,0.86472692746469737,0.13527307253530257,null,1999,13,0.33725494390314326,0,698.576834304454,0,8.5000000000000006E-2,0,242.91420976425201,0,0.74198988796060683,0.25801011203939306]
["Year","% difference","CAGR(rebal)","CAGR(none)",null,null,null,null,null,0.13798263439855951,0.15818401658534809,null,null,2000,14,-0.27930849702476163,0,494.36985657183158,0,8.5000000000000006E-2,0,116.42994448968977,0,0.8093811682856743,0.19061883171432575,null,2000,14,-0.27930849702476163,0,474.96721691132149,0,8.5000000000000006E-2,0,306.45533484436385,0,0.60782378988701324,0.39217621011298665]
[1980,0.10433950773826431,0.1609887815197224,0.15638891215130069,null,null,null,null,null,0.12700417052904545,0.14661523886664241,null,null,2001,15,-3.7462753649726219E-2,0,475.84940042323058,0,7.4999999999999997E-2,0,125.16219032641651,0,0.79174746002768326,0.20825253997231666,null,2001,15,-3.7462753649726219E-2,0,526.50381784204512,0,7.4999999999999997E-2,0,252.00877294120858,0,0.67629454433400349,0.32370545566599646]
[1981,5.4527041130009266E-2,0.16991432732887524,0.16743242296478678,null,null,null,null,null,0.11225758639828398,0.13154639141650892,null,null,2002,16,-0.12124173116001562,0,418.15659534446263,0,4.2500000000000003E-2,0,130.48158341528921,0,0.762171885831469,0.23782811416853109,null,2002,16,-0.12124173116001562,0,478.88706358277625,0,4.2500000000000003E-2,0,243.47981276746262,0,0.6629416149344427,0.3370583850655573]
[1982,6.961731037630732E-2,0.16699961450859768,0.16386223297245328,null,null,null,null,null,0.13815834625027712,0.15465518343448736,null,null,2003,17,0.8337531816631244,0,766.79598714632789,0,0.04,0,135.70084675190077,0,0.84963842347705865,0.1503615765229413,null,2003,17,0.8337531816631244,0,927.24979048471209,0,0.04,0,225.37846542127457,0,0.80446560782589616,0.1955343921741039]
[1983,1.2704611962253079E-2,0.17370713081604805,0.17311457780374573,null,null,null,null,null,0.13853791552580241,0.15319866769088675,null,null,2004,18,0.16138160483669003,0,890.54275413433629,0,5.2499999999999998E-2,0,142.82514120637555,0,0.86178674424631252,0.13821325575368748,null,2004,18,0.16138160483669003,0,937.04887753694686,0,5.2499999999999998E-2,0,363.94237180231534,0,0.72025763279564592,0.27974236720435414]
[1984,0.21754771594892322,0.15489278773294268,0.14583538459343415,null,null,null,null,null,0.16075563704370999,0.17065245198168433,null,null,2005,19,0.737303667743754,0,1547.1431930402066,0,0.06,0,151.3946496787581,0,0.91086766166103761,8.9132338338962419E-2,null,2005,19,0.737303667743754,0,1582.15180842574,0,0.06,0,413.7152172898854,0,0.79271403757895831,0.20728596242104169]
[1985,0.17820521757974506,0.16306392505124001,0.15545955335801431,null,null,null,null,null,0.16022980150979227,0.16858355339458075,null,null,2006,20,0.15887411347517733,0,1792.9441962536243,0,6.25E-2,0,160.85681528368048,0,0.91766980652901087,8.2330193470989077E-2,null,2006,20,0.15887411347517733,0,1619.0710410283737,0,6.25E-2,0,636.18261444685572,0,0.71791083770007291,0.28208916229992703]
[1986,0.26555723635058404,0.15020410827643538,0.13941948720050323,null,null,null,null,null,0.16146002258995318,0.16811313864399691,null,null,2007,21,0.19677787042632777,0,2145.7559369856567,0,7.0000000000000007E-2,0,172.11679235353813,0,0.92574363977153862,7.4256360228461341E-2,null,2007,21,0.19677787042632777,0,1889.3263669495857,0,7.0000000000000007E-2,0,723.93642340754877,0,0.72297603360869267,0.27702396639130727]
[1987,0.27065567034576626,0.15077171825283542,0.13979845730760765,null,null,null,null,null,0.13158577203831512,0.14540291366528457,null,null,2008,22,-0.37942650405256834,0,1331.5992632651457,0,0.08,0,185.88613574182119,0,0.87750383900664619,0.12249616099335384,null,2008,22,-0.37942650405256834,0,1135.2051379488869,0,0.08,0,846.69714407571178,0,0.57278562532822042,0.42721437467177953]
[null,null,null,null,null,null,null,null,null,0.15230739158419504,0.16187636175865494,null,null,2009,23,0.80532523046814641,0,2403.9697468453633,0,0.09,0,202.61588795858512,0,0.92226770329230923,7.7732296707690815E-2,null,2009,23,0.80532523046814641,0,2504.5847358429824,0,0.09,0,648.08204622204391,0,0.79443369977795619,0.20556630022204378]
[null,null,null,null,null,null,null,null,null,0.15045654767167149,0.15951906480427613,null,null,2010,24,0.10943116334797741,0,2667.038952895994,0,0.1,0,222.87747675444365,0,0.92287753567275199,7.7122464327247953E-2,null,2010,24,0.10943116334797741,0,2448.3667429724483,0,0.1,0,1040.3800380814589,0,0.70178975334885918,0.29821024665114071]
[null,null,null,null,null,null,null,null,null,0.13979845730760765,0.15077171825283542,null,null,2011,25,-0.10495242993057337,0,2387.1267340700674,2387.1267340700674,0.11,0,247.39399919743246,247.39399919743246,0.9060952544144153,9.3904745585584634E-2,null,2011,25,-0.10495242993057337,0,2185.8160302788833,2185.8160302788833,0.11,0,1161.7526780909513,1161.7526780909513,0.65295628580042198,0.34704371419957797]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Note 35% returns listed against 1980 actually is the return"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"generated in the financial year 1980-1981"]

{"sheet":"Annual Rebalancing SIP","index":3,"n_rows":46,"n_cols":92,"rows_kept":46,"cut":false,"hidden":false,"merged":[[38,8,38,14],[45,8,45,14],[1,12,1,13],[36,8,36,10],[1,8,1,10],[19,0,19,2],[20,0,20,2],[17,0,17,2],[0,16,0,29],[1,28,1,31],[3,28,3,29],[3,30,3,31],[0,33,0,48],[3,45,3,46],[3,47,3,48],[1,45,1,48],[18,0,18,2],[0,50,0,53],[0,68,0,69],[0,101,0,102],[0,103,0,104],[0,83,0,84],[0,89,0,90],[0,91,0,92],[0,93,0,94],[0,125,0,126],[0,127,0,128],[0,115,0,116],[0,117,0,118],[0,119,0,120],[0,121,0,122],[0,123,0,124],[0,105,0,106],[0,107,0,108],[0,109,0,110],[0,111,0,112],[0,113,0,114],[0,95,0,96],[0,97,0,98],[0,99,0,100]]}
["Enter data only in green cells",null,null,null,null,null,null,null,{"$f":"time1","v":11},{"$f":"CD1","v":0.15019770679919217},{"$f":"BX1","v":0.15464359156245797},null,null,null,null,null,"No Rebalancing",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Annual Rebalancing",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"rebalancing",null,null,null,null,null,"no rebalancing",null,null,null,23,12,11,null,null,null,null,null,null,null,null,null,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(1:1))-4,2)","v":-120000},{"$f":"IF(ISERROR(BV1),\"\",BV1)","v":-120000},{"$f":"IRR(BW1:BW39)","v":0.15464359156245797},null,null,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(1:1))-4,2)","v":-120000},{"$f":"IF(ISERROR(CB1),\"\",CB1)","v":-120000},{"$f":"IRR(CC1:CC39)","v":0.15019770679919217}]
["Enter no of years (max 32)",null,null,11,null,null,null,null,"CAGR at the end of each investment year",null,null,null,"Effective annual returns",null,"Annual gain",null,"Year of","Year","Sensex","Annual","invest. From","Total Equity",null,"FD","Annual","invest. From","Total Debt ",null,"Asset Allocation",null,null,null,null,"Year of","Year","Sensex","Annual","invest. From","Total Equity",null,"FD","Annual","invest. From","Total Debt ",null,"Asset Allocation",null,null,null,null,null,null,null,{"$f":"XIRR(BA5:BA39,BB5:BB39)","v":0.15453718304634101},null,null,null,{"$f":"XIRR(BE5:BE39,BF5:BF39)","v":0.15009461045265202},null,null,null,null,null,{"$f":"XIRR(BK5:BK39,BL5:BL39)","v":0.15453718304634101},null,null,{"$f":"XIRR(BN5:BN39,BO5:BO39)","v":0.15009461045265202},null,null,null,null,null,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(2:2))-4,2)","v":-120000},{"$f":"IF(ISERROR(BV2),\"\",BV2)","v":-120000},"rebalanced",null,null,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(2:2))-4,2)","v":-120000},{"$f":"IF(ISERROR(CB2),\"\",CB2)","v":-120000},"without",null,null,null,null,{"$f":"(PRODUCT(CI5:CI36))-1","v":0.1501977067991922},null,null,null,null,{"$f":"(PRODUCT(CN5:CN36))-1","v":0.15464359156245799}]
["Choose a starting year between",null,null,1980,"and",{"$f":"2011-time1+1","v":2001},null,null,"CAGR at the ","Without","With",null,"Without","With","or loss with ",null,"return",null,"return","investment","Previous","Corpus",null,"return","investment","Previous","Corpus",null,"Equity","Debt","Equity","Debt",null,null,null,"return ","Investment","Previous","Corpus",null,"return","investment","Previous","Corpus",null,"Equity","Debt","Equity","Debt",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IRR(BK5:BK37)","v":0.15464359156244656},null,null,null,null,null,null,null,null,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(3:3))-4,2)","v":-120000},-120000,null,null,null,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(3:3))-4,2)","v":-120000},-120000,"rebalancing",null,null,"CAGR without",null,null,null,null,"CAGR with"]
["Starting year",null,2000,null,null,null,null,null,"end of year","rebalancing","rebalancing",null,"rebalancing","rebalancing","rebalancing",null,null,null,null,null,"year",null,null,null,null,"year",null,null,"Prior investment",null,"After investment",null,null,"return",null,null,null,"year",null,null,null,null,"year",null,null,"Prior investment",null,"After investment",null,null,null,null,{"$f":"-1E-300","v":-1E-300},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(4:4))-4,2)","v":-120000},-120000,null,null,null,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(4:4))-4,2)","v":-120000},-120000,null,null,null,"rebalancing",null,null,null,null,"rebalancing"]
[null,null,null,null,null,null,null,null,1,-0.1335850982148569,-0.1335850982148569,null,{"$f":"IF(I5=\"\",\"\",CH5)","v":-0.1335850982148569},{"$f":"IF(I5=\"\",\"\",CM5)","v":-0.1335850982148569},{"$f":"IF(I5=\"\",\"\",N5-M5)","v":0},null,1980,{"$f":"IF(Q4=start-1+time1,0,IF(R4<>0,R4+1,IF(Q5=start,1,0)))","v":0},0.34899276658629547,{"$f":"IF(R5=0,0,IF(X4=start-1+time1,0,IF(T4<>0,T4*(1+inca),IF(Q5=start,12*cont1*eqper,0))))","v":0},{"$f":"IF(R5=1,0,IF(R5=0,0,V4))","v":0},{"$f":"IF(R5=0,0,IF(R5=1,T5*(1+S5),(U5+T5)*(1+S5)))","v":0},{"$f":"IF(R5=time1,V5,0)","v":0},7.4999999999999997E-2,{"$f":"IF(R5=0,0,IF(X4=start-1+time1,0,IF(Y4<>0,Y4*(1+inca),IF(Q5=start,12*cont1*bondper,0))))","v":0},{"$f":"IF(R5=1,0,IF(R5=0,0,AA4))","v":0},{"$f":"IF(R5=0,0,IF(R5=1,Y5*(1+X5),(Z5+Y5)*(1+X5)))","v":0},{"$f":"IF(R5=time1,AA5,0)","v":0},{"$f":"IF(R5=0,0,(T5+U5)/(T5+U5+Y5+Z5))","v":0},{"$f":"IF(R5=0,0,(Y5+Z5)/(T5+U5+Y5+Z5))","v":0},{"$f":"IF(R5=0,0,V5/(V5+AA5))","v":0},{"$f":"IF(R5=0,0,AA5/(V5+AA5))","v":0},null,1980,{"$f":"IF(AO4=start-1+time1,0,IF(AI4<>0,AI4+1,IF(AH5=start,1,0)))","v":0},0.34899276658629547,{"$f":"IF(AI5=0,0,IF(AO4=start-1+time1,0,IF(AK4<>0,AK4*(1+inca),IF(AH5=start,12*cont1*eqper,0))))","v":0},{"$f":"IF(AI5=1,0,IF(AI5=0,0,eqper*(AM4+AR4)))","v":0},{"$f":"IF(AI5=0,0,(AK5+AL5)*(1+AJ5))","v":0},{"$f":"IF(AI5=time1,AM5,0)","v":0},7.4999999999999997E-2,{"$f":"IF(AI5=0,0,IF(AO4=start-1+time1,0,IF(AP4<>0,AP4*(1+inca),IF(AH5=start,12*cont1*bondper,0))))","v":0},{"$f":"IF(AI5=1,0,IF(AI5=0,0,bondper*(AM4+AR4)))","v":0},{"$f":"IF(AI5=0,0,(AP5+AQ5)*(1+AO5))","v":0},{"$f":"IF(AI5=time1,AR5,0)","v":0},{"$f":"IF(AI5=0,0,(AK5+AL5)/(AK5+AL5+AP5+AQ5))","v":0},{"$f":"IF(AI5=0,0,(AP5+AQ5)/(AK5+AL5+AP5+AQ5))","v":0},{"$f":"IF(AI5=0,0,AM5/(AM5+AR5))","v":0},{"$f":"IF(AI5=0,0,AR5/(AM5+AR5))","v":0},null,{"$f":"AR5+AM5","v":0},{"$f":"IF(Q4=start-1+time1,0,IF(R4<>0,R4+1,IF(Q5=start,1,0)))","v":0},{"$f":"IF(AZ4=time1,totalcorpus,IF(R5=0,-1E-300,IF(X4=start-1+time1,-1E-300,IF(BA4<>-1E-300,-ABS(BA4*(1+inca)),IF(Q5=start,-12*cont1,-1E-300)))))","v":-1E-300},{"$f":"IF(AZ4=time1,DATE(YEAR(BB4)+1,1,1),IF(X4=start-1+time1,0,IF(R4<>0,DATE(YEAR(BB4)+1,1,1),IF(Q5=start,DATE(start,1,1),0))))","v":"00:00:00"},null,{"$f":"V5+AA5","v":0},{"$f":"IF(AZ4=time1,nototalcorpu,IF(R5=0,-1E-300,IF(X4=start-1+time1,-1E-300,IF(BA4<>-1E-300,-ABS(BA4*(1+inca)),IF(Q5=start,-12*cont1,-1E-300)))))","v":-1E-300},{"$f":"IF(AZ4=time1,DATE(YEAR(BB4)+1,1,1),IF(X4=start-1+time1,0,IF(R4<>0,DATE(YEAR(BB4)+1,1,1),IF(Q5=start,DATE(start,1,1),0))))","v":"00:00:00"},null,null,null,{"$f":"IF(Q5=start,1,IF(Q5>start,IF(Q5<=start+year2,1,0),0))","v":0},{"$f":"IF(BJ5-AZ5=0,IF(AZ4=year2,BA5,IF(BA5>0,0,BA5)),IF(AZ5-BJ5=year2,AY4,IF(AZ5-BJ5<year2,BA5,0)))","v":-1E-300},{"$f":"BB5","v":"00:00:00"},null,{"$f":"IF(BJ5-AZ5=0,IF(AZ4=year2,BE5,IF(BE5>0,0,BE5)),IF(AZ5-BJ5=year2,BD4,IF(AZ5-BJ5<year2,BE5,0)))","v":-1E-300},{"$f":"BB5","v":"00:00:00"},null,null,null,{"$f":"IF(ABS(BK5)<>1E-300,1,\"\")","v":""},{"$f":"IF(BK5<>0,BK5,\"\")","v":-1E-300},null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(5:5))-4,2)","v":-120000},-120000,null,{"$f":"IF(ABS(BN5)<>1E-300,1,\"\")","v":""},{"$f":"IF(BN5<>0,BN5,\"\")","v":-1E-300},null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(5:5))-4,2)","v":-120000},-120000,null,null,{"$f":"I5","v":1},{"$f":"J5","v":-0.1335850982148569},{"$f":"CG5","v":-0.1335850982148569},{"$f":"(1+CH5)","v":0.86641490178514313},null,null,{"$f":"K5","v":-0.1335850982148569},{"$f":"CL5","v":-0.1335850982148569},{"$f":"(1+CM5)","v":0.86641490178514313}]
["monthly contribution in 1st year",null,null,10000,null,null,null,null,2,-3.7027752602425489E-2,-4.0392270562086448E-2,null,{"$f":"IF(I6=\"\",\"\",CH6)","v":0.11144469631522558},{"$f":"IF(I6=\"\",\"\",CM6)","v":0.10756143212767677},{"$f":"IF(I6=\"\",\"\",N6-M6)","v":-3.8832641875488072E-3},null,{"$f":"Q5+1","v":1981},0,0.25524677121771222,0,{"$f":"IF(R6=1,0,IF(R6=0,0,V5))","v":0},{"$f":"IF(R6=0,0,IF(R6=1,T6*(1+S6),(U6+T6)*(1+S6)))","v":0},0,0.08,0,{"$f":"IF(R6=1,0,IF(R6=0,0,AA5))","v":0},{"$f":"IF(R6=0,0,IF(R6=1,Y6*(1+X6),(Z6+Y6)*(1+X6)))","v":0},0,{"$f":"IF(R6=0,0,(T6+U6)/(T6+U6+Y6+Z6))","v":0},{"$f":"IF(R6=0,0,(Y6+Z6)/(T6+U6+Y6+Z6))","v":0},0,0,null,{"$f":"AH5+1","v":1981},{"$f":"IF(AH5=start-1+time1,0,IF(AI5<>0,AI5+1,IF(AH6=start,1,0)))","v":0},0.25524677121771222,0,0,{"$f":"IF(AI6=0,0,(AK6+AL6)*(1+AJ6))","v":0},0,0.08,0,0,{"$f":"IF(AI6=0,0,(AP6+AQ6)*(1+AO6))","v":0},0,{"$f":"IF(AI6=0,0,(AK6+AL6)/(AK6+AL6+AP6+AQ6))","v":0},{"$f":"IF(AI6=0,0,(AP6+AQ6)/(AK6+AL6+AP6+AQ6))","v":0},{"$f":"IF(AI6=0,0,AM6/(AM6+AR6))","v":0},{"$f":"IF(AI6=0,0,AR6/(AM6+AR6))","v":0},null,{"$f":"AR6+AM6","v":0},0,-1E-300,"00:00:00",null,{"$f":"V6+AA6","v":0},-1E-300,"00:00:00",null,null,null,0,-1E-300,{"$f":"BB6","v":"00:00:00"},null,-1E-300,{"$f":"BB6","v":"00:00:00"},null,null,null,{"$f":"IF(ABS(BK6)<>1E-300,1,\"\")","v":""},{"$f":"IF(BK6<>0,BK6,\"\")","v":-1E-300},null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(6:6))-4,2)","v":-120000},-120000,null,{"$f":"IF(ABS(BN6)<>1E-300,1,\"\")","v":""},{"$f":"IF(BN6<>0,BN6,\"\")","v":-1E-300},null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(6:6))-4,2)","v":-120000},-120000,null,null,{"$f":"I6","v":2},{"$f":"J6","v":-3.7027752602425489E-2},{"$f":"IF(CF6=0,0,((1+CG6)/(1+CG5)-1))","v":0.11144469631522558},{"$f":"(1+CH6)","v":1.1114446963152256},null,null,{"$f":"K6","v":-4.0392270562086448E-2},{"$f":"IF(CF6=0,0,((1+CL6)/(1+CL5)-1))","v":0.10756143212767677},{"$f":"(1+CM6)","v":1.1075614321276768}]
["% increase or","decrease each year",null,null,null,null,null,null,3,-4.2497280382852845E-2,-4.8217312007601668E-2,null,{"$f":"IF(I7=\"\",\"\",CH7)","v":-5.6798394711879974E-3},{"$f":"IF(I7=\"\",\"\",CM7)","v":-8.1544168574994025E-3},-2.4745773863114051E-3,null,{"$f":"Q6+1","v":1982},0,-2.8478250884203839E-2,0,0,0,0,0.08,0,0,0,0,0,0,0,0,null,{"$f":"AH6+1","v":1982},0,-2.8478250884203839E-2,0,0,0,0,0.08,0,0,0,0,0,0,0,0,null,{"$f":"AR7+AM7","v":0},0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,{"$f":"BB7","v":"00:00:00"},null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(7:7))-4,2)","v":-120000},-120000,null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(7:7))-4,2)","v":-120000},-120000,null,null,3,-4.2497280382852845E-2,-5.6798394711879974E-3,0.994320160528812,null,null,-4.8217312007601668E-2,{"$f":"IF(CF7=0,0,((1+CL7)/(1+CL6)-1))","v":-8.1544168574994025E-3},0.9918455831425006]
[null,null,null,null,null,null,null,null,4,0.13076843626820486,0.14229775119125196,null,{"$f":"IF(I8=\"\",\"\",CH8)","v":0.18095584806311282},{"$f":"IF(I8=\"\",\"\",CM8)","v":0.20016655650746129},1.9210708444348468E-2,null,1983,0,0.15989787716892828,0,0,0,0,0.08,0,0,0,0,0,0,0,0,null,1983,0,0.15989787716892828,0,0,0,0,0.08,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,{"$f":"BB8","v":"00:00:00"},null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(8:8))-4,2)","v":-120000},-120000,null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(8:8))-4,2)","v":-120000},-120000,null,null,4,0.13076843626820486,0.18095584806311282,1.1809558480631128,null,null,0.14229775119125196,0.20016655650746129,1.2001665565074613]
["% in equities",0.6,"% in FDs",{"$f":"100%-eqper","v":0.4},null,null,null,null,5,0.12871042837369479,0.13472993424679289,null,{"$f":"IF(I9=\"\",\"\",CH9)","v":-1.8200082603135881E-3},{"$f":"IF(I9=\"\",\"\",CM9)","v":-6.6250825903901855E-3},-4.8050743300765975E-3,null,1984,0,0.44238372803978315,0,0,0,{"$f":"IF(R9=time1,V9,0)","v":0},0.08,0,0,0,0,0,0,0,0,null,1984,0,0.44238372803978315,0,0,0,{"$f":"IF(AI9=time1,AM9,0)","v":0},0.08,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(9:9))-4,2)","v":-120000},-120000,null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(9:9))-4,2)","v":-120000},-120000,null,null,5,0.12871042837369479,-1.8200082603135881E-3,0.99817999173968641,null,null,0.13472993424679289,-6.6250825903901855E-3,0.99337491740960981]
[null,null,null,null,null,null,null,null,6,0.21567982886030637,0.21071496619524918,null,{"$f":"IF(I10=\"\",\"\",CH10)","v":7.7052004039620314E-2},{"$f":"IF(I10=\"\",\"\",CM10)","v":6.6963098139200206E-2},-1.0088905900420109E-2,null,1985,0,0.62242129655796075,0,0,0,{"$f":"IF(R10=time1,V10,0)","v":0},8.5000000000000006E-2,0,0,0,0,0,0,0,0,null,1985,0,0.62242129655796075,0,0,0,{"$f":"IF(AI10=time1,AM10,0)","v":0},8.5000000000000006E-2,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(10:10))-4,2)","v":-120000},-120000,null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(10:10))-4,2)","v":-120000},-120000,null,null,6,0.21567982886030637,7.7052004039620314E-2,1.0770520040396203,null,null,0.21071496619524918,6.6963098139200206E-2,1.0669630981392002]
["Total must be 100%",null,null,null,null,null,null,null,7,0.1982501227097816,0.19094224083448982,null,{"$f":"IF(I11=\"\",\"\",CH11)","v":-1.4337414948198068E-2},{"$f":"IF(I11=\"\",\"\",CM11)","v":-1.6331445396183031E-2},-1.9940304479849624E-3,null,1986,0,-0.11104143805194128,0,0,0,0,8.5000000000000006E-2,0,0,0,0,0,0,0,0,null,1986,0,-0.11104143805194128,0,0,0,{"$f":"IF(AI11=time1,AM11,0)","v":0},8.5000000000000006E-2,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(11:11))-4,2)","v":-120000},-120000,null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(11:11))-4,2)","v":-120000},-120000,null,null,7,0.1982501227097816,-1.4337414948198068E-2,0.98566258505180193,null,null,0.19094224083448982,-1.6331445396183031E-2,0.98366855460381697]
[null,null,null,null,null,null,null,null,8,0.19232098961201421,0.18245649254012453,null,{"$f":"IF(I12=\"\",\"\",CH12)","v":-4.9481598085373557E-3},{"$f":"IF(I12=\"\",\"\",CM12)","v":-7.1252391622447231E-3},-2.1770793537073674E-3,null,1987,0,-0.21943334117093818,0,0,0,0,0.09,0,0,0,0,0,0,0,0,null,1987,0,-0.21943334117093818,0,0,0,0,0.09,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(12:12))-4,2)","v":3461394.905804967},3461394.905804967,null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(12:12))-4,2)","v":3364445.9512146874},3364445.9512146874,null,null,8,0.19232098961201421,-4.9481598085373557E-3,0.99505184019146264,null,null,0.18245649254012453,-7.1252391622447231E-3,0.99287476083775528]
[null,"Equity corpus","Debt Corpus","Total corpus","% Equity","%Debt","Final CAGR",null,9,9.5443313964036144E-2,0.10740349432669961,null,{"$f":"IF(I13=\"\",\"\",CH13)","v":-8.1251337929983514E-2},{"$f":"IF(I13=\"\",\"\",CM13)","v":-6.347210124593905E-2},1.7779236684044464E-2,null,1988,0,0.79129954564851768,0,0,0,0,0.09,0,0,0,0,0,0,0,0,null,1988,0,0.79129954564851768,0,0,0,0,0.09,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(13:13))-4,2)","v":{"$error":"#NUM!"}},"",null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(13:13))-4,2)","v":{"$error":"#NUM!"}},"",null,null,9,9.5443313964036144E-2,-8.1251337929983514E-2,0.91874866207001649,null,null,0.10740349432669961,-6.347210124593905E-2,0.93652789875406095]
["no rebalancing",{"$f":"MAX(W2:W57)","v":2541984.3222897435},{"$f":"MAX(AB2:AB57)","v":822461.62892494386},{"$f":"B14+C14","v":3364445.9512146874},{"$f":"B14/D14","v":0.75554321845235606},{"$f":"C14/D14","v":0.24445678154764391},{"$f":"XIRR(BE5:BE39,BF5:BF39)","v":0.15009461045265202},null,10,0.15710892738817372,0.16247801008405169,null,{"$f":"IF(I14=\"\",\"\",CH14)","v":5.6292838376995391E-2},{"$f":"IF(I14=\"\",\"\",CM14)","v":4.9733016050159007E-2},-6.5598223268363842E-3,null,1989,0,9.4520739910313803E-2,0,0,0,0,0.09,0,0,0,0,0,0,0,0,null,1989,0,9.4520739910313803E-2,0,0,0,0,0.09,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(14:14))-4,2)","v":{"$error":"#NUM!"}},"",null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(14:14))-4,2)","v":{"$error":"#NUM!"}},"",null,null,10,0.15710892738817372,5.6292838376995391E-2,1.0562928383769954,null,null,0.16247801008405169,4.9733016050159007E-2,1.049733016050159]
["annual rebalancing",{"$f":"MAX(AN4:AN58)","v":2083923.0319825783},{"$f":"MAX(AS4:AS58)","v":1377471.8738223887},{"$f":"B15+C15","v":3461394.905804967},{"$f":"B15/D15","v":0.60204717713304379},{"$f":"C15/D15","v":0.39795282286695621},{"$f":"XIRR(BA5:BA39,BB5:BB39)","v":0.15453718304634101},null,11,0.15019770679919217,0.15464359156245797,null,{"$f":"IF(I15=\"\",\"\",CH15)","v":-5.9728349037818651E-3},{"$f":"IF(I15=\"\",\"\",CM15)","v":-6.7394122328621187E-3},-7.6657732908025356E-4,null,1990,0,0.49538441841111336,0,0,0,0,0.09,0,0,0,0,0,0,0,0,null,1990,0,0.49538441841111336,0,0,0,0,0.09,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(15:15))-4,2)","v":{"$error":"#NUM!"}},"",null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(15:15))-4,2)","v":{"$error":"#NUM!"}},"",null,null,11,0.15019770679919217,-5.9728349037818651E-3,0.99402716509621813,null,null,0.15464359156245797,-6.7394122328621187E-3,0.99326058776713788]
["The % equity and debt shown here represents the final asset allocation",null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(I16=\"\",\"\",CH16)","v":""},{"$f":"IF(I16=\"\",\"\",CM16)","v":""},"",null,1991,0,2.6687586153753942,0,0,0,0,0.12,0,0,0,0,0,0,0,0,null,1991,0,2.6687586153753942,0,0,0,0,0.12,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(16:16))-4,2)","v":{"$error":"#NUM!"}},"",null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(16:16))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
["% difference bet the two corpuses is",null,{"$f":"(D15-D14)/D14","v":2.8815726570158604E-2},"if started at",{"$f":"start","v":2000},null,null,null,null,null,null,null,{"$f":"IF(I17=\"\",\"\",CH17)","v":""},{"$f":"IF(I17=\"\",\"\",CM17)","v":""},"",null,1992,0,-0.4677899649941657,0,0,0,0,0.11,0,0,0,0,0,0,0,0,null,1992,0,-0.4677899649941657,0,0,0,0,0.11,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(17:17))-4,2)","v":{"$error":"#NUM!"}},"",null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(17:17))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
["+ve means rebalanced portfolio is higher",null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(I18=\"\",\"\",CH18)","v":""},{"$f":"IF(I18=\"\",\"\",CM18)","v":""},"",null,1993,0,0.65707382526792135,0,0,0,0,0.1,0,0,0,0,0,0,0,0,null,1993,0,0.65707382526792135,0,0,0,0,0.1,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(18:18))-4,2)","v":{"$error":"#NUM!"}},"",null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(18:18))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
["Click on the macro to find out the % difference",null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(I19=\"\",\"\",CH19)","v":""},{"$f":"IF(I19=\"\",\"\",CM19)","v":""},"",null,1994,0,-0.13708160116856616,0,0,0,0,0.11,0,0,0,0,0,0,0,0,null,1994,0,-0.13708160116856616,0,0,0,0,0.11,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(19:19))-4,2)","v":{"$error":"#NUM!"}},"",null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(19:19))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
["between the two corpuses for all possible ",null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(I20=\"\",\"\",CH20)","v":""},{"$f":"IF(I20=\"\",\"\",CM20)","v":""},"",null,1995,0,3.2398434816741109E-2,0,0,0,0,0.12,0,0,0,0,0,0,0,0,null,1995,0,3.2398434816741109E-2,0,0,0,0,0.12,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(20:20))-4,2)","v":{"$error":"#NUM!"}},"",null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(20:20))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
["investment durations between",null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(I21=\"\",\"\",CH21)","v":""},{"$f":"IF(I21=\"\",\"\",CM21)","v":""},"",null,1996,0,-1.6990384986678749E-3,0,0,0,0,0.12,0,0,0,0,0,0,0,0,null,1996,0,-1.6990384986678749E-3,0,0,0,0,0.12,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(21:21))-4,2)","v":{"$error":"#NUM!"}},"",null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(21:21))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
[{"$f":"D3","v":1980},"and",{"$f":"F3","v":2001},null,null,null,null,null,null,null,null,null,{"$f":"IF(I22=\"\",\"\",CH22)","v":""},{"$f":"IF(I22=\"\",\"\",CM22)","v":""},"",null,1997,0,0.15824974932235217,0,0,0,0,0.11,0,0,0,0,0,0,0,0,null,1997,0,0.15824974932235217,0,0,0,0,0.11,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(22:22))-4,2)","v":{"$error":"#NUM!"}},"",null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(22:22))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
["Average % difference bet the two corpuses",null,null,{"$f":"AVERAGE(B26:B48)","v":7.6324664722151409E-2},null,null,null,null,null,null,null,null,{"$f":"IF(I23=\"\",\"\",CH23)","v":""},{"$f":"IF(I23=\"\",\"\",CM23)","v":""},"",null,1998,0,-3.9249887611585633E-2,0,0,0,0,0.105,0,0,0,0,0,0,0,0,null,1998,0,-3.9249887611585633E-2,0,0,0,0,0.105,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(23:23))-4,2)","v":{"$error":"#NUM!"}},"",null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(23:23))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(I24=\"\",\"\",CH24)","v":""},{"$f":"IF(I24=\"\",\"\",CM24)","v":""},"",null,1999,0,0.33725494390314326,0,0,0,0,8.5000000000000006E-2,0,0,0,0,0,0,0,0,null,1999,0,0.33725494390314326,0,0,0,0,8.5000000000000006E-2,0,0,0,0,0,0,0,0,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(24:24))-4,2)","v":{"$error":"#NUM!"}},"",null,"",-1E-300,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(24:24))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
["Start Year","% difference","CAGR(rebal)","CAGR(none)",null,null,null,null,null,null,null,null,{"$f":"IF(I25=\"\",\"\",CH25)","v":""},{"$f":"IF(I25=\"\",\"\",CM25)","v":""},"",null,2000,1,-0.27930849702476163,72000,0,51889.788214217158,0,8.5000000000000006E-2,48000,0,52080,0,0.6,0.4,0.49908525452898428,0.50091474547101578,null,2000,1,-0.27930849702476163,72000,0,51889.788214217158,0,8.5000000000000006E-2,48000,0,52080,0,0.6,0.4,0.49908525452898428,0.50091474547101578,null,103969.78821421716,1,-120000,"2000-01-01",null,103969.78821421716,-120000,"2000-01-01",null,null,null,1,-120000,"2000-01-01",null,-120000,"2000-01-01",null,null,null,1,-120000,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(25:25))-4,2)","v":{"$error":"#NUM!"}},"",null,1,-120000,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(25:25))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
[1980,-0.16490075065446394,0.29320656657218946,0.31865902543067937,null,null,null,null,null,null,null,null,{"$f":"IF(I26=\"\",\"\",CH26)","v":""},{"$f":"IF(I26=\"\",\"\",CM26)","v":""},"",null,2001,2,-3.7462753649726219E-2,72000,51889.788214217158,119248.53559863119,0,7.4999999999999997E-2,48000,52080,107586,0,0.55315401778976581,0.44684598221023414,0.52570714280312958,0.47429285719687042,null,2001,2,-3.7462753649726219E-2,72000,62381.872928530291,129347.55792801996,0,7.4999999999999997E-2,48000,41587.915285686868,96307.008932113386,0,0.6,0.4,0.57321045936638626,0.42678954063361374,null,225654.56686013335,2,-120000,"2001-01-01",null,226834.53559863119,-120000,"2001-01-01",null,null,null,1,-120000,"2001-01-01",null,-120000,"2001-01-01",null,null,null,1,-120000,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(26:26))-4,2)","v":{"$error":"#NUM!"}},"",null,1,-120000,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(26:26))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
[1981,0.12160234809589367,0.23079150319099428,0.21465945839881898,null,null,null,null,null,null,null,null,{"$f":"IF(I27=\"\",\"\",CH27)","v":""},{"$f":"IF(I27=\"\",\"\",CM27)","v":""},"",null,2002,3,-0.12124173116001562,72000,119248.53559863119,168061.23206083529,0,4.2500000000000003E-2,48000,107586,162198.405,0,0.55141145407719883,0.44858854592280123,0.50887608778507099,0.49112391221492907,null,2002,3,-0.12124173116001562,72000,135392.74011608001,182248.08527438727,0,4.2500000000000003E-2,48000,90261.826744053338,144137.9543806756,0,0.60000000000000009,0.4,0.55838198676326345,0.44161801323673666,null,326386.03965506284,3,-120000,"2002-01-01",null,330259.63706083526,-120000,"2002-01-01",null,null,null,1,-120000,"2002-01-01",null,-120000,"2002-01-01",null,null,null,1,-120000,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(27:27))-4,2)","v":{"$error":"#NUM!"}},"",null,1,-120000,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(27:27))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
[1982,4.2396364864497736E-2,0.25929985642433162,0.25346238017082223,null,null,null,null,null,null,null,null,{"$f":"IF(I28=\"\",\"\",CH28)","v":""},{"$f":"IF(I28=\"\",\"\",CM28)","v":""},"",null,2003,4,0.8337531816631244,72000,168061.23206083529,440213.04808552633,0,0.04,48000,162198.405,218606.3412,0,0.5331617855597397,0.46683821444026036,0.66818471836860582,0.33181528163139412,null,2003,4,0.8337531816631244,72000,195831.6237930377,491137.09228048386,0,0.04,48000,130554.41586202514,185696.59249650614,0,0.60000000000000009,0.4,0.7256392572162077,0.27436074278379236,null,676833.68477698998,4,-120000,"2003-01-01",null,658819.38928552635,-120000,"2003-01-01",null,null,null,1,-120000,"2003-01-01",null,-120000,"2003-01-01",null,null,null,1,-120000,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(28:28))-4,2)","v":{"$error":"#NUM!"}},"",null,1,-120000,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(28:28))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
[1983,0.12236955294165101,0.23145001530647277,0.21522065997123715,null,null,null,null,null,null,null,null,{"$f":"IF(I29=\"\",\"\",CH29)","v":""},{"$f":"IF(I29=\"\",\"\",CM29)","v":""},"",null,2004,5,0.16138160483669003,72000,440213.04808552633,594874.81180386129,0,5.2499999999999998E-2,48000,218606.3412,280603.17411300004,0,0.65767885999271347,0.34232114000728647,0.67948574535642614,0.32051425464357386,null,2004,5,0.16138160483669003,72000,406100.21086619399,555256.79016854032,0,5.2499999999999998E-2,48000,270733.47391079599,335466.98129111278,0,0.6,0.4,0.62337708721821361,0.37662291278178645,null,890723.77145965304,5,-120000,"2004-01-01",null,875477.98591686133,-120000,"2004-01-01",null,null,null,1,-120000,"2004-01-01",null,-120000,"2004-01-01",null,null,null,1,-120000,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(29:29))-4,2)","v":{"$error":"#NUM!"}},"",null,1,-120000,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(29:29))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
[1984,0.1535808526477245,0.21457883715629578,0.19446830153465269,null,null,null,null,null,null,null,null,{"$f":"IF(I30=\"\",\"\",CH30)","v":""},{"$f":"IF(I30=\"\",\"\",CM30)","v":""},"",null,2005,6,0.737303667743754,72000,594874.81180386129,1158564.0564727739,0,0.06,48000,280603.17411300004,348319.36455978005,0,0.66990412770369012,0.33009587229630988,0.76884783540779622,0.23115216459220378,null,2005,6,0.737303667743754,72000,534434.26287579176,1053560.4691395927,0,0.06,48000,356289.50858386123,428546.87909889291,0,0.6,0.4,0.71085300966341647,0.28914699033658359,null,1482107.3482384856,6,-120000,"2005-01-01",null,1506883.421032554,-120000,"2005-01-01",null,null,null,1,-120000,"2005-01-01",null,-120000,"2005-01-01",null,null,null,1,-120000,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(30:30))-4,2)","v":{"$error":"#NUM!"}},"",null,1,-120000,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(30:30))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
[1985,0.18393994086674834,0.19312987923622135,0.1693016588687897,null,null,null,null,null,null,null,null,{"$f":"IF(I31=\"\",\"\",CH31)","v":""},{"$f":"IF(I31=\"\",\"\",CM31)","v":""},"",null,2006,7,0.15887411347517733,72000,1158564.0564727739,1426068.8300193038,0,6.25E-2,48000,348319.36455978005,421089.32484476629,0,0.75639350709699704,0.24360649290300296,0.77203396269240754,0.22796603730759249,null,2006,7,0.15887411347517733,72000,889264.4089430914,1113984.4397291653,0,6.25E-2,48000,592842.93929539423,680895.62300135638,0,0.6,0.39999999999999997,0.62064561463481804,0.37935438536518201,null,1794880.0627305217,7,-120000,"2006-01-01",null,1847158.15486407,-120000,"2006-01-01",null,null,null,1,-120000,"2006-01-01",null,-120000,"2006-01-01",null,null,null,1,-120000,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(31:31))-4,2)","v":{"$error":"#NUM!"}},"",null,1,-120000,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(31:31))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
[1986,0.16584987029423137,0.18615588545799258,0.16447941660881046,null,null,null,null,null,null,null,null,{"$f":"IF(I32=\"\",\"\",CH32)","v":""},{"$f":"IF(I32=\"\",\"\",CM32)","v":""},"",null,2007,8,0.19677787042632777,72000,1426068.8300193038,1792855.624142563,0,7.0000000000000007E-2,48000,421089.32484476629,501925.57758389995,0,0.76153959777719038,0.2384604022228097,0.7812751920722204,0.21872480792777965,null,2007,8,0.19677787042632777,72000,1076928.0376383129,1375011.65015788,0,7.0000000000000007E-2,48000,717952.02509220876,819568.66684866347,0,0.6,0.4,0.62654879363605465,0.37345120636394546,null,2194580.3170065433,8,-120000,"2007-01-01",null,2294781.2017264627,-120000,"2007-01-01",null,null,null,1,-120000,"2007-01-01",null,-120000,"2007-01-01",null,null,null,1,-120000,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(32:32))-4,2)","v":{"$error":"#NUM!"}},"",null,1,-120000,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(32:32))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
[1987,0.16322041975949195,0.16820288300514225,0.14676902890205387,null,null,null,null,null,null,null,null,{"$f":"IF(I33=\"\",\"\",CH33)","v":""},{"$f":"IF(I33=\"\",\"\",CM33)","v":""},"",null,2008,9,-0.37942650405256834,72000,1792855.624142563,1157279.9741113798,0,0.08,48000,501925.57758389995,593919.62379061198,0,0.77226691296473282,0.22773308703526732,0.66084984001700786,0.33915015998299214,null,2008,9,-0.37942650405256834,72000,1316748.1902039258,861820.3193855189,0,0.08,48000,877832.12680261733,999898.69694682676,0,0.6,0.4,0.46291642929196503,0.53708357070803503,null,1861719.0163323455,9,-120000,"2008-01-01",null,1751199.5979019918,-120000,"2008-01-01",null,null,null,1,-120000,"2008-01-01",null,-120000,"2008-01-01",null,null,null,1,-120000,null,{"$f":"INDEX($BS$5:$BT$37,SMALL(IF($BS$5:$BT$37=1,ROW($BS$5:$BT$37)),ROW(33:33))-4,2)","v":{"$error":"#NUM!"}},"",null,1,-120000,null,{"$f":"INDEX($BY$5:$BZ$37,SMALL(IF($BY$5:$BZ$37=1,ROW($BY$5:$BZ$37)),ROW(33:33))-4,2)","v":{"$error":"#NUM!"}},"",null,null,0,0,0,1,null,null,0,0,1]
[1988,0.12028633333831158,0.17300785183906556,0.15693004727363591,null,null,null,null,null,null,null,null,{"$f":"IF(I34=\"\",\"\",CH34)","v":""},{"$f":"IF(I34=\"\",\"\",CM34)","v":""},"",null,2009,10,0.80532523046814641,72000,1157279.9741113798,2219250.1525725038,0,0.09,48000,593919.62379061198,699692.38993176713,0,0.65694754075923334,0.34305245924076666,0.76029251013229104,0.23970748986770896,null,2009,10,0.80532523046814641,72000,1117031.4097994072,2146588.40392998,0,0.09,48000,744687.60653293831,864029.49112090282,0,0.6,0.4,0.71300592727450729,0.28699407272549271,null,3010617.8950508828,10,-120000,"2009-01-01",null,2918942.542504271,-120000,"2009-01-01",null,null,null,1,-120000,"2009-01-01",null,-120000,"2009-01-01",null,null,null,1,-120000,null,null,null,null,1,-120000,null,null,null,null,null,0,0,0,1,null,null,0,0,1]
[1989,0.1538160901762779,0.11627735495567321,9.5668938755989072E-2,null,null,null,null,null,null,null,null,{"$f":"IF(I35=\"\",\"\",CH35)","v":""},{"$f":"IF(I35=\"\",\"\",CM35)","v":""},"",null,2010,11,0.10943116334797741,72000,2219250.1525725038,2541984.3222897435,2541984.3222897435,0.1,48000,699692.38993176713,822461.62892494386,822461.62892494386,0.75396297248988986,0.24603702751011006,0.75554321845235606,0.24445678154764391,null,2010,11,0.10943116334797741,72000,1806370.7370305296,2083923.0319825783,2083923.0319825783,0.1,48000,1204247.1580203532,1377471.8738223887,1377471.8738223887,0.6,0.4,0.60204717713304379,0.39795282286695621,null,3461394.905804967,11,-120000,"2010-01-01",null,3364445.9512146874,-120000,"2010-01-01",null,null,null,1,-120000,"2010-01-01",null,-120000,"2010-01-01",null,null,null,1,-120000,null,null,null,null,1,-120000,null,null,null,null,null,0,0,0,1,null,null,0,0,1]
[1990,0.10720888087346088,8.9413604140281697E-2,7.4585077166557309E-2,null,null,null,null,null,null,null,null,{"$f":"IF(I36=\"\",\"\",CH36)","v":""},{"$f":"IF(I36=\"\",\"\",CM36)","v":""},"",null,2011,0,-0.10495242993057337,0,0,0,0,0.11,0,0,0,0,0,0,0,0,null,2011,0,-0.10495242993057337,0,0,0,0,0.11,0,0,0,0,0,0,0,0,null,0,0,3461394.905804967,"2011-01-01",null,0,3364445.9512146874,"2011-01-01",null,null,null,1,3461394.905804967,"2011-01-01",null,3364445.9512146874,"2011-01-01",null,null,null,1,3461394.905804967,null,null,null,null,1,3364445.9512146874,null,null,null,null,null,0,0,0,1,null,null,0,0,1]
[1991,3.2629568980431206E-2,4.973492920398713E-2,4.4967147707939165E-2,null,null,null,null,"Final CAGR is the last entry",null,null,null,null,null,null,null,"Note 35% returns listed against 1980 actually is the return",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0,-1E-300,"00:00:00",null,0,-1E-300,"00:00:00",null,null,null,0,-1E-300,"00:00:00",null,-1E-300,"00:00:00",null,null,null,"",-1E-300,null,null,null,null,"",-1E-300]
[1992,6.4014439603712989E-2,8.5082140564918529E-2,7.6039698719978344E-2,null,null,null,null,null,null,null,null,null,null,null,null,"generated in the financial year 1980-1981"]
[1993,5.7611027274921796E-2,9.3434426188468908E-2,8.5308435559272797E-2,null,null,null,null,"Relation between CAGR and annual returns:"]
[1994,3.6503742521873855E-2,0.13442491888999944,0.12930799126625062,null,null,null,null,"CAGR at the end of the second investment year is 9.8%"]
[1995,1.5191724806190032E-2,0.13995973467826842,0.13781223893165592,null,null,null,null,"CAGR at the end of the first investment year is 40.75%. This is also the"]
[1996,-9.9289402089443451E-3,0.1465568959712982,0.14797504544258122,null,null,null,null,"annual return after the first year. Using both these the effective annual"]
[1997,8.8793374660244215E-2,0.10177865624427795,8.9472016692161593E-2,null,null,null,null,"return at the end of the second year is calculated to be -21.99%"]
[1998,4.9005598912082601E-2,0.1488471329212189,0.14204559922218327,null,null,null,null,"That is returns at the end of year one is 40.75% and at the end of "]
[1999,3.6830439117394954E-2,0.14941253066062926,0.1442721903324127,null,null,null,null,"year two is -21.99%, the CAGR at the end of year two is 9.8%"]
[2000,6.2797080293447091E-2,0.13136433959007263,0.12265719771385192,null,null,null,null,"The formula is: (1+40.75%)*(1-21.99%)-1=9.8%"]