{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Plan1","Premissas","Instruções","parametros"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":0,"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":"Plan1","index":0,"n_rows":34,"n_cols":3,"rows_kept":34,"cut":false,"hidden":true,"merged":[[4,0,5,0]]}
["CNPB"]
["Ano de cálculo"]
["Prazo do Fluxo"]
["TA"]
[]
[]
[]
["FLUXOS ANUAIS PROJETADOS",{"$f":"+Premissas!B8","v":2014},{"$f":"+B8+1","v":2015}]
["FLUXOS DE SEGURIDADE para o cálculo da duration (Fi)",{"$f":"+B10-B20","v":0},{"$f":"+C10-C20","v":0}]
["RECEBIMENTOS de Assistidos",{"$f":"SUM(B11:B14)","v":0},{"$f":"SUM(C11:C14)","v":0}]
["- Contribuições Normais de Assistidos"]
["- Contribuições Normais da Patrocinadora (Contraparte Contribuição de  Assistidos)"]
["- Contribuições Extraordinárias de assistidos"]
["- Contribuições Extraordinárias da Patrocinadora (Contraparte Contribuição de Assistidos)"]
["RECEBIMENTOS de Ativos *",{"$f":"SUM(B16:B19)","v":0},{"$f":"SUM(C16:C19)","v":0}]
["- Contribuições Normais de Ativos *"]
["- Contribuições Normais da Patrocinadora (Contraparte Contribuição de Ativos) *"]
["- Contribuições Extraordinárias de Ativos *"]
["- Contribuições Extraordinárias da Patrocinadora (Contraparte Contribuição de Ativos) *"]
["PAGAMENTOS",{"$f":"SUM(B21:B24)","v":0},{"$f":"SUM(C21:C24)","v":0}]
["- Benefícios Programados"]
["- Benefícios de Risco"]
["- Resgates"]
["- Portabilidade"]
["* Fluxos não considerados no cálculo da Duration."]
[]
[]
[]
[]
[]
[]
[]
["Numerador",{"$f":"(B9/(1+$B$4)^(B8-$B$8+0.5))*(B8-$B$8+0.5)","v":0},{"$f":"(C9/(1+$B$4)^(C8-$B$8+0.5))*(C8-$B$8+0.5)","v":0}]
["Denominador",{"$f":"(B9/(1+$B$4)^(B8-$B$8+0.5))","v":0},{"$f":"(C9/(1+$B$4)^(C8-$B$8+0.5))","v":0}]

{"sheet":"Premissas","index":1,"n_rows":10,"n_cols":20,"rows_kept":10,"cut":false,"hidden":false,"merged":[[3,0,3,12]]}
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"RANDBETWEEN(1,1000)","v":875},727,{"$f":"RANDBETWEEN(1,1000)","v":517},537]
[]
[]
["Planilha para o cálculo da Duração e Taxa de Juros Parâmetro"]
[]
["Informações"]
["Data da avaliação","2013-12-31","A avaliação de encerramento de exercício que gerou os fluxos a serem informados "]
["Ano de cálculo",2014]
["Prazo do fluxo",null,"anos"]
["TA",null,"onde, TA = a taxa de juros real anual aplicada no ano anterior pelo respectivo plano de benefícios."]

