{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Inputs and Results","Calculation_hide","Cover Sheet_hide","Tables_hide","Sheet1"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":44,"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":"Inputs and Results","index":0,"n_rows":31,"n_cols":5,"rows_kept":31,"cut":false,"hidden":false,"merged":[[22,1,22,4],[4,1,4,4],[3,2,3,5]]}
[]
[null,null,"                             CILON Calculation Tool"]
[]
[null,null,"** For use where the departure date is 1 April 2015 or later**"]
[null,"Enter Data"]
[null,null,null,"Name"]
[]
[null,null,null,"National Insurance Number"]
[]
[null,null,null,"Date of birth"]
[]
[null,null,null,"Departure date"]
[]
[null,null,null,"Pensionable earnings for unexpired notice period"]
[]
[null,null,null,"Non-pensionable earnings for unexpired notice period"]
[]
[null,null,null,"Scheme"]
[]
[null,null,null,"Current contribution rate (%)"]
[]
[]
[null,"Results"]
[null,null,null,"Compensation for loss of pension",{"$f":"dCompLossOfPension","v":"Enter Values"}]
[null,null,null,"Total CILON amount",{"$f":"dTotalCilon","v":"Enter Values"}]
[]
[]
[null,"Audit"]
[null,{"$f":"\"Date : \"&TEXT(NOW(),\"dd/mm/yyyy hh:mm\")","v":"Date : 10/05/2019 13:45"}]
[null,{"$f":"\"User : \"&CELL(\"filename\")","v":"User : C:\\Users\\vaughaj\\AppData\\Local\\Microsoft\\Windows\\Temporary Internet Files\\Content.Outlook\\ZN6TYH79\\[PCSPS CILON.xlsm]Inputs and Results"}]
[null,{"$f":"\"Name and Version : \"&strToolName&\":V\"&dVersionNumber","v":"Name and Version : CILON:V1.1"}]

