{"text_format":"3","workbook":"xlsx","n_sheets":5,"sheets":["Maand","4-weken","Gewogen salaris","Versiebeheer","Tijdvakken"],"not_rendered":{"macros":1,"defined_names":4},"read":"whole","date_system":"1900"}

{"sheet":"Maand","index":0,"n_rows":49,"n_cols":27,"rows_kept":49,"merged":[[0,0,0,1]]}
["Invoer"]
["Geboortedatum dlnr","1986-01-01"]
["Datum aanvang deelneming","2017-01-09",{"$nulls":20},"Leeftijd","Afgerond leeftijd",null,"Leeftijd","Bedrag AP"]
["Datum einde deelneming",{"$nulls":21},{"$f":"(YEAR(B3)-YEAR(B2))+((MONTH(B3)-MONTH(B2))/12)+((DAY(B3)-DAY(B2))/360)","v":31.022222222222222},{"$f":"ROUNDDOWN(W4,0)","v":31},null,15,{"v":6032,"d":"€ 6,032.00"}]
["Regelingloon",{"v":45000,"d":"€ 45,000.00"}]
["Normuren per maand",156]
["Franchise OP/NP",{"v":11829,"d":"€ 11,829.00"},{"$nulls":23},16,{"v":6937,"d":"€ 6,937.00"}]
["Franchise AP",{"$f":"IFERROR(VLOOKUP(X4,Z4:AA13,2,FALSE),20108)","v":{"v":20108,"d":"€ 20,108.00"}},{"$nulls":23},17,{"v":7943,"d":"€ 7,943.00"}]
["Maximum pensioengevend salaris",{"v":103317,"d":"€ 103,317.00"},{"$nulls":23},18,{"v":9149,"d":"€ 9,149.00"}]
["Premie % OP/OP",{"v":0.235,"d":"23.5%"},{"$nulls":23},19,{"v":10557,"d":"€ 10,557.00"}]
["Premie % AP",{"v":0.005,"d":"0.5%"},{"$nulls":23},20,{"v":12367,"d":"€ 12,367.00"}]
[{"$nulls":25},21,{"v":14578,"d":"€ 14,578.00"}]
["OP/NP premie",{"$nulls":24},22,{"v":17092,"d":"€ 17,092.00"}]
["Maand","Maand getal","Genormeerd aantal dagen deelneming","Verloonde uren voor regeling","Normuren per maand","Normuren deelneming","Regelingloon","Deeltijd regelingloon cumulatief","Maandbedrag ORT","Jaarbedrag ORT Cumulatief","Definitieve deeltijdfactor","PT jaar Franchise Cumulatief ","Deeltijd PG (ongemaximeerd) Cumulatief incl. negatief","Deeltijd PG (ongemaximeerd) Cumulatief","Max PT PG cumulatief","Max FT PG cumulatief","PG Gemaximeerd ","Aanwas pensioengrondslag",null,"Premie"]
[]
["januari",1,{"$f":"IF(MONTH($B$3)>B16,0,IF(AND(MONTH($B$3)=B16,IF($B$4=\"\",13,MONTH($B$4))=B16),(DAY(DATE(YEAR($B$4),MONTH($B$4),DAY($B$4))-DAY($B$3)+1)*(30/DAY(DATE(YEAR($B$4),MONTH($B$4),DAY(EOMONTH(DATE(YEAR($B$4),MONTH($B$4),1),0)))))),IF(MONTH($B$3)=B16,(DAY(DATE(YEAR($B$3),B16+1,0)-DAY($B$3)+1)*(30/DAY(DATE(YEAR($B$3),B16+1,0)))),IF(MONTH($B$4)=B16,(DAY(DATE(YEAR($B$4),MONTH($B$4),DAY($B$4)))*(30/DAY(DATE(YEAR($B$4),MONTH($B$4),DAY(EOMONTH(DATE(YEAR($B$4),MONTH($B$4),1),0)))))),IF(IF($B$4=\"\",13,MONTH($B$4))<B16,0,(30/DAY(DATE(YEAR($B$4),B16,0)))*DAY(DATE(YEAR($B$4),B16,0)))))))","v":22.258064516129032},78,156,{"$f":"IF(SUM(C16:$C$27)=0,\"\",E16*C16/30)","v":115.74193548387096},{"$f":"FT_jaar_salaris","v":{"v":45000,"d":"€ 45,000"}},{"$f":"SUMPRODUCT($G$16:G16,$K$16:K16)","v":{"v":30325.500000000004,"d":"€ 30,325.50"}},{"v":200,"d":"€ 200.00"},{"$f":"IF(C16=0,J15,SUM(I16)*12*30/C16+J15)","v":{"v":3234.782608695652,"d":"€ 3,234.78"}},{"$f":"IFERROR(IF(SUM(C16:$C$27)=0,\"\",ROUND(D16/F16,4)),0)","v":{"v":0.6739,"d":"0.67390"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K16),0)","v":{"v":7971.5631,"d":"€ 7,971.56"}},{"$f":"IF(K16=\"\",M15,(H16+J16-J15-L16-H15+L15)*C16/30+M15)","v":{"v":18985.178990322584,"d":"€ 18,985.18"}},{"$f":"MAX(0,M16)","v":{"v":18985.178990322584,"d":"€ 18,985.18"}},{"$f":"IF(K16=\"\",O15,($B$9-$B$7)*K16*C16/30+O15)","v":{"v":45743.11463225807,"d":"€ 45,743.11"}},{"$f":"($B$9-$B$7)*SUM($C$16:C16)/30","v":{"v":67878.19354838709,"d":"€ 67,878.19"}},{"$f":"MIN(N16,O16,P16)","v":{"v":18985.178990322584,"d":"€ 18,985.18"}},{"$f":"(Q16-Q15)/12","v":{"v":1582.0982491935486,"d":"€ 1,582.10"}},null,{"$f":"(Q16-Q15)/12*$B$10","v":{"v":371.7930885604839,"d":"€ 371.79"}}]
["februari",2,30,156,156,{"$f":"IF(SUM(C17:$C$27)=0,\"\",E17*C17/30)","v":156},{"$f":"FT_jaar_salaris","v":{"v":45000,"d":"€ 45,000"}},{"$f":"SUMPRODUCT($G$16:G17,$K$16:K17)","v":{"v":75325.5,"d":"€ 75,325.50"}},null,{"v":3234.782608695652,"d":"€ 3,234.78"},{"$f":"IFERROR(IF(SUM(C17:$C$27)=0,\"\",ROUND(D17/F17,4)),0)","v":{"v":1,"d":"1.00000"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K17),L16)","v":{"v":19800.5631,"d":"€ 19,800.56"}},{"$f":"IF(K17=\"\",M16,(H17+J17-J16-L17-H16+L16)*C17/30+M16)","v":{"v":52156.17899032259,"d":"€ 52,156.18"}},{"$f":"MAX(0,M17)","v":{"v":52156.17899032259,"d":"€ 52,156.18"}},{"$f":"IF(K17=\"\",O16,($B$9-$B$7)*K17*C17/30+O16)","v":{"v":137231.11463225808,"d":"€ 137,231.11"}},{"$f":"($B$9-$B$7)*SUM($C$16:C17)/30","v":{"v":159366.1935483871,"d":"€ 159,366.19"}},{"$f":"MIN(N17,O17,P17)","v":{"v":52156.17899032259,"d":"€ 52,156.18"}},{"$f":"(Q17-Q16)/12","v":{"v":2764.25,"d":"€ 2,764.25"}},null,{"$f":"(Q17-Q16)/12*$B$10","v":{"v":649.59875,"d":"€ 649.60"}}]
["maart",3,30.000000000000004,156,156,{"$f":"IF(SUM(C18:$C$27)=0,\"\",E18*C18/30)","v":156.00000000000003},{"v":45000,"d":"€ 45,000"},{"$f":"SUMPRODUCT($G$16:G18,$K$16:K18)","v":{"v":120325.5,"d":"€ 120,325.50"}},null,{"v":3234.782608695652,"d":"€ 3,234.78"},{"$f":"IFERROR(IF(SUM(C18:$C$27)=0,\"\",ROUND(D18/F18,4)),0)","v":{"v":1,"d":"1.00000"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K18),L17)","v":{"v":31629.5631,"d":"€ 31,629.56"}},{"v":85327.1789903226,"d":"€ 85,327.18"},{"v":85327.1789903226,"d":"€ 85,327.18"},{"v":228719.11463225808,"d":"€ 228,719.11"},{"$f":"($B$9-$B$7)*SUM($C$16:C18)/30","v":{"v":250854.1935483871,"d":"€ 250,854.19"}},{"v":85327.1789903226,"d":"€ 85,327.18"},{"$f":"(Q18-Q17)/12","v":{"v":2764.2500000000014,"d":"€ 2,764.25"}},null,{"v":649.5987500000003,"d":"€ 649.60"}]
["april",4,30,156,156,{"$f":"IF(SUM(C19:$C$27)=0,\"\",E19*C19/30)","v":156},{"v":45000,"d":"€ 45,000"},{"$f":"SUMPRODUCT($G$16:G19,$K$16:K19)","v":{"v":165325.5,"d":"€ 165,325.50"}},null,{"v":3234.782608695652,"d":"€ 3,234.78"},{"$f":"IFERROR(IF(SUM(C19:$C$27)=0,\"\",ROUND(D19/F19,4)),0)","v":{"v":1,"d":"1.00000"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K19),L18)","v":{"v":43458.5631,"d":"€ 43,458.56"}},{"v":118498.1789903226,"d":"€ 118,498.18"},{"v":118498.1789903226,"d":"€ 118,498.18"},{"v":320207.1146322581,"d":"€ 320,207.11"},{"$f":"($B$9-$B$7)*SUM($C$16:C19)/30","v":{"v":342342.1935483871,"d":"€ 342,342.19"}},{"v":118498.1789903226,"d":"€ 118,498.18"},{"v":2764.25,"d":"€ 2,764.25"},null,{"v":649.59875,"d":"€ 649.60"}]
["mei",5,30,156,156,{"$f":"IF(SUM(C20:$C$27)=0,\"\",E20*C20/30)","v":156},{"v":45000,"d":"€ 45,000"},{"$f":"SUMPRODUCT($G$16:G20,$K$16:K20)","v":{"v":210325.5,"d":"€ 210,325.50"}},null,{"v":3234.782608695652,"d":"€ 3,234.78"},{"$f":"IFERROR(IF(SUM(C20:$C$27)=0,\"\",ROUND(D20/F20,4)),0)","v":{"v":1,"d":"1.00000"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K20),L19)","v":{"v":55287.5631,"d":"€ 55,287.56"}},{"v":151669.1789903226,"d":"€ 151,669.18"},{"v":151669.1789903226,"d":"€ 151,669.18"},{"v":411695.1146322581,"d":"€ 411,695.11"},{"$f":"($B$9-$B$7)*SUM($C$16:C20)/30","v":{"v":433830.1935483871,"d":"€ 433,830.19"}},{"v":151669.1789903226,"d":"€ 151,669.18"},{"v":2764.25,"d":"€ 2,764.25"},null,{"v":649.59875,"d":"€ 649.60"}]
["juni",6,30,156,156,{"$f":"IF(SUM(C21:$C$27)=0,\"\",E21*C21/30)","v":156},{"v":45000,"d":"€ 45,000"},{"$f":"SUMPRODUCT($G$16:G21,$K$16:K21)","v":{"v":255325.5,"d":"€ 255,325.50"}},null,{"v":3234.782608695652,"d":"€ 3,234.78"},{"$f":"IFERROR(IF(SUM(C21:$C$27)=0,\"\",ROUND(D21/F21,4)),0)","v":{"v":1,"d":"1.00000"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K21),L20)","v":{"v":67116.5631,"d":"€ 67,116.56"}},{"v":184840.1789903226,"d":"€ 184,840.18"},{"v":184840.1789903226,"d":"€ 184,840.18"},{"v":503183.1146322581,"d":"€ 503,183.11"},{"$f":"($B$9-$B$7)*SUM($C$16:C21)/30","v":{"v":525318.1935483871,"d":"€ 525,318.19"}},{"v":184840.1789903226,"d":"€ 184,840.18"},{"v":2764.25,"d":"€ 2,764.25"},null,{"v":649.59875,"d":"€ 649.60"}]
["juli",7,{"$f":"IF(MONTH($B$3)>B22,0,IF(AND(MONTH($B$3)=B22,IF($B$4=\"\",13,MONTH($B$4))=B22),(DAY(DATE(YEAR($B$4),MONTH($B$4),DAY($B$4))-DAY($B$3)+1)*(30/DAY(DATE(YEAR($B$4),MONTH($B$4),DAY(EOMONTH(DATE(YEAR($B$4),MONTH($B$4),1),0)))))),IF(MONTH($B$3)=B22,(DAY(DATE(YEAR($B$3),B22+1,0)-DAY($B$3)+1)*(30/DAY(DATE(YEAR($B$3),B22+1,0)))),IF(MONTH($B$4)=B22,(DAY(DATE(YEAR($B$4),MONTH($B$4),DAY($B$4)))*(30/DAY(DATE(YEAR($B$4),MONTH($B$4),DAY(EOMONTH(DATE(YEAR($B$4),MONTH($B$4),1),0)))))),IF(IF($B$4=\"\",13,MONTH($B$4))<B22,0,(30/DAY(DATE(YEAR($B$4),B22,0)))*DAY(DATE(YEAR($B$4),B22,0)))))))","v":30},156,156,{"$f":"IF(SUM(C22:$C$27)=0,\"\",E22*C22/30)","v":156},{"v":45000,"d":"€ 45,000"},{"$f":"SUMPRODUCT($G$16:G22,$K$16:K22)","v":{"v":300325.5,"d":"€ 300,325.50"}},null,{"v":3234.782608695652,"d":"€ 3,234.78"},{"$f":"IFERROR(IF(SUM(C22:$C$27)=0,\"\",ROUND(D22/F22,4)),0)","v":{"v":1,"d":"1.00000"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K22),L21)","v":{"v":78945.5631,"d":"€ 78,945.56"}},{"v":218011.1789903226,"d":"€ 218,011.18"},{"v":218011.1789903226,"d":"€ 218,011.18"},{"v":594671.1146322581,"d":"€ 594,671.11"},{"$f":"($B$9-$B$7)*SUM($C$16:C22)/30","v":{"v":616806.193548387,"d":"€ 616,806.19"}},{"v":218011.1789903226,"d":"€ 218,011.18"},{"v":2764.25,"d":"€ 2,764.25"},null,{"v":649.59875,"d":"€ 649.60"}]
["augustus",8,{"$f":"IF(MONTH($B$3)>B23,0,IF(AND(MONTH($B$3)=B23,IF($B$4=\"\",13,MONTH($B$4))=B23),(DAY(DATE(YEAR($B$4),MONTH($B$4),DAY($B$4))-DAY($B$3)+1)*(30/DAY(DATE(YEAR($B$4),MONTH($B$4),DAY(EOMONTH(DATE(YEAR($B$4),MONTH($B$4),1),0)))))),IF(MONTH($B$3)=B23,(DAY(DATE(YEAR($B$3),B23+1,0)-DAY($B$3)+1)*(30/DAY(DATE(YEAR($B$3),B23+1,0)))),IF(MONTH($B$4)=B23,(DAY(DATE(YEAR($B$4),MONTH($B$4),DAY($B$4)))*(30/DAY(DATE(YEAR($B$4),MONTH($B$4),DAY(EOMONTH(DATE(YEAR($B$4),MONTH($B$4),1),0)))))),IF(IF($B$4=\"\",13,MONTH($B$4))<B23,0,(30/DAY(DATE(YEAR($B$4),B23,0)))*DAY(DATE(YEAR($B$4),B23,0)))))))","v":30},156,156,{"$f":"IF(SUM(C23:$C$27)=0,\"\",E23*C23/30)","v":156},{"v":45000,"d":"€ 45,000"},{"$f":"SUMPRODUCT($G$16:G23,$K$16:K23)","v":{"v":345325.5,"d":"€ 345,325.50"}},null,{"v":3234.782608695652,"d":"€ 3,234.78"},{"$f":"IFERROR(IF(SUM(C23:$C$27)=0,\"\",ROUND(D23/F23,4)),0)","v":{"v":1,"d":"1.00000"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K23),L22)","v":{"v":90774.5631,"d":"€ 90,774.56"}},{"v":251182.1789903226,"d":"€ 251,182.18"},{"v":251182.1789903226,"d":"€ 251,182.18"},{"v":686159.1146322581,"d":"€ 686,159.11"},{"$f":"($B$9-$B$7)*SUM($C$16:C23)/30","v":{"v":708294.193548387,"d":"€ 708,294.19"}},{"v":251182.1789903226,"d":"€ 251,182.18"},{"v":2764.25,"d":"€ 2,764.25"},null,{"v":649.59875,"d":"€ 649.60"}]
["september",9,30,156,156,{"$f":"IF(SUM(C24:$C$27)=0,\"\",E24*C24/30)","v":156},{"v":45000,"d":"€ 45,000"},{"$f":"SUMPRODUCT($G$16:G24,$K$16:K24)","v":{"v":390325.5,"d":"€ 390,325.50"}},null,{"v":3234.782608695652,"d":"€ 3,234.78"},{"$f":"IFERROR(IF(SUM(C24:$C$27)=0,\"\",ROUND(D24/F24,4)),0)","v":{"v":1,"d":"1.00000"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K24),L23)","v":{"v":102603.5631,"d":"€ 102,603.56"}},{"v":284353.1789903226,"d":"€ 284,353.18"},{"v":284353.1789903226,"d":"€ 284,353.18"},{"v":777647.1146322581,"d":"€ 777,647.11"},{"$f":"($B$9-$B$7)*SUM($C$16:C24)/30","v":{"v":799782.193548387,"d":"€ 799,782.19"}},{"v":284353.1789903226,"d":"€ 284,353.18"},{"v":2764.2499999999977,"d":"€ 2,764.25"},null,{"v":649.5987499999994,"d":"€ 649.60"}]
["oktober",10,30,156,156,{"$f":"IF(SUM(C25:$C$27)=0,\"\",E25*C25/30)","v":156},{"v":45000,"d":"€ 45,000"},{"$f":"SUMPRODUCT($G$16:G25,$K$16:K25)","v":{"v":435325.5,"d":"€ 435,325.50"}},null,{"v":3234.782608695652,"d":"€ 3,234.78"},{"$f":"IFERROR(IF(SUM(C25:$C$27)=0,\"\",ROUND(D25/F25,4)),0)","v":{"v":1,"d":"1.00000"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K25),L24)","v":{"v":114432.5631,"d":"€ 114,432.56"}},{"v":317524.1789903225,"d":"€ 317,524.18"},{"v":317524.1789903225,"d":"€ 317,524.18"},{"v":869135.1146322581,"d":"€ 869,135.11"},{"$f":"($B$9-$B$7)*SUM($C$16:C25)/30","v":{"v":891270.193548387,"d":"€ 891,270.19"}},{"v":317524.1789903225,"d":"€ 317,524.18"},{"v":2764.249999999995,"d":"€ 2,764.25"},null,{"v":649.5987499999987,"d":"€ 649.60"}]
["november",11,30,156,156,{"$f":"IF(SUM(C26:$C$27)=0,\"\",E26*C26/30)","v":156},{"v":45000,"d":"€ 45,000"},{"$f":"SUMPRODUCT($G$16:G26,$K$16:K26)","v":{"v":480325.5,"d":"€ 480,325.50"}},null,{"v":3234.782608695652,"d":"€ 3,234.78"},{"$f":"IFERROR(IF(SUM(C26:$C$27)=0,\"\",ROUND(D26/F26,4)),0)","v":{"v":1,"d":"1.00000"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K26),L25)","v":{"v":126261.5631,"d":"€ 126,261.56"}},{"v":350695.1789903225,"d":"€ 350,695.18"},{"v":350695.1789903225,"d":"€ 350,695.18"},{"v":960623.1146322581,"d":"€ 960,623.11"},{"$f":"($B$9-$B$7)*SUM($C$16:C26)/30","v":{"v":982758.193548387,"d":"€ 982,758.19"}},{"v":350695.1789903225,"d":"€ 350,695.18"},{"v":2764.25,"d":"€ 2,764.25"},null,{"v":649.59875,"d":"€ 649.60"}]
["december",12,30,156,156,{"$f":"IF(SUM(C27:$C$27)=0,\"\",E27*C27/30)","v":156},{"v":45000,"d":"€ 45,000"},{"$f":"SUMPRODUCT($G$16:G27,$K$16:K27)","v":{"v":525325.5,"d":"€ 525,325.50"}},null,{"v":3234.782608695652,"d":"€ 3,234.78"},{"$f":"IFERROR(IF(SUM(C27:$C$27)=0,\"\",ROUND(D27/F27,4)),0)","v":{"v":1,"d":"1.00000"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K27),L26)","v":{"v":138090.5631,"d":"€ 138,090.56"}},{"v":383866.1789903225,"d":"€ 383,866.18"},{"v":383866.1789903225,"d":"€ 383,866.18"},{"v":1052111.1146322582,"d":"€ 1,052,111.11"},{"$f":"($B$9-$B$7)*SUM($C$16:C27)/30","v":{"v":1074246.1935483871,"d":"€ 1,074,246.19"}},{"v":383866.1789903225,"d":"€ 383,866.18"},{"v":2764.25,"d":"€ 2,764.25"},null,{"v":649.59875,"d":"€ 649.60"}]
[null,13]
["Totaal",{"$nulls":18},{"$f":"MAX(SUM(T16:T27),0)","v":{"v":7517.379338560482,"d":"€ 7,517.38"}}]
[]
[]
[]
["AP premie",{"$nulls":24},22,{"v":17092,"d":"€ 17,092.00"}]
["Maand","Maand getal","Genormeerd aantal dagen deelneming","Verloonde uren voor regeling","Normuren per maand","Normuren deelneming","Regelingloon","Deeltijd regelingloon cumulatief","Maandbedrag ORT","Jaarbedrag ORT","Definitieve deeltijdfactor","FT Jaar Franchise Cumulatief ","Deeltijd PG Cumulatief incl. negatief","Deeltijd PG Cumulatief",{"$nulls":3},"Aanwas pensioengrondslag",null,"Premie"]
[]
["januari",1,{"$f":"C16","v":22.258064516129032},{"$f":"D16","v":78},{"$f":"E16","v":156},115.74193548387096,{"v":45000,"d":"€ 45,000"},{"$f":"H16","v":{"v":30325.500000000004,"d":"€ 30,325.50"}},{"v":200,"d":"€ 200.00"},{"v":3234.782608695652,"d":"€ 3,234.78"},{"$f":"K16","v":{"v":0.6739,"d":"67.390%"}},{"$f":"COUNTIF($C$36:C36,\">0\")*$B$8","v":{"v":20108,"d":"€ 20,108.00"}},{"$f":"IF(K36=\"\",M35,(H36+J36-J35-L36-H35+L35)*C36/30+M35)","v":{"v":9980.725806451615,"d":"€ 9,980.73"}},{"$f":"MAX(0,M36)","v":{"v":9980.725806451615,"d":"€ 9,980.73"}},{"$nulls":3},{"$f":"(N36-N35)/12","v":{"v":831.7271505376347,"d":"€ 831.73"}},null,{"$f":"(N36-N35)/12*$B$11","v":{"v":4.158635752688173,"d":"€ 4.16"}}]
["februari",2,30,156,156,156,{"v":45000,"d":"€ 45,000"},{"v":75325.5,"d":"€ 75,325.50"},0,{"v":3234.782608695652,"d":"€ 3,234.78"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$36:C37,\">0\")*$B$8","v":{"v":40216,"d":"€ 40,216.00"}},{"$f":"IF(K37=\"\",M36,(H37+J37-J36-L37-H36+L36)*C37/30+M36)","v":{"v":34872.72580645161,"d":"€ 34,872.73"}},{"v":34872.72580645161,"d":"€ 34,872.73"},{"$nulls":3},{"$f":"(N37-N36)/12","v":{"v":2074.3333333333335,"d":"€ 2,074.33"}},null,{"v":10.371666666666668,"d":"€ 10.37"}]
["maart",3,30.000000000000004,156,156,156.00000000000003,{"v":45000,"d":"€ 45,000"},{"v":120325.5,"d":"€ 120,325.50"},0,{"$f":"J18","v":{"v":3234.782608695652,"d":"€ 3,234.78"}},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$36:C38,\">0\")*$B$8","v":{"v":60324,"d":"€ 60,324.00"}},{"v":59764.72580645162,"d":"€ 59,764.73"},{"v":59764.72580645162,"d":"€ 59,764.73"},{"$nulls":3},{"v":2074.333333333334,"d":"€ 2,074.33"},null,{"v":10.37166666666667,"d":"€ 10.37"}]
["april",4,30,156,156,156,{"v":45000,"d":"€ 45,000"},{"v":165325.5,"d":"€ 165,325.50"},0,{"v":3234.782608695652,"d":"€ 3,234.78"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$36:C39,\">0\")*$B$8","v":{"v":80432,"d":"€ 80,432.00"}},{"v":84656.72580645162,"d":"€ 84,656.73"},{"v":84656.72580645162,"d":"€ 84,656.73"},{"$nulls":3},{"v":2074.3333333333335,"d":"€ 2,074.33"},null,{"v":10.371666666666668,"d":"€ 10.37"}]
["mei",5,30,156,156,156,{"v":45000,"d":"€ 45,000"},{"v":210325.5,"d":"€ 210,325.50"},0,{"v":3234.782608695652,"d":"€ 3,234.78"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$36:C40,\">0\")*$B$8","v":{"v":100540,"d":"€ 100,540.00"}},{"v":109548.72580645162,"d":"€ 109,548.73"},{"v":109548.72580645162,"d":"€ 109,548.73"},{"$nulls":3},{"v":2074.3333333333335,"d":"€ 2,074.33"},null,{"v":10.371666666666668,"d":"€ 10.37"}]
["juni",6,30,156,156,156,{"v":45000,"d":"€ 45,000"},{"v":255325.5,"d":"€ 255,325.50"},0,{"v":3234.782608695652,"d":"€ 3,234.78"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$36:C41,\">0\")*$B$8","v":{"v":120648,"d":"€ 120,648.00"}},{"v":134440.72580645164,"d":"€ 134,440.73"},{"v":134440.72580645164,"d":"€ 134,440.73"},{"$nulls":3},{"v":2074.3333333333344,"d":"€ 2,074.33"},null,{"v":10.371666666666671,"d":"€ 10.37"}]
["juli",7,30,156,156,156,{"v":45000,"d":"€ 45,000"},{"v":300325.5,"d":"€ 300,325.50"},0,{"v":3234.782608695652,"d":"€ 3,234.78"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$36:C42,\">0\")*$B$8","v":{"v":140756,"d":"€ 140,756.00"}},{"v":159332.72580645164,"d":"€ 159,332.73"},{"v":159332.72580645164,"d":"€ 159,332.73"},{"$nulls":3},{"v":2074.3333333333335,"d":"€ 2,074.33"},null,{"v":10.371666666666668,"d":"€ 10.37"}]
["augustus",8,30,156,156,156,{"v":45000,"d":"€ 45,000"},{"v":345325.5,"d":"€ 345,325.50"},0,{"v":3234.782608695652,"d":"€ 3,234.78"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$36:C43,\">0\")*$B$8","v":{"v":160864,"d":"€ 160,864.00"}},{"v":184224.72580645164,"d":"€ 184,224.73"},{"v":184224.72580645164,"d":"€ 184,224.73"},{"$nulls":3},{"v":2074.3333333333335,"d":"€ 2,074.33"},null,{"v":10.371666666666668,"d":"€ 10.37"}]
["september",9,30,156,156,156,{"v":45000,"d":"€ 45,000"},{"v":390325.5,"d":"€ 390,325.50"},0,{"v":3234.782608695652,"d":"€ 3,234.78"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$36:C44,\">0\")*$B$8","v":{"v":180972,"d":"€ 180,972.00"}},{"v":209116.72580645164,"d":"€ 209,116.73"},{"v":209116.72580645164,"d":"€ 209,116.73"},{"$nulls":3},{"v":2074.3333333333335,"d":"€ 2,074.33"},null,{"v":10.371666666666668,"d":"€ 10.37"}]
["oktober",10,30,156,156,156,{"v":45000,"d":"€ 45,000"},{"v":435325.5,"d":"€ 435,325.50"},0,{"v":3234.782608695652,"d":"€ 3,234.78"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$36:C45,\">0\")*$B$8","v":{"v":201080,"d":"€ 201,080.00"}},{"v":234008.72580645164,"d":"€ 234,008.73"},{"v":234008.72580645164,"d":"€ 234,008.73"},{"$nulls":3},{"v":2074.3333333333335,"d":"€ 2,074.33"},null,{"v":10.371666666666668,"d":"€ 10.37"}]
["november",11,30,156,156,156,{"v":45000,"d":"€ 45,000"},{"v":480325.5,"d":"€ 480,325.50"},0,{"v":3234.782608695652,"d":"€ 3,234.78"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$36:C46,\">0\")*$B$8","v":{"v":221188,"d":"€ 221,188.00"}},{"v":258900.72580645164,"d":"€ 258,900.73"},{"v":258900.72580645164,"d":"€ 258,900.73"},{"$nulls":3},{"v":2074.3333333333335,"d":"€ 2,074.33"},null,{"v":10.371666666666668,"d":"€ 10.37"}]
["december",12,30,156,156,156,{"v":45000,"d":"€ 45,000"},{"v":525325.5,"d":"€ 525,325.50"},0,{"v":3234.782608695652,"d":"€ 3,234.78"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$36:C47,\">0\")*$B$8","v":{"v":241296,"d":"€ 241,296.00"}},{"v":283792.72580645164,"d":"€ 283,792.73"},{"v":283792.72580645164,"d":"€ 283,792.73"},{"$nulls":3},{"v":2074.3333333333335,"d":"€ 2,074.33"},null,{"v":10.371666666666668,"d":"€ 10.37"}]
[null,13]
["Totaal",{"$nulls":18},{"$f":"MAX(SUM(T36:T47),0)","v":{"v":118.24696908602154,"d":"€ 118.25"}}]

{"sheet":"4-weken","index":1,"n_rows":51,"n_cols":27,"rows_kept":51,"merged":[[0,0,0,1]]}
["Invoer"]
["Geboortedatum dlnr","1986-01-01"]
["Datum aanvang deelneming","2017-01-01",{"$nulls":20},"Leeftijd","Afgerond leeftijd",null,"Leeftijd","Bedrag AP"]
["Datum einde deelneming","2017-12-31",{"$nulls":20},{"$f":"(YEAR(B3)-YEAR(B2))+((MONTH(B3)-MONTH(B2))/12)+((DAY(B3)-DAY(B2))/360)","v":31},{"$f":"ROUNDDOWN(W4,0)","v":31},null,15,{"v":6032,"d":"€ 6,032.00"}]
["Regelingloon",{"v":50000,"d":"€ 50,000.00"}]
["Normuren per 4-weken",144]
["Franchise OP/NP",{"v":11829,"d":"€ 11,829.00"},{"$nulls":23},16,{"v":6937,"d":"€ 6,937.00"}]
["Franchise AP",{"$f":"IFERROR(VLOOKUP(X4,Z4:AA13,2,FALSE),20108)","v":{"v":20108,"d":"€ 20,108.00"}},{"$nulls":23},17,{"v":7943,"d":"€ 7,943.00"}]
["Maximum pensioengevend salaris",{"v":103317,"d":"€ 103,317.00"},{"$nulls":23},18,{"v":9149,"d":"€ 9,149.00"}]
["Premie % OP/OP",{"v":0.235,"d":"23.5%"},{"$nulls":23},19,{"v":10557,"d":"€ 10,557.00"}]
["Premie % AP",{"v":0.005,"d":"0.5%"},{"$nulls":23},20,{"v":12367,"d":"€ 12,367.00"}]
[{"$nulls":25},21,{"v":14578,"d":"€ 14,578.00"}]
["OP/NP premie",{"$nulls":24},22,{"v":17092,"d":"€ 17,092.00"}]
["Eerste dag periode","periode getal","Genormeerd aantal dagen deelneming","Verloonde uren voor regeling","Normuren per 4-weken","Normuren deelneming","Regelingloon","Deeltijd regelingloon cumulatief","4-weken ORT","Jaarbedrag ORT Cumulatief","DTF","PT jaar Franchise Cumulatief ","Deeltijd PG (ongemaximeerd) Cumulatief incl. negatief","Deeltijd PG (ongemaximeerd) Cumulatief","Max PT PG cumulatief","Max FT PG cumulatief","PG Gemaximeerd ","Aanwas pensioengrondslag",null,"Premie"]
[]
["2017-01-02",1,{"$f":"MAX(0,MIN(28,$B$4-A16+1))-MIN(28,MAX(0,$B$3-A16))","v":28},144,{"$f":"B$6","v":144},{"$f":"IF(SUM(C16:$C$28)=0,\"\",E16*C16/28)","v":144},{"$f":"FT_jaar_salaris","v":{"v":50000,"d":"€ 50,000"}},{"$f":"SUMPRODUCT($G$16:G16,$K$16:K16)","v":{"v":50000,"d":"€ 50,000.00"}},{"v":1000,"d":"€ 1,000.00"},{"$f":"IF(C16=0,J15,SUM(I16)*13*28/C16+J15)","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C16:$C$28)=0,\"\",ROUND(D16/F16,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K16),0)","v":{"v":11829,"d":"€ 11,829.00"}},{"$f":"IF(K16=\"\",M15,(H16+J16-J15-L16-H15+L15)*C16/28+M15)","v":{"v":51171,"d":"€ 51,171.00"}},{"$f":"MAX(0,M16)","v":{"v":51171,"d":"€ 51,171.00"}},{"$f":"IF(K16=\"\",O15,($B$9-$B$7)*K16*C16/28+O15)","v":{"v":91488,"d":"€ 91,488.00"}},{"$f":"($B$9-$B$7)*SUM($C$16:C16)/28","v":{"v":91488,"d":"€ 91,488.00"}},{"$f":"MIN(N16,O16,P16)","v":{"v":51171,"d":"€ 51,171.00"}},{"$f":"(Q16-Q15)/13","v":{"v":3936.230769230769,"d":"€ 3,936.23"}},null,{"$f":"(Q16-Q15)/13*$B$10","v":{"v":925.0142307692307,"d":"€ 925.01"}}]
["2017-01-30",2,{"$f":"MAX(0,MIN(28,$B$4-A17+1))-MIN(28,MAX(0,$B$3-A17))","v":28},144,{"$f":"B$6","v":144},{"$f":"IF(SUM(C17:$C$28)=0,\"\",E17*C17/28)","v":144},{"v":50000,"d":"€ 50,000"},{"$f":"SUMPRODUCT($G$16:G17,$K$16:K17)","v":{"v":100000,"d":"€ 100,000.00"}},null,{"$f":"IF(C17=0,J16,SUM(I17)*13*28/C17+J16)","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C17:$C$28)=0,\"\",ROUND(D17/F17,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K17),0)","v":{"v":23658,"d":"€ 23,658.00"}},{"$f":"IF(K17=\"\",M16,(H17+J17-J16-L17-H16+L16)*C17/28+M16)","v":{"v":89342,"d":"€ 89,342.00"}},{"v":89342,"d":"€ 89,342.00"},{"v":182976,"d":"€ 182,976.00"},{"$f":"($B$9-$B$7)*SUM($C$16:C17)/28","v":{"v":182976,"d":"€ 182,976.00"}},{"v":89342,"d":"€ 89,342.00"},{"$f":"(Q17-Q16)/13","v":{"v":2936.230769230769,"d":"€ 2,936.23"}},null,{"$f":"(Q17-Q16)/13*$B$10","v":{"v":690.0142307692307,"d":"€ 690.01"}}]
["2017-02-27",3,28,144,144,{"$f":"IF(SUM(C18:$C$28)=0,\"\",E18*C18/28)","v":144},{"v":50000,"d":"€ 50,000"},{"$f":"SUMPRODUCT($G$16:G18,$K$16:K18)","v":{"v":150000,"d":"€ 150,000.00"}},null,{"$f":"SUM($I$16:I18)*13","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C18:$C$28)=0,\"\",ROUND(D18/F18,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K18),0)","v":{"v":35487,"d":"€ 35,487.00"}},{"$f":"IF(K18=\"\",M17,(H18+J18-J17-L18-H17+L17)*C18/28+M17)","v":{"v":127513,"d":"€ 127,513.00"}},{"v":127513,"d":"€ 127,513.00"},{"v":274464,"d":"€ 274,464.00"},{"$f":"($B$9-$B$7)*SUM($C$16:C18)/28","v":{"v":274464,"d":"€ 274,464.00"}},{"v":127513,"d":"€ 127,513.00"},{"v":2936.230769230769,"d":"€ 2,936.23"},null,{"v":690.0142307692307,"d":"€ 690.01"}]
["2017-03-27",4,28,144,144,{"$f":"IF(SUM(C19:$C$28)=0,\"\",E19*C19/28)","v":144},{"v":50000,"d":"€ 50,000"},{"$f":"SUMPRODUCT($G$16:G19,$K$16:K19)","v":{"v":200000,"d":"€ 200,000.00"}},null,{"$f":"SUM($I$16:I19)*13","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C19:$C$28)=0,\"\",ROUND(D19/F19,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K19),0)","v":{"v":47316,"d":"€ 47,316.00"}},{"$f":"IF(K19=\"\",M18,(H19+J19-J18-L19-H18+L18)*C19/28+M18)","v":{"v":165684,"d":"€ 165,684.00"}},{"v":165684,"d":"€ 165,684.00"},{"v":365952,"d":"€ 365,952.00"},{"$f":"($B$9-$B$7)*SUM($C$16:C19)/28","v":{"v":365952,"d":"€ 365,952.00"}},{"v":165684,"d":"€ 165,684.00"},{"v":2936.230769230769,"d":"€ 2,936.23"},null,{"v":690.0142307692307,"d":"€ 690.01"}]
["2017-04-24",5,28,144,144,{"$f":"IF(SUM(C20:$C$28)=0,\"\",E20*C20/28)","v":144},{"v":50000,"d":"€ 50,000"},{"$f":"SUMPRODUCT($G$16:G20,$K$16:K20)","v":{"v":250000,"d":"€ 250,000.00"}},null,{"$f":"SUM($I$16:I20)*13","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C20:$C$28)=0,\"\",ROUND(D20/F20,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K20),0)","v":{"v":59145,"d":"€ 59,145.00"}},{"$f":"IF(K20=\"\",M19,(H20+J20-J19-L20-H19+L19)*C20/28+M19)","v":{"v":203855,"d":"€ 203,855.00"}},{"v":203855,"d":"€ 203,855.00"},{"v":457440,"d":"€ 457,440.00"},{"$f":"($B$9-$B$7)*SUM($C$16:C20)/28","v":{"v":457440,"d":"€ 457,440.00"}},{"v":203855,"d":"€ 203,855.00"},{"v":2936.230769230769,"d":"€ 2,936.23"},null,{"v":690.0142307692307,"d":"€ 690.01"}]
["2017-05-22",6,28,144,144,{"$f":"IF(SUM(C21:$C$28)=0,\"\",E21*C21/28)","v":144},{"v":50000,"d":"€ 50,000"},{"$f":"SUMPRODUCT($G$16:G21,$K$16:K21)","v":{"v":300000,"d":"€ 300,000.00"}},null,{"$f":"SUM($I$16:I21)*13","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C21:$C$28)=0,\"\",ROUND(D21/F21,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K21),0)","v":{"v":70974,"d":"€ 70,974.00"}},{"v":242026,"d":"€ 242,026.00"},{"v":242026,"d":"€ 242,026.00"},{"v":548928,"d":"€ 548,928.00"},{"$f":"($B$9-$B$7)*SUM($C$16:C21)/28","v":{"v":548928,"d":"€ 548,928.00"}},{"v":242026,"d":"€ 242,026.00"},{"v":2936.230769230769,"d":"€ 2,936.23"},null,{"v":690.0142307692307,"d":"€ 690.01"}]
["2017-06-19",7,28,144,144,{"$f":"IF(SUM(C22:$C$28)=0,\"\",E22*C22/28)","v":144},{"v":50000,"d":"€ 50,000"},{"$f":"SUMPRODUCT($G$16:G22,$K$16:K22)","v":{"v":350000,"d":"€ 350,000.00"}},null,{"$f":"SUM($I$16:I22)*13","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C22:$C$28)=0,\"\",ROUND(D22/F22,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K22),0)","v":{"v":82803,"d":"€ 82,803.00"}},{"v":280197,"d":"€ 280,197.00"},{"v":280197,"d":"€ 280,197.00"},{"v":640416,"d":"€ 640,416.00"},{"$f":"($B$9-$B$7)*SUM($C$16:C22)/28","v":{"v":640416,"d":"€ 640,416.00"}},{"v":280197,"d":"€ 280,197.00"},{"v":2936.230769230769,"d":"€ 2,936.23"},null,{"v":690.0142307692307,"d":"€ 690.01"}]
["2017-07-17",8,28,144,144,{"$f":"IF(SUM(C23:$C$28)=0,\"\",E23*C23/28)","v":144},{"v":50000,"d":"€ 50,000"},{"$f":"SUMPRODUCT($G$16:G23,$K$16:K23)","v":{"v":400000,"d":"€ 400,000.00"}},null,{"$f":"SUM($I$16:I23)*13","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C23:$C$28)=0,\"\",ROUND(D23/F23,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K23),0)","v":{"v":94632,"d":"€ 94,632.00"}},{"v":318368,"d":"€ 318,368.00"},{"v":318368,"d":"€ 318,368.00"},{"v":731904,"d":"€ 731,904.00"},{"$f":"($B$9-$B$7)*SUM($C$16:C23)/28","v":{"v":731904,"d":"€ 731,904.00"}},{"v":318368,"d":"€ 318,368.00"},{"v":2936.230769230769,"d":"€ 2,936.23"},null,{"v":690.0142307692307,"d":"€ 690.01"}]
["2017-08-14",9,28,144,144,{"$f":"IF(SUM(C24:$C$28)=0,\"\",E24*C24/28)","v":144},{"v":50000,"d":"€ 50,000"},{"$f":"SUMPRODUCT($G$16:G24,$K$16:K24)","v":{"v":450000,"d":"€ 450,000.00"}},null,{"$f":"SUM($I$16:I24)*13","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C24:$C$28)=0,\"\",ROUND(D24/F24,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K24),0)","v":{"v":106461,"d":"€ 106,461.00"}},{"v":356539,"d":"€ 356,539.00"},{"v":356539,"d":"€ 356,539.00"},{"v":823392,"d":"€ 823,392.00"},{"$f":"($B$9-$B$7)*SUM($C$16:C24)/28","v":{"v":823392,"d":"€ 823,392.00"}},{"v":356539,"d":"€ 356,539.00"},{"v":2936.230769230769,"d":"€ 2,936.23"},null,{"v":690.0142307692307,"d":"€ 690.01"}]
["2017-09-11",10,28,144,144,{"$f":"IF(SUM(C25:$C$28)=0,\"\",E25*C25/28)","v":144},{"v":50000,"d":"€ 50,000"},{"$f":"SUMPRODUCT($G$16:G25,$K$16:K25)","v":{"v":500000,"d":"€ 500,000.00"}},null,{"$f":"SUM($I$16:I25)*13","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C25:$C$28)=0,\"\",ROUND(D25/F25,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K25),0)","v":{"v":118290,"d":"€ 118,290.00"}},{"v":394710,"d":"€ 394,710.00"},{"v":394710,"d":"€ 394,710.00"},{"v":914880,"d":"€ 914,880.00"},{"$f":"($B$9-$B$7)*SUM($C$16:C25)/28","v":{"v":914880,"d":"€ 914,880.00"}},{"v":394710,"d":"€ 394,710.00"},{"v":2936.230769230769,"d":"€ 2,936.23"},null,{"v":690.0142307692307,"d":"€ 690.01"}]
["2017-10-09",11,28,144,144,{"$f":"IF(SUM(C26:$C$28)=0,\"\",E26*C26/28)","v":144},{"v":50000,"d":"€ 50,000"},{"$f":"SUMPRODUCT($G$16:G26,$K$16:K26)","v":{"v":550000,"d":"€ 550,000.00"}},null,{"$f":"SUM($I$16:I26)*13","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C26:$C$28)=0,\"\",ROUND(D26/F26,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K26),0)","v":{"v":130119,"d":"€ 130,119.00"}},{"v":432881,"d":"€ 432,881.00"},{"v":432881,"d":"€ 432,881.00"},{"v":1006368,"d":"€ 1,006,368.00"},{"$f":"($B$9-$B$7)*SUM($C$16:C26)/28","v":{"v":1006368,"d":"€ 1,006,368.00"}},{"v":432881,"d":"€ 432,881.00"},{"v":2936.230769230769,"d":"€ 2,936.23"},null,{"v":690.0142307692307,"d":"€ 690.01"}]
["2017-11-06",12,28,144,144,{"$f":"IF(SUM(C27:$C$28)=0,\"\",E27*C27/28)","v":144},{"v":50000,"d":"€ 50,000"},{"$f":"SUMPRODUCT($G$16:G27,$K$16:K27)","v":{"v":600000,"d":"€ 600,000.00"}},null,{"$f":"SUM($I$16:I27)*13","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C27:$C$28)=0,\"\",ROUND(D27/F27,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K27),0)","v":{"v":141948,"d":"€ 141,948.00"}},{"$f":"IF(K27=\"\",M26,(H27+J27-J26-L27-H26+L26)*C27/28+M26)","v":{"v":471052,"d":"€ 471,052.00"}},{"v":471052,"d":"€ 471,052.00"},{"v":1097856,"d":"€ 1,097,856.00"},{"$f":"($B$9-$B$7)*SUM($C$16:C27)/28","v":{"v":1097856,"d":"€ 1,097,856.00"}},{"v":471052,"d":"€ 471,052.00"},{"v":2936.230769230769,"d":"€ 2,936.23"},null,{"v":690.0142307692307,"d":"€ 690.01"}]
["2017-12-04",13,28,144,144,{"$f":"IF(SUM(C$28:$C28)=0,\"\",E28*C28/28)","v":144},{"v":50000,"d":"€ 50,000"},{"$f":"SUMPRODUCT($G$16:G28,$K$16:K28)","v":{"v":650000,"d":"€ 650,000.00"}},null,{"$f":"SUM($I$16:I28)*13","v":{"v":13000,"d":"€ 13,000.00"}},{"$f":"IFERROR(IF(SUM(C$28:$C28)=0,\"\",ROUND(D28/F28,4)),0)","v":{"v":1,"d":"100.00%"}},{"$f":"IFERROR(SUMPRODUCT($B$7*$K$16:K28),0)","v":{"v":153777,"d":"€ 153,777.00"}},{"$f":"IF(K28=\"\",M27,(H28+J28-J27-L28-H27+L27)*C28/28+M27)","v":{"v":509223,"d":"€ 509,223.00"}},{"v":509223,"d":"€ 509,223.00"},{"v":1189344,"d":"€ 1,189,344.00"},{"$f":"($B$9-$B$7)*SUM($C$16:C28)/28","v":{"v":1189344,"d":"€ 1,189,344.00"}},{"v":509223,"d":"€ 509,223.00"},{"v":2936.230769230769,"d":"€ 2,936.23"},null,{"v":690.0142307692307,"d":"€ 690.01"}]
[{"$f":"A28+28","v":43101},13]
["Totaal",{"$nulls":18},{"$f":"MAX(SUM(T16:T28),0)","v":{"v":9205.184999999998,"d":"€ 9,205.19"}}]
[]
[]
[]
["AP premie",{"$nulls":24},22,{"v":17092,"d":"€ 17,092.00"}]
["Eerste dag periode","periode getal","Genormeerd aantal dagen deelneming","Verloonde uren voor regeling","Normuren per 4-weken","Normuren deelneming","Regelingloon","Deeltijd regelingloon cumulatief","4-weken ORT","Jaarbedrag ORT Cumulatief","DTF","PT jaar Franchise Cumulatief ","Deeltijd PG Cumulatief incl. negatief","Deeltijd PG Cumulatief",{"$nulls":3},"Aanwas pensioengrondslag",null,"Premie"]
[]
[{"$f":"A16","v":"2017-01-02"},1,{"$f":"C16","v":28},144,144,144,{"v":50000,"d":"€ 50,000"},{"v":50000,"d":"€ 50,000.00"},{"$f":"I16","v":{"v":1000,"d":"€ 1,000.00"}},{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C37,\">0\")*$B$8","v":{"v":20108,"d":"€ 20,108.00"}},{"$f":"IF(K37=\"\",M36,(H37+J37-J36-L37-H36+L36)*C37/28+M36)","v":{"v":42892,"d":"€ 42,892.00"}},{"$f":"MAX(0,M37)","v":{"v":42892,"d":"€ 42,892.00"}},{"$nulls":3},{"$f":"(N37-N36)/13","v":{"v":3299.3846153846152,"d":"€ 3,299.38"}},null,{"$f":"(N37-N36)/13*$B$11","v":{"v":16.496923076923075,"d":"€ 16.50"}}]
[{"$f":"A17","v":"2017-01-30"},2,28,144,144,144,{"v":50000,"d":"€ 50,000"},{"v":100000,"d":"€ 100,000.00"},{"$f":"I17","v":0},{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C38,\">0\")*$B$8","v":{"v":40216,"d":"€ 40,216.00"}},{"$f":"IF(K38=\"\",M37,(H38+J38-J37-L38-H37+L37)*C38/28+M37)","v":{"v":72784,"d":"€ 72,784.00"}},{"v":72784,"d":"€ 72,784.00"},{"$nulls":3},{"$f":"(N38-N37)/13","v":{"v":2299.3846153846152,"d":"€ 2,299.38"}},null,{"v":11.496923076923077,"d":"€ 11.50"}]
["2017-02-27",3,28,144,144,144,{"v":50000,"d":"€ 50,000"},{"v":150000,"d":"€ 150,000.00"},0,{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C39,\">0\")*$B$8","v":{"v":60324,"d":"€ 60,324.00"}},{"v":102676,"d":"€ 102,676.00"},{"v":102676,"d":"€ 102,676.00"},{"$nulls":3},{"v":2299.3846153846152,"d":"€ 2,299.38"},null,{"v":11.496923076923077,"d":"€ 11.50"}]
["2017-03-27",4,28,144,144,144,{"v":50000,"d":"€ 50,000"},{"v":200000,"d":"€ 200,000.00"},0,{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C40,\">0\")*$B$8","v":{"v":80432,"d":"€ 80,432.00"}},{"v":132568,"d":"€ 132,568.00"},{"v":132568,"d":"€ 132,568.00"},{"$nulls":3},{"v":2299.3846153846152,"d":"€ 2,299.38"},null,{"v":11.496923076923077,"d":"€ 11.50"}]
["2017-04-24",5,28,144,144,144,{"v":50000,"d":"€ 50,000"},{"v":250000,"d":"€ 250,000.00"},0,{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C41,\">0\")*$B$8","v":{"v":100540,"d":"€ 100,540.00"}},{"v":162460,"d":"€ 162,460.00"},{"v":162460,"d":"€ 162,460.00"},{"$nulls":3},{"v":2299.3846153846152,"d":"€ 2,299.38"},null,{"v":11.496923076923077,"d":"€ 11.50"}]
["2017-05-22",6,28,144,144,144,{"v":50000,"d":"€ 50,000"},{"v":300000,"d":"€ 300,000.00"},0,{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C42,\">0\")*$B$8","v":{"v":120648,"d":"€ 120,648.00"}},{"v":192352,"d":"€ 192,352.00"},{"v":192352,"d":"€ 192,352.00"},{"$nulls":3},{"v":2299.3846153846152,"d":"€ 2,299.38"},null,{"v":11.496923076923077,"d":"€ 11.50"}]
["2017-06-19",7,28,144,144,144,{"v":50000,"d":"€ 50,000"},{"v":350000,"d":"€ 350,000.00"},0,{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C43,\">0\")*$B$8","v":{"v":140756,"d":"€ 140,756.00"}},{"v":222244,"d":"€ 222,244.00"},{"v":222244,"d":"€ 222,244.00"},{"$nulls":3},{"v":2299.3846153846152,"d":"€ 2,299.38"},null,{"v":11.496923076923077,"d":"€ 11.50"}]
["2017-07-17",8,28,144,144,144,{"v":50000,"d":"€ 50,000"},{"v":400000,"d":"€ 400,000.00"},0,{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C44,\">0\")*$B$8","v":{"v":160864,"d":"€ 160,864.00"}},{"v":252136,"d":"€ 252,136.00"},{"v":252136,"d":"€ 252,136.00"},{"$nulls":3},{"v":2299.3846153846152,"d":"€ 2,299.38"},null,{"v":11.496923076923077,"d":"€ 11.50"}]
["2017-08-14",9,28,144,144,144,{"v":50000,"d":"€ 50,000"},{"v":450000,"d":"€ 450,000.00"},0,{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C45,\">0\")*$B$8","v":{"v":180972,"d":"€ 180,972.00"}},{"v":282028,"d":"€ 282,028.00"},{"v":282028,"d":"€ 282,028.00"},{"$nulls":3},{"v":2299.3846153846152,"d":"€ 2,299.38"},null,{"v":11.496923076923077,"d":"€ 11.50"}]
["2017-09-11",10,28,144,144,144,{"v":50000,"d":"€ 50,000"},{"v":500000,"d":"€ 500,000.00"},0,{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C46,\">0\")*$B$8","v":{"v":201080,"d":"€ 201,080.00"}},{"v":311920,"d":"€ 311,920.00"},{"v":311920,"d":"€ 311,920.00"},{"$nulls":3},{"v":2299.3846153846152,"d":"€ 2,299.38"},null,{"v":11.496923076923077,"d":"€ 11.50"}]
["2017-10-09",11,28,144,144,144,{"v":50000,"d":"€ 50,000"},{"v":550000,"d":"€ 550,000.00"},0,{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C47,\">0\")*$B$8","v":{"v":221188,"d":"€ 221,188.00"}},{"v":341812,"d":"€ 341,812.00"},{"v":341812,"d":"€ 341,812.00"},{"$nulls":3},{"v":2299.3846153846152,"d":"€ 2,299.38"},null,{"v":11.496923076923077,"d":"€ 11.50"}]
["2017-11-06",12,28,144,144,144,{"v":50000,"d":"€ 50,000"},{"v":600000,"d":"€ 600,000.00"},0,{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C48,\">0\")*$B$8","v":{"v":241296,"d":"€ 241,296.00"}},{"v":371704,"d":"€ 371,704.00"},{"v":371704,"d":"€ 371,704.00"},{"$nulls":3},{"v":2299.3846153846152,"d":"€ 2,299.38"},null,{"v":11.496923076923077,"d":"€ 11.50"}]
["2017-12-04",13,28,144,144,144,{"v":50000,"d":"€ 50,000"},{"v":650000,"d":"€ 650,000.00"},0,{"v":13000,"d":"€ 13,000.00"},{"v":1,"d":"100.000%"},{"$f":"COUNTIF($C$37:C49,\">0\")*$B$8","v":{"v":261404,"d":"€ 261,404.00"}},{"v":401596,"d":"€ 401,596.00"},{"v":401596,"d":"€ 401,596.00"},{"$nulls":3},{"v":2299.3846153846152,"d":"€ 2,299.38"},null,{"v":11.496923076923077,"d":"€ 11.50"}]
[43101,13]
["Totaal",{"$nulls":18},{"$f":"MAX(SUM(T37:T49),0)","v":{"v":154.46,"d":"€ 154.46"}}]

{"sheet":"Gewogen salaris","index":2,"n_rows":26,"n_cols":6,"rows_kept":26,"merged":[[1,1,1,5]]}
[]
[null,"Fultime salaris bij ziekte berekenen"]
[null,"FT jaar salaris per 1-1",{"v":32250,"d":"€ 32,250.00"}]
[]
[null,"Bereken gewogen salaris per maand of per 4 weken"]
[null,"Start datum","Einddatum","Looptijd","Korting","Gewogen salaris"]
[null,"2017-06-19","2017-06-30",{"$f":"IF(B7=\"\",\"\",DAY(C7)-DAY(B7)+1)","v":12},{"v":0,"d":"0%"},{"$f":"IFERROR((100%-E7)*$C$3*(D7/DAY(EOMONTH(B7,0))),\"\")","v":{"v":12900,"d":"€ 12,900.00"}}]
[null,"2017-07-01","2017-07-16",{"$f":"IF(B8=\"\",\"\",DAY(C8)-DAY(B8)+1)","v":16},{"v":0.3,"d":"30%"},{"$f":"IFERROR((100%-E8)*$C$3*(D8/DAY(EOMONTH(B8,0))),\"\")","v":{"v":11651.612903225807,"d":"€ 11,651.61"}}]
[{"$nulls":3},"",null,{"$f":"IFERROR((100%-E9)*$C$3*(D9/DAY(EOMONTH(B9,0))),\"\")","v":""}]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"",null,""]
[{"$nulls":3},"Gewogen salaris:",null,{"$f":"CEILING(SUM(F7:F25),1)","v":{"v":24552,"d":"€ 24,552.00"}}]

{"sheet":"Versiebeheer","index":3,"n_rows":13,"n_cols":3,"rows_kept":13}
["Versie","Datum","Wijzigingen"]
["1.3","2016-09-08","Samenvoegen Maand en 4-weken in 1 sheet/2 tabbladen"]
["1.3","2016-09-08","Tekstueel: Maximaal Salaris --> Maximaal Pensioengevend Salaris"]
["1.3","2016-09-08","Tekstueel: Gewerkte uren --> Verloonde uren voor pensioen"]
["1.3","2016-09-08","Formule: <4-weken> Totaal premie (T30) (T16:T27) --> (T16:T28)"]
["1.3","2016-09-08","Formule: <4-weken> Aanwas/12 --> Aanwas/13"]
["1.3","2016-09-08","Formule: <4-weken> DTF som(C16:C27) --> Som(C16:C28)"]
["1.3","2016-09-08","Formule: <4-weken> Norm periode som(C16:C27) --> Som(C16:C28)"]
["1.4","2016-09-08","formule \"PG (ongemaximeerd) Cumulatief incl. negatief\": ORT NIET maal (dagen deelneming (volgens tieland) / 30)"]
["1.41","2016-09-12","Aanpassing versie 1.4 teruggedraait en formule Jaar ORT aangepast, In gebroken maanden wordt de ORT eerst omgerekend naar hele maanden vervolgens op jaarbasis"]
["1.41","2016-12-06","Blad \"gewogen salaris\" cell F26: uitkomst afronden in gehele euro's"]
["1.5","2016-12-15","2017 parameters toegevoegd"]
["1.51","2016-12-16","Waarden: Tijdvakken 4-weken 2017 (+ tabblad Tijdvakken); \nFormule: normuren per tijdvak (B6) worden overgenomen per tijdvak (E16:E28); \nWaarden: bij 4-weken is normuren per week o.b.v. 36 u/wk is 144u p. tijdvak (i.p.v. 160); \nWaarden: voorbeeld gewogen salaris aangepast naar 2017; \nFormule: waarden overnemen regelingloon per tijdvak (G16:G27); \nFormule: Waarden overnemen ORT (I37:I38)"]

{"sheet":"Tijdvakken","index":4,"n_rows":30,"n_cols":13,"rows_kept":30}
["Tijdvak","Tijdvakcode","Ingangsdatum","Einddatum","Uiterste aangiftedatum PFZW",{"$nulls":5},"Loontijdvak",null,"Aangiftetijdvak"]
["Periode 1",7710,"2017-01-01","2017-01-29",{"$f":"D2+10","v":"2017-02-08"},{"$nulls":4},1,"ma 2 januari tot en met zo 29 januari",null,"zo 1 januari tot en met zo 29 januari"]
["Periode 2",7720,"2017-01-30","2017-02-26",{"$f":"D3+10","v":"2017-03-08"},{"$nulls":4},2,"ma 30 januari tot en met zo 26 februari",null,"ma 30 januari tot en met zo 26 februari"]
["Periode 3",7730,"2017-02-27","2017-03-26","2017-04-05",{"$nulls":4},3,"ma 27 februari tot en met zo 26 maart",null,"ma 27 februari tot en met zo 26 maart"]
["Periode 4",7740,"2017-03-27","2017-04-23","2017-05-03",{"$nulls":4},4,"ma 27 maart tot en met zo 23 april",null,"ma 27 maart tot en met zo 23 april"]
["Periode 5",7750,"2017-04-24","2017-05-21","2017-05-31",{"$nulls":4},5,"ma 24 april tot en met zo 21 mei",null,"ma 24 april tot en met zo 21 mei"]
["Periode 6",7760,"2017-05-22","2017-06-18","2017-06-28",{"$nulls":4},6,"ma 22 mei tot en met zo 18 juni",null,"ma 22 mei tot en met zo 18 juni"]
["Periode 7",7770,"2017-06-19","2017-07-16","2017-07-26",{"$nulls":4},7,"ma 19 juni tot en met zo 16 juli",null,"ma 19 juni tot en met zo 16 juli"]
["Periode 8",7780,"2017-07-17","2017-08-13","2017-08-23",{"$nulls":4},8,"ma 17 juli tot en met zo 13 augustus",null,"ma 17 juli tot en met zo 13 augustus"]
["Periode 9",7790,"2017-08-14","2017-09-10","2017-09-20",{"$nulls":4},9,"ma 14 augustus tot en met zo 10 september",null,"ma 14 augustus tot en met zo 10 september"]
["Periode 10",7800,"2017-09-11","2017-10-08","2017-10-18",{"$nulls":4},10,"ma 11 september tot en met zo 8 oktober",null,"ma 11 september tot en met zo 8 oktober"]
["Periode 11",7810,"2017-10-09","2017-11-05","2017-11-15",{"$nulls":4},11,"ma 9 oktober tot en met zo 5 november",null,"ma 9 oktober tot en met zo 5 november"]
["Periode 12",7820,"2017-11-06","2017-12-03","2017-12-13",{"$nulls":4},12,"ma 6 november tot en met zo 3 december",null,"ma 6 november tot en met zo 3 december"]
["Periode 13",7830,"2017-12-04","2017-12-31","2018-01-10",{"$nulls":4},13,"ma 4 december tot en met zo 31 december",null,"ma 4 december tot en met zo 31 december"]
[]
[]
[]
["Tijdvak","Tijdvakcode","Ingangsdatum","Einddatum","Uiterste aangiftedatum PFZW"]
["Januari",7010,"2017-01-01","2017-01-31",{"$f":"D19+10","v":"2017-02-10"}]
["Februari",7020,"2017-02-01","2017-02-28",{"$f":"D20+10","v":"2017-03-10"}]
["Maart",7030,"2017-03-01","2017-03-31","2017-04-10"]
["April",7040,"2017-04-01","2017-04-30","2017-05-10"]
["Mei",7050,"2017-05-01","2017-05-31","2017-06-10"]
["Juni",7060,"2017-06-01","2017-06-30","2017-07-10"]
["Juli",7070,"2017-07-01","2017-07-31","2017-08-10"]
["Augustus",7080,"2017-08-01","2017-08-31","2017-09-10"]
["September",7090,"2017-09-01","2017-09-30","2017-10-10"]
["Oktober",7100,"2017-10-01","2017-10-31","2017-11-10"]
["November",7110,"2017-11-01","2017-11-30","2017-12-10"]
["December",7120,"2017-12-01","2017-12-31","2018-01-10"]