{"sheet":"Instruções","index":2,"n_rows":25,"n_cols":3,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[]
["A Planilha está formatada para o cálculo automático da Duração do passivo calculada e taxa parâmetro."]
["Para geração da tabela, deverão ser observados os seguintes procedimentos:"]
[null,"Ao abrir a tabela o usuário deverá autorizar a execução de macros."]
[null,"Para iniciar o cálculo, o usuário deverá preencher os campos: Prazo do Fluxo e TA"]
[null,null,"Prazo do Fluxo é a quantidade de anos para a projeção do pagamento de benefícios."]
[null,null,"TA é a taxa de juros real anual aplicada no ano anterior pelo respectivo plano de benefícios."]
["Após esse preenchimento deverá clicar no botão \"Gerar Tabela\"."]
["Uma macro será executada e criará uma tabela para a inserção dos fluxos até o prazo informado."]
["Deverão ser preenchidos, em módulo, os valores por ano para os campos: "]
[null,"Contribuições Normais de Assistidos"]
[null,"Contribuições Normais da Patrocinadora (Contraparte Contribuição de Assistidos)"]
[null,"Contribuições Extraordinárias de assistidos"]
[null,"Contribuições Extraordinárias da Patrocinadora (Contraparte Contribuição de Assistidos)"]
[null,"Contribuições Normais de Ativos"]
[null,"Contribuições Normais da Patrocinadora (Contraparte Contribuição de Ativos)"]
[null,"Contribuições Extraordinárias de Ativos"]
[null,"Contribuições Extraordinárias da Patrocinadora (Contraparte Contribuição de Ativos)"]
[null,"Benefícios Programados"]
[null,"Benefícios de Risco"]
[null,"Resgates"]
[null,"Portabilidade"]
["Após esse preenchimento a planilha calculará a duração do passivo e as taxas de referência, mínima e máxima"]
["Esta aba poderá ser exportada clicando no botão \"Exportar Fluxo\". Assim o usuário deverá \"salvar como\" caso queira guardar o resultado."]
["A mesma planilha poderá ser utilizada no cálculo da duração do passivo de mais de um CNPB, bastando para isso, após a exportação dos resultados clicar no botão \"Excluir aba do Fluxo\" e reiniciar a operação."]

{"sheet":"parametros","index":3,"n_rows":70,"n_cols":10,"rows_kept":70,"cut":false,"hidden":true,"merged":[]}
["Prazo anos",null,"Média 3 anos","Máximo","Mínimo",100]
[1,{"$f":"A2*2","v":2},3.2100952380952411E-2,{"$f":"+C2+0.004","v":3.6100952380952414E-2},{"$f":"ROUND(C2*0.7,4)","v":2.2499999999999999E-2},null,1,3.2100952380952408,{"$f":"H2+0.4","v":3.6100952380952407},{"$f":"ROUND(H2*0.7,2)","v":2.25}]
[1.5,{"$f":"A3*2","v":3},3.6410101851851846E-2,4.0410101851851843E-2,2.5499999999999998E-2,null,1.5,3.6410101851851846,4.0410101851851845,{"$f":"ROUND(H3*0.7,2)","v":2.5499999999999998}]
[2,4,3.9949100529100542E-2,4.3949100529100546E-2,2.8000000000000001E-2,null,2,3.9949100529100541,4.3949100529100544,2.8]
[2.5,5,4.2479580687830698E-2,4.6479580687830702E-2,2.9700000000000001E-2,null,2.5,4.2479580687830696,4.6479580687830699,2.97]
[3,6,4.4305383597883594E-2,4.8305383597883597E-2,3.1E-2,null,3,4.4305383597883594,4.8305383597883598,3.1]
[3.5,7,4.5667653439153452E-2,4.9667653439153456E-2,3.2000000000000001E-2,null,3.5,4.5667653439153453,4.9667653439153456,3.2]
[4,8,4.6720251322751309E-2,5.0720251322751306E-2,3.27E-2,null,4,4.6720251322751309,5.0720251322751313,3.27]
[4.5,9,4.7559207671957621E-2,5.1559207671957624E-2,3.3300000000000003E-2,null,4.5,4.7559207671957617,5.1559207671957621,3.33]
[5,10,4.8245589947089933E-2,5.2245589947089929E-2,3.3799999999999997E-2,null,5,4.8245589947089931,5.2245589947089934,3.38]
[5.5,11,4.8819480158730159E-2,5.2819480158730156E-2,3.4200000000000001E-2,null,5.5,4.8819480158730162,5.2819480158730165,3.42]
[6,12,4.9307941798941865E-2,5.3307941798941869E-2,3.4500000000000003E-2,null,6,4.9307941798941863,5.3307941798941867,3.45]
[6.5,13,4.9729904761904781E-2,5.3729904761904784E-2,3.4799999999999998E-2,null,6.5,4.9729904761904784,5.3729904761904788,3.48]
[7,14,5.0099083333333329E-2,5.4099083333333325E-2,3.5099999999999999E-2,null,7,5.0099083333333327,5.4099083333333331,3.51]
[7.5,15,5.0425458994708974E-2,5.4425458994708978E-2,3.5299999999999998E-2,null,7.5,5.0425458994708974,5.4425458994708977,3.53]
[8,16,5.0716596560846587E-2,5.4716596560846584E-2,3.5499999999999997E-2,null,8,5.071659656084659,5.4716596560846593,3.55]
[8.5,17,5.0978324074074044E-2,5.4978324074074048E-2,3.5700000000000003E-2,null,8.5,5.0978324074074042,5.4978324074074045,3.57]
[9,18,5.1215267195767245E-2,5.5215267195767248E-2,3.5900000000000001E-2,null,9,5.1215267195767247,5.521526719576725,3.59]
[9.5,19,5.1431037037037081E-2,5.5431037037037084E-2,3.5999999999999997E-2,null,9.5,5.1431037037037077,5.5431037037037081,3.6]
[10,20,5.1628589947089916E-2,5.5628589947089913E-2,3.61E-2,null,10,5.1628589947089916,5.5628589947089919,3.61]
[10.5,21,5.1810318783068764E-2,5.581031878306876E-2,3.6299999999999999E-2,null,10.5,5.1810318783068761,5.5810318783068764,3.63]
[11,22,5.1978219576719546E-2,5.5978219576719543E-2,3.6400000000000002E-2,null,11,5.1978219576719544,5.5978219576719548,3.64]
[11.5,23,5.2133962962962972E-2,5.6133962962962969E-2,3.6499999999999998E-2,null,11.5,5.2133962962962972,5.6133962962962975,3.65]
[12,24,5.2278912698412665E-2,5.6278912698412661E-2,3.6600000000000001E-2,null,12,5.2278912698412663,5.6278912698412666,3.66]
[12.5,25,5.2414242063492059E-2,5.6414242063492062E-2,3.6700000000000003E-2,null,12.5,5.2414242063492056,5.6414242063492059,3.67]
[13,26,5.2541033068783077E-2,5.6541033068783081E-2,3.6799999999999999E-2,null,13,5.2541033068783074,5.6541033068783078,3.68]
[13.5,27,5.2660093915344014E-2,5.6660093915344018E-2,3.6900000000000002E-2,null,13.5,5.2660093915344017,5.6660093915344021,3.69]
[14,28,5.2772205026455063E-2,5.6772205026455067E-2,3.6900000000000002E-2,null,14,5.2772205026455064,5.6772205026455067,3.69]
[14.5,29,5.2877973544973589E-2,5.6877973544973592E-2,3.6999999999999998E-2,null,14.5,5.2877973544973589,5.6877973544973592,3.7]
[15,30,5.2978058201058172E-2,5.6978058201058168E-2,3.7100000000000001E-2,null,15,5.2978058201058174,5.6978058201058177,3.71]
[15.5,31,5.3072858465608483E-2,5.7072858465608486E-2,3.7199999999999997E-2,null,15.5,5.3072858465608483,5.7072858465608487,3.72]
[16,32,5.3162895502645503E-2,5.71628955026455E-2,3.7199999999999997E-2,null,16,5.3162895502645506,5.716289550264551,3.72]
[16.5,33,5.3248531746031752E-2,5.7248531746031756E-2,3.73E-2,null,16.5,5.3248531746031755,5.7248531746031759,3.73]
[17,34,5.333008862433869E-2,{"$f":"+C34+0.004","v":5.7330088624338693E-2},3.73E-2,null,17,5.3330088624338687,{"$f":"H34+0.4","v":5.7330088624338691},3.73]
[17.5,35,5.3407932539682478E-2,5.7407932539682474E-2,3.7400000000000003E-2,null,17.5,5.3407932539682479,5.7407932539682482,3.74]
[18,36,5.3482306878306879E-2,5.7482306878306882E-2,3.7400000000000003E-2,null,18,5.3482306878306876,5.7482306878306879,3.74]
[18.5,37,5.355347486772491E-2,5.7553474867724913E-2,3.7499999999999999E-2,null,18.5,5.3553474867724908,5.7553474867724912,3.75]
[19,38,5.362162566137562E-2,5.7621625661375617E-2,3.7499999999999999E-2,null,19,5.3621625661375623,5.7621625661375626,3.75]
[19.5,39,5.3687034391534418E-2,5.7687034391534414E-2,3.7600000000000001E-2,null,19.5,5.3687034391534416,5.768703439153442,3.76]
[20,40,5.3749809523809505E-2,5.7749809523809509E-2,3.7600000000000001E-2,null,20,5.3749809523809509,5.7749809523809512,3.76]
[20.5,41,5.3810166666666638E-2,5.7810166666666635E-2,3.7699999999999997E-2,null,20.5,5.3810166666666639,5.7810166666666642,3.77]
[21,42,5.3868255291005335E-2,5.7868255291005338E-2,3.7699999999999997E-2,null,21,5.3868255291005331,5.7868255291005335,3.77]
[21.5,43,5.3924203703703683E-2,5.7924203703703686E-2,3.7699999999999997E-2,null,21.5,5.3924203703703686,5.7924203703703689,3.77]
[22,44,5.3978137566137632E-2,5.7978137566137636E-2,3.78E-2,null,22,5.3978137566137629,5.7978137566137633,3.78]
[22.5,45,5.4030190476190419E-2,5.8030190476190416E-2,3.78E-2,null,22.5,5.4030190476190416,5.803019047619042,3.78]
[23,46,5.4080419312169337E-2,5.808041931216934E-2,3.7900000000000003E-2,null,23,5.4080419312169337,5.808041931216934,3.79]
[23.5,47,5.4129002645502661E-2,5.8129002645502664E-2,3.7900000000000003E-2,null,23.5,5.4129002645502657,5.8129002645502661,3.79]
[24,48,5.4175989417989394E-2,5.817598941798939E-2,3.7900000000000003E-2,null,24,5.4175989417989392,5.8175989417989395,3.79]
[24.5,49,5.4221432539682493E-2,5.8221432539682497E-2,3.7999999999999999E-2,null,24.5,5.4221432539682493,5.8221432539682496,3.8]
[25,50,5.426547089947089E-2,5.8265470899470886E-2,3.7999999999999999E-2,null,25,5.4265470899470891,5.8265470899470895,3.8]
[25.5,51,5.4308129629629616E-2,5.8308129629629613E-2,3.7999999999999999E-2,null,25.5,5.4308129629629613,5.8308129629629617,3.8]
[26,52,5.4349509259259313E-2,5.8349509259259316E-2,3.7999999999999999E-2,null,26,5.4349509259259312,5.8349509259259316,3.8]
[26.5,53,5.4389650793650775E-2,5.8389650793650771E-2,3.8100000000000002E-2,null,26.5,5.4389650793650777,5.838965079365078,3.81]
[27,54,5.4428636243386216E-2,5.842863624338622E-2,3.8100000000000002E-2,null,27,5.4428636243386217,5.8428636243386221,3.81]
[27.5,55,5.4466480158730145E-2,5.8466480158730141E-2,3.8100000000000002E-2,null,27.5,5.4466480158730146,5.846648015873015,3.81]
[28,56,5.4503277777777838E-2,5.8503277777777835E-2,3.8199999999999998E-2,null,28,5.4503277777777841,5.8503277777777845,3.82]
[28.5,57,5.4539019841269874E-2,5.8539019841269871E-2,3.8199999999999998E-2,null,28.5,5.4539019841269871,5.8539019841269875,3.82]
[29,58,5.4573802910052968E-2,5.8573802910052972E-2,3.8199999999999998E-2,null,29,5.4573802910052969,5.8573802910052972,3.82]
[29.5,59,5.4607665343915285E-2,5.8607665343915288E-2,3.8199999999999998E-2,null,29.5,5.4607665343915288,5.8607665343915292,3.82]
[30,60,5.4640656084656049E-2,5.8640656084656045E-2,3.8199999999999998E-2,null,30,5.4640656084656047,5.8640656084656051,3.82]
[30.5,61,5.467275000000002E-2,5.8672750000000023E-2,3.8300000000000001E-2,null,30.5,5.4672750000000017,5.867275000000002,3.83]
[31,62,5.4704035714285698E-2,5.8704035714285702E-2,3.8300000000000001E-2,null,31,5.4704035714285695,5.8704035714285698,3.83]
[31.5,63,5.4734526455026453E-2,5.8734526455026456E-2,3.8300000000000001E-2,null,31.5,5.4734526455026451,5.8734526455026455,3.83]
[32,64,5.4764243386243333E-2,5.8764243386243337E-2,3.8300000000000001E-2,null,32,5.4764243386243336,5.876424338624334,3.83]
[32.5,65,5.479326587301582E-2,5.8793265873015824E-2,3.8399999999999997E-2,null,32.5,5.479326587301582,5.8793265873015823,3.84]
[33,66,5.4821552910052897E-2,{"$f":"+C66+0.004","v":5.8821552910052893E-2},{"$f":"ROUND(C66*0.7,4)","v":3.8399999999999997E-2},null,33,5.4821552910052898,{"$f":"H66+0.4","v":5.8821552910052901},3.84]
[33.5,{"$f":"A67*2","v":67},5.4849185185185174E-2,5.8849185185185171E-2,3.8399999999999997E-2,null,33.5,5.4849185185185174,5.8849185185185178,{"$f":"ROUND(H67*0.7,2)","v":3.84}]
[34,68,5.4876169312169355E-2,5.8876169312169352E-2,3.8399999999999997E-2,null,34,5.4876169312169356,5.887616931216936,3.84]
[34.5,69,5.4902485449735458E-2,5.8902485449735462E-2,3.8399999999999997E-2,null,34.5,5.4902485449735456,5.890248544973546,3.84]
[35,70,5.4928220899470921E-2,5.8928220899470918E-2,{"$f":"ROUND(C70*0.7,4)","v":3.8399999999999997E-2},null,35,5.4928220899470919,5.8928220899470922,3.84]