{"text_format":"1","workbook":"xlsx","n_sheets":10,"sheets":["Cover","Version control","AnnGenHiddenLists","Guidance","Input and results","Calculations","v0.04 Checks","Tables","Scheme Info","State Pension Age"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":88,"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":"Cover","index":0,"n_rows":40,"n_cols":2,"rows_kept":40,"cut":false,"hidden":true,"merged":[]}
["Government Actuary's Department"]
["NICSPS Added Pension Calculator"]
["Cover"]
[{"$f":"CELL(\"filename\",A1)","v":"L:\\PSPS\\NICSPS\\_tools\\development area\\added pension\\[NICSPS Added Pension Calculator v0.07.1.xlsm]Cover"}]
[]
["Specification"]
[]
[null,"Added years calculator for PCSPS (NI)"]
[]
["Explanation"]
[]
["Sheet","Description"]
["input and results","User control sheet and calculator"]
["Calculations","Where the calculations are done. Will be hidden."]
["Tables","Where current tables will be imported. Will be hidden."]
["Scheme Info","Tables and info needed to calculations. Will be hidden."]
["State Pension Age","State pension age calculator. Will be hidden."]
[]
[]
[]
[]
[]
["User instructions"]
[]
[]
[]
["External links: Links from other workbooks to this workbook"]
[]
["List of workbooks which link into this workbook (nb workbooks which this workbook links to can be viewed by selecting View>Links):"]
[null,"> Enter hyperlinks to workbooks here"]
[]
["Legend"]
["Blue text","Input data"]
["Red text","Links from another (external) workbook"]
["Pink text","Assumptions"]
["Green text","Links from another cell within this workbook"]
["Black text","Calculations"]
["Orange background","Linked upwards to other workbooks"]
["Turquoise background","Quoted in external advice"]
["Grey text","Checks"]

{"sheet":"Version control","index":1,"n_rows":23,"n_cols":12,"rows_kept":23,"cut":false,"hidden":true,"merged":[]}
["Government Actuary's Department"]
[{"$f":"IF(title=\"> Enter workbook title here\",\"Enter workbook title in Cover sheet\",title)","v":"NICSPS Added Pension Calculator"}]
["Version Control"]
[{"$f":"CELL(\"filename\",A1)","v":"L:\\PSPS\\NICSPS\\_tools\\development area\\added pension\\[NICSPS Added Pension Calculator v0.07.1.xlsm]Version control"}]
[]
["Version number","File pathname","Paper file","eBis code","Internal or external documents based on this version","Author or Editor","Date","Comments","Checked by","Date","Comments","External links checked?"]
["v0.01",null,null,null,null,"Michael Tagg","2018-09-11","Initial set up","D Hirani","2018-10-30","Inputs and results - extended height of row 20 to ensure full error message can be seen\nCalculations - cells D22, D42, D49 - updated wording\ncell D49 updated, was showing as blank when AP limit breached\nScheme info\nUpdate B19 so blank if not a scheme which allows self only"]
["v0.02",null,null,null,null,"D Hirani","2018-10-31","Corrected for checking comments. Added guidance tab and notes"]
["v0.03",null,null,null,null,"Michael Tagg","2018-12-10",">Added in revised guidance wording supplied by PCPS\n>Added in revised important info and disclaimers supplied by PCPS\n>Changed validation message for date of birth\n>Corrected calcOptions (on calculation sheet) references to added years to added pension\n>Inputs and results - updated cell D20 to also check if \"Lump Sum\" selected when checking start date of contract\n>Changed validation on contract start date to go from current date","D Hirani","2018-12-11","Updated typo 'memebers'\nUpdated capitalisation for consistency e.g. Alpha\n Happy with changes to contract start date. Error message restricts to future date, and functionality allows for non 1 April date only when:\no paying by lump sum and joined more than 12 months ago\no pay by monthly conts and joined less than 3 months ago\nNo results are produced in any other case of non-1 April date as required\n\nDoB restriction such that DoB must be before current date okay. \n\nWe cannot apply restriction to DoB such that member cannot be aged older than 75 because we need to know payment date to calculate age last birthday. However, exisiting error message in cell D34 covers this off. \n"]
[null,null,null,null,null,"Richard Pethick","2020-01-27","Edited the SCHEME_DB table to include different AP limits for 2019/20 and 2020/21. Changed the formula for \"calcMaxLimit\" to select the correct rate from the list.","D Hirani","2020-01-30","Okay. AP limit feeds in as appropriate on before 01/04/2020 and on and after 01/04/2020 "]
[null,null,null,null,null,null,"2020-01-28","Added some test results to check the variable AP limits. "]
[]
["v0.05","PCSPS(NI) Added Pension Calculator v0.05 WIP.xlsm",null,null,null,"Richard Pethick","2020-02-03","Imported 2019 factors.","D Hirani","2020-02-03","Okay, chec against 2019 factor guidance notes saved here: \\\\Gad-ast\\ast\\Factors\\2019\\Processing\\CS_NI"]
[]
[null,null,null,null,"Scheme Info","Richard Pethick","2020-01-04","Manually added 2021/22 AP limits (source linked on sheet)"]
[null,null,null,null,null,null,null,"Added estimated 2022/23 AP limits (increase by CPI at 2%), as this will prevent the tool from breaking if users want to use monthly contributions after 01/04/2021"]
[]
[null,null,null,null,null,"Richard Pethick","2022-01-20","Manually added 2022/23 AP limits (source linked on sheet)"]
[null,null,null,null,null,null,null,"Added estimated 2023/24 AP limits (increased by CPI at 2%)."]
[null,null,null,null,null,null,null,"Extended the note in row 55 of Guidance to state that the 12 month restriction does not apply to full/taper protected members."]
[]
[null,null,null,null,null,"Richard Pethick","2022-01-28","Changed title / headers to refer to NICSPS, rather than PCSPS(NI)"]
[null,null,null,null,null,null,null,"Amended the question in \"Input and results\" B14 to imply that transition members should select the date they joined their legacy scheme, not when they joined Alpha"]

{"sheet":"AnnGenHiddenLists","index":2,"n_rows":160,"n_cols":3,"rows_kept":160,"cut":false,"hidden":true,"merged":[]}
["DO NOT REMOVE WORKSHEET"]
[]
["BaseTablesList",null,"ImprovementsList"]
["PCFA00",null,"CMI2016F-02-1pt25"]
["PCMA00",null,"CMI2016F-07-1pt5"]
["PFA80",null,"CMI2016M-02-1pt25"]
["PFA92",null,"CMI2016M-07-1pt5"]
["PFA92 - 08",null,"Long Cohort"]
["PFA92-10",null,"Medium Cohort"]
["PMA80",null,"PFA80imp"]
["PMA92",null,"PFA92"]
["PMA92 - 08",null,"PMA80imp"]
["PMA92-10",null,"PMA92"]
["PNFA00",null,"Short Cohort"]
["PNFA00-06",null,"SMPI-2018imp"]
["PNFA00-08",null,"UKF2004imp"]
["PNFA00-10",null,"UKF2006imp"]
["PNMA00",null,"UKF2006imp_HLE"]
["PNMA00-06",null,"UKF2006imp_LLE"]
["PNMA00-08",null,"UKF2008imp"]
["PNMA00-10",null,"UKF2010imp"]
["S1DFA",null,"UKF2012imp"]
["S1DFA-06",null,"UKF2014imp"]
["S1DFA-08",null,"UKf2016HLEimp"]
["S1DFA-10",null,"UKF2016imp"]
["S1DFA-12",null,"UKf2016LLEimp"]
["S1DFA-14",null,"UKM2004imp"]
["S1DFA-16",null,"UKM2006imp"]
["S1DFA-L",null,"UKM2006imp_HLE"]
["S1DFA-L-06",null,"UKM2006imp_LLE"]
["S1DFA-L-08",null,"UKM2008imp"]
["S1DFA-L-10",null,"UKM2010imp"]
["S1DFA-L-12",null,"UKM2012imp"]
["S1IFA",null,"UKM2014imp"]
["S1IFA-06",null,"UKm2016HLEimp"]
["S1IFA-08",null,"UKM2016imp"]
["S1IFA-10",null,"UKm2016LLEimp"]
["S1IFA-12",null,"None"]
["S1IFA-14"]
["S1IFA-16"]
["S1IFA-STSS-16"]
["S1IFA-TPS-16"]
["S1IMA"]
["S1IMA-06"]
["S1IMA-08"]
["S1IMA-10"]
["S1IMA-12"]
["S1IMA-14"]
["S1IMA-16"]
["S1IMA-STSS-16"]
["S1IMA-TPS-16"]
["S1NFA"]
["S1NFA-06"]
["S1NFA-08"]
["S1NFA-10"]
["S1NFA-12"]
["S1NFA-14"]
["S1NFA-16"]
["S1NFA-L"]
["S1NFA-L-06"]
["S1NFA-L-08"]
["S1NFA-L-10"]
["S1NFA-L-12"]
["S1NFA-L-14"]
["S1NFA-L-16"]
["S1NFA-L-STSS"]
["S1NFA-L-STSS-06"]
["S1NFA-L-STSS-08"]
["S1NFA-L-STSS-10"]
["S1NFA-L-STSS-12"]
["S1NFA-L-STSS-14"]
["S1NFA-L-STSS-16"]
["S1NFA-L-TPS"]
["S1NFA-L-TPS-06"]
["S1NFA-L-TPS-08"]
["S1NFA-L-TPS-10"]
["S1NFA-L-TPS-12"]
["S1NFA-L-TPS-14"]
["S1NFA-L-TPS-16"]
["S1NMA"]
["S1NMA-06"]
["S1NMA-08"]
["S1NMA-10"]
["S1NMA-12"]
["S1NMA-14"]
["S1NMA-16"]
["S1NMA-L"]
["S1NMA-L-06"]
["S1NMA-L-08"]
["S1NMA-L-10"]
["S1NMA-L-12"]
["S1NMA-L-14"]
["S1NMA-L-16"]
["S1PFA"]
["S1PFA-06"]
["S1PFA-08"]
["S1PFA-10"]
["S1PFA-12"]
["S1PFA-14"]
["S1PFA-16"]
["S1PMA"]
["S1PMA-06"]
["S1PMA-08"]
["S1PMA-10"]
["S1PMA-12"]
["S1PMA-14"]
["S1PMA-16"]
["S2DFA"]
["S2DFA-12"]
["S2DFA-14"]
["S2DFA-16"]
["S2DFL"]
["S2DFL-12"]
["S2DFL-16"]
["S2IFA"]
["S2IFA-12"]
["S2IFA-14"]
["S2IFA-16"]
["S2IMA"]
["S2IMA-12"]
["S2IMA-14"]
["S2IMA-16"]
["S2NFA"]
["S2NFA-12"]
["S2NFA-14"]
["S2NFA-16"]
["S2NFA-CMI"]
["S2NMA"]
["S2NMA_L-16"]
["S2NMA-12 "]
["S2NMA-14"]
["S2NMA-16"]
["S2NMA-CMI"]
["S2PFA"]
["S2PFA-12"]
["S2PFA-14"]
["S2PFA-16"]
["S2PFL"]
["S2PFL-12"]
["S2PFL-16"]
["S2PMA"]
["S2PMA-12"]
["S2PMA-14"]
["S2PMA-16"]
["S2PML"]
["S2PML-12"]
["S2PML-16"]
["SMPI-2018"]
["UKF"]
["UKF2004"]
["UKF2006"]
["UKF2008"]
["UKF2010"]
["UKF2012"]
["UKM"]
["UKM2004"]
["UKM2006"]
["UKM2008"]
["UKM2010"]
["UKM2012"]

{"sheet":"Guidance","index":3,"n_rows":55,"n_cols":1,"rows_kept":55,"cut":false,"hidden":false,"merged":[[52,0,53,14]]}
["Government Actuary's Department"]
[{"$f":"IF(title=\"> Enter workbook title here\",\"Enter workbook title in Cover sheet\",title)","v":"NICSPS Added Pension Calculator"}]
[]
["Added pension calculator for Classic, Classic Plus, Premium, Nuvos and Alpha"]
[]
["You can use this tool to calculate your added pension."]
[]
["Hints and tips"]
[]
["*Complete each step in order"]
["*Always clear all fields at the start of each calculation"]
[]
["Guidance"]
[]
["Step 1 - About you"]
["The modeller requires member's personal details, for example date of birth"]
[]
["Step 2 - Your added pension options"]
["The modeller has been setup so that you choose how your added pension is calculated. "]
[]
["The following inputs are required:"]
["(a) When would you like your added pension contract to start? Enter the date you wish to start your added pension contract (ensuring that you adhere to the comments provided). "]
[]
["(b) What pension do you want to buy?"]
["(1) 'Self' (only for Nuvos and Alpha) - pension for member only"]
["(2) 'Self and dependants' - pension for member and dependants."]
[]
["(c) What do you want to calculate:"]
["(1) 'Pension' - the cost of buying a specific amount of added pension? Then you need to decide:"]
["(i) Do you pay for added pension by paying a one-off lump sum or making regular monthly contributions?"]
["(ii) How much added pension do you wish to buy?"]
["(2) 'Payment' - If you make a one-off lump sum payment or regular monthly payments how much added pension do you buy? Then you need to decide:"]
["(i) Do you make a one-off lump sum payment or regular monthly contributions?"]
["(ii) How much will you contribute as a one-off lump sum or regular monthly payments?"]
[]
["Results"]
["The scheme year in which payment or payments begin."]
[]
["Provides your Normal Pension Age (NPA) for the relevant scheme."]
[]
["If you have chosen 'Pension' and:"]
["(a) 'Lump sum' - you will be told the size of the lump sum which you will have to pay in order to purchase the added pension stated in 'Step 2'"]
["(b) 'Monthly contributions' - you will be told how much it will cost each month in order to purchase the added pension stated in 'Step 2'."]
[]
["If you have chosen 'Payment' and:"]
["(a) 'Lump Sum' -  you will be told how much additional pension you are expected to receive each year from the lump sum payment."]
["(b) 'Monthly Contributions' -  you will be told how much additional pension you are expected to receive each year from the monthly contributions."]
[]
["Your purchase is subject to the maximum amount of added pension that you can buy. You can find information on the current maximum limit on the Department of Finance website."]
["https://www.finance-ni.gov.uk/topics/working-northern-ireland-civil-service/key-sections-and-resources-nics-pension-schemes-members"]
[]
["Notes"]
["All monthly payments are assumed to be maintained at a constant level for the full period applied for. This calculator does not project future expected earnings, thus cannot be used to project any changes in pensionable earnings."]
[]
["Lump sum pension can only be purchased when a member has over 12 months' service in the scheme. This restriction does not apply to members moving to Alpha who had full or tapered transitional protection."]

{"sheet":"Input and results","index":4,"n_rows":66,"n_cols":5,"rows_kept":66,"cut":false,"hidden":false,"merged":[[63,1,63,3],[64,1,64,3],[17,1,17,2],[49,1,50,3],[51,1,52,3],[62,1,62,3]]}
["Government Actuary's Department"]
[{"$f":"IF(title=\"> Enter workbook title here\",\"Enter workbook title in Cover sheet\",title)","v":"NICSPS Added Pension Calculator"}]
[]
[null,"You can use this tool to calculate and cost your added pension"]
[]
[null,"Step 1 - About you"]
[]
[null,"Member of which scheme?",null,{"$f":"IF(inScheme=\"\",\"Enter Scheme\",\"\")","v":"Enter Scheme"},{"$f":"IF(D8=\"\",0,1)","v":1}]
[null,null,null,null,{"$f":"IF(D9=\"\",0,1)","v":0}]
[null,"Date of Birth",null,{"$f":"IF(inDoB=\"\",\"Enter Date of Birth\",\"\")","v":"Enter Date of Birth"},1]
[null,null,null,null,0]
[null,"Gender",null,{"$f":"IF(inGender=\"\",\"Enter Gender\",\"\")","v":"Enter Gender"},1]
[null,null,null,null,0]
[null,"How long ago did you join a Northern Ireland Civil Service Pension Scheme?",null,{"$f":"IF(inJoined=\"\",\"Enter time of joining\",\"\")","v":"Enter time of joining"},1]
[null,null,null,null,0]
[null,"Step 2 - Your added pension options",null,null,0]
[null,null,null,null,0]
[null,{"$f":"IF(inJoined=\"\",\"\",calcOptions)","v":""}]
[]
[null,"When would you like your added pension contract to start?",null,{"$f":"IF(inCalcDate=\"\",\"Enter contract start date\",IF(AND(indexJoined>=2,OR(MONTH(inCalcDate)<>4,DAY(inCalcDate)<>1),inHowPay<>\"Lump Sum\"),\"More than 3 months from joining so contract start date must fall on the next 1 April.\",\"\"))","v":"Enter contract start date"},1]
[null,null,null,null,0]
[null,"What sort of pension do you want to buy?",null,{"$f":"IF(inPenType=\"\",\"Enter type of pension\",\"\")","v":"Enter type of pension"},1]
[null,null,null,null,0]
[null,"Do you want to calculate an added pension amount or lump sum payment/monthly payments?",null,{"$f":"IF(inHowType=\"\",\"Enter calculation type\",\"\")","v":"Enter calculation type"},1]
[null,null,null,null,0]
[null,"How would you like to pay for your added pension?",null,{"$f":"IF(inHowPay=\"\",\"Enter method of payment\",IF(AND(OR(indexJoined<=2),inHowPay=\"Lump Sum\"),\"Less than 12 months from joining. Unable to purchase by lump sum.\",\"\"))","v":"Enter method of payment"},1]
[null,null,null,null,0]
[null,{"$f":"IF(inHowType=\"Pension\",\"How much added pension do you wish to buy?\",IF(inHowType=\"Payment\",\"How much contribution would you like to pay\" & IF(inHowPay=\"Lump Sum\",\"?\",\" each month?\"),\"\"))","v":""},null,{"$f":"IF(inHowMuch=\"\",\"Enter how much\",\"\")","v":"Enter how much"},1]
[]
[null,"Results"]
[]
[null,"Scheme Year",{"$f":"calcSchemeYear","v":""}]
[]
[null,"Your age last birthday at the calcuation date is:",{"$f":"calcAge","v":""},{"$f":"IF(AND(NOT(ISBLANK(C34)),OR(C34<16,C34>75)),\"Unable to produce quote for ages less than 16 or greater than 75\",\"\")","v":"Unable to produce quote for ages less than 16 or greater than 75"}]
[]
[null,"Your Normal Pension Age is:",{"$f":"calcNPA","v":""}]
[]
[null,{"$f":"calcDesc","v":""},{"$f":"TEXT(calcResult,\"£#,##0.00\")","v":""},{"$f":"IF(C38=\"\",\"\",IF(inHowType=\"Payment\",\"a year\",IF(inHowPay=\"Lump sum\",\"\",\"each month\")))","v":""}]
[]
[]
[null,{"$f":"calcLSDesc","v":""}]
[]
[null,"Added pension limit"]
[]
[null,{"$f":"calcLimitDesc","v":""}]
[]
[null,"Important notes"]
[null,"1 This added pension calculator uses the latest calculation factors. Factors, and therefore results, may be subject to change in the future."]
[null,"2 Please note that if you do not use the most recent version of this added pension calculator then the results will not be valid."]
[null,"3 The cost shown is the gross amount. You will normally get income tax relief on these contributions. If you make your payment through payroll the tax relief will be applied automatically. If you make a lump sum purchase by cheque you will have to claim tax relief from HM Revenue & Customs (HMRC)."]
[]
[null,"4 In most circumstances your added pension will come into payment at the same time as your standard pension. If you draw your pension before Normal Pension Age (NPA) it will be reduced for early payment. If you continue to work beyond NPA, your pension will be adjusted for late payment."]
[]
[null,"5 The figures shown are an illustration of the current cost of buying added pension."]
[null,"6 Your total pension contributions (not just added pension) in any year cannot be more than your salary."]
[null,"7 If you increase your pension savings in any year over the Annual Allowance set by HMRC, you may incur a tax charge. For more information, including examples, see the HMRC wesbite: "]
[null,"www.hmrc.gov.uk "]
[null,"8 The added pension limit is based on the amount of added pension calculated above and it does not allow for any added pension you might have purchased in previous scheme years."]
[null,"9 Added pension will be increased in line with price inflation."]
[null,"10 The added pension limit for 2023-2024 and beyond has not yet been determined. This tool instead estimates the contract value by increasing the current limit in line with inflation (assumed to be at 2% per annum)"]
[]
[null,"Disclaimer: "]
[null,"Every effort has been made to make this tool as accurate as possible but if the tool differs to the regulations the regulations will apply. \n"]
[null,"This tool is based on the scheme regulations in force at the time of publication and there is no guarantee that any part of the regulations will not change in the future. "]
[null,"You should not take anything in this tool as financial advice. You might want to consider contacting an Independent Financial Advisor (IFA) to discuss your retirement planning. You can find tips on finding an IFA by visiting the Financial Conduct Authority website: "]
[null,"www.fca.org.uk "]

{"sheet":"Calculations","index":5,"n_rows":49,"n_cols":8,"rows_kept":49,"cut":false,"hidden":true,"merged":[]}
["Government Actuary's Department"]
[{"$f":"IF(title=\"> Enter workbook title here\",\"Enter workbook title in Cover sheet\",title)","v":"NICSPS Added Pension Calculator"}]
["Calculations"]
[{"$f":"CELL(\"filename\",A1)","v":"L:\\PSPS\\NICSPS\\_tools\\development area\\added pension\\[NICSPS Added Pension Calculator v0.07.1.xlsm]Calculations"}]
[]
[null,"named range on other sheet"]
[null,"named range on this sheet"]
[]
["description","named range","format","value"]
["Scheme","inScheme","@",{"$f":"TEXT(IF(ISBLANK(INDIRECT(B10)),\"\",INDIRECT(B10)),C10)","v":""}]
["Date of Birth","inDoB","dd/mm/yyyy",""]
["Gender","inGender","@",""]
["Joined","inJoined","@","",null,"indexJoined",{"$f":"IFERROR(MATCH(inJoined,JoinedList,0),-1)","v":-1}]
["Calculation Date","inCalcDate","dd/mm/yyyy",""]
["Pension Type","inPenType","@",""]
["How Type","inHowType","@",""]
["How to pay","inHowPay","@",""]
["Amount","inHowMuch","£#,##0.00",""]
[]
["Input Errors?","inErrors",null,{"$f":"IF(debug=TRUE,FALSE,IF(SUM(INPUTS_LOGIC)>0,TRUE,FALSE))","v":true},null,"debug",false,"set to TRUE to ignore input errors"]
[]
["Options available","calcOptions",null,{"$f":"TRIM(IF(indexJoined=1,\"You may opt to buy added pension by regular deduction from salary at any time if you apply within 3 months of joining the scheme. \",\" \")&IF(OR(indexJoined=1,indexJoined=2),\"As you are within 12 months of joining the scheme you are currently unable to buy added pension by lump sum. This restriction does not apply to fully or taper protected members of the pre-2015 schemes who have transitioned to Alpha. \",\"\")&IF(OR(indexJoined=2,indexJoined=3),\"You may opt to buy added pension by regular deduction from salary from the beginning of the next scheme year or beginning of any subsequent scheme years ie on 1 April each year.\",\"\")&IF(indexJoined=3,\"You are also able to opt to purchase added pension by a one off lump sum payment (once a year). \",\"\"))","v":""}]
["Calculation Age","calcAge",null,{"$f":"IFERROR(IF(OR(inDoB=\"\",inCalcDate=\"\"),\"\",DATEDIF(inDoB,inCalcDate,\"y\")),\"\")","v":""}]
["NPA yrs","NPA_YRS",null,{"$f":"IF(OR(inScheme=\"\",calcAge=\"\"),\"\",INDEX(SCHEME_DB,MATCH(inScheme,SCHEMES,0),MATCH(\"Yrs\",HEADERS,0)))","v":""}]
["NPA mths","NPA_MTH",null,{"$f":"IF(OR(inScheme=\"\",calcAge=\"\"),\"\",INDEX(SCHEME_DB,MATCH(inScheme,SCHEMES,0),MATCH(\"Mths\",HEADERS,0)))","v":""}]
["NPA yrs and mths","calcNPA",null,{"$f":"IF(OR(inScheme=\"\",calcAge=\"\"),\"\",INDEX(SCHEME_DB,MATCH(inScheme,SCHEMES,0),MATCH(\"NPA\",HEADERS,0)))","v":""}]
["NPA date","calcNPADate",null,{"$f":"IF(OR(inScheme=\"\",calcAge=\"\"),\"\",INDEX(SCHEME_DB,MATCH(inScheme,SCHEMES,0),MATCH(\"NPA_DATE\",HEADERS,0)))","v":""}]
["Number of 1 Aprils from calc date to NPA (inclusive)","calcNumAprils",null,{"$f":"IF(OR(inCalcDate=\"\",calcNPADate=\"\"),\"\",GetNumAprils(inCalcDate,calcNPADate))","v":""},"custom function see vba project"]
["Start Scheme Year","calcStartYear",null,{"$f":"IF(inCalcDate=\"\",\"\",DATE(YEAR(inCalcDate)+IF(MONTH(inCalcDate)<4,-1,0),4,1))","v":""}]
["End Scheme Year for contract start date","calcEndYear",null,{"$f":"IF(inCalcDate=\"\",\"\",DATE(YEAR(inCalcDate)+IF(MONTH(inCalcDate)<4,0,1),3,31))","v":""}]
["Scheme Year","calcSchemeYear",null,{"$f":"IF(calcStartYear=\"\",\"\",YEAR(calcStartYear)&\"/\"&(RIGHT(YEAR(calcStartYear),2)+1))","v":""}]
["Number of months in remaining in scheme year","calcRemMonths",null,{"$f":"IF(inCalcDate=\"\",\"\",DATEDIF(DATE(YEAR(inCalcDate),MONTH(inCalcDate),1),calcEndYear+1,\"m\"))","v":""}]
[]
["Factor table","factorTable",null,{"$f":"IF(inScheme=\"\",\"\",IF(inScheme=\"Alpha\",\"P2AP\",\"P1AP\")&IF(inHowPay=\"Lump Sum\",\"LS\",\"PC\")&IF(inScheme=\"Alpha\",SPA_YRS,\"\")&IF(inScheme=\"Classic\",\"CL1\",IF(inScheme=\"Nuvos\",\"NU1\",IF(inScheme=\"Alpha\",\"\",\"CP1\"))))","v":""}]
["Column in table to use","columnInTable",null,{"$f":"IF(OR(inScheme=\"Nuvos\",inScheme=\"Alpha\"),IF(inPenType=\"Self\",IF(inGender=\"Male\",2,3),4),2)","v":2}]
[{"$f":"inHowPay&\" Factor NPA\"","v":" Factor NPA"},"multiFactor",null,{"$f":"IFERROR(VLOOKUP(calcAge,INDIRECT(INDEX(TABLE_DB,MATCH(factorTable,(OFFSET(TABLE_DB,0,MATCH(\"TABNAME\",TABLE_HEADERS,0)-1,,1)),0),MATCH(\"INDEX\",TABLE_HEADERS,0))),columnInTable,FALSE),\"ERR\")","v":"ERR"}]
[{"$f":"inHowPay&\" Factor Next NPA\"","v":" Factor Next NPA"},"multiFactorNext",null,{"$f":"IFERROR(IF(inScheme=\"Alpha\",VLOOKUP(calcAge,INDIRECT(INDEX(TABLE_DB,MATCH(LEFT(factorTable,6)&MIN(68,(RIGHT(factorTable,2)+1)),(OFFSET(TABLE_DB,0,MATCH(\"TABNAME\",TABLE_HEADERS,0)-1,,1)),0),MATCH(\"INDEX\",TABLE_HEADERS,0))),columnInTable,FALSE),VLOOKUP(calcAge,INDIRECT(INDEX(TABLE_DB,MATCH(factorTable,(OFFSET(TABLE_DB,0,MATCH(\"TABNAME\",TABLE_HEADERS,0)-1,,1)),0),MATCH(\"INDEX\",TABLE_HEADERS,0))),columnInTable,FALSE)),\"ERR\")","v":"ERR"}]
[{"$f":"inHowPay&\" Factor interpolated\"","v":" Factor interpolated"},"multiFactorFinal",null,{"$f":"IFERROR(multiFactor+(multiFactorNext-multiFactor)*NPA_MTH/12,\"ERR\")","v":"ERR"}]
["Revluation table","revalTable",null,{"$f":"IF(inScheme=\"\",\"\",IF(inScheme=\"Alpha\",\"P2APREVAL\",\"P1APREVAL1\"))","v":""}]
["Revaluation Factor","revalFactor",null,{"$f":"IFERROR(VLOOKUP(calcNumAprils,INDIRECT(INDEX(TABLE_DB,MATCH(revalTable,(OFFSET(TABLE_DB,0,MATCH(\"TABNAME\",TABLE_HEADERS,0)-1,,1)),0),MATCH(\"INDEX\",TABLE_HEADERS,0))),2,FALSE),\"ERR\")","v":"ERR"}]
[]
["Output description","calcDesc","£#,##0.00",{"$f":"IF(calcResult=\"\",\"\",IF(inErrors=TRUE,\"\",IF(inHowType=\"Pension\",\"You have chosen to purchase a \"&LOWER(inPenType)&\" added pension of \"&TEXT(inHowMuch,C42)&IF(inHowPay=\"Lump Sum\",\" payable via lump sum, the lump sum required for this is:\",\" by way of monthly payments, the monthly amount payable to achieve this is:\"),\"You have chosen to contribute a \"&IF(inHowPay=\"Lump Sum\",\"lump sum\",\"monthly\")&\" payment of \"&TEXT(inHowMuch,C42)&IF(inHowPay=\"Lump Sum\",\"\",\" per month\")&\", this will purchase a \"&LOWER(inPenType)&\" added pension of:\")))","v":""}]
["Result","calcResult",null,{"$f":"IFERROR(IF(inErrors=TRUE,\"\",ROUND(IF(inHowType=\"Pension\",IF(inHowPay=\"Lump sum\",1,1/calcRemMonths),IF(inHowPay=\"Lump sum\",1,calcRemMonths))*inHowMuch*IF(inHowType=\"Pension\",multiFactorFinal*revalFactor,1/(multiFactorFinal*revalFactor)),2)),\"\")","v":""}]
["Lump Sum","calcLS",null,{"$f":"IF(calcResult=\"\",\"\",IF(inErrors=TRUE,\"\",IF(inScheme=\"Classic\",IF(inHowType=\"Payment\",ROUND(calcResult*3,2),ROUND(inHowMuch*3,2)),\"None\")))","v":""}]
["Lump Sum description","calcLSDesc","£#,##0.00",{"$f":"IF(calcLS=\"\",\"\",IF(inErrors=TRUE,\"\",IF(calcLS=\"None\",\"There is no automatic lump sum associated with this added pension estimate.\",\"You would also receive an automatic lump sum of \"&TEXT(calcLS,C45) & \" at Normal Pension Age.\")))","v":""}]
["Max AP Limit","calcMaxLimit",null,{"$f":"IF(OR(inScheme=\"\",calcAge=\"\"),\"\",INDEX(SCHEME_DB,MATCH(inScheme,SCHEMES,0),MATCH(calcSchemeYear,HEADERS,0)))","v":""}]
["Remaining Limit","calcRemLimit",null,{"$f":"IF(calcResult=\"\",\"\",IF(inErrors=TRUE,\"\",calcMaxLimit-IF(inHowType=\"Pension\",inHowMuch,calcResult)))","v":""}]
["Over limit?","calcOverLimit",null,{"$f":"IF(inErrors=TRUE,\"\",IF(calcRemLimit<0,TRUE,FALSE))","v":""}]
["Limit description","calcLimitDesc","£#,##0.00",{"$f":"IF(calcRemLimit=\"\",\"\",IF(calcOverLimit=FALSE,\"You have a remaining added pension balance of \" & TEXT(calcRemLimit,C49) & \" out of the added pension limit of \" & TEXT(calcMaxLimit,C49)&\". This assumes you have not purchased any other added pension.\",\"You have selected an added pension that exceeds the added pension limit\"))","v":""}]

{"sheet":"v0.04 Checks","index":6,"n_rows":50,"n_cols":20,"rows_kept":50,"cut":false,"hidden":true,"merged":[]}
["Government Actuary's Department"]
[{"$f":"IF(title=\"> Enter workbook title here\",\"Enter workbook title in Cover sheet\",title)","v":"NICSPS Added Pension Calculator"}]
["> Enter worksheet title here"]
[{"$f":"CELL(\"filename\",A1)","v":"L:\\PSPS\\NICSPS\\_tools\\development area\\added pension\\[NICSPS Added Pension Calculator v0.07.1.xlsm]v0.04 Checks"}]
[]
["Expected AP limit",5700,null,5800,null,7000,null,7200,null,7000,null,7200,null,7000,null,7200,null,7000,null,7200]
["Actual AP limit",{"$f":"B47","v":5700},null,5800,null,7000,null,7200,null,7000,null,7200,null,7000,null,7200,null,7000,null,{"$f":"T47","v":7200}]
[null,{"$f":"B7=B6","v":true},null,{"$f":"D7=D6","v":true},null,true,null,true,null,true,null,true,null,true,null,true,null,true,null,true]
[null,"1. Classic, 2019-20",null,"2. Classic, 2020-21",null,"3. C+, 2019-20",null,"4. C+ 2020-21",null,"5. Premium 2019-20",null,"6. Premium 2020-21",null,"7. Nuvos 2019-20",null,"8. Nuvos 2020-21",null,"9. Alpha 2019-20",null,"10. Alpha 2020-21"]
[]
[null,"Classic",null,"Classic",null,"Classic Plus",null,"Classic Plus",null,"Premium",null,"Premium",null,"Nuvos",null,"Nuvos",null,"Alpha",null,"Alpha"]
[null,"01/09/1980",null,"01/09/1980",null,"01/09/1960",null,"01/09/1980",null,"01/09/1965",null,"01/09/1965",null,"01/09/1971",null,"01/09/1971",null,"12/07/1990",null,"12/07/1990"]
[null,"Female",null,"Female",null,"Female",null,"Female",null,"Female",null,"Female",null,"Male",null,"Male",null,"Female",null,"Male"]
[null,"Less than 3 months",null,"Greater than 12 months",null,"Greater than 12 months",null,"Greater than 12 months",null,"Greater than 12 months",null,"Between 3 months and 12 months",null,"Greater than 12 months",null,"Greater than 12 months",null,"Less than 3 months",null,"Between 3 months and 12 months"]
[null,"29/02/2020",null,"01/04/2020",null,"11/03/2020",null,"01/04/2020",null,"11/03/2020",null,"01/04/2020",null,"04/02/2020",null,"01/04/2020",null,"01/03/2020",null,"01/04/2020"]
[null,"Self and dependant",null,"Self and dependant",null,"Self and dependant",null,"Self and dependant",null,"Self and dependant",null,"Self and dependant",null,"Self",null,"Self",null,"Self and dependant",null,"Self and dependant"]
[null,"Pension",null,"Pension",null,"Pension",null,"Pension",null,"Pension",null,"Pension",null,"Pension",null,"Pension",null,"Payment",null,"Pension"]
[null,"Monthly Contributions",null,"Lump Sum",null,"Lump Sum",null,"Monthly Contributions",null,"Lump Sum",null,"Monthly Contributions",null,"Lump Sum",null,"Monthly Contributions",null,"Monthly Contributions",null,"Monthly Contributions"]
[null,"£200.00",null,"£500.00",null,"£1,000.00",null,"£350.00",null,"£600.00",null,"£600.00",null,"£2,500.00",null,"£750.00",null,"£500.00",null,"£1,000.00"]
[]
[null,false,null,false,null,false,null,false,null,false,null,false,null,false,null,false,null,false,null,false]
[]
[null,"You may opt to buy added pension by regular deduction from salary at any time if you apply within 3 months of joining the scheme. As you are within 12 months of joining the scheme you are currently unable to buy added pension by lump sum.",null,"You may opt to buy added pension by regular deduction from salary from the beginning of the next scheme year or beginning of any subsequent scheme years ie on 1 April each year.You are also able to opt to purchase added pension by a one off lump sum payment (once a year).",null,"You may opt to buy added pension by regular deduction from salary from the beginning of the next scheme year or beginning of any subsequent scheme years ie on 1 April each year.You are also able to opt to purchase added pension by a one off lump sum payment (once a year).",null,"You may opt to buy added pension by regular deduction from salary from the beginning of the next scheme year or beginning of any subsequent scheme years ie on 1 April each year.You are also able to opt to purchase added pension by a one off lump sum payment (once a year).",null,"You may opt to buy added pension by regular deduction from salary from the beginning of the next scheme year or beginning of any subsequent scheme years ie on 1 April each year.You are also able to opt to purchase added pension by a one off lump sum payment (once a year).",null,"As you are within 12 months of joining the scheme you are currently unable to buy added pension by lump sum. You may opt to buy added pension by regular deduction from salary from the beginning of the next scheme year or beginning of any subsequent scheme years ie on 1 April each year.",null,"You may opt to buy added pension by regular deduction from salary from the beginning of the next scheme year or beginning of any subsequent scheme years ie on 1 April each year.You are also able to opt to purchase added pension by a one off lump sum payment (once a year).",null,"You may opt to buy added pension by regular deduction from salary from the beginning of the next scheme year or beginning of any subsequent scheme years ie on 1 April each year.You are also able to opt to purchase added pension by a one off lump sum payment (once a year).",null,"You may opt to buy added pension by regular deduction from salary at any time if you apply within 3 months of joining the scheme. As you are within 12 months of joining the scheme you are currently unable to buy added pension by lump sum.",null,"As you are within 12 months of joining the scheme you are currently unable to buy added pension by lump sum. You may opt to buy added pension by regular deduction from salary from the beginning of the next scheme year or beginning of any subsequent scheme years ie on 1 April each year."]
[null,39,null,39,null,59,null,39,null,54,null,54,null,48,null,48,null,29,null,29]
[null,60,null,60,null,60,null,60,null,60,null,60,null,65,null,65,null,68,null,68]
[null,0,null,0,null,0,null,0,null,0,null,0,null,0,null,0,null,0,null,0]
[null,"60 years 0 months",null,"60 years 0 months",null,"60 years 0 months",null,"60 years 0 months",null,"60 years 0 months",null,"60 years 0 months",null,"65 years 0 months",null,"65 years 0 months",null,"68 years 0 months",null,"68 years 0 months"]
[null,"2040-09-01",null,"2040-09-01",null,"2020-09-01",null,"2040-09-01",null,"2025-09-01",null,"2025-09-01",null,"2036-09-01",null,"2036-09-01",null,"2058-07-12",null,"2058-07-12"]
[null,21,null,20,null,1,null,20,null,6,null,5,null,17,null,16,null,39,null,38]
[null,"2019-04-01",null,"2020-04-01",null,"2019-04-01",null,"2020-04-01",null,"2019-04-01",null,"2020-04-01",null,"2019-04-01",null,"2020-04-01",null,"2019-04-01",null,"2020-04-01"]
[null,"2020-03-31",null,"2021-03-31",null,"2020-03-31",null,"2021-03-31",null,"2020-03-31",null,"2021-03-31",null,"2020-03-31",null,"2021-03-31",null,"2020-03-31",null,"2021-03-31"]
[null,"2019/20",null,"2020/21",null,"2019/20",null,"2020/21",null,"2019/20",null,"2020/21",null,"2019/20",null,"2020/21",null,"2019/20",null,"2020/21"]
[null,2,null,12,null,1,null,12,null,1,null,12,null,2,null,12,null,1,null,12]
[]
[null,"P1APPCCL1",null,"P1APLSCL1",null,"P1APLSCP1",null,"P1APPCCP1",null,"P1APLSCP1",null,"P1APPCCP1",null,"P1APLSNU1",null,"P1APPCNU1",null,"P2APPC68",null,"P2APPC68"]
[null,2,null,2,null,2,null,2,null,2,null,2,null,2,null,2,null,4,null,4]
[null,9.3699999999999992,null,9.1199999999999992,null,19.72,null,8.14,null,15.49,null,15.91,null,7.82,null,8.02,null,3.26,null,3.26]
[null,9.3699999999999992,null,9.1199999999999992,null,19.72,null,8.14,null,15.49,null,15.91,null,7.82,null,8.02,null,3.26,null,3.26]
[null,9.3699999999999992,null,9.1199999999999992,null,19.72,null,8.14,null,15.49,null,15.91,null,7.82,null,8.02,null,3.26,null,3.26]
[null,"P1APREVAL1",null,"P1APREVAL1",null,"P1APREVAL1",null,"P1APREVAL1",null,"P1APREVAL1",null,"P1APREVAL1",null,"P1APREVAL1",null,"P1APREVAL1",null,"P2APREVAL",null,"P2APREVAL"]
[null,1.52,null,1.49,null,1.02,null,1.49,null,1.1299999999999999,null,1.1000000000000001,null,1.4,null,1.37,null,2.16,null,2.12]
[]
[null,"You have chosen to purchase a self and dependant added pension of £200.00 by way of monthly payments, the monthly amount payable to achieve this is:",null,"You have chosen to purchase a self and dependant added pension of £500.00 payable via lump sum, the lump sum required for this is:",null,"You have chosen to purchase a self and dependant added pension of £1,000.00 payable via lump sum, the lump sum required for this is:",null,"You have chosen to purchase a self and dependant added pension of £350.00 by way of monthly payments, the monthly amount payable to achieve this is:",null,"You have chosen to purchase a self and dependant added pension of £600.00 payable via lump sum, the lump sum required for this is:",null,"You have chosen to purchase a self and dependant added pension of £600.00 by way of monthly payments, the monthly amount payable to achieve this is:",null,"You have chosen to purchase a self added pension of £2,500.00 payable via lump sum, the lump sum required for this is:",null,"You have chosen to purchase a self added pension of £750.00 by way of monthly payments, the monthly amount payable to achieve this is:",null,"You have chosen to contribute a monthly payment of £500.00 per month, this will purchase a self and dependant added pension of:",null,"You have chosen to purchase a self and dependant added pension of £1,000.00 by way of monthly payments, the monthly amount payable to achieve this is:"]
[null,1424.24,null,6794.4,null,20114.400000000001,null,353.75,null,10502.22,null,875.05,null,27370,null,686.71,null,71.010000000000005,null,575.92999999999995]
[null,600,null,1500,null,"None",null,"None",null,"None",null,"None",null,"None",null,"None",null,"None",null,"None"]
[null,"You would also receive an automatic lump sum of £600.00 at Normal Pension Age.",null,"You would also receive an automatic lump sum of £1,500.00 at Normal Pension Age.",null,"There is no automatic lump sum associated with this added pension estimate.",null,"There is no automatic lump sum associated with this added pension estimate.",null,"There is no automatic lump sum associated with this added pension estimate.",null,"There is no automatic lump sum associated with this added pension estimate.",null,"There is no automatic lump sum associated with this added pension estimate.",null,"There is no automatic lump sum associated with this added pension estimate.",null,"There is no automatic lump sum associated with this added pension estimate.",null,"There is no automatic lump sum associated with this added pension estimate."]
[null,5700,null,5800,null,7000,null,7200,null,7000,null,7200,null,7000,null,7200,null,7000,null,7200]
[null,5500,null,5300,null,6000,null,6850,null,6400,null,6600,null,4500,null,6450,null,6928.99,null,6200]
[null,false,null,false,null,false,null,false,null,false,null,false,null,false,null,false,null,false,null,false]
[null,"You have a remaining added pension balance of £5,500.00 out of the added pension limit of £5,700.00. This assumes you have not purchased any other added pension.",null,"You have a remaining added pension balance of £5,300.00 out of the added pension limit of £5,800.00. This assumes you have not purchased any other added pension.",null,"You have a remaining added pension balance of £6,000.00 out of the added pension limit of £7,000.00. This assumes you have not purchased any other added pension.",null,"You have a remaining added pension balance of £6,850.00 out of the added pension limit of £7,200.00. This assumes you have not purchased any other added pension.",null,"You have a remaining added pension balance of £6,400.00 out of the added pension limit of £7,000.00. This assumes you have not purchased any other added pension.",null,"You have a remaining added pension balance of £6,600.00 out of the added pension limit of £7,200.00. This assumes you have not purchased any other added pension.",null,"You have a remaining added pension balance of £4,500.00 out of the added pension limit of £7,000.00. This assumes you have not purchased any other added pension.",null,"You have a remaining added pension balance of £6,450.00 out of the added pension limit of £7,200.00. This assumes you have not purchased any other added pension.",null,"You have a remaining added pension balance of £6,928.99 out of the added pension limit of £7,000.00. This assumes you have not purchased any other added pension.",null,"You have a remaining added pension balance of £6,200.00 out of the added pension limit of £7,200.00. This assumes you have not purchased any other added pension."]

{"sheet":"Tables","index":7,"n_rows":68,"n_cols":81,"rows_kept":68,"cut":false,"hidden":true,"merged":[]}
["Government Actuary's Department"]
[{"$f":"IF(title=\"> Enter workbook title here\",\"Enter workbook title in Cover sheet\",title)","v":"NICSPS Added Pension Calculator"}]
["Tables"]
[{"$f":"CELL(\"filename\",A1)","v":"L:\\PSPS\\NICSPS\\_tools\\development area\\added pension\\[NICSPS Added Pension Calculator v0.07.1.xlsm]Tables"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"TABLE_01",null,null,"TABLE_02",null,null,"TABLE_03",null,null,null,null,"TABLE_04",null,null,"TABLE_05",null,null,"TABLE_06",null,null,null,null,"TABLE_07",null,null,"TABLE_08",null,null,null,null,"TABLE_09",null,null,null,null,"TABLE_10",null,null,null,null,"TABLE_11",null,null,null,null,"TABLE_12",null,null,null,null,"TABLE_13",null,null,null,null,"TABLE_14",null,null,null,null,"TABLE_15",null,null,null,null,"TABLE_16"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"Table \"&VLOOKUP(O5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(O5,TABLE_DB,3,FALSE)","v":"Table 1: P1APLSCL1"},null,null,{"$f":"\"Table \"&VLOOKUP(R5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(R5,TABLE_DB,3,FALSE)","v":"Table 2: P1APLSCP1"},null,null,{"$f":"\"Table \"&VLOOKUP(U5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(U5,TABLE_DB,3,FALSE)","v":"Table 3: P1APLSNU1"},null,null,null,null,{"$f":"\"Table \"&VLOOKUP(Z5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(Z5,TABLE_DB,3,FALSE)","v":"Table 4: P1APPCCL1"},null,null,{"$f":"\"Table \"&VLOOKUP(AC5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(AC5,TABLE_DB,3,FALSE)","v":"Table 5: P1APPCCP1"},null,null,{"$f":"\"Table \"&VLOOKUP(AF5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(AF5,TABLE_DB,3,FALSE)","v":"Table 6: P1APPCNU1"},null,null,null,null,{"$f":"\"Table \"&VLOOKUP(AK5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(AK5,TABLE_DB,3,FALSE)","v":"Table 7: P1APREVAL1"},null,null,{"$f":"\"Table \"&VLOOKUP(AN5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(AN5,TABLE_DB,3,FALSE)","v":"Table 1: P2APLS65"},null,null,null,null,{"$f":"\"Table \"&VLOOKUP(AS5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(AS5,TABLE_DB,3,FALSE)","v":"Table 2: P2APLS66"},null,null,null,null,{"$f":"\"Table \"&VLOOKUP(AX5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(AX5,TABLE_DB,3,FALSE)","v":"Table 3: P2APLS67"},null,null,null,null,{"$f":"\"Table \"&VLOOKUP(BC5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(BC5,TABLE_DB,3,FALSE)","v":"Table 4: P2APLS68"},null,null,null,null,{"$f":"\"Table \"&VLOOKUP(BH5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(BH5,TABLE_DB,3,FALSE)","v":"Table 5: P2APPC65"},null,null,null,null,{"$f":"\"Table \"&VLOOKUP(BM5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(BM5,TABLE_DB,3,FALSE)","v":"Table 6: P2APPC66"},null,null,null,null,{"$f":"\"Table \"&VLOOKUP(BR5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(BR5,TABLE_DB,3,FALSE)","v":"Table 7: P2APPC67"},null,null,null,null,{"$f":"\"Table \"&VLOOKUP(BW5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(BW5,TABLE_DB,3,FALSE)","v":"Table 8: P2APPC68"},null,null,null,null,{"$f":"\"Table \"&VLOOKUP(CB5,TABLE_DB,2,FALSE)&\": \"&VLOOKUP(CB5,TABLE_DB,3,FALSE)","v":"Table 9: P2APREVAL"}]
["Named range in tool","Table Number in guidance","Table Name","Description","Number of Rows","Number of Columns","Path to Workbook","Workbook Name (inc extension)","WorkSheet","Range","Imported by","Imported on","Result of import",null,null,"Unisex",null,null,"Unisex",null,null,"Male","Female","Unisex",null,null,"Unisex",null,null,"Unisex",null,null,"Male","Female","Unisex",null,null,null,null,null,"Male","Female","Unisex",null,null,"Male","Female","Unisex",null,null,"Male","Female","Unisex",null,null,"Male","Female","Unisex",null,null,"Male","Female","Unisex",null,null,"Male","Female","Unisex",null,null,"Male","Female","Unisex",null,null,"Male","Female","Unisex"]
["INDEX","TABGUIDE","TABNAME","TABDESC","NUMROWS","NUMCOLS","FPATH","FNAME","FSHEET","FRANGE","USER","TIMESTAMP","RESULT",null,"Age","Member + spouse",null,"Age","Member + spouse",null,"Age","Member's pension factor","Member's pension factor","Member + spouse",null,"Age","Member + spouse",null,"Age","Member + spouse",null,"Age","Member's pension factor","Member's pension factor","Member + spouse",null,"Number of 1 Aprils","Factor",null,"Age","Member's pension factor","Member's pension factor","Member + spouse",null,"Age","Member's pension factor","Member's pension factor","Member + spouse",null,"Age","Member's pension factor","Member's pension factor","Member + spouse",null,"Age","Member's pension factor","Member's pension factor","Member + spouse",null,"Age","Member's pension factor","Member's pension factor","Member + spouse",null,"Age","Member's pension factor","Member's pension factor","Member + spouse",null,"Age","Member's pension factor","Member's pension factor","Member + spouse",null,"Age","Member's pension factor","Member's pension factor","Member + spouse",null,"Number of 1 Aprils","Factor"]
["TABLE_01",1,"P1APLSCL1","Added Pension by Lump Sum factors for classic",{"$f":"ROWS(INDIRECT($A9))","v":60},{"$f":"COLUMNS(INDIRECT($A9))","v":2},"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-712","A26:B85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,16,4.0599999999999996,null,16,3.5,null,16,2.5099999999999998,2.5099999999999998,2.69,null,16,4.1500000000000004,null,16,3.59,null,16,2.57,2.57,2.76,null,0,1,null,16,2.5099999999999998,2.5099999999999998,2.69,null,16,2.37,2.37,2.5499999999999998,null,16,2.23,2.23,2.41,null,16,2.11,2.11,2.2799999999999998,null,16,2.57,2.57,2.75,null,16,2.4300000000000002,2.4300000000000002,2.61,null,16,2.2799999999999998,2.2799999999999998,2.4700000000000002,null,16,2.16,2.16,2.33,null,0,1]
["TABLE_02",2,"P1APLSCP1","Added Pension by Lump Sum factors for classic plus and premium",{"$f":"ROWS(INDIRECT($A10))","v":60},{"$f":"COLUMNS(INDIRECT($A10))","v":2},"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-713","A26:B85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,17,4.24,null,17,3.66,null,17,2.62,2.62,2.81,null,17,4.34,null,17,3.75,null,17,2.68,2.68,2.88,null,1,1.02,null,17,2.62,2.62,2.81,null,17,2.4700000000000002,2.4700000000000002,2.66,null,17,2.33,2.33,2.52,null,17,2.19,2.19,2.38,null,17,2.68,2.68,2.88,null,17,2.5299999999999998,2.5299999999999998,2.72,null,17,2.39,2.39,2.58,null,17,2.2400000000000002,2.2400000000000002,2.44,null,1,1.02]
["TABLE_03",3,"P1APLSNU1","Added Pension by Lump Sum factors for nuvos",60,4,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-714","A26:D85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,18,4.42,null,18,3.82,null,18,2.73,2.73,2.93,null,18,4.53,null,18,3.91,null,18,2.79,2.79,3,null,2,1.04,null,18,2.73,2.73,2.93,null,18,2.57,2.57,2.77,null,18,2.42,2.42,2.62,null,18,2.2799999999999998,2.2799999999999998,2.48,null,18,2.79,2.79,3,null,18,2.63,2.63,2.84,null,18,2.48,2.48,2.68,null,18,2.33,2.33,2.54,null,2,1.04]
["TABLE_04",4,"P1APPCCL1","Added Pension by Periodical Contributions factors for classic",60,2,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-709","A26:B85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,19,4.5999999999999996,null,19,3.98,null,19,2.84,2.84,3.06,null,19,4.71,null,19,4.07,null,19,2.91,2.91,3.13,null,3,1.06,null,19,2.84,2.84,3.06,null,19,2.68,2.68,2.89,null,19,2.52,2.52,2.73,null,19,2.38,2.38,2.58,null,19,2.91,2.91,3.13,null,19,2.74,2.74,2.96,null,19,2.58,2.58,2.79,null,19,2.44,2.44,2.64,null,3,1.06]
["TABLE_05",5,"P1APPCCP1","Added Pension by Periodical Contributions factors for classic plus and premium",60,2,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-710","A26:B85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,20,4.79,null,20,4.1500000000000004,null,20,2.96,2.96,3.18,null,20,4.9000000000000004,null,20,4.25,null,20,3.03,3.03,3.26,null,4,1.08,null,20,2.96,2.96,3.18,null,20,2.79,2.79,3.01,null,20,2.63,2.63,2.84,null,20,2.4700000000000002,2.4700000000000002,2.69,null,20,3.03,3.03,3.26,null,20,2.86,2.86,3.08,null,20,2.69,2.69,2.91,null,20,2.5299999999999998,2.5299999999999998,2.75,null,4,1.08]
["TABLE_06",6,"P1APPCNU1","Added Pension by Periodical Contributions factors for nuvos",60,4,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-711","A26:D85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,21,4.99,null,21,4.32,null,21,3.08,3.08,3.31,null,21,5.1100000000000003,null,21,4.42,null,21,3.15,3.15,3.39,null,5,1.1000000000000001,null,21,3.08,3.08,3.31,null,21,2.9,2.9,3.13,null,21,2.73,2.73,2.96,null,21,2.58,2.58,2.8,null,21,3.15,3.15,3.39,null,21,2.97,2.97,3.2,null,21,2.79,2.79,3.03,null,21,2.64,2.64,2.87,null,5,1.1000000000000001]
["TABLE_07",7,"P1APREVAL1","Added Pension Revaluation Factors",51,2,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-716","A26:B76","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,22,5.2,null,22,4.5,null,22,3.21,3.21,3.45,null,22,5.32,null,22,4.6100000000000003,null,22,3.29,3.29,3.53,null,6,1.1299999999999999,null,22,3.21,3.21,3.45,null,22,3.02,3.02,3.26,null,22,2.85,2.85,3.08,null,22,2.68,2.68,2.91,null,22,3.29,3.29,3.53,null,22,3.09,3.09,3.34,null,22,2.92,2.92,3.15,null,22,2.74,2.74,2.98,null,6,1.1299999999999999]
["TABLE_08",1,"P2APLS65","Alpha Added Pension by Lump Sum factors for normal pension age 65",60,4,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-701","A26:D85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,23,5.41,null,23,4.6900000000000004,null,23,3.34,3.34,3.59,null,23,5.54,null,23,4.8,null,23,3.42,3.42,3.68,null,7,1.1499999999999999,null,23,3.34,3.34,3.59,null,23,3.15,3.15,3.4,null,23,2.96,2.96,3.21,null,23,2.79,2.79,3.03,null,23,3.42,3.42,3.68,null,23,3.22,3.22,3.48,null,23,3.03,3.03,3.29,null,23,2.86,2.86,3.1,null,7,1.1499999999999999]
["TABLE_09",2,"P2APLS66","Alpha Added Pension by Lump Sum factors for normal pension age 66",60,4,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-702","A26:D85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,24,5.64,null,24,4.88,null,24,3.48,3.48,3.74,null,24,5.77,null,24,5,null,24,3.56,3.56,3.83,null,8,1.17,null,24,3.48,3.48,3.74,null,24,3.28,3.28,3.54,null,24,3.09,3.09,3.34,null,24,2.91,2.91,3.16,null,24,3.56,3.56,3.83,null,24,3.36,3.36,3.62,null,24,3.16,3.16,3.42,null,24,2.98,2.98,3.24,null,8,1.17]
["TABLE_10",3,"P2APLS67","Alpha Added Pension by Lump Sum factors for normal pension age 67",60,4,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-703","A26:D85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,25,5.87,null,25,5.09,null,25,3.62,3.62,3.9,null,25,6.01,null,25,5.21,null,25,3.71,3.71,3.99,null,9,1.2,null,25,3.62,3.62,3.9,null,25,3.41,3.41,3.68,null,25,3.21,3.21,3.48,null,25,3.03,3.03,3.29,null,25,3.71,3.71,3.99,null,25,3.49,3.49,3.77,null,25,3.29,3.29,3.56,null,25,3.1,3.1,3.37,null,9,1.2]
["TABLE_11",4,"P2APLS68","Alpha Added Pension by Lump Sum factors for normal pension age 68",60,4,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-704","A26:D85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,26,6.12,null,26,5.3,null,26,3.77,3.77,4.0599999999999996,null,26,6.27,null,26,5.43,null,26,3.86,3.86,4.16,null,10,1.22,null,26,3.77,3.77,4.0599999999999996,null,26,3.55,3.55,3.83,null,26,3.35,3.35,3.62,null,26,3.15,3.15,3.42,null,26,3.86,3.86,4.16,null,26,3.63,3.63,3.92,null,26,3.43,3.43,3.71,null,26,3.22,3.22,3.5,null,10,1.22]
["TABLE_12",5,"P2APPC65","Alpha Added Pension by Periodical Contributions factors for normal pension age 65",60,4,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-705","A26:D85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,27,6.37,null,27,5.53,null,27,3.93,3.93,4.2300000000000004,null,27,6.52,null,27,5.66,null,27,4.0199999999999996,4.0199999999999996,4.33,null,11,1.24,null,27,3.93,3.93,4.2300000000000004,null,27,3.7,3.7,3.99,null,27,3.48,3.48,3.77,null,27,3.28,3.28,3.57,null,27,4.0199999999999996,4.0199999999999996,4.33,null,27,3.79,3.79,4.08,null,27,3.56,3.56,3.86,null,27,3.36,3.36,3.65,null,11,1.24]
["TABLE_13",6,"P2APPC66","Alpha Added Pension by Periodical Contributions factors for normal pension age 66",60,4,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-706","A26:D85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,28,6.64,null,28,5.76,null,28,4.09,4.09,4.4000000000000004,null,28,6.8,null,28,5.9,null,28,4.1900000000000004,4.1900000000000004,4.5,null,12,1.27,null,28,4.09,4.09,4.4000000000000004,null,28,3.85,3.85,4.16,null,28,3.63,3.63,3.93,null,28,3.42,3.42,3.71,null,28,4.1900000000000004,4.1900000000000004,4.5,null,28,3.94,3.94,4.26,null,28,3.72,3.72,4.0199999999999996,null,28,3.5,3.5,3.8,null,12,1.27]
["TABLE_14",7,"P2APPC67","Alpha Added Pension by Periodical Contributions factors for normal pension age 67",60,4,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-707","A26:D85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,29,6.91,null,29,6,null,29,4.26,4.26,4.59,null,29,7.07,null,29,6.14,null,29,4.3600000000000003,4.3600000000000003,4.7,null,13,1.29,null,29,4.26,4.26,4.59,null,29,4.01,4.01,4.33,null,29,3.78,3.78,4.09,null,29,3.56,3.56,3.86,null,29,4.3600000000000003,4.3600000000000003,4.7,null,29,4.1100000000000003,4.1100000000000003,4.43,null,29,3.87,3.87,4.1900000000000004,null,29,3.64,3.64,3.95,null,13,1.29]
["TABLE_15",8,"P2APPC68","Alpha Added Pension by Periodical Contributions factors for normal pension age 68",60,4,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-708","A26:D85","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,30,7.2,null,30,6.25,null,30,4.4400000000000004,4.4400000000000004,4.78,null,30,7.37,null,30,6.4,null,30,4.55,4.55,4.8899999999999997,null,14,1.32,null,30,4.4400000000000004,4.4400000000000004,4.78,null,30,4.18,4.18,4.51,null,30,3.93,3.93,4.26,null,30,3.7,3.7,4.0199999999999996,null,30,4.55,4.55,4.8899999999999997,null,30,4.28,4.28,4.62,null,30,4.0199999999999996,4.0199999999999996,4.3600000000000003,null,30,3.79,3.79,4.12,null,14,1.32]
["TABLE_16",9,"P2APREVAL","Alpha Added Pension Revaluation Factors",51,2,"L:\\PSPS\\NICSPS\\guidance notes\\","NICSPS Consolidated Factor Spreadsheet - version 2019-1.xlsm","x-715","A26:B76","Richard.Pethick","2020-02-03T13:24:36","SUCCESS",null,31,7.5,null,31,6.51,null,31,4.62,4.62,4.97,null,31,7.68,null,31,6.66,null,31,4.7300000000000004,4.7300000000000004,5.09,null,15,1.35,null,31,4.62,4.62,4.97,null,31,4.3499999999999996,4.3499999999999996,4.6900000000000004,null,31,4.0999999999999996,4.0999999999999996,4.43,null,31,3.86,3.86,4.1900000000000004,null,31,4.7300000000000004,4.7300000000000004,5.09,null,31,4.45,4.45,4.8,null,31,4.2,4.2,4.54,null,31,3.95,3.95,4.29,null,15,1.35]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,32,7.81,null,32,6.78,null,32,4.8099999999999996,4.8099999999999996,5.17,null,32,8,null,32,6.94,null,32,4.92,4.92,5.29,null,16,1.37,null,32,4.8099999999999996,4.8099999999999996,5.17,null,32,4.53,4.53,4.88,null,32,4.26,4.26,4.6100000000000003,null,32,4.01,4.01,4.3600000000000003,null,32,4.92,4.92,5.29,null,32,4.6399999999999997,4.6399999999999997,5,null,32,4.3600000000000003,4.3600000000000003,4.72,null,32,4.1100000000000003,4.1100000000000003,4.46,null,16,1.37]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,33,8.1300000000000008,null,33,7.06,null,33,5.01,5.01,5.38,null,33,8.32,null,33,7.23,null,33,5.13,5.13,5.51,null,17,1.4,null,33,5.01,5.01,5.38,null,33,4.71,4.71,5.08,null,33,4.43,4.43,4.8,null,33,4.17,4.17,4.53,null,33,5.13,5.13,5.51,null,33,4.82,4.82,5.2,null,33,4.54,4.54,4.91,null,33,4.2699999999999996,4.2699999999999996,4.6399999999999997,null,17,1.4]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,34,8.4700000000000006,null,34,7.36,null,34,5.21,5.21,5.6,null,34,8.67,null,34,7.53,null,34,5.33,5.33,5.73,null,18,1.43,null,34,5.21,5.21,5.6,null,34,4.9000000000000004,4.9000000000000004,5.29,null,34,4.6100000000000003,4.6100000000000003,4.99,null,34,4.34,4.34,4.71,null,34,5.33,5.33,5.73,null,34,5.0199999999999996,5.0199999999999996,5.42,null,34,4.72,4.72,5.1100000000000003,null,34,4.4400000000000004,4.4400000000000004,4.82,null,18,1.43]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,35,8.82,null,35,7.66,null,35,5.42,5.42,5.83,null,35,9.0299999999999994,null,35,7.84,null,35,5.55,5.55,5.97,null,19,1.46,null,35,5.42,5.42,5.83,null,35,5.0999999999999996,5.0999999999999996,5.5,null,35,4.8,4.8,5.19,null,35,4.51,4.51,4.9000000000000004,null,35,5.55,5.55,5.97,null,35,5.22,5.22,5.63,null,35,4.91,4.91,5.31,null,35,4.62,4.62,5.0199999999999996,null,19,1.46]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,36,9.18,null,36,7.97,null,36,5.64,5.64,6.06,null,36,9.4,null,36,8.16,null,36,5.77,5.77,6.2,null,20,1.49,null,36,5.64,5.64,6.06,null,36,5.31,5.31,5.72,null,36,4.99,4.99,5.4,null,36,4.6900000000000004,4.6900000000000004,5.0999999999999996,null,36,5.77,5.77,6.2,null,36,5.44,5.44,5.86,null,36,5.1100000000000003,5.1100000000000003,5.53,null,36,4.8,4.8,5.22,null,20,1.49]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,37,9.56,null,37,8.3000000000000007,null,37,5.87,5.87,6.31,null,37,9.7899999999999991,null,37,8.5,null,37,6.01,6.01,6.46,null,21,1.52,null,37,5.87,5.87,6.31,null,37,5.52,5.52,5.95,null,37,5.19,5.19,5.61,null,37,4.88,4.88,5.3,null,37,6.01,6.01,6.46,null,37,5.65,5.65,6.09,null,37,5.31,5.31,5.74,null,37,5,5,5.43,null,21,1.52]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,38,9.9499999999999993,null,38,8.64,null,38,6.1,6.1,6.56,null,38,10.19,null,38,8.85,null,38,6.25,6.25,6.72,null,22,1.55,null,38,6.1,6.1,6.56,null,38,5.74,5.74,6.19,null,38,5.39,5.39,5.84,null,38,5.07,5.07,5.51,null,38,6.25,6.25,6.72,null,38,5.88,5.88,6.34,null,38,5.52,5.52,5.98,null,38,5.19,5.19,5.64,null,22,1.55]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,39,10.35,null,39,9,null,39,6.35,6.35,6.82,null,39,10.6,null,39,9.2100000000000009,null,39,6.5,6.5,6.98,null,23,1.58,null,39,6.35,6.35,6.82,null,39,5.97,5.97,6.43,null,39,5.61,5.61,6.07,null,39,5.27,5.27,5.72,null,39,6.5,6.5,6.98,null,39,6.11,6.11,6.58,null,39,5.74,5.74,6.21,null,39,5.4,5.4,5.86,null,23,1.58]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,40,10.78,null,40,9.3699999999999992,null,40,6.6,6.6,7.09,null,40,11.04,null,40,9.59,null,40,6.76,6.76,7.26,null,24,1.61,null,40,6.6,6.6,7.09,null,40,6.2,6.2,6.69,null,40,5.83,5.83,6.3,null,40,5.48,5.48,5.95,null,40,6.76,6.76,7.26,null,40,6.35,6.35,6.85,null,40,5.97,5.97,6.45,null,40,5.61,5.61,6.09,null,24,1.61]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,41,11.22,null,41,9.75,null,41,6.87,6.87,7.38,null,41,11.49,null,41,9.98,null,41,7.03,7.03,7.56,null,25,1.64,null,41,6.87,6.87,7.38,null,41,6.45,6.45,6.95,null,41,6.06,6.06,6.55,null,41,5.69,5.69,6.18,null,41,7.03,7.03,7.56,null,41,6.6,6.6,7.12,null,41,6.2,6.2,6.71,null,41,5.83,5.83,6.33,null,25,1.64]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,42,11.68,null,42,10.15,null,42,7.14,7.14,7.67,null,42,11.96,null,42,10.39,null,42,7.31,7.31,7.85,null,26,1.67,null,42,7.14,7.14,7.67,null,42,6.71,6.71,7.23,null,42,6.3,6.3,6.81,null,42,5.92,5.92,6.42,null,42,7.31,7.31,7.85,null,42,6.87,6.87,7.4,null,42,6.45,6.45,6.97,null,42,6.06,6.06,6.57,null,26,1.67]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,43,12.16,null,43,10.56,null,43,7.42,7.42,7.97,null,43,12.45,null,43,10.81,null,43,7.6,7.6,8.16,null,27,1.71,null,43,7.42,7.42,7.97,null,43,6.97,6.97,7.51,null,43,6.54,6.54,7.08,null,43,6.15,6.15,6.67,null,43,7.6,7.6,8.16,null,43,7.14,7.14,7.69,null,43,6.7,6.7,7.25,null,43,6.3,6.3,6.83,null,27,1.71]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,44,12.65,null,44,10.99,null,44,7.72,7.72,8.2899999999999991,null,44,12.95,null,44,11.25,null,44,7.9,7.9,8.49,null,28,1.74,null,44,7.72,7.72,8.2899999999999991,null,44,7.25,7.25,7.81,null,44,6.8,6.8,7.35,null,44,6.39,6.39,6.93,null,44,7.9,7.9,8.49,null,44,7.42,7.42,8,null,44,6.96,6.96,7.52,null,44,6.54,6.54,7.09,null,28,1.74]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,45,13.17,null,45,11.44,null,45,8.0299999999999994,8.0299999999999994,8.6199999999999992,null,45,13.48,null,45,11.71,null,45,8.2200000000000006,8.2200000000000006,8.82,null,29,1.78,null,45,8.0299999999999994,8.0299999999999994,8.6199999999999992,null,45,7.53,7.53,8.11,null,45,7.07,7.07,7.64,null,45,6.63,6.63,7.19,null,45,8.2200000000000006,8.2200000000000006,8.82,null,45,7.71,7.71,8.3000000000000007,null,45,7.24,7.24,7.82,null,45,6.79,6.79,7.36,null,29,1.78]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,46,13.71,null,46,11.91,null,46,8.34,8.34,8.9600000000000009,null,46,14.04,null,46,12.19,null,46,8.5399999999999991,8.5399999999999991,9.17,null,30,1.81,null,46,8.34,8.34,8.9600000000000009,null,46,7.83,7.83,8.43,null,46,7.34,7.34,7.93,null,46,6.89,6.89,7.47,null,46,8.5399999999999991,8.5399999999999991,9.17,null,46,8.02,8.02,8.6300000000000008,null,46,7.51,7.51,8.1199999999999992,null,46,7.05,7.05,7.65,null,30,1.81]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,47,14.27,null,47,12.4,null,47,8.67,8.67,9.31,null,47,14.61,null,47,12.69,null,47,8.8800000000000008,8.8800000000000008,9.5299999999999994,null,31,1.85,null,47,8.67,8.67,9.31,null,47,8.1300000000000008,8.1300000000000008,8.76,null,47,7.63,7.63,8.24,null,47,7.15,7.15,7.76,null,47,8.8800000000000008,8.8800000000000008,9.5299999999999994,null,47,8.32,8.32,8.9700000000000006,null,47,7.81,7.81,8.44,null,47,7.32,7.32,7.94,null,31,1.85]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,48,14.86,null,48,12.9,null,48,9.01,9.01,9.67,null,48,15.21,null,48,13.21,null,48,9.2200000000000006,9.2200000000000006,9.9,null,32,1.88,null,48,9.01,9.01,9.67,null,48,8.4499999999999993,8.4499999999999993,9.1,null,48,7.92,7.92,8.56,null,48,7.43,7.43,8.0500000000000007,null,48,9.2200000000000006,9.2200000000000006,9.9,null,48,8.65,8.65,9.32,null,48,8.11,8.11,8.76,null,48,7.61,7.61,8.24,null,32,1.88]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,49,15.47,null,49,13.43,null,49,9.3699999999999992,9.3699999999999992,10.050000000000001,null,49,15.84,null,49,13.75,null,49,9.59,9.59,10.29,null,33,1.92,null,49,9.3699999999999992,9.3699999999999992,10.050000000000001,null,49,8.7799999999999994,8.7799999999999994,9.4499999999999993,null,49,8.23,8.23,8.89,null,49,7.71,7.71,8.36,null,49,9.59,9.59,10.29,null,49,8.99,8.99,9.67,null,49,8.43,8.43,9.1,null,49,7.89,7.89,8.56,null,33,1.92]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,50,16.100000000000001,null,50,13.98,null,50,9.74,9.74,10.44,null,50,16.48,null,50,14.31,null,50,9.9700000000000006,9.9700000000000006,10.69,null,34,1.96,null,50,9.74,9.74,10.44,null,50,9.1199999999999992,9.1199999999999992,9.81,null,50,8.5399999999999991,8.5399999999999991,9.2200000000000006,null,50,8,8,8.67,null,50,9.9700000000000006,9.9700000000000006,10.69,null,50,9.34,9.34,10.039999999999999,null,50,8.74,8.74,9.44,null,50,8.19,8.19,8.8800000000000008,null,34,1.96]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,51,16.77,null,51,14.55,null,51,10.119999999999999,10.119999999999999,10.85,null,51,17.170000000000002,null,51,14.9,null,51,10.36,10.36,11.11,null,35,2,null,51,10.119999999999999,10.119999999999999,10.85,null,51,9.4700000000000006,9.4700000000000006,10.19,null,51,8.8699999999999992,8.8699999999999992,9.57,null,51,8.31,8.31,9,null,51,10.36,10.36,11.11,null,51,9.6999999999999993,9.6999999999999993,10.43,null,51,9.08,9.08,9.8000000000000007,null,51,8.51,8.51,9.2100000000000009,null,35,2]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,52,17.46,null,52,15.15,null,52,10.52,10.52,11.27,null,52,17.88,null,52,15.51,null,52,10.77,10.77,11.54,null,36,2.04,null,52,10.52,10.52,11.27,null,52,9.84,9.84,10.58,null,52,9.2100000000000009,9.2100000000000009,9.94,null,52,8.6199999999999992,8.6199999999999992,9.33,null,52,10.77,10.77,11.54,null,52,10.07,10.07,10.83,null,52,9.43,9.43,10.18,null,52,8.82,8.82,9.5500000000000007,null,36,2.04]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,53,18.18,null,53,15.78,null,53,10.93,10.93,11.71,null,53,18.61,null,53,16.16,null,53,11.19,11.19,11.99,null,37,2.08,null,53,10.93,10.93,11.71,null,53,10.220000000000001,10.220000000000001,10.99,null,53,9.56,9.56,10.31,null,53,8.94,8.94,9.68,null,53,11.19,11.19,11.99,null,53,10.46,10.46,11.25,null,53,9.7899999999999991,9.7899999999999991,10.56,null,53,9.15,9.15,9.91,null,37,2.08]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,54,18.940000000000001,null,54,16.43,null,54,11.35,11.35,12.16,null,54,19.39,null,54,16.82,null,54,11.62,11.62,12.45,null,38,2.12,null,54,11.35,11.35,12.16,null,54,10.61,10.61,11.41,null,54,9.92,9.92,10.7,null,54,9.2799999999999994,9.2799999999999994,10.039999999999999,null,54,11.62,11.62,12.45,null,54,10.86,10.86,11.68,null,54,10.16,10.16,10.95,null,54,9.5,9.5,10.28,null,38,2.12]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,55,19.73,null,55,17.11,null,55,11.8,11.8,12.63,null,55,20.2,null,55,17.52,null,55,12.08,12.08,12.93,null,39,2.16,null,55,11.8,11.8,12.63,null,55,11.03,11.03,11.84,null,55,10.3,10.3,11.1,null,55,9.6199999999999992,9.6199999999999992,10.41,null,55,12.08,12.08,12.93,null,55,11.29,11.29,12.12,null,55,10.54,10.54,11.36,null,55,9.85,9.85,10.66,null,39,2.16]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,56,20.56,null,56,17.829999999999998,null,56,12.27,12.27,13.13,null,56,21.05,null,56,18.25,null,56,12.56,12.56,13.44,null,40,2.21,null,56,12.27,12.27,13.13,null,56,11.45,11.45,12.3,null,56,10.69,10.69,11.52,null,56,9.98,9.98,10.8,null,56,12.56,12.56,13.44,null,56,11.72,11.72,12.59,null,56,10.94,10.94,11.79,null,56,10.220000000000001,10.220000000000001,11.06,null,40,2.21]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,57,21.44,null,57,18.579999999999998,null,57,12.75,12.75,13.64,null,57,21.95,null,57,19.02,null,57,13.05,13.05,13.96,null,41,2.25,null,57,12.75,12.75,13.64,null,57,11.9,11.9,12.77,null,57,11.1,11.1,11.96,null,57,10.36,10.36,11.19,null,57,13.05,13.05,13.96,null,57,12.18,12.18,13.07,null,57,11.36,11.36,12.24,null,57,10.61,10.61,11.46,null,41,2.25]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,58,22.36,null,58,19.38,null,58,13.26,13.26,14.17,null,58,22.89,null,58,19.84,null,58,13.58,13.58,14.51,null,42,2.2999999999999998,null,58,13.26,13.26,14.17,null,58,12.37,12.37,13.26,null,58,11.53,11.53,12.41,null,58,10.75,10.75,11.61,null,58,13.58,13.58,14.51,null,58,12.66,12.66,13.58,null,58,11.8,11.8,12.71,null,58,11.01,11.01,11.89,null,42,2.2999999999999998]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,59,23.33,null,59,20.22,null,59,13.8,13.8,14.74,null,59,23.88,null,59,20.7,null,59,14.13,14.13,15.09,null,43,2.34,null,59,13.8,13.8,14.74,null,59,12.85,12.85,13.77,null,59,11.97,11.97,12.88,null,59,11.15,11.15,12.04,null,59,14.13,14.13,15.09,null,59,13.16,13.16,14.1,null,59,12.25,12.25,13.19,null,59,11.42,11.42,12.33,null,43,2.34]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,60,23.59,null,60,20.399999999999999,null,60,14.36,14.36,15.32,null,60,24.15,null,60,20.88,null,60,14.7,14.7,15.68,null,44,2.39,null,60,14.36,14.36,15.32,null,60,13.37,13.37,14.31,null,60,12.44,12.44,13.37,null,60,11.57,11.57,12.49,null,60,14.7,14.7,15.68,null,60,13.69,13.69,14.65,null,60,12.74,12.74,13.69,null,60,11.85,11.85,12.79,null,44,2.39]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,61,23.09,null,61,19.899999999999999,null,61,14.95,14.95,15.95,null,61,23.64,null,61,20.37,null,61,15.31,15.31,16.329999999999998,null,45,2.44,null,61,14.95,14.95,15.95,null,61,13.91,13.91,14.88,null,61,12.93,12.93,13.89,null,61,12.02,12.02,12.96,null,61,15.31,15.31,16.329999999999998,null,61,14.24,14.24,15.23,null,61,13.24,13.24,14.22,null,61,12.31,12.31,13.27,null,45,2.44]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,62,22.58,null,62,19.39,null,62,15.58,15.58,16.61,null,62,23.12,null,62,19.850000000000001,null,62,15.95,15.95,17,null,46,2.4900000000000002,null,62,15.58,15.58,16.61,null,62,14.48,14.48,15.48,null,62,13.45,13.45,14.43,null,62,12.49,12.49,13.46,null,62,15.95,15.95,17,null,62,14.82,14.82,15.85,null,62,13.77,13.77,14.77,null,62,12.79,12.79,13.78,null,46,2.4900000000000002]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,63,22.06,null,63,18.87,null,63,16.260000000000002,16.260000000000002,17.309999999999999,null,63,22.58,null,63,19.32,null,63,16.649999999999999,16.649999999999999,17.72,null,47,2.54,null,63,16.260000000000002,16.260000000000002,17.309999999999999,null,63,15.09,15.09,16.12,null,63,14.01,14.01,15.01,null,63,12.99,12.99,13.98,null,63,16.649999999999999,16.649999999999999,17.72,null,63,15.45,15.45,16.5,null,63,14.34,14.34,15.37,null,63,13.3,13.3,14.31,null,47,2.54]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,64,21.53,null,64,18.34,null,64,16.98,16.98,18.05,null,64,22.04,null,64,18.78,null,64,17.38,17.38,18.48,null,48,2.59,null,64,16.98,16.98,18.05,null,64,15.75,15.75,16.8,null,64,14.6,14.6,15.63,null,64,13.53,13.53,14.54,null,64,17.38,17.38,18.48,null,64,16.12,16.12,17.2,null,64,14.95,14.95,16,null,64,13.85,13.85,14.89,null,48,2.59]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,65,21,null,65,17.809999999999999,null,65,17.079999999999998,17.079999999999998,18.18,null,65,21.5,null,65,18.23,null,65,17.489999999999998,17.489999999999998,18.61,null,49,2.64,null,65,17.079999999999998,17.079999999999998,18.18,null,65,16.45,16.45,17.53,null,65,15.23,15.23,16.29,null,65,14.1,14.1,15.14,null,65,17.489999999999998,17.489999999999998,18.61,null,65,16.84,16.84,17.95,null,65,15.59,15.59,16.68,null,65,14.44,14.44,15.5,null,49,2.64]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,66,20.46,null,66,17.27,null,66,16.54,16.54,17.64,null,66,20.95,null,66,17.68,null,66,16.93,16.93,18.059999999999999,null,50,2.69,null,66,16.54,16.54,17.64,null,66,16.54,16.54,17.64,null,66,15.91,15.91,17,null,66,14.71,14.71,15.77,null,66,16.93,16.93,18.059999999999999,null,66,16.93,16.93,18.059999999999999,null,66,16.29,16.29,17.399999999999999,null,66,15.06,15.06,16.14,null,50,2.69]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,67,19.920000000000002,null,67,16.72,null,67,15.99,15.99,17.09,null,67,20.39,null,67,17.12,null,67,16.37,16.37,17.5,null,null,null,null,67,15.99,15.99,17.09,null,67,15.99,15.99,17.09,null,67,15.99,15.99,17.09,null,67,15.37,15.37,16.46,null,67,16.37,16.37,17.5,null,67,16.37,16.37,17.5,null,67,16.37,16.37,17.5,null,67,15.74,15.74,16.850000000000001]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,68,19.37,null,68,16.170000000000002,null,68,15.44,15.44,16.54,null,68,19.829999999999998,null,68,16.55,null,68,15.81,15.81,16.93,null,null,null,null,68,15.44,15.44,16.54,null,68,15.44,15.44,16.54,null,68,15.44,15.44,16.54,null,68,15.44,15.44,16.54,null,68,15.81,15.81,16.93,null,68,15.81,15.81,16.93,null,68,15.81,15.81,16.93,null,68,15.81,15.81,16.93]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,69,18.809999999999999,null,69,15.62,null,69,14.89,14.89,15.98,null,69,19.260000000000002,null,69,15.99,null,69,15.24,15.24,16.36,null,null,null,null,69,14.89,14.89,15.98,null,69,14.89,14.89,15.98,null,69,14.89,14.89,15.98,null,69,14.89,14.89,15.98,null,69,15.24,15.24,16.36,null,69,15.24,15.24,16.36,null,69,15.24,15.24,16.36,null,69,15.24,15.24,16.36]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,70,18.25,null,70,15.06,null,70,14.33,14.33,15.42,null,70,18.68,null,70,15.42,null,70,14.67,14.67,15.79,null,null,null,null,70,14.33,14.33,15.42,null,70,14.33,14.33,15.42,null,70,14.33,14.33,15.42,null,70,14.33,14.33,15.42,null,70,14.67,14.67,15.79,null,70,14.67,14.67,15.79,null,70,14.67,14.67,15.79,null,70,14.67,14.67,15.79]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,71,17.690000000000001,null,71,14.5,null,71,13.77,13.77,14.86,null,71,18.11,null,71,14.84,null,71,14.1,14.1,15.21,null,null,null,null,71,13.77,13.77,14.86,null,71,13.77,13.77,14.86,null,71,13.77,13.77,14.86,null,71,13.77,13.77,14.86,null,71,14.1,14.1,15.21,null,71,14.1,14.1,15.21,null,71,14.1,14.1,15.21,null,71,14.1,14.1,15.21]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,72,17.13,null,72,13.94,null,72,13.21,13.21,14.3,null,72,17.54,null,72,14.27,null,72,13.52,13.52,14.64,null,null,null,null,72,13.21,13.21,14.3,null,72,13.21,13.21,14.3,null,72,13.21,13.21,14.3,null,72,13.21,13.21,14.3,null,72,13.52,13.52,14.64,null,72,13.52,13.52,14.64,null,72,13.52,13.52,14.64,null,72,13.52,13.52,14.64]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,73,16.57,null,73,13.38,null,73,12.66,12.66,13.73,null,73,16.96,null,73,13.7,null,73,12.96,12.96,14.06,null,null,null,null,73,12.66,12.66,13.73,null,73,12.66,12.66,13.73,null,73,12.66,12.66,13.73,null,73,12.66,12.66,13.73,null,73,12.96,12.96,14.06,null,73,12.96,12.96,14.06,null,73,12.96,12.96,14.06,null,73,12.96,12.96,14.06]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,74,16.010000000000002,null,74,12.82,null,74,12.11,12.11,13.17,null,74,16.39,null,74,13.12,null,74,12.4,12.4,13.48,null,null,null,null,74,12.11,12.11,13.17,null,74,12.11,12.11,13.17,null,74,12.11,12.11,13.17,null,74,12.11,12.11,13.17,null,74,12.4,12.4,13.48,null,74,12.4,12.4,13.48,null,74,12.4,12.4,13.48,null,74,12.4,12.4,13.48]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,75,15.73,null,75,12.54,null,75,11.83,11.83,12.89,null,75,16.100000000000001,null,75,12.84,null,75,12.11,12.11,13.2,null,null,null,null,75,11.83,11.83,12.89,null,75,11.83,11.83,12.89,null,75,11.83,11.83,12.89,null,75,11.83,11.83,12.89,null,75,12.11,12.11,13.2,null,75,12.11,12.11,13.2,null,75,12.11,12.11,13.2,null,75,12.11,12.11,13.2]

