{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Pre_April 2024 Ben_Calcs","Calculator","Sheet2"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":7,"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":"Pre_April 2024 Ben_Calcs","index":0,"n_rows":57,"n_cols":5,"rows_kept":57,"cut":false,"hidden":true,"merged":[]}
[null,null,"Lump Sum Allowance and Lump Sum Death Benefit Allowance "]
[null,null,"Reductions for pre-6th April 2024 Benefit Crystallisation Events"]
[]
[null,"This calculator is designed to help you assess the impact of benefit crystallisation events (BCEs) that occurred"]
[null,"prior to 6th April 2024, on a client's lump sum allowance and lump sum death benefit allowance."]
[]
[null,"It compares the reduction to the allowances using the 'default' or 'standard' calculation with the reduction "]
[null,"using a Transitional Tax-Free Amount Certificate.  Please note that certificates must be obtained prior to the first"]
[null,"relevant crystallisation event after 5th April 2024.  Once in force, a certificate cannot be revoked."]
[]
[null,null,null,null,"Input Amounts Below"]
[null,"Select Protection Type (if any)",null,null,"None"]
[null,"Please Input Individual Protection Amount",null,null,1073100]
[]
[null,"Please provide total lifetime allowance % used as at 5th April 2024 (max 100%)*"]
[]
[null,"Total tax-free payments received prior to 6th April 2024**"]
[]
[null,"Has the client received a serious ill-health lump sum prior to 6th April 2024?",null,null,"No"]
[null,"Enter amount of serious-ill health lump sum",null,null,0]
[]
[]
[null,"Starting lump sum allowance",null,null,{"$f":"IF(E12=\"Individual Protection (any)\",E25*0.25,VLOOKUP(E12,Sheet2!$A$6:$C$15,3,FALSE)*0.25)","v":268275}]
[]
[null,"Starting lump sum and death benefit allowance",null,null,{"$f":"IF(E12=Sheet2!A10,'Pre_April 2024 Ben_Calcs'!E13,VLOOKUP(E12,Sheet2!$A$6:$C$15,3,FALSE))","v":1073100}]
[]
[null,"Default adjustments to LSA",null,null,{"$f":"MAX(0,E15*0.25*E25)","v":0}]
[]
[null,"Default adjustments to LSDBA",null,null,{"$f":"MAX(0,E15*E25*IF(E19=\"No\",0.25,1))","v":0}]
[]
[]
[]
[]
[null,"Default Basis","Allowance","Used","Remaining"]
[null,"Lump sum allowance",{"$f":"VLOOKUP($E$12,Sheet2!$A$6:$C$10,3,FALSE)*0.25","v":268275},{"$f":"C35-E35","v":0},{"$f":"IF(E15<1,E23-E27,0)","v":268275}]
[null,"Lump sum and death benefit allowance",{"$f":"VLOOKUP($E$12,Sheet2!$A$6:$C$10,3,FALSE)","v":1073100},{"$f":"C36-E36","v":0},{"$f":"IF(E15<1,E25-E29,0)","v":1073100}]
[]
[null,"Transitional Tax-Free Amount Certificate","Allowance","Used","Remaining"]
[null,"Lump sum allowance",{"$f":"IF(E15=0,\"N/A\",VLOOKUP($E$12,Sheet2!$A$6:$C$10,3,FALSE)*0.25)","v":"N/A"},{"$f":"IF(E15=0,\"N/A\",C39-E39)","v":"N/A"},{"$f":"IF(E15=0,\"N/A\",MAX(0,E23-E17))","v":"N/A"}]
[null,"Lump sum and death benefit allowance",{"$f":"IF(E15=0,\"N/A\",VLOOKUP($E$12,Sheet2!$A$6:$C$10,3,FALSE))","v":"N/A"},{"$f":"IF(E15=0,\"N/A\",C40-E40)","v":"N/A"},{"$f":"IF(E15=0,\"N/A\",MAX(0,E25-E17-IF(E19=\"Yes\",E20,0)))","v":"N/A"}]
[]
[null,"* For clients aged 75 or over, this should include all BCEs arising at age 75 (BCE5A and BCE5B)."]
[null,"** Include all pension commencement lump sums and 25% of any uncrystallised funds pension lump sums."]
[]
[null,"Do not use for clients with enhanced or primary protection, or if pension benefits were taken prior to 6th April 2006."]
[]
[null,"Please note: - this calculator is provided by Integrated Financial Arrangements Limited for use only by professional "]
[null,"advisers registered to use the Transact platform.  It is based on our current understanding of the Finance Act 2024"]
[null," and draft HMRC guidance which may change at any time.  Changes may also apply retrospectively.   Any changes "]
[null,"could impact the figures provided."]
[]
[null,"The calculator and figures provided are for guidance and information only.  They do not, and are not intended to,"]
[null," constitute professional advice, a recommendation or a financial promotion."]
[]
[null,"Integrated Financial Arrangements Ltd. 29 Clement's Lane, London EC4N 7AE. Tel: 020 7608 4900 Fax: 020 7608 5300. "]
[null,"(Registered office: as above; Registered in England and Wales under number: 3727592). Authorised and regulated by"]
[null," the Financial Conduct Authority (entered on the Financial Services Register; no. 190856)."]

