{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Inputs","Pension cont, income tax, NI","Results"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":23,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Inputs","index":0,"n_rows":20,"n_cols":8,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["NHS Pension Scheme 2015 (NHSPS 2015)",null,null,null,null,null,null,{"$f":"'Pension cont, income tax, NI'!C10","v":5.0999999999999997E-2}]
["Opt Out Calculator",null,null,null,{"$f":"'Pension cont, income tax, NI'!C11","v":5.7000000000000002E-2}]
["For the tax year 2023 to 2024",null,null,null,{"$f":"'Pension cont, income tax, NI'!C12","v":6.0999999999999999E-2}]
["(From 6 April 2023 after the reduction in income tax additional rate threshold)",null,null,null,{"$f":"'Pension cont, income tax, NI'!C16","v":9.8000000000000004E-2}]
[null,null,null,null,{"$f":"'Pension cont, income tax, NI'!C18","v":0.11600000000000001}]
["Annual salary",null,null,null,{"$f":"'Pension cont, income tax, NI'!C19","v":0.125}]
["Actual pensionable earnings",null,null,null,{"$f":"'Pension cont, income tax, NI'!C20","v":0.13500000000000001}]
[]
["Pension contribution rate",null,null,null,"(If this is left blank, the calculator will look up the contribution rate based on the salary details provided above.)"]
[]
["Tax code",null,null,null,"(If this is left blank, the calculator will assume a standard tax code (such as 1257L).)"]
[]
[]
["Notes:"]
["-","The calculator only allows for tax codes L, K, BR, D0, D1, NT and 0T."]
["-","The calculator assumes that the tax code put in will allow for the income-related reduction to the Personal Allowance for earnings over £100,000."]
["-","The calculator does not allow for people born before 6 April 1948 who are eligible for a Personal Allowance above the basic level."]
["-","The calculator considers the cost of scheme contributions compared to opting out allowing for income tax and NI contributions."]
["-","The calculator does not consider any Annual Allowance or Lifetime Allowance tax charges that may be incurred as a result of scheme membership."]
[null,"Calculator updated February 2023"]

{"sheet":"Pension cont, income tax, NI","index":1,"n_rows":68,"n_cols":7,"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":{"$error":"#REF!"}}]
["Interim calculation to derive the pension contributions, income tax and NI contribution"]
[{"$f":"CELL(\"filename\",A1)","v":"C:\\Users\\eleca\\AppData\\Local\\Microsoft\\Windows\\INetCache\\Content.Outlook\\MP3XZV8N\\[Opt-Out Calculator 2023-24 (003).xlsm]Pension cont, income tax, NI"}]
[]
["Contribution rates and bands for year 2023/24"]
["Copied from ","NHS Pension Scheme – changes to member contributions from 1 October 2022 | NHS Employers"]
["Lower limit","Upper limit","Cont rate"]
[]
[0,13246,5.0999999999999997E-2]
[{"$f":"B10+1","v":13247},16831,5.7000000000000002E-2]
[{"$f":"B11+1","v":16832},22878,6.0999999999999999E-2]
[{"$f":"B12+1","v":22879},23948,6.8000000000000005E-2]
[{"$f":"B13+1","v":23949},{"$f":"28223","v":28223},7.6999999999999999E-2]
[28224,29179,8.7999999999999995E-2]
[29180,43805,9.8000000000000004E-2]
[43806,49245,0.1]
[49246,56163,0.11600000000000001]
[56164,72030,0.125]
[72031,999999.99,0.13500000000000001]
["Member contribution rate",null,null,{"$f":"IF(Inputs!$D$9=\"\",VLOOKUP(ActPay2016,'Pension cont, income tax, NI'!$A$10:$C$20,3,TRUE),Inputs!$D$9)","v":5.0999999999999997E-2}]
["Contribution payable",null,null,{"$f":"IF(Inputs!$D$9=\"\",VLOOKUP(ActPay2016,'Pension cont, income tax, NI'!$A$10:$C$20,3,TRUE),Inputs!$D$9)*ActPay2016","v":0},{"$f":"D22/12","v":0}]
[]
["Actual salary",null,null,{"$f":"ActPay2016","v":0}]
["Actual salary net of contribution",null,null,{"$f":"ActPay2016-D22","v":0}]
[]
["Employer contribution rate",null,0.20599999999999999,"Copied from ","https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/783411/nhsps-consultation-response.pdf"]
["Employer contribution",null,{"$f":"C27*ActPay2016","v":0}]
[]
[]
["Income tax rates and taxable bands for year 2023/24"]
["Copied from:",null,"Annex A: rates and allowances - GOV.UK (www.gov.uk)"]
["Additional rate change:",null,"AUTUMN STATEMENT 2022 (publishing.service.gov.uk)"]
["Tax code",null,{"$f":"IF(ISNUMBER(SEARCH(\"BR\",Inputs!D11))=TRUE,Basic,IF(ISNUMBER(SEARCH(\"D0\",Inputs!D11))=TRUE,Higher,IF(ISNUMBER(SEARCH(\"D1\",Inputs!D11))=TRUE,Additional,IF(ISNUMBER(SEARCH(\"NT\",Inputs!D11))=TRUE,0,\"Standard\"))))","v":"Standard"}]
["Personal allowance",null,{"$f":"IF(ISNUMBER(SEARCH(\"L\",Inputs!D11))=TRUE,LEFT(Inputs!D11,SEARCH(\"L\",Inputs!D11)-1)*10,IF(ISNUMBER(SEARCH(\"k\",Inputs!D11))=TRUE,RIGHT(Inputs!D11,LEN(Inputs!D11)-SEARCH(\"k\",Inputs!D11))*-10,IF(ISNUMBER(SEARCH(\"T\",Inputs!D11))=TRUE,LEFT(Inputs!D11,SEARCH(\"T\",Inputs!D11)-1)*10,perallow)))","v":12570},null,12570,"Personal Allowance"]
[]
["Taxable income"]
["- before opting out",null,{"$f":"D25-C35","v":-12570}]
["- after opting out",null,{"$f":"D24-C35","v":-12570}]
[]
[]
["Rate","Lower limit","Upper limit","Taxable income falls within the band",null,"Tax amount"]
[null,null,null,"Before opting out","After opting out","Before opting out","After opting out"]
[0.2,0,37700,{"$f":"MAX(MIN($C44-$C43,C$38-$C43),0)","v":0},{"$f":"MAX(MIN($C44-$C43,C$39-$C43),0)","v":0},{"$f":"$A44*D44","v":0},{"$f":"$A44*E44","v":0}]
[0.4,{"$f":"C44+1","v":37701},125140,{"$f":"MAX(MIN($C45-$C44,C$38-$C44),0)","v":0},{"$f":"MAX(MIN($C45-$C44,C$39-$C44),0)","v":0},{"$f":"$A45*D45","v":0},0]
[0.45,{"$f":"C45+1","v":125141},9999999,{"$f":"MAX(MIN($C46-$C45,C$38-$C45),0)","v":0},{"$f":"MAX(MIN($C46-$C45,C$39-$C45),0)","v":0},0,0]
[]
["Standard",null,null,null,null,{"$f":"SUM(F43:F46)","v":0},{"$f":"SUM(G43:G46)","v":0}]
[{"$f":"Basic","v":0.2},null,null," ",null,{"$f":"Basic*$D$25","v":0},{"$f":"Basic*$D$24","v":0}]
[{"$f":"Higher","v":0.4},null,null,null,null,{"$f":"Higher*$D$25","v":0},{"$f":"Higher*$D$24","v":0}]
[{"$f":"Additional","v":0.45},null,null,null,null,{"$f":"Additional*$D$25","v":0},{"$f":"Additional*$D$24","v":0}]
[0,null,null,null,null,{"$f":"A52*$D$25","v":0},{"$f":"A52*$D$24","v":0}]
[]
[]
["National Insurance contributions - rates and allowance for 2023/24"]
["Copied from:",null,"Rates and allowances: National Insurance contributions - GOV.UK (www.gov.uk)"]
[]
[null,null,"£ per week","£ per year","£ per month"]
["Lower Earnings Limit",null,123,{"$f":"C59*52","v":6396}]
["Primary threshold (PT)",null,241.73,{"$f":"C60*52","v":12569.96}]
["Upper earnings limit (UEL)",null,966.73,{"$f":"C61*52","v":50269.96}]
["Rate between PT and UEL",null,0.12]
["Rate above UEL",null,0.02]
[]
["Earnings between PT and UEL",null,null,{"$f":"IF(ActPay2016<=PT,0,IF(ActPay2016>UEL,UEL-PT,ActPay2016-PT))","v":0},{"$f":"D65/12","v":0},{"$f":"E65*NI_Cont","v":0}]
["Earnings above UEL",null,null,{"$f":"MAX(0,ActPay2016-D61)","v":0},{"$f":"D66/12","v":0},{"$f":"E66*NI_cont_UEL","v":0}]
[]
["National Insurance contribution",null,null,null,{"$f":"$E$65*$C$62+$E$66*$C$63","v":0}]