{"sheet":"Calculation_hide","index":1,"n_rows":70,"n_cols":8,"rows_kept":70,"cut":false,"hidden":true,"merged":[[16,1,16,6],[3,2,3,6],[4,2,4,6],[12,1,12,5],[13,1,13,5],[14,1,14,5],[15,1,15,6]]}
[null,null,{"$f":"strToolName","v":"CILON"}]
[]
[]
[null,"Description","This calculator is shows the calculations and intermediate stages behind CILON calculations.  This sheet is intended to be used by ADMINISTRATION staff as a backend tool.  It is not expected that this will be exposed to the public users."]
[null,"Schemes","Classic, Classic Plus, Premium, Nuvos"]
[]
[]
[]
[null,null,{"$f":"$C$1&\" - Instructions\"","v":"CILON - Instructions"}]
[]
[null,null,null,null,null,null,"Key to cell colours:"]
[null,"How to use this Partial Retirement Tool and exceptions",null,null,null,null,"User inputs from input sheet (yellow)"]
[null,"Please provide all details in the input section, these inputs are fed in from the 'Inputs and Results' sheet",null,null,null,null,"Work Variable (Orange)"]
[null,null,null,null,null,null,"Output (Green)"]
[null,null,null,null,null,null,"System"]
[]
[]
[]
[]
[null,null,{"$f":"$C$1&\" - Inputs\"","v":"CILON - Inputs"}]
[]
[]
[null,"Inputs from UI"]
[null,"Date of Birth",{"$f":"uidayDOB","v":"00:00:00"},"dayDOB"]
[null,"Date of departure",{"$f":"uidayDepDate","v":"00:00:00"},"dayDepDate"]
[null,"Scheme",{"$f":"uistrScheme","v":"00:00:00"},"strScheme"]
[null,"Contribution rate (%)",{"$f":"uidContRate","v":0},"dContRate"]
[null,"Earnings figure",{"$f":"uidPenEarn","v":0},"dPenEarn"]
[null,"Non-pensionable earnings for unexpired notice period",{"$f":"uidNonPenEarn","v":0},"dNonPenEarn"]
[]
[]
[]
[null,null,{"$f":"$C$1&\" - Calculations\"","v":"CILON - Calculations"}]
[]
[]
[]
[null,"Calculations"]
[null,"Years from DOB to Departure Date\n=YEAR(dayDepDate)-YEAR(dayDOB)",{"$f":"YEAR(dayDepDate)-YEAR(dayDOB)","v":0},"dYearsDOBtoDep"]
[null,"Birthday At departure \n=DATE(YEAR(dayDOB)+dYearsDOBtoDep,MONTH(dayDOB),DAY(dayDOB)-1)",{"$f":"DATE(YEAR(dayDOB)+dYearsDOBtoDep,MONTH(dayDOB),DAY(dayDOB)-1)","v":{"$error":"#NUM!"}},"dayBDayAtDep"]
[null,"Additional Year\n=IF(OR(dayBDayAtDep<dayDepDate,dayBDayAtDep=dayDepDate), 0, 1)",{"$f":"IF(OR(dayBDayAtDep<dayDepDate,dayBDayAtDep=dayDepDate), 0, 1)","v":{"$error":"#NUM!"}},"dAddYear"]
[null,"Adjusted Birthday at Departure\n=DATE(YEAR(dayBDayAtDep)-dAddYear,MONTH(dayBDayAtDep),DAY(dayBDayAtDep))",{"$f":"DATE(YEAR(dayBDayAtDep)-dAddYear,MONTH(dayBDayAtDep),DAY(dayBDayAtDep))","v":{"$error":"#NUM!"}},"dayAdjBDayAtDep"]
[null,"Age in Years\n=SUM(dYearsDOBtoDep-dAddYear)",{"$f":"SUM(dYearsDOBtoDep-dAddYear)","v":{"$error":"#NUM!"}},"dAgeYears"]
[]
[null,"CILON Factors at Age"]
[null,"Classic % (C)",{"$f":"VLOOKUP(dAgeYears,tabCILON,2)","v":{"$error":"#NUM!"}},"dClassicFactor"]
[null,"Premium or Classic Plus % (P/C+)",{"$f":"VLOOKUP(dAgeYears,tabCILON,3)","v":{"$error":"#NUM!"}},"dPremClassPlusFactor"]
[null,"Nuvos % (N)",{"$f":"VLOOKUP(dAgeYears,tabCILON,4)","v":{"$error":"#NUM!"}},"dNuvosFactor"]
[null,{"$f":"\"CILON age factor (%) based on scheme \"&strScheme","v":"CILON age factor (%) based on scheme 0"},{"$f":"IF((strScheme=\"nuvos\"),dNuvosFactor,IF((strScheme=\"classic\"),dClassicFactor,dPremClassPlusFactor))","v":{"$error":"#NUM!"}},"dCILONAgeFactor"]
[null,"Net CILON factor (%)\n= dCILONAgeFactor-dContRate",{"$f":"dCILONAgeFactor-dContRate","v":{"$error":"#NUM!"}},"dNetCILONFactor"]
[]
[null,"Compensation for loss of pension\n=ROUND((dPenEarn*dNetCILONFactor/100),2)",{"$f":"ROUND((dPenEarn*dNetCILONFactor/100),2)","v":{"$error":"#NUM!"}},"dCompLoassOfPensionCALC"]
[null,"Total Cilon amount\n=dPenEarn+dNonPenEarn+dCompLoassOfPensionCALC",{"$f":"dPenEarn+dNonPenEarn+dCompLoassOfPensionCALC","v":{"$error":"#NUM!"}},"dTotalCilonCALC"]
[]
[]
[]
[null,null,{"$f":"$C$1&\" - Results\"","v":"CILON - Results"}]
[]
[]
[null,"Results",null,null,null,null,null," "]
[null,"Compensation for loss of pension",{"$f":"IF(OR(dContRate=0,strScheme=0,ISERROR(dCompLoassOfPensionCALC),dayDOB=0),\"Enter Values\",dCompLoassOfPensionCALC)","v":"Enter Values"},"dCompLossOfPension"]
[null,"Total Cilon amount",{"$f":"IF(OR(dContRate=0,strScheme=0,ISERROR(dTotalCilonCALC),dayDOB=0),\"Enter Values\",dTotalCilonCALC)","v":"Enter Values"},"dTotalCilon"]
[]
[]
[]
[null,null,{"$f":"$C$1&\" - System Variables\"","v":"CILON - System Variables"}]
[]
[]
[null,"Version Number",1.1000000000000001,"dVersionNumber"]
[null,"Security Classification","Unclassified","strSecurityClassification"]
[null,"Tool Name","CILON","strToolName"]