{"sheet":"Calculator","index":1,"n_rows":71,"n_cols":15,"rows_kept":71,"cut":false,"hidden":false,"merged":[[0,4,0,11]]}
[null,null,null,null,"Lump Sum Allowance and Lump Sum Death Benefit Allowance Calculator\n\nThis calculator is designed to help you calculate the lump sum allowance, the lifetime allowance and \nlump sum and death benefit allowance available, based on the lifetime allowance used prior to \nthe 6 April 2024.\n\n"]
[null,null,null,null,null,"Calculation of Lump Sum Allowance and Lump Sum Death Benefit Allowances"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"LSA","LSDBA"]
[null,null,null,null,null,"Lifetime Allowance Protection",null,null,null,null,"None",null,null,{"$f":"IF(Protection_Type=\"Individual Protection (any)\",$K$7,VLOOKUP(Protection_Type,Sheet2!A6:C10,3,FALSE))*0.25","v":268275},{"$f":"IF(Protection_Type=\"Individual Protection (any)\",$K$7,VLOOKUP(Protection_Type,Sheet2!A6:C10,3,FALSE))","v":1073100}]
[]
[null,null,null,null,null,"Please provide Individual Protection Amount (£)"]
[]
[null,null,null,null,null,"Has client taken benefits prior to 6 April 2024? *",null,null,null,null,"Yes"]
[]
[null,null,null,null,null,"Benefits Taken Prior to 6 April 2024"]
[]
[null,null,null,null,null,"Was a serious ill-health lump sum paid",null,null,null,null,"No"]
[]
[null,null,null,null,null,"Provide total LTA % used @ 5 April 2024",null,null,null,null,null,null,"Used %",{"$f":"(Start_LSDBA*0.25*K15)","v":0},{"$f":"(Start_LSDBA*IF(K13<>\"Yes\",0.25*K15,K15))","v":0}]
[]
[null,null,null,null,null,null,"OR"]
[]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF(N19=1,\"\",N19)","v":""},null,null,{"$f":"IF(AND(K15>0,OR(TLSA>0,TLSDBA>0)),\"Please Complete Either LTA% or LSA and LSDBA\",1)","v":1}]
[]
[null,null,null,null,null,"Provide reductions to allowances @ 5 April 2024**"]
[null,null,null,null,null,"Lump Sum Allowance (£)",null,null,null,null,null,null,"Used Amount",{"$f":"TLSA","v":0},{"$f":"TLSDBA","v":0}]
[null,null,null,null,null,"Lump Sum and Death Benefit Allowance (£)",null,null,null,null,null,null,"Selected Amount to Use",{"$f":"IF(TTFAC_Applies=\"No\",0,N15*N19+N22)","v":0},{"$f":"IF(TTFAC_Applies=\"No\",0,O15*N19+O22)","v":0}]
[]
[null,null,null,null,null,"Have benefits been taken since 6 April 2024?",null,null,null,null,"No"]
[null,null,null,null,null,"Input amounts used 6th April 2024 for:"]
[null,null,null,null,null,"Total Lump Sum Allowance (£)",null,null,null,null,0,null,"To Use 2",{"$f":"IF(K25=\"No\",0,K27)","v":0},{"$f":"IF(K25=\"No\",0,K28)","v":0}]
[null,null,null,null,null,"Total Lump Sum and Death Benefit Allowance Used (£)",null,null,null,null,0]
[]
[]
[null,null,null,null,null,"Calculations"]
[]
[null,null,null,null,null,"Lump Sum Allowance @ 5th April 2024",null,null,null,null,{"$f":"Start_LSA","v":268275}]
[null,null,null,null,null,"Lump Sum Death Benefit Allowance @ 5th April 2024",null,null,null,null,{"$f":"Start_LSDBA","v":1073100}]
[]
[null,null,null,null,null,"Reduction for Pre 6th April 2024 benefits"]
[]
[null,null,null,null,null,null,null,null,"LSA",null,{"$f":"MAX(N15,N40)","v":0}]
[null,null,null,null,null,null,null,null,"LSDBA",null,{"$f":"MAX(O15,O40)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Used",{"$f":"IF(TTFAC_Applies=\"Yes\",TLSA,0)","v":0},{"$f":"IF(TTFAC_Applies=\"Yes\",TLSDBA,0)","v":0}]
[null,null,null,null,null,"Reduction for Benefits Since 6th April 2024"]
[null,null,null,null,null,null,null,null,null,null,null,null,"Remaining after Pre 6th April 2024",{"$f":"Start_LSA-N15-N40","v":268275},{"$f":"Start_LSDBA-O15-O40","v":1073100}]
[null,null,null,null,null,null,null,null,"LSA",null,{"$f":"N45","v":0}]
[null,null,null,null,null,null,null,null,"LSDBA",null,{"$f":"O45","v":0}]
[]
[null,null,null,null,null,"Available Allowances"]
[null,null,null,null,null,"Total LSA Remaining",null,null,null,null,{"$f":"MAX(0,N48)","v":268275}]
[null,null,null,null,null,"Total LSDBA Remaining",null,null,null,null,{"$f":"MAX(0,O48)","v":1073100},null,"Remaining after RBCEs",{"$f":"Start_LSA-N23-N27","v":268275},{"$f":"IF(K15>=100%,0,Start_LSDBA-O23-O27)","v":1073100}]
[]
[null,null,null,null,null,"Do not use if:"]
[null,null,null,null,null,"Enhanced or primary protection applies"]
[null,null,null,null,null,"Lifetime allowance enhancement factors apply"]
[null,null,null,null,null,"If pension benefits were taken prior to 6 April 2006"]
[]
[]
[null,null,null,null,"* For clients aged 75 prior to 6 April 2024, only include BCE 5, 5A and 5B (age 75 lifetime allowance tests) if the client received a tax-free lump sum ",null,null,null,null,null,null,null,null,null,5]
[null,null,null,null,"   between age 75 and 5 April 2024."]
[]
[null,null,null,null,"** You must also use these fields to input amounts where a Transitional Tax-Free Amount Certificate applies"]
[]
[null,null,null,null,"Please note: - this calculator is provided by Integrated Financial Arrangements Limited for use only by professional "]
[null,null,null,null,"advisers registered to use the Transact platform.  It is based on our current understanding of the Finance Act 2024"]
[null,null,null,null," and draft HMRC guidance which may change at any time.  Changes may also apply retrospectively.   Any changes "]
[null,null,null,null,"could impact the figures provided."]
[]
[null,null,null,null,"The calculator and figures provided are for guidance and information only.  They do not, and are not intended to,"]
[null,null,null,null," constitute professional advice, a recommendation or a financial promotion."]
[]
[null,null,null,null,"Integrated Financial Arrangements Ltd. 4th Floor, 2 Gresham Street, London, EC2V 7AD. Tel: (020) 7608 4900 "]
[null,null,null,null,"(Registered office: as above; Registered in England and Wales under number: 03727592). Authorised and regulated by"]
[null,null,null,null," the Financial Conduct Authority (entered on the Financial Services Register; no. 190856)."]

{"sheet":"Sheet2","index":2,"n_rows":19,"n_cols":3,"rows_kept":19,"cut":false,"hidden":true,"merged":[]}
[]
[]
[]
["Protection Types"]
[]
["None",null,1073100]
["Fixed Protection 2012",null,1800000]
["Fixed Protection 2014",null,1500000]
["Fixed Protection 2016",null,1250000]
["Individual Protection (any)",null,{"$f":"'Pre_April 2024 Ben_Calcs'!E13","v":1073100}]
[]
[]
[]
[]
[]
[]
["Yes"]
["No"]
["Individual Protection 2016"]