{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Inputs","Pension cont, income tax, NI","Results"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":25,"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":22,"n_cols":8,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[]
["NHS Pension Scheme (2015 Scheme)",null,null,null,null,null,null,{"$f":"'Pension cont, income tax, NI'!C10","v":5.1999999999999998E-2}]
["Opt-out calculator",null,null,null,{"$f":"'Pension cont, income tax, NI'!C11","v":6.5000000000000002E-2}]
["For the tax year 2024 to 2025",null,null,null,{"$f":"'Pension cont, income tax, NI'!C12","v":8.3000000000000004E-2}]
["(From 6 April 2024 for the changes to National Insurance rate and contribution structure)",null,null,null,{"$f":"'Pension cont, income tax, NI'!C16","v":0}]
[null,null,null,null,{"$f":"'Pension cont, income tax, NI'!C18","v":0}]
["Annual salary",null,null,null,{"$f":"'Pension cont, income tax, NI'!C19","v":0}]
["Actual pensionable earnings",null,null,30000,{"$f":"'Pension cont, income tax, NI'!C20","v":0}]
[]
["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))."]
[]
["Date of Birth ",null,null,"1968-04-01"]
[]
["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 March 2024"]

{"sheet":"Pension cont, income tax, NI","index":1,"n_rows":140,"n_cols":7,"rows_kept":140,"cut":false,"hidden":true,"merged":[[78,1,79,1]]}
["Government Actuary's Department"]
["Pension cont, income tax, NI"]
["Interim calculation to derive the pension contributions, income tax and NI contribution"]
[{"$f":"CELL(\"filename\",A1)","v":"C:\\Users\\NIRAT\\AppData\\Local\\Microsoft\\Windows\\INetCache\\Content.Outlook\\L5BFJUC5\\[Copy of Opt-Out Calculator 2023-24 - March 2024 output update v4 - EC.xlsm]Pension cont, income tax, NI"}]
[]
["Contribution rates and bands for year 2024/25"]
["Copied from ","https://www.nhsbsa.nhs.uk/member-hub/cost-being-scheme"]
["Lower limit","Upper limit","Cont rate"]
[]
[0,13259,5.1999999999999998E-2]
[{"$f":"B10+1","v":13260},26831,6.5000000000000002E-2]
[{"$f":"B11+1","v":26832},32691,8.3000000000000004E-2]
[{"$f":"B12+1","v":32692},49078,9.8000000000000004E-2]
[{"$f":"B13+1","v":49079},62924,0.107]
[62925,999999,0.125]
[]
[]
[]
[]
[]
["Member contribution rate",null,null,{"$f":"IF(Inputs!$D$10=\"\",VLOOKUP(ActPay2016,'Pension cont, income tax, NI'!$A$10:$C$15,3,TRUE),Inputs!$D$10)","v":8.3000000000000004E-2}]
["Contribution payable",null,null,{"$f":"IF(Inputs!$D$10=\"\",VLOOKUP(ActPay2016,'Pension cont, income tax, NI'!$A$10:$C$15,3,TRUE),Inputs!$D$10)*ActPay2016","v":2490},{"$f":"D22/12","v":207.5}]
[]
["Actual salary",null,null,{"$f":"ActPay2016","v":30000}]
["Actual salary net of contribution",null,null,{"$f":"ActPay2016-D22","v":27510}]
[]
["Employer contribution rate",null,0.23699999999999999,"Copied from ","https://www.gov.uk/government/consultations/nhs-pension-scheme-proposed-policy-changes-for-april-2024/outcome/government-response-to-the-nhs-pension-scheme-member-contributions-phase-2-and-miscellaneous-amendments-consultation#reforming-member-contributions-phase-2"]
["Employer contribution",null,{"$f":"C27*ActPay2016","v":7110}]
[]
[]
["Income tax rates and taxable bands for year 2024/25"]
["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!D12))=TRUE,Basic,IF(ISNUMBER(SEARCH(\"D0\",Inputs!D12))=TRUE,Higher,IF(ISNUMBER(SEARCH(\"D1\",Inputs!D12))=TRUE,Additional,IF(ISNUMBER(SEARCH(\"NT\",Inputs!D12))=TRUE,0,\"Standard\"))))","v":"Standard"}]
["Personal allowance",null,{"$f":"IF(ISNUMBER(SEARCH(\"L\",Inputs!D12))=TRUE,LEFT(Inputs!D12,SEARCH(\"L\",Inputs!D12)-1)*10,IF(ISNUMBER(SEARCH(\"k\",Inputs!D12))=TRUE,RIGHT(Inputs!D12,LEN(Inputs!D12)-SEARCH(\"k\",Inputs!D12))*-10,IF(ISNUMBER(SEARCH(\"T\",Inputs!D12))=TRUE,LEFT(Inputs!D12,SEARCH(\"T\",Inputs!D12)-1)*10,perallow)))","v":12570},null,12570,"Personal Allowance"]
[]
["Taxable income"]
["- before opting out",null,{"$f":"D25-C35","v":14940}]
["- after opting out",null,{"$f":"D24-C35","v":17430}]
[]
[]
["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":14940},{"$f":"MAX(MIN($C44-$C43,C$39-$C43),0)","v":17430},{"$f":"$A44*D44","v":2988},{"$f":"$A44*E44","v":3486}]
[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":2988},{"$f":"SUM(G43:G46)","v":3486}]
[{"$f":"Basic","v":0.2},null,null," ",null,{"$f":"Basic*$D$25","v":5502},{"$f":"Basic*$D$24","v":6000}]
[{"$f":"Higher","v":0.4},null,null,null,null,{"$f":"Higher*$D$25","v":11004},{"$f":"Higher*$D$24","v":12000}]
[{"$f":"Additional","v":0.45},null,null,null,null,{"$f":"Additional*$D$25","v":12379.5},{"$f":"Additional*$D$24","v":13500}]
[0,null,null,null,null,{"$f":"A52*$D$25","v":0},{"$f":"A52*$D$24","v":0}]
[]
[]
["National Insurance contributions - rates and allowance for 2024/25 - from 6 April 2024"]
["Copied from:",null,"Rates and thresholds for employers 2024 to 2025 - 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.08]
["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":17430.04},{"$f":"D65/12","v":1452.5033333333333},{"$f":"E65*NI_Cont","v":116.20026666666666}]
["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":116.20026666666666}]
[]
["Summary information"]
["Member age last birthday",null,{"$f":"IF(AND(DAY(DoB) = DAY(TODAY()),MONTH(DoB) = MONTH(TODAY())),YEAR(TODAY()) - YEAR(DoB),ROUNDDOWN(YEARFRAC(DoB,TODAY()),0))","v":56}]
["Annuity cost",null,{"$f":"INDEX(D81:D140,MATCH(C71,B81:B140))","v":18.100000000000001}]
["Annual pension",null,{"$f":"1/54*ActPay2016","v":555.55555555555554},{"$f":"MROUND(C73,10)","v":560}]
["Hypothetical cost",null,{"$f":"Annuity_Cost*C73","v":10055.555555555557},{"$f":"MROUND(C74,100)","v":10100}]
["Increase in take home",null,{"$f":"Results!F35-Results!E35","v":1992},{"$f":"ROUND(C75,0)","v":1992}]
[]
[null,"Hypothetical Annuity Cost factors at 31 March 2024"]
[]
[null,"Age last birthday","NHSPS","NHSPS"]
[null,null,"(1995 section)","(2008 section) and NHSPS 2015"]
[null,16,22.3,16.5]
[null,17,22.4,16.600000000000001]
[null,18,22.5,16.7]
[null,19,22.6,16.7]
[null,20,22.7,16.7]
[null,21,22.7,16.8]
[null,22,22.8,16.8]
[null,23,22.8,16.899999999999999]
[null,24,22.9,16.899999999999999]
[null,25,23,16.899999999999999]
[null,26,23,17]
[null,27,23.1,17]
[null,28,23.1,17]
[null,29,23.2,17.100000000000001]
[null,30,23.3,17.100000000000001]
[null,31,23.3,17.100000000000001]
[null,32,23.4,17.2]
[null,33,23.4,17.2]
[null,34,23.5,17.2]
[null,35,23.5,17.3]
[null,36,23.6,17.3]
[null,37,23.7,17.3]
[null,38,23.7,17.399999999999999]
[null,39,23.8,17.399999999999999]
[null,40,23.9,17.399999999999999]
[null,41,23.9,17.5]
[null,42,24,17.5]
[null,43,24,17.600000000000001]
[null,44,24.1,17.600000000000001]
[null,45,24.2,17.600000000000001]
[null,46,24.2,17.7]
[null,47,24.3,17.7]
[null,48,24.4,17.8]
[null,49,24.4,17.8]
[null,50,24.5,17.8]
[null,51,24.6,17.899999999999999]
[null,52,24.7,17.899999999999999]
[null,53,24.7,18]
[null,54,24.8,18]
[null,55,24.9,18.100000000000001]
[null,56,25,18.100000000000001]
[null,57,25.1,18.2]
[null,58,25.1,18.2]
[null,59,25.2,18.3]
[null,60,25.3,18.399999999999999]
[null,61,24.8,18.399999999999999]
[null,62,24.3,18.5]
[null,63,23.8,18.600000000000001]
[null,64,23.2,18.600000000000001]
[null,65,22.7,18.7]
[null,66,22.1,18.2]
[null,67,21.6,17.7]
[null,68,21,17.2]
[null,69,20.5,16.7]
[null,70,19.899999999999999,16.2]
[null,71,19.3,15.7]
[null,72,18.8,15.2]
[null,73,18.2,14.7]
[null,74,17.600000000000001,14.2]
[null,75,17.100000000000001,13.7]