{"sheet":"Cover Sheet_hide","index":2,"n_rows":18,"n_cols":11,"rows_kept":18,"cut":false,"hidden":true,"merged":[[1,1,7,10],[10,1,10,10]]}
[]
[null,"MyCSP\nSCU\nCILON Calculator\nName : CILON_April YYYY_VX.xlsm"]
[]
[]
[]
[]
[]
[]
[]
[]
[null,{"$f":"strSecurityClassification","v":"Unclassified"}]
[]
[]
[null,"Revision History"]
[null,"Version ","Name of file","Previous Version","Date","Author ","Team ","Description","Reviewed By, Name","Review Date","Review Comments and sign off"]
[null,"V1.0","CILON_April2014_V1.xlsm","X:\\Quality Assurance\\Development\\CILON calculator\\Historic calculators\\CILON_April2013_Cracked_RPM.xls","2014-04-10","Rob Mann","SCU","Updated existing file for the following changes (See Email in folder :X:\\Quality Assurance\\Development\\CILON calculator\\Documentation )","Hannah Byrne","2014-04-11","Results were not showing to 2 decimal places - fixed.\nFor use sentence edited to be clearer.\nNINo changed to National Insurance Number.\nCalc tested for 8 cases, 2 for each scheme."]
[null,"V1.1","CILON_April2014_V2.xlsm","X:\\Quality Assurance\\Development\\CILON calculator\\V1 for Review 10042014\\","2014-04-15",null,"SCU",null,"Lisa Judge","2014-04-15","V1.1 created  with a slight modification to the contribution rates drop down list - the display is now defaulting to the top of the list rather than the bottom as it was in v1.0"]
[null,"V1.2","CILON_April2014_V11.xlsm",null,"2015-04-08","Scott McGee","Applications Team","Updated existing factors and extended the lookup to the age of 18."]