{"sheet":"Scheme Info","index":8,"n_rows":40,"n_cols":15,"rows_kept":40,"cut":false,"hidden":true,"merged":[[4,9,4,10]]}
["Government Actuary's Department"]
[{"$f":"IF(title=\"> Enter workbook title here\",\"Enter workbook title in Cover sheet\",title)","v":"NICSPS Added Pension Calculator"}]
["> Enter worksheet title here"]
[{"$f":"CELL(\"filename\",A1)","v":"L:\\PSPS\\NICSPS\\_tools\\development area\\added pension\\[NICSPS Added Pension Calculator v0.07.1.xlsm]Scheme Info"},null,null,null,null,null,null,null,null,null,null,null,null,"CPI",0.02]
[null,null,"NPA",null,null,null,null,null,null,"AP Limits",null,null,null,"Estimate"]
[null,"Scheme","Yrs","Mths","NPA","NPA_DATE","Pension","Automatic Lump sum","NumTypes","2019/20","2020/21","2021/22","2022/23","2023/24"]
[null,"Classic",60,0,{"$f":"C7&\" years \"&D7&\" months\"","v":"60 years 0 months"},{"$f":"DATE(YEAR(inDoB)+C7,MONTH(inDoB),DAY(inDoB))","v":"1959-12-31"},"Y","Y",1,5700,5800,5900,6100,{"$f":"MROUND(M7*(1+$O$4), 100)","v":6200}]
[null,"Classic Plus",60,0,{"$f":"C8&\" years \"&D8&\" months\"","v":"60 years 0 months"},{"$f":"DATE(YEAR(inDoB)+C8,MONTH(inDoB),DAY(inDoB))","v":"1959-12-31"},"Y","N",1,7000,7200,7300,7600,{"$f":"MROUND(M8*(1+$O$4), 100)","v":7800}]
[null,"Premium",60,0,"60 years 0 months",{"$f":"DATE(YEAR(inDoB)+C9,MONTH(inDoB),DAY(inDoB))","v":"1959-12-31"},"Y","N",1,7000,7200,7300,7600,{"$f":"MROUND(M9*(1+$O$4), 100)","v":7800}]
[null,"Nuvos",65,0,"65 years 0 months",{"$f":"DATE(YEAR(inDoB)+C10,MONTH(inDoB),DAY(inDoB))","v":"1964-12-31"},"Y","N",2,7000,7200,7300,7600,{"$f":"MROUND(M10*(1+$O$4), 100)","v":7800}]
[null,"Alpha",{"$f":"SPA_YRS","v":""},{"$f":"SPA_MTH","v":""}," years  months",{"$f":"SPA_DATE","v":""},"Y","N",2,7000,7200,7300,7600,{"$f":"MROUND(M11*(1+$O$4), 100)","v":7800}]
[]
[null,"Gender",null,null,null,null,null,null,2019,"https://www.finance-ni.gov.uk/sites/default/files/publications/dfp/Employee-Pension-Notice-Buying-Added-Pension-reminder-for-2018-2019-and-information-about-purchases-for-2019-2020.pdf "]
[null,"Male",null,null,null,null,null,null,2020,"https://www.civilservicepensionscheme.org.uk/members/buying-added-pensions-and-epa/limitations-and-allowances/ "]
[null,"Female",null,null,null,null,null,null,2021,"https://www.civilservicepensionscheme.org.uk/employers/employer-pension-notices/epn616-reminder-buying-added-pension-before-1-april-2021-the-deadline-for-purchasing-added-pension-and-epa-during-202122/"]
[null,null,null,null,null,null,null,null,2022,"https://www.civilservicepensionscheme.org.uk/employers/employer-pension-notices/epn-643-reminder-buying-added-pension-before-1-april-2022-the-deadline-for-purchasing-added-pension-and-epa-during-202223/"]
[null,"TypeList"]
[null,"Self and dependant"]
[null,{"$f":"IF(OR(inScheme=B11, inScheme=B10), \"Self\", \"\")","v":""}]
[]
[null,"HowList"]
[null,"Pension"]
[null,"Payment"]
[]
[null,"MethodList"]
[null,"Lump Sum"]
[null,"Monthly Contributions"]
[]
[null,"JoinedList"]
[null,"Less than 3 months"]
[null,"Between 3 months and 12 months"]
[null,"Greater than 12 months"]
[]
[null,"SchemeYears"]
[null,"2018/19"]
[null,"2019/20"]
[null,"2020/21"]
[null,"2021/22"]
[null,"2022/23"]
[null,"2023/24"]