{"sheet":"Results","index":2,"n_rows":48,"n_cols":6,"rows_kept":48,"cut":false,"hidden":false,"merged":[[44,1,45,5],[6,1,7,5],[18,0,19,5],[39,1,39,5],[8,1,9,5]]}
["NHS Pension Scheme (2015 Scheme)"]
["Opt-out calculator"]
["For the tax year 2024 to 2025"]
["(From 6 April 2024 for the changes to National Insurance rate and contribution structure)"]
[]
["Benefits you will be giving up by opting out of the 2015 NHS Pension Scheme"]
["-",{"$f":"\"Based on your current pensionable earnings, if you opt out of the Scheme, you would give up an annual pension accrual of around £\"&TEXT('Pension cont, income tax, NI'!D73,\"#,###\")&\" per year for life after retirement.\"","v":"Based on your current pensionable earnings, if you opt out of the Scheme, you would give up an annual pension accrual of around £560 per year for life after retirement."}]
[]
["-",{"$f":"\"To help illustrate the value of the NHS pension you would be giving up, a hypothetical comparison to buy similar benefits on the “open market” might cost around £\"&TEXT('Pension cont, income tax, NI'!D74,\"#,###\")&\" for each year you give up accruing benefits in the NHS Pension Scheme.\"","v":"To help illustrate the value of the NHS pension you would be giving up, a hypothetical comparison to buy similar benefits on the “open market” might cost around £10,100 for each year you give up accruing benefits in the NHS Pension Scheme."}]
[]
["-","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 2015 NHS Pension Scheme)"]
["-","Death in service life assurance lump sum to nominated people or an organisation"]
["-","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: https://www.nhsbsa.nhs.uk/nhs-pensions or speak to your employer."]
[]
[]
["Impact on take home pay"]
[null,"Monthly Payments","£","Deductions","As a member of the 2015 NHS Pension Scheme","After opting out"]
[null,"Monthly salary",{"$f":"Inputs!D8/12","v":2500},"Tax",{"$f":"VLOOKUP('Pension cont, income tax, NI'!C34,'Pension cont, income tax, NI'!$A$48:$G$52,6,FALSE)/12","v":249},{"$f":"VLOOKUP('Pension cont, income tax, NI'!C34,'Pension cont, income tax, NI'!$A$48:$G$52,7,FALSE)/12","v":290.5}]
[null,null,null,"N.I. Contribution",{"$f":"'Pension cont, income tax, NI'!E68","v":116.20026666666666},{"$f":"'Pension cont, income tax, NI'!E68","v":116.20026666666666}]
[null,null,null,"Pension contribution",{"$f":"'Pension cont, income tax, NI'!D22/12","v":207.5},0]
[]
[null,"Total payments",{"$f":"SUM(C24:C27)","v":2500},"Total deductions",{"$f":"SUM(E24:E27)","v":572.70026666666672},{"$f":"SUM(F24:F27)","v":406.70026666666666}]
[null,null,null,"Net pay",{"$f":"$C28-E28","v":1927.2997333333333},{"$f":"$C28-F28","v":2093.2997333333333}]
[]
[]
["Difference in net pay per month",null,null,null,null,{"$f":"F29-E29","v":166}]
[]
[]
[null,null,null,"Net annual pay ",{"$f":"E29*12","v":23127.596799999999},{"$f":"F29*12","v":25119.596799999999}]
[]
[]
["Notes:"]
["-","The calculator above is for illustrative purposes only."]
["-","The Net annnual pay is calculated as 12 times the net monthly pay.  It is therefore based on the lower National Insurance rate effective from 6 April 2024."]
["-","The calculator above is based on the following inputs:"]
[null,"- Actual pensionable earnings",null,null,{"$f":"ActPay2016","v":30000}]
[null,"- Pension contribution rate",null,null,{"$f":"IF(ISBLANK(Inputs!D10),\"Not completed - calculation uses \"&TEXT('Pension cont, income tax, NI'!D21,\"0.0%\"),Inputs!D10)","v":"Not completed - calculation uses 8.3%"}]
[null,"- Tax code",null,null,{"$f":"IF(ISBLANK(Inputs!D12),\"Not completed\",Inputs!D12)","v":"Not completed"}]
["-","The illustrative value of the benefits is for general comparison purposes only and it is not possible to take this value from the pension scheme.  The figures are based on market conditions at 29 December 2023 and costs could vary significantly at different dates. "]
[]
[]
[null,{"$f":"Inputs!B22","v":"Calculator updated March 2024"}]