{"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":41,"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 24 Oct 2023 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 : 25/10/2023 11:53"}]
[null,{"$f":"\"User : \"&CELL(\"filename\")","v":"User : https://ukri.sharepoint.com/sites/bb-jss/JSS Shared Documents/Factors/Factor Review 2023/[rcps-cilon-calculator-october-2023.xlsm]Inputs and Results"}]
[null,{"$f":"\"Name and Version : \"&strToolName&\":V\"&dVersionNumber","v":"Name and Version : CILON:V1.4"}]

{"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.4,"dVersionNumber"]
[null,"Security Classification","Unclassified","strSecurityClassification"]
[null,"Tool Name","CILON","strToolName"]

{"sheet":"Cover Sheet_hide","index":2,"n_rows":21,"n_cols":11,"rows_kept":21,"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",{"$f":"HYPERLINK(\"http://www.civilservicepensionscheme.org.uk/media/94708/\",\"X:\\Quality Assurance\\Development\\CILON calculator\\V1 for Review 10042014\\\")","v":"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","v1.1","2015-04-08","Scott McGee","Applications Team","Updated existing factors and extended the lookup to the age of 18."]
[null,"V1.3","rcps-cilon-calculator-march-2019.xlsm",null,"2019-03-05","Teresa Gemoli",null,"Updated for RCPS CILON factors issued 14/2/2019","Kye Honor","2019-03-05","Ok"]
[null,"V1.4","rcps-cilon-calculator-june-2021.xlsm","V1.3","2021-06-14","Kye Honor",null,"Updated for RCPS factors issued 7 Jun 2021","Kye Honor","2021-06-14","ok"]
[null,"V1.5","rcps-cilon-calculator-october-2023.xlsm","V1.4","2021-10-25","Kye Honor",null,"Updated for RCPS factors issued 24 October 2023","Kye Honor","2021-10-25","ok"]

{"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,null,null,"Factors Effective 24 Oct 2023 (Table 1204 - Series 801)"]
[]
[]
[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,"2023 Contribution Rates"]
[null,17,18.3,21.4,24.4,null,"classic",null,4.5999999999999996]
[null,18,18.3,21.4,24.4,null,"classic plus",null,4.5999999999999996]
[null,19,18.3,21.4,24.4,null,"premium",null,5.45]
[null,20,18.3,21.4,24.4,null,"nuvos",null,7.35]
[null,21,18.600000000000001,21.7,24.7,null,null,null,8.0500000000000007]
[null,22,18.899999999999999,22.1,25.1]
[null,23,19.2,22.400000000000002,25.5,"`"]
[null,24,19.400000000000002,22.7,25.8]
[null,25,19.7,23.1,26.200000000000003]
[null,26,20,23.400000000000002,26.6]
[null,27,20.3,23.799999999999997,27]
[null,28,20.599999999999998,24.099999999999998,27.400000000000002]
[null,29,20.9,24.5,27.800000000000004]
[null,30,21.3,24.8,28.199999999999996]
[null,31,21.6,25.2,28.599999999999998,null,null,null,"2015 Data, For reference"]
[null,32,21.9,25.6,28.999999999999996,null,null,null,3]
[null,33,22.2,26,29.4,null,null,null,4.5999999999999996]
[null,34,22.6,26.3,29.799999999999997,null,null,null,5.45]
[null,35,22.900000000000002,26.700000000000003,30.2,null,null,null,7.35]
[null,36,23.200000000000003,27.1,30.7,null,null,null,8.0500000000000007]
[null,37,23.599999999999998,27.500000000000004,31.1]
[null,38,23.9,27.900000000000002,31.5]
[null,39,24.3,28.299999999999997,32]
[null,40,24.6,28.7,32.4]
[null,41,25,29.2,32.9]
[null,42,25.4,29.599999999999998,33.300000000000004]
[null,43,25.7,30,33.800000000000004]
[null,44,26.1,30.5,34.300000000000004]
[null,45,26.5,30.9,34.699999999999996]
[null,46,26.900000000000002,31.4,35.199999999999996]
[null,47,27.3,31.8,35.699999999999996]
[null,48,27.700000000000003,32.300000000000004,36.199999999999996]
[null,49,28.1,32.800000000000004,36.700000000000003]
[null,50,28.499999999999996,33.300000000000004,37.200000000000003]
[null,51,28.999999999999996,33.800000000000004,37.700000000000003]
[null,52,29.4,34.300000000000004,38.200000000000003]
[null,53,29.9,34.799999999999997,38.800000000000004]
[null,54,30.3,35.299999999999997,39.300000000000004]
[null,55,30.8,35.9,39.900000000000006]
[null,56,31.3,36.4,40.400000000000006]
[null,57,31.8,37,41]
[null,58,32.300000000000004,37.6,41.6]
[null,59,32.800000000000004,38.200000000000003,42.199999999999996]
[null,60,32.700000000000003,38,42.9]
[null,61,32,37,43.5]
[null,62,31.2,36,44.2]
[null,63,30.5,35,45]
[null,64,29.7,33.900000000000006,45.7]
[null,65,28.9,32.9,45.4]
[null,66,28.1,31.900000000000002,44]
[null,67,27.3,30.8,42.5]
[null,68,26.5,29.7,41]
[null,69,25.7,28.7,39.6]
[null,70,24.9,27.6,38.1]
[null,71,24.099999999999998,26.6,36.700000000000003]
[null,72,23.3,25.5,35.199999999999996]
[null,73,22.6,24.5,33.800000000000004]
[null,74,21.8,23.400000000000002,32.300000000000004]

{"sheet":"Sheet1","index":4,"n_rows":58,"n_cols":7,"rows_kept":58,"cut":false,"hidden":true,"merged":[]}
[12.8,15.03,16.650000000000002,null,12.8,15.03,16.650000000000002]
[12.8,15.03,16.650000000000002,null,12.8,15.03,16.650000000000002]
[12.8,15.03,16.650000000000002,null,12.8,15.03,16.650000000000002]
[12.8,15.03,16.650000000000002,null,12.8,15.03,16.650000000000002]
[13.11,15.340000000000002,17.2,null,13.11,15.340000000000002,17.2]
[13.420000000000002,15.629999999999999,17.760000000000002,null,13.420000000000002,15.629999999999999,17.760000000000002]
[13.73,16.05,18.18,null,13.73,16.05,18.18]
[14.030000000000001,16.34,18.459999999999997,null,14.030000000000001,16.34,18.459999999999997]
[14.32,16.75,18.87,null,14.32,16.75,18.87]
[14.610000000000001,17.150000000000002,19.259999999999998,null,14.610000000000001,17.150000000000002,19.259999999999998]
[15.02,17.669999999999998,19.66,null,15.02,17.669999999999998,19.66]
[15.310000000000002,18.060000000000002,20.169999999999998,null,15.310000000000002,18.060000000000002,20.169999999999998]
[15.72,18.45,20.68,null,15.72,18.45,20.68]
[16.11,18.84,21.18,null,16.11,18.84,21.18]
[16.5,19.329999999999998,21.790000000000003,null,16.5,19.329999999999998,21.790000000000003]
[16.900000000000002,19.830000000000002,22.29,null,16.900000000000002,19.830000000000002,22.29]
[17.28,20.190000000000001,22.89,null,17.28,20.190000000000001,22.89]
[17.649999999999999,20.669999999999998,23.36,null,17.649999999999999,20.669999999999998,23.36]
[18.029999999999998,21.15,23.94,null,18.029999999999998,21.15,23.94]
[18.52,21.62,24.529999999999998,null,18.52,21.62,24.529999999999998]
[18.88,22.189999999999998,25.1,null,18.88,22.189999999999998,25.1]
[19.350000000000001,22.650000000000002,25.650000000000002,null,19.350000000000001,22.650000000000002,25.650000000000002]
[19.82,23.22,26.21,null,19.82,23.22,26.21]
[20.28,23.66,26.87,null,20.28,23.66,26.87]
[20.73,24.21,27.41,null,20.73,24.21,27.41]
[21.19,24.759999999999998,28.060000000000002,null,21.19,24.759999999999998,28.060000000000002]
[21.75,25.290000000000003,28.7,null,21.75,25.290000000000003,28.7]
[22.3,25.94,29.32,null,22.3,25.94,29.32]
[22.85,26.47,29.94,null,22.85,26.47,29.94]
[23.39,27.089999999999996,30.669999999999998,null,23.39,27.089999999999996,30.669999999999998]
[23.919999999999998,27.72,31.39,null,23.919999999999998,27.72,31.39]
[24.55,28.32,32.08,null,24.55,28.32,32.08]
[25.169999999999998,29.049999999999997,32.78,null,25.169999999999998,29.049999999999997,32.78]
[25.790000000000003,29.64,33.589999999999996,null,25.790000000000003,29.64,33.589999999999996]
[26.41,30.349999999999998,34.369999999999997,null,26.41,30.349999999999998,34.369999999999997]
[27.12,31.03,35.15,null,27.12,31.03,35.15]
[27.82,31.72,35.809999999999995,null,27.82,31.72,35.809999999999995]
[28.52,32.4,36.57,null,28.52,32.4,36.57]
[29.2,33.06,37.419999999999995,null,29.2,33.06,37.419999999999995]
[30,33.83,38.159999999999997,null,30,33.83,38.159999999999997]
[30.570000000000004,34.589999999999996,38.99,null,30.570000000000004,34.589999999999996,38.99]
[31.230000000000004,35.25,39.72,null,31.230000000000004,35.25,39.72]
[31.69,36.19,40.43,null,31.69,36.19,40.43]
[31.419999999999998,36.32,41.13,null,31.419999999999998,36.32,41.13]
[30.759999999999998,35.5,41.83,null,30.759999999999998,35.5,41.83]
[30.2,34.68,42.52,null,30.2,34.68,42.52]
[29.549999999999997,33.879999999999995,43.21,null,29.549999999999997,33.879999999999995,43.21]
[28.99,33.07,44.1,null,28.99,33.07,44.1]
[28.249999999999996,32.15,43.65,null,28.249999999999996,32.15,43.65]
[27.6,31.35,42.370000000000005,null,27.6,31.35,42.370000000000005]
[27.060000000000002,30.56,41.199999999999996,null,27.060000000000002,30.56,41.199999999999996]
[26.400000000000002,29.65,39.910000000000004,null,26.400000000000002,29.65,39.910000000000004]
[25.779999999999998,28.88,38.690000000000005,null,25.779999999999998,28.88,38.690000000000005]
[25.169999999999998,28.13,37.5,null,25.169999999999998,28.13,37.5]
[24.55,27.36,36.270000000000003,null,24.55,27.36,36.270000000000003]
[24.01,26.590000000000003,35.120000000000005,null,24.01,26.590000000000003,35.120000000000005]
[23.41,25.840000000000003,33.94,null,23.41,25.840000000000003,33.94]
[22.88,25.14,32.700000000000003,null,22.88,25.14,32.700000000000003]