{"sheet":"State Pension Age","index":9,"n_rows":90,"n_cols":9,"rows_kept":90,"cut":false,"hidden":true,"merged":[]}
["Government Actuary's Department"]
[{"$f":"IF(title=\"> Enter workbook title here\",\"Enter workbook title in Cover sheet\",title)","v":"NICSPS Added Pension Calculator"}]
["State Pension Age calculation"]
[{"$f":"CELL(\"filename\",A1)","v":"L:\\PSPS\\NICSPS\\_tools\\development area\\added pension\\[NICSPS Added Pension Calculator v0.07.1.xlsm]State Pension Age"},null,null,null,null,null,"Years","Complete months","Retirement Date"]
[null,null,null,null,"DOB",{"$f":"inDoB","v":"00:00:00"},{"$f":"IFERROR(IF(AND(SPA_DOB<SPA_F60,SPA_SEX=\"F\"),60,IF(AND(SPA_DOB<SPA_M65,SPA_SEX=\"M\"),65,VLOOKUP(SPA_DOB,SPA_TABLE,4,TRUE))),\"\")","v":""},{"$f":"IFERROR(IF(AND(SPA_DOB<SPA_F60,SPA_SEX=\"F\"),0,IF(AND(SPA_DOB<SPA_M65,SPA_SEX=\"M\"),0,VLOOKUP(SPA_DOB,SPA_TABLE,5,TRUE))),\"\")","v":""},{"$f":"IFERROR(IF(AND(SPA_DOB<SPA_F60,SPA_SEX=\"F\"),DATE(YEAR(SPA_DOB)+60,MONTH(SPA_DOB),DAY(SPA_DOB)),IF(AND(SPA_DOB<SPA_M65,SPA_SEX=\"M\"),DATE(YEAR(SPA_DOB)+65,MONTH(SPA_DOB),DAY(SPA_DOB)),VLOOKUP(SPA_DOB,SPA_TABLE,3,TRUE))),\"\")","v":""}]
["Source link to SPA timetable",null,null,null,"Gender",{"$f":"LEFT(inGender,1)","v":""}]
["https://www.gov.uk/government/publications/state-pension-age-timetable/state-pension-age-timetable",null,null,null,"F60 before","1950-04-06"]
["Female only ",null,null,null,"M65 before","1953-12-06"]
[]
["Date of birth","Date State Pension age reached",null,"Start","End","Date State Pension age reached","Years","Months"]
["6 April 1950 – 5 May 1950","2010-05-06",null,{"$f":"VALUE(TRIM(LEFT(A11,FIND(\"–\",A11)-1)))","v":"1950-04-06"},{"$f":"VALUE(TRIM(MID(A11,FIND(\"–\",A11)+1,LEN(A11)-FIND(\"–\",A11))))","v":"1950-05-05"},{"$f":"B11","v":"2010-05-06"},{"$f":"DATEDIF(SPA_DOB,F11,\"y\")","v":110},{"$f":"DATEDIF(SPA_DOB,F11,\"ym\")","v":4}]
["6 May 1950 – 5 June 1950","2010-07-06",null,{"$f":"VALUE(TRIM(LEFT(A12,FIND(\"–\",A12)-1)))","v":"1950-05-06"},{"$f":"VALUE(TRIM(MID(A12,FIND(\"–\",A12)+1,LEN(A12)-FIND(\"–\",A12))))","v":"1950-06-05"},{"$f":"B12","v":"2010-07-06"},110,6]
["6 June 1950 – 5 July 1950","2010-09-06",null,"1950-06-06","1950-07-05","2010-09-06",110,8]
["6 July 1950 – 5 August 1950","2010-11-06",null,"1950-07-06","1950-08-05","2010-11-06",110,10]
["6 August 1950 – 5 September 1950","2011-01-06",null,"1950-08-06","1950-09-05","2011-01-06",111,0]
["6 September 1950 – 5 October 1950","2011-03-06",null,"1950-09-06","1950-10-05","2011-03-06",111,2]
["6 October 1950 – 5 November 1950","2011-05-06",null,"1950-10-06","1950-11-05","2011-05-06",111,4]
["6 November 1950 – 5 December 1950","2011-07-06",null,"1950-11-06","1950-12-05","2011-07-06",111,6]
["6 December 1950 – 5 January 1951","2011-09-06",null,"1950-12-06","1951-01-05","2011-09-06",111,8]
["6 January 1951 – 5 February 1951","2011-11-06",null,"1951-01-06","1951-02-05","2011-11-06",111,10]
["6 February 1951 – 5 March 1951","2012-01-06",null,"1951-02-06","1951-03-05","2012-01-06",112,0]
["6 March 1951 – 5 April 1951","2012-03-06",null,"1951-03-06","1951-04-05","2012-03-06",112,2]
["6 April 1951 – 5 May 1951","2012-05-06",null,"1951-04-06","1951-05-05","2012-05-06",112,4]
["6 May 1951 – 5 June 1951","2012-07-06",null,"1951-05-06","1951-06-05","2012-07-06",112,6]
["6 June 1951 – 5 July 1951","2012-09-06",null,"1951-06-06","1951-07-05","2012-09-06",112,8]
["6 July 1951 – 5 August 1951","2012-11-06",null,"1951-07-06","1951-08-05","2012-11-06",112,10]
["6 August 1951 – 5 September 1951","2013-01-06",null,"1951-08-06","1951-09-05","2013-01-06",113,0]
["6 September 1951 – 5 October 1951","2013-03-06",null,"1951-09-06","1951-10-05","2013-03-06",113,2]
["6 October 1951 – 5 November 1951","2013-05-06",null,"1951-10-06","1951-11-05","2013-05-06",113,4]
["6 November 1951 – 5 December 1951","2013-07-06",null,"1951-11-06","1951-12-05","2013-07-06",113,6]
["6 December 1951 – 5 January 1952","2013-09-06",null,"1951-12-06","1952-01-05","2013-09-06",113,8]
["6 January 1952 – 5 February 1952","2013-11-06",null,"1952-01-06","1952-02-05","2013-11-06",113,10]
["6 February 1952 – 5 March 1952","2014-01-06",null,"1952-02-06","1952-03-05","2014-01-06",114,0]
["6 March 1952 – 5 April 1952","2014-03-06",null,"1952-03-06","1952-04-05","2014-03-06",114,2]
["6 April 1952 – 5 May 1952","2014-05-06",null,"1952-04-06","1952-05-05","2014-05-06",114,4]
["6 May 1952 – 5 June 1952","2014-07-06",null,"1952-05-06","1952-06-05","2014-07-06",114,6]
["6 June 1952 – 5 July 1952","2014-09-06",null,"1952-06-06","1952-07-05","2014-09-06",114,8]
["6 July 1952 – 5 August 1952","2014-11-06",null,"1952-07-06","1952-08-05","2014-11-06",114,10]
["6 August 1952 – 5 September 1952","2015-01-06",null,"1952-08-06","1952-09-05","2015-01-06",115,0]
["6 September 1952 – 5 October 1952","2015-03-06",null,"1952-09-06","1952-10-05","2015-03-06",115,2]
["6 October 1952 – 5 November 1952","2015-05-06",null,"1952-10-06","1952-11-05","2015-05-06",115,4]
["6 November 1952 – 5 December 1952","2015-07-06",null,"1952-11-06","1952-12-05","2015-07-06",115,6]
["6 December 1952 – 5 January 1953","2015-09-06",null,"1952-12-06","1953-01-05","2015-09-06",{"$f":"DATEDIF(SPA_DOB,F43,\"y\")","v":115},{"$f":"DATEDIF(SPA_DOB,F43,\"ym\")","v":8}]
["6 January 1953 – 5 February 1953","2015-11-06",null,"1953-01-06","1953-02-05","2015-11-06",115,10]
["6 February 1953 – 5 March 1953","2016-01-06",null,"1953-02-06","1953-03-05","2016-01-06",116,0]
["6 March 1953 – 5 April 1953","2016-03-06",null,"1953-03-06","1953-04-05","2016-03-06",116,2]
["6 April 1953 – 5 May 1953","2016-07-06",null,"1953-04-06","1953-05-05","2016-07-06",116,6]
["6 May 1953 – 5 June 1953","2016-11-06",null,"1953-05-06","1953-06-05","2016-11-06",116,10]
["6 June 1953 – 5 July 1953","2017-03-06",null,"1953-06-06","1953-07-05","2017-03-06",117,2]
["6 July 1953 – 5 August 1953","2017-07-06",null,"1953-07-06","1953-08-05","2017-07-06",117,6]
["6 August 1953 – 5 September 1953","2017-11-06",null,"1953-08-06","1953-09-05","2017-11-06",117,10]
["6 September 1953 – 5 October 1953","2018-03-06",null,"1953-09-06","1953-10-05","2018-03-06",118,2]
["6 October 1953 – 5 November 1953","2018-07-06",null,"1953-10-06","1953-11-05","2018-07-06",118,6]
["6 November 1953 – 5 December 1953","2018-11-06",null,"1953-11-06","1953-12-05","2018-11-06",118,10]
["6 December 1953 – 5 January 1954","2019-03-06",null,"1953-12-06","1954-01-05","2019-03-06",119,2]
["6 January 1954 – 5 February 1954","2019-05-06",null,"1954-01-06","1954-02-05","2019-05-06",119,4]
["6 February 1954 – 5 March 1954","2019-07-06",null,"1954-02-06","1954-03-05","2019-07-06",119,6]
["6 March 1954 – 5 April 1954","2019-09-06",null,"1954-03-06","1954-04-05","2019-09-06",119,8]
["6 April 1954 – 5 May 1954","2019-11-06",null,"1954-04-06","1954-05-05","2019-11-06",119,10]
["6 May 1954 – 5 June 1954","2020-01-06",null,"1954-05-06","1954-06-05","2020-01-06",120,0]
["6 June 1954 – 5 July 1954","2020-03-06",null,"1954-06-06","1954-07-05","2020-03-06",120,2]
["6 July 1954 – 5 August 1954","2020-05-06",null,"1954-07-06","1954-08-05","2020-05-06",120,4]
["6 August 1954 – 5 September 1954","2020-07-06",null,"1954-08-06","1954-09-05","2020-07-06",120,6]
["6 September 1954 – 5 October 1954","2020-09-06",null,"1954-09-06","1954-10-05","2020-09-06",120,8]
["6 October 1954 – 5 April 1960","66th birthday",null,"1954-10-06","1960-04-05",{"$f":"DATE(YEAR(SPA_DOB)+G65,MONTH(SPA_DOB)+H65,DAY(SPA_DOB))","v":"1965-12-31"},66,0,"inconsistent formula needed"]
["6 April 1960 – 5 May 1960","66 years and 1 month",null,"1960-04-06","1960-05-05","1966-01-31",66,1,"inconsistent formula needed"]
["6 May 1960 – 5 June 1960","66 years and 2 months",null,"1960-05-06","1960-06-05","1966-02-28",66,2,"inconsistent formula needed"]
["6 June 1960 – 5 July 1960","66 years and 3 months",null,"1960-06-06","1960-07-05","1966-03-31",66,3,"inconsistent formula needed"]
["6 July 1960 – 5 August 1960","66 years and 4 months (1)",null,"1960-07-06","1960-08-05","1966-04-30",66,4,"inconsistent formula needed"]
["6 August 1960 – 5 September 1960","66 years and 5 months",null,"1960-08-06","1960-09-05","1966-05-31",66,5,"inconsistent formula needed"]
["6 September 1960 – 5 October 1960","66 years and 6 months",null,"1960-09-06","1960-10-05","1966-06-30",66,6,"inconsistent formula needed"]
["6 October 1960 – 5 November 1960","66 years and 7 months",null,"1960-10-06","1960-11-05","1966-07-31",66,7,"inconsistent formula needed"]
["6 November 1960 – 5 December 1960","66 years and 8 months",null,"1960-11-06","1960-12-05","1966-08-31",66,8,"inconsistent formula needed"]
["6 December 1960 – 5 January 1961","66 years and 9 months (2)",null,"1960-12-06","1961-01-05","1966-09-30",66,9,"inconsistent formula needed"]
["6 January 1961 – 5 February 1961","66 years and 10 months (3)",null,"1961-01-06","1961-02-05","1966-10-31",66,10,"inconsistent formula needed"]
["6 February 1961 – 5 March 1961","66 years and 11 months",null,{"$f":"VALUE(TRIM(LEFT(A76,FIND(\"–\",A76)-1)))","v":"1961-02-06"},{"$f":"VALUE(TRIM(MID(A76,FIND(\"–\",A76)+1,LEN(A76)-FIND(\"–\",A76))))","v":"1961-03-05"},"1966-11-30",66,11,"inconsistent formula needed"]
["6 March 1961 – 5 April 1977","67th birthday",null,"1961-03-06","1977-04-05","1966-12-31",67,0,"inconsistent formula needed"]
["6 April 1977 – 5 May 1977","2044-05-06",null,"1977-04-06","1977-05-05",{"$f":"B78","v":"2044-05-06"},{"$f":"DATEDIF(SPA_DOB,F78,\"y\")","v":144},{"$f":"DATEDIF(SPA_DOB,F78,\"ym\")","v":4}]
["6 May 1977 – 5 June 1977","2044-07-06",null,"1977-05-06","1977-06-05","2044-07-06",144,6]
["6 June 1977 – 5 July 1977","2044-09-06",null,"1977-06-06","1977-07-05","2044-09-06",144,8]
["6 July 1977 – 5 August 1977","2044-11-06",null,"1977-07-06","1977-08-05","2044-11-06",144,10]
["6 August 1977 – 5 September 1977","2045-01-06",null,"1977-08-06","1977-09-05","2045-01-06",145,0]
["6 September 1977 – 5 October 1977","2045-03-06",null,"1977-09-06","1977-10-05","2045-03-06",145,2]
["6 October 1977 – 5 November 1977","2045-05-06",null,"1977-10-06","1977-11-05","2045-05-06",145,4]
["6 November 1977 – 5 December 1977","2045-07-06",null,"1977-11-06","1977-12-05","2045-07-06",145,6]
["6 December 1977 – 5 January 1978","2045-09-06",null,"1977-12-06","1978-01-05","2045-09-06",145,8]
["6 January 1978 – 5 February 1978","2045-11-06",null,"1978-01-06","1978-02-05","2045-11-06",145,10]
["6 February 1978 – 5 March 1978","2046-01-06",null,"1978-02-06","1978-03-05","2046-01-06",146,0]
["6 March 1978 – 5 April 1978","2046-03-06",null,"1978-03-06","1978-04-05","2046-03-06",146,2]
["6 April 1978 onwards","68th birthday",null,{"$f":"E89+1","v":"1978-04-06"},null,{"$f":"DATE(YEAR(SPA_DOB)+G90,MONTH(SPA_DOB)+H90,DAY(SPA_DOB))","v":"1967-12-31"},68,0]