{"sheet":"Tables_hide","index":3,"n_rows":67,"n_cols":9,"rows_kept":67,"cut":false,"hidden":true,"merged":[]}
[]
[null,null,{"$f":"\"CILON\"&\" - Tables\"","v":"CILON - Tables"}]
[]
[]
[null,"Tables used in calculations/sheets"]
[]
[null,"tabCILON"]
[null,"CILON age factors",null,null,null,null,"tabSchemes",null,"tabContRates"]
[null,"Age","% (C)","% (P/C+)","% (N)",null,"Scheme Names",null,"2016 Contribution Rates"]
[null,17,12.8,14.9,16.600000000000001,null,"classic",null,4.5999999999999996]
[null,18,12.8,14.9,16.600000000000001,null,"classic plus",null,5.45]
[null,19,12.8,14.9,16.600000000000001,null,"premium",null,7.35]
[null,20,12.8,14.9,16.600000000000001,null,"nuvos",null,8.0500000000000007]
[null,21,13.1,15.2,16.899999999999999]
[null,22,13.3,15.7,17.399999999999999]
[null,23,13.6,16,17.600000000000001]
[null,24,13.9,16.3,18.100000000000001]
[null,25,14.2,16.7,18.5]
[null,26,14.6,17.100000000000001,18.899999999999999]
[null,27,14.9,17.5,19.399999999999999]
[null,28,15.3,17.899999999999999,19.8]
[null,29,15.6,18.399999999999999,20.3]
[null,30,16,18.8,20.8]
[null,31,16.399999999999999,19.2,21.3,null,null,"2015 Contribution Rates","2014 Data, For reference"]
[null,32,16.8,19.7,21.8,null,null,3,1.5]
[null,33,17.2,20.2,22.3,null,null,4.5999999999999996,3]
[null,34,17.600000000000001,20.7,22.8,null,null,5.45,3.5]
[null,35,18,21.2,23.4,null,null,7.35,4.4800000000000004]
[null,36,18.3,21.6,23.8,null,null,8.0500000000000007,5]
[null,37,18.8,22.1,24.4,null,null,null,5.27]
[null,38,19.3,22.6,25,null,null,null,6.06]
[null,39,19.7,23,25.5,null,null,null,6.48]
[null,40,20.100000000000001,23.6,26,null,null,null,6.85]
[null,41,20.6,24.2,26.7,null,null,null,7.27]
[null,42,21.2,24.7,27.2,null,null,null,8.06]
[null,43,21.6,25.3,27.8,null,null,null,8.85]
[null,44,22.2,25.8,28.4]
[null,45,22.7,26.3,29.1]
[null,46,23.3,27,29.8]
[null,47,23.8,27.6,30.4]
[null,48,24.3,28.2,31.1]
[null,49,25,28.8,31.9]
[null,50,25.6,29.5,32.6]
[null,51,26.3,30.1,33.299999999999997]
[null,52,26.9,30.8,34.1]
[null,53,27.6,31.5,34.799999999999997]
[null,54,28.3,32.200000000000003,35.5]
[null,55,29.1,32.9,36.299999999999997]
[null,56,29.8,33.6,37.1]
[null,57,30.5,34.299999999999997,37.9]
[null,58,31,35,38.700000000000003]
[null,59,31.4,35.9,39.4]
[null,60,30.6,35.4,40.200000000000003]
[null,61,30.1,34.6,41]
[null,62,29.5,33.9,41.8]
[null,63,29,33.200000000000003,42.6]
[null,64,28.5,32.5,43.5]
[null,65,27.9,31.8,43.1]
[null,66,27.3,31.1,41.9]
[null,67,26.9,30.4,40.9]
[null,68,26.3,29.7,39.799999999999997]
[null,69,25.7,28.9,38.6]
[null,70,25.1,28.2,37.6]
[null,71,24.7,27.5,36.5]
[null,72,24.1,26.9,35.5]
[null,73,23.6,26.3,34.4]
[null,74,23.2,25.7,33.299999999999997]

