{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Interface","Calculations","Data"],"not_rendered":{"charts":0,"images":3,"macros":1,"external_links":0,"defined_names":49,"comments":1,"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":"Interface","index":0,"n_rows":56,"n_cols":10,"rows_kept":56,"cut":false,"hidden":false,"merged":[[46,5,47,7],[16,8,16,11],[39,5,40,7],[31,8,31,11],[35,8,35,11],[20,8,20,11],[16,5,16,7],[18,8,18,11],[33,8,33,11],[25,8,25,11],[25,5,25,7],[22,8,22,11],[23,8,23,11],[29,8,29,11],[0,9,1,11],[44,1,44,2],[6,8,6,11],[8,8,8,11],[12,8,12,11],[14,8,14,11],[10,8,10,11],[6,5,6,7],[8,5,8,7],[12,5,12,7],[43,5,44,7],[14,5,14,7]]}
[null,null,null,null,null,null,null,null,null,"CLEAR ALL DATA"]
[null,null,null,null,"Compensation calculator"]
[]
[null,null,null,null,"Please ensure all the green boxes are completed in order for a compensation amount to be calculated"]
[]
[]
[null,null,null,null,null,"Name"]
[]
[null,null,null,null,null,"Date of birth"]
[]
[null,null,null,null,null,"Date joined NICS"]
[null,"Notes - Please read these carefully before continuing"]
[null,null,null,null,null,"Last day of service"]
[null,"1. This calculator is designed to give you an indication"]
[null,"     of the amount of compensation you may receive if",null,null,null,"Age at last day of service",null,null,{"$f":"ageText","v":""}]
[null,"     you left the NICS through an early exit scheme."]
[null,"     Please note that this is an estimate only and is not ",null,null,null,"Pension scheme"]
[null,"     intended to be finally binding."]
[null,"2. In order to facilitate the use of this calculator, you will",null,null,null,"Does tapering apply?",null,null,{"$f":"TaperingRequired","v":{"$error":"#N/A"}}]
[null,"     require information that is contained in your most recent"]
[null,"     annual benefit statement. Please ensure that you keep ",null,null,null,"Have you been part time in the last 3 years?"]
[null,"     this information in a safe place."]
[null,"3. Enter your reckonable service from your most recent",null,null,null,"Reckonable service (years)"]
[null,"     annual benefit statement in the full time reckonable",null,null,null,"Reckonable service (days)"]
[null,"     service years and days fields. As your last benefit"]
[null,"     statement is calculated up to 31 March 2020, ",null,null,null,"Current salary"]
[null,"     remember to add the amount of time from this date"]
[null,"     to your last day of service, if your service is less than"]
[null,"     21 years. Please note that added years/pension, "]
[null,"     transferred in service and previous periods of service",null,null,null,"Service",null,null,{"$f":"servicetext","v":""}]
[null,"     (even if aggregated) will not count for compensation"]
[null,"     and therefore must be deducted from your total",null,null,null,"Reckonable service",null,null,{"$f":" IF(PartTime = \"Yes\", PartTimeService, FullTimeService)","v":0}]
[null,"     reckonable service shown on your annual benefit"]
[null,"     statement.",null,null,null,"Tapered service (if applicable)",null,null,{"$f":"IF(TaperingRequired=\"Yes\",NRAMonthsPlus6M,0)","v":{"$error":"#N/A"}}]
[null,"4. If your current full time equivalent salary is less than"]
[null,"    £26.051 enter this figure into the Salary field as this is",null,null,null,"Compensation reckonable service",null,null,{"$f":"ReckonableService","v":{"$error":"#N/A"}}]
[null,"    the deemed minimum amount of pay that will be used for"]
[null,"    compensation."]
[null,"5. If your current full time equivalent salary is greater than",null,null,"  Compensation Payment"]
[null,"    £105,447 enter this figure into the Salary field as this is",null,null,null,{"$f":"compensation","v":{"$error":"#N/A"}}]
[null,"    the deemed maximum pay that can be used for"]
[null,"    compensation."]
[null,"6. Following the 2019 pay award, the new pay scales for",null,null,"  Compensation Payment After Tax"]
[null,"     this year, including Senior Civil Service, can be found at: ",null,null,null,{"$f":"AfterTax","v":{"$error":"#N/A"}}]
[null,"https://www.finance-ni.gov.uk/topics/working-northern-ireland-civil-service/civil-service-pay"]
[null,"7. Compensation payments up to £30,000 are tax free. Any",null,null,"  Total Tax Deductions "]
[null,"    amount over this is subject to tax. Any queries relating",null,null,null,{"$f":"TotalTax","v":{"$error":"#N/A"}}]
[null,"    to the amount of tax payable should be directed to HMRC."]
[null,"8. If you have been part time at any point in the last 3 years,"]
[null,"    are partially retired or a non-scheme member, you will be "]
[null,"    unable to use this calculator. Civil ServicePensions will be"]
[null,"    in contact with you regarding a quote for your"]
[null,"    compensation."]
[null,"9. If you wish to print a copy of this calculator, click File - "]
[null,"    Print - Preview - Page Set Up. Ensure the portrait option is "]
[null,"    selected and adjust to 50% normal size and click Print."]

{"sheet":"Calculations","index":1,"n_rows":34,"n_cols":10,"rows_kept":34,"cut":false,"hidden":true,"merged":[]}
[]
[]
[null,"Age Calculations",null,null,null,null,"Compensation Calculatations"]
[]
[null,"Age at Last day of Service",{"$f":"ROUND(YEAR(lastDay)-YEAR(dob)+(lastDay-DATE(YEAR(lastDay),MONTH(dob),DAY(dob)))/365,4)","v":0},null,null,null,"If Full Time",{"$f":"IF(TaperingRequired=\"Yes\", MIN((ReckonableService*PensionableEarnings)/12,(NRAMonthsPlus6M*PensionableEarnings)/12), (ReckonableService*PensionableEarnings)/12)","v":{"$error":"#N/A"}}]
[null,"Age at Last day of Service text",{"$f":"IF(INT(ROUND(ageLastDay*12,0)/12),INT(ROUND(ageLastDay*12,0)/12)&\" Years \",\"\")&IF(MOD(ROUND(ageLastDay*12,0),12),MOD(ROUND(ageLastDay*12,0),12)&\" Months\",\"\")","v":""}]
[null,"Pension Age",{"$f":"VLOOKUP((scheme),pensionSchemesData,2,FALSE)","v":{"$error":"#N/A"}},null,null,null,"If Part Time",{"$f":"IF(TaperingRequired=\"Yes\", MIN((ReckonableService * PensionableEarnings) / 12, ((maxReckonable*PensionableEarnings) / 12) * (PartTimeService / FullTimeService), ((X_Months * PensionableEarnings) / 12)), MIN((ReckonableService * PensionableEarnings) / 12, ((maxReckonable*PensionableEarnings) / 12) * (PartTimeService / FullTimeService)))","v":{"$error":"#N/A"}}]
[null,"Time to pension age",{"$f":"MAX(0,C7-C5)","v":{"$error":"#N/A"}}]
[null,"Time to pension age text",{"$f":"IF(INT(ROUND(timeToPensionAge*12,0)/12),INT(ROUND(timeToPensionAge*12,0)/12)&\" Years \",\"\")&IF(MOD(ROUND(timeToPensionAge*12,0),12),MOD(ROUND(timeToPensionAge*12,0),12)&\" Months\",\"\")","v":{"$error":"#N/A"}}]
[null,"Time to pension in Months",{"$f":"ROUND(timeToPensionAge*12, 0)","v":{"$error":"#N/A"}},null,null,null,"Compensation",{"$f":"IF(PartTime = \"Yes\", IF(TaperingRequired=\"Yes\", Compensation_Payment_Part_Time_Tapering, Compensation_Payment_Part_Time), IF(TaperingRequired=\"Yes\", Compensation_Payment_Tapering, Compensation_Payment))","v":{"$error":"#N/A"}}]
[null,"Full Time Hours",37]
[]
[null,"Service Calculations"]
[null,null,null,null,null,null,"Rate","Start of Band","End of Band","Difference"]
[null,"Max Reckonable Service",{"$f":"IF((ageLastDay<C7),21,6)","v":{"$error":"#N/A"}},null,null,null,0,0,0,{"$f":"I15-H15","v":0}]
[null,"Reckonable Service",{"$f":"IF((ageLastDay>C7)*AND(qService<6),qService,IF(qService>maxReckonable,maxReckonable,qService))","v":{"$error":"#N/A"}},null,null,null,0.2,{"$f":"IF(I15>=1,I15+1,I15)","v":0},2875,{"$f":"I16-H16","v":2875}]
[null,"Reckonable Service in Months",{"$f":"C16*12","v":{"$error":"#N/A"}},null,null,null,0.4,{"$f":"I16+0.01","v":2875.01},12500,{"$f":"I17-H17+0.01","v":9625}]
[null,"Service",{"$f":"ROUND(YEAR(lastDay)-YEAR(joindate)+(lastDay-DATE(YEAR(lastDay),MONTH(joindate),DAY(joindate)))/365,4)","v":0},null,null,null,0.45,{"$f":"I17","v":12500},99999999,{"$f":"I18-H18+1","v":99987500}]
[null,"Service text",{"$f":"IF(INT(ROUND(service*365,0)/365),INT(ROUND(service*365,0)/365)&\" Years \",\"\")&IF(MOD(ROUND(service*365,0),365),MOD(ROUND(service*365,0),365)&\" Days\",\"\")","v":""}]
[]
[null,"Tapering"]
[null,"Tapering Required",{"$f":"IF(C10>=16,\"No\",\"Yes\")","v":{"$error":"#N/A"}}]
[null,"NRA (Months)",{"$f":"C10","v":{"$error":"#N/A"}},null,null,null,"Taxable ",{"$f":"IF(compensation<30000,0,compensation-30000)","v":{"$error":"#N/A"}}]
[null,"NRA + 6 Months (Months)",{"$f":"C10+6","v":{"$error":"#N/A"}}]
[null,"NRA + 6 Months (Years)",{"$f":"ROUND(C24/12,1)","v":{"$error":"#N/A"}}]
[null,"X Months",{"$f":"ROUND((NRAMonths*(PartTimeHours/FullTimeHours))+(6*PartTimeService/FullTimeService),0)","v":{"$error":"#N/A"}}]
[null,"Full Time Service",{"$f":" ROUND(FullTimeYears + (FullTimeDays/365), 4)","v":0},null,null,null,null,"Tax","Carry Over"]
[null,"Part Time Service",{"$f":" ROUND(PartTimeYears + (PartTimeDays / 365),4)","v":{"$error":"#REF!"}},null,null,null,{"$f":"G15*100&\"% Rate\"","v":"0% Rate"},{"$f":"IF(H23>=H15,IF(H23>=J15,J15*G15,H23*G15),0)","v":{"$error":"#N/A"}},{"$f":"IF((H23-J15)>0,H23-J15,0)","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,{"$f":"G16*100&\"% Rate\"","v":"20% Rate"},{"$f":"IF(I28>0,IF(I28>=J16,J16*G16,I28*G16),0)","v":{"$error":"#N/A"}},{"$f":"IF((I28-J16)>0,I28-J16,0)","v":{"$error":"#N/A"}}]
[null,"Compensation Payment",{"$f":" ReckonableService * PensionableEarnings /12","v":{"$error":"#N/A"}},null,null,null,{"$f":"G17*100&\"% Rate\"","v":"40% Rate"},{"$f":"IF(I29>0,IF(I29>=J17,J17*G17,I29*G17),0)","v":{"$error":"#N/A"}},{"$f":"IF((I29-J17)>0,I29-J17,0)","v":{"$error":"#N/A"}}]
[null,"Compensation Payment Part Time",{"$f":"MIN((ReckonableService*PensionableEarnings)/12,((maxReckonable*PensionableEarnings)/12)*(PartTimeService/FullTimeService))","v":{"$error":"#N/A"}},null,null,null,{"$f":"G18*100&\"% Rate\"","v":"45% Rate"},{"$f":"IF(I30>0,IF(I30>=J18,J18*G18,I30*G18),0)","v":{"$error":"#N/A"}},{"$f":"IF((I30-J18)>0,I30-J18,0)","v":{"$error":"#N/A"}}]
[null,"Compensation Payment Tapering",{"$f":"MIN(ReckonableService * PensionableEarnings / 12,NRAMonthsPlus6M * PensionableEarnings / 12)","v":{"$error":"#N/A"}}]
[null,"Compensation Payment Part Time Tapering",{"$f":"MIN(ReckonableService * PensionableEarnings / 12,maxReckonable * PensionableEarnings / 12 * PartTimeService / FullTimeService, X_Months * PensionableEarnings / 12)","v":{"$error":"#N/A"}},null,null,null,"Total  Tax",{"$f":"SUM(H28:H31)","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,"Compensation After Tax",{"$f":"compensation-TotalTax","v":{"$error":"#N/A"}}]

{"sheet":"Data","index":2,"n_rows":5,"n_cols":9,"rows_kept":5,"cut":false,"hidden":true,"merged":[]}
["Schemes","Scheme Pension Age","Part Time",null,null,"Rate","Start of Band","End of Band","Difference"]
["Classic",60,"Yes",null,null,0,0,0,{"$f":"H2-G2","v":0}]
["Premium",60,"No",null,null,0.2,{"$f":"IF(H2>=1,H2+1,H2)","v":0},2875,{"$f":"H3-G3","v":2875}]
["Classic Plus",60,null,null,null,0.4,{"$f":"H3+0.01","v":2875.01},12500,{"$f":"H4-G4+0.01","v":9625}]
["Nuvos",65,null,null,null,0.45,{"$f":"H4","v":12500},99999999,{"$f":"H5-G5+1","v":99987500}]