{"sheet":"Results","index":2,"n_rows":42,"n_cols":6,"rows_kept":42,"cut":false,"hidden":false,"merged":[[31,0,32,5],[21,1,21,5]]}
["NHS Pension Scheme 2015 (NHSPS 2015)"]
["Opt Out Calculator"]
["For the tax year 2023 to 2024"]
[]
[]
[null,"Monthly Payments","£","Deductions","As a member of the NHS Pension Scheme or the 2015 NHS Pension Scheme","After opting out"]
[null,"Monthly salary",{"$f":"Inputs!D7/12","v":0},"Tax",{"$f":"VLOOKUP('Pension cont, income tax, NI'!C34,'Pension cont, income tax, NI'!$A$48:$G$52,6,FALSE)/12","v":0},{"$f":"VLOOKUP('Pension cont, income tax, NI'!C34,'Pension cont, income tax, NI'!$A$48:$G$52,7,FALSE)/12","v":0}]
[null,null,null,"N.I. Contribution",{"$f":"'Pension cont, income tax, NI'!E68","v":0},{"$f":"'Pension cont, income tax, NI'!E68","v":0}]
[null,null,null,"Pension contribution",{"$f":"'Pension cont, income tax, NI'!D22/12","v":0},0]
[]
[null,"Total payments",{"$f":"SUM(C7:C10)","v":0},"Total deductions",{"$f":"SUM(E7:E10)","v":0},{"$f":"SUM(F7:F10)","v":0}]
[null,null,null,"Net pay",{"$f":"$C11-E11","v":0},{"$f":"$C11-F11","v":0}]
[]
[]
["Difference in net pay per month",null,null,null,null,{"$f":"F12-E12","v":0}]
[]
[]
[null,null,null,"Net annual pay ",{"$f":"E12*12","v":0},{"$f":"F12*12","v":0}]
[]
[]
["Benefits you will be giving up by opting out of the NHS Pensions Scheme or NHS Pension Scheme 2015"]
["-",{"$f":"'Pension cont, income tax, NI'!C28","v":0}]
["-","An annual pension payable for life after retirement"]
["-","Tax free cash lump sum on retirement for those who have 1995 section membership of NHS Pension Scheme"]
[null," (optional for all other members of the NHS Pension Scheme and the NHS Pension Scheme 2015)"]
["-","Death in service life assurance lump sum"]
["-","Pension benefits payable to dependents including children"]
["-","Ill-health retirement benefits"]
["-","Tax relief on pension contributions"]
[]
[]
["More information about the benefits that the Scheme provides can be found in the NHS Pension Scheme Guide available on the website at: www.nhsbsa.nhs.uk/pensions or speak to your employer."]
[]
[]
["Notes:"]
["-","The calculator above is for illustrative purposes only."]
["-","The calculator above is based on the following inputs:"]
[null,"- Actual pensionable earnings",null,null,{"$f":"ActPay2016","v":0}]
[null,"- Pension contribution rate",null,null,{"$f":"IF(ISBLANK(Inputs!D9),\"Not completed - calculation uses \"&TEXT('Pension cont, income tax, NI'!D21,\"0.0%\"),Inputs!D9)","v":"Not completed - calculation uses 5.1%"}]
[null,"- Tax code",null,null,{"$f":"IF(ISBLANK(Inputs!D11),\"Not completed\",Inputs!D11)","v":"Not completed"}]
[]
[null,{"$f":"Inputs!B20","v":"Calculator updated February 2023"}]