{"sheet":"Sheet1","index":4,"n_rows":58,"n_cols":7,"rows_kept":58,"cut":false,"hidden":true,"merged":[]}
[9.7000000000000011,11.3,12.3,null,9.7000000000000011,11.3,12.3]
[9.7000000000000011,11.3,12.3,null,9.7000000000000011,11.3,12.3]
[9.7000000000000011,11.3,12.3,null,9.7000000000000011,11.3,12.3]
[9.7000000000000011,11.3,12.3,null,9.7000000000000011,11.3,12.3]
[10,11.600000000000001,12.7,null,10,11.600000000000001,12.7]
[10.199999999999999,12,13,null,10.199999999999999,12,13]
[10.5,12.3,13.4,null,10.5,12.3,13.4]
[10.8,12.6,13.8,null,10.8,12.6,13.8]
[11.1,13,14.099999999999998,null,11.1,13,14.099999999999998]
[11.5,13.4,14.499999999999998,null,11.5,13.4,14.499999999999998]
[11.799999999999999,13.8,14.899999999999999,null,11.799999999999999,13.8,14.899999999999999]
[12.2,14.2,15.4,null,12.2,14.2,15.4]
[12.5,14.7,15.8,null,12.5,14.7,15.8]
[12.9,15.1,16.2,null,12.9,15.1,16.2]
[13.3,15.5,16.7,null,13.3,15.5,16.7]
[13.700000000000001,16,17.100000000000001,null,13.700000000000001,16,17.100000000000001]
[14.099999999999998,16.5,17.599999999999998,null,14.099999999999998,16.5,17.599999999999998]
[14.499999999999998,17,18.099999999999998,null,14.499999999999998,17,18.099999999999998]
[14.899999999999999,17.5,18.600000000000001,null,14.899999999999999,17.5,18.600000000000001]
[15.299999999999999,18,19.100000000000001,null,15.299999999999999,18,19.100000000000001]
[15.8,18.5,19.600000000000001,null,15.8,18.5,19.600000000000001]
[16.3,19,20.200000000000003,null,16.3,19,20.200000000000003]
[16.7,19.5,20.8,null,16.7,19.5,20.8]
[17.2,20.100000000000001,21.3,null,17.2,20.100000000000001,21.3]
[17.7,20.7,21.9,null,17.7,20.7,21.9]
[18.3,21.3,22.5,null,18.3,21.3,22.5]
[18.8,21.9,23.200000000000003,null,18.8,21.9,23.200000000000003]
[19.400000000000002,22.5,23.799999999999997,null,19.400000000000002,22.5,23.799999999999997]
[20,23.1,24.5,null,20,23.1,24.5]
[20.599999999999998,23.799999999999997,25.1,null,20.599999999999998,23.799999999999997,25.1]
[21.2,24.5,25.900000000000002,null,21.2,24.5,25.900000000000002]
[21.8,25.2,26.6,null,21.8,25.2,26.6]
[22.5,25.900000000000002,27.3,null,22.5,25.900000000000002,27.3]
[23.200000000000003,26.700000000000003,28.1,null,23.200000000000003,26.700000000000003,28.1]
[24,27.400000000000002,28.9,null,24,27.400000000000002,28.9]
[24.7,28.199999999999996,29.7,null,24.7,28.199999999999996,29.7]
[25.5,28.999999999999996,30.599999999999998,null,25.5,28.999999999999996,30.599999999999998]
[26.3,29.799999999999997,31.4,null,26.3,29.799999999999997,31.4]
[27.200000000000003,30.599999999999998,32.300000000000004,null,27.200000000000003,30.599999999999998,32.300000000000004]
[28.000000000000004,31.5,33.200000000000003,null,28.000000000000004,31.5,33.200000000000003]
[28.799999999999997,32.300000000000004,34.200000000000003,null,28.799999999999997,32.300000000000004,34.200000000000003]
[29.5,33.200000000000003,35.199999999999996,null,29.5,33.200000000000003,35.199999999999996]
[30,34.200000000000003,36.199999999999996,null,30,34.200000000000003,36.199999999999996]
[29.9,34.5,37.200000000000003,null,29.9,34.5,37.200000000000003]
[29.5,33.800000000000004,38.299999999999997,null,29.5,33.800000000000004,38.299999999999997]
[28.999999999999996,33.200000000000003,39.5,null,28.999999999999996,33.200000000000003,39.5]
[28.499999999999996,32.6,40.6,null,28.499999999999996,32.6,40.6]
[28.1,32,42.1,null,28.1,32,42.1]
[27.6,31.4,42.5,null,27.6,31.4,42.5]
[27.1,30.8,41.5,null,27.1,30.8,41.5]
[26.700000000000003,30.2,40.6,null,26.700000000000003,30.2,40.6]
[26.200000000000003,29.599999999999998,39.6,null,26.200000000000003,29.599999999999998,39.6]
[25.7,28.9,38.6,null,25.7,28.9,38.6]
[25.2,28.299999999999997,37.700000000000003,null,25.2,28.299999999999997,37.700000000000003]
[24.8,27.700000000000003,36.700000000000003,null,24.8,27.700000000000003,36.700000000000003]
[24.3,27.200000000000003,35.799999999999997,null,24.3,27.200000000000003,35.799999999999997]
[23.9,26.6,34.799999999999997,null,23.9,26.6,34.799999999999997]
[23.5,26.1,33.900000000000006,null,23.5,26.1,33.900000000000006]