{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Tool","Printer Friendly Output"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":2,"comments":2,"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":"Tool","index":0,"n_rows":19,"n_cols":17,"rows_kept":19,"cut":false,"hidden":false,"merged":[[0,1,0,12],[10,10,10,11],[14,10,18,12],[2,1,2,12],[4,10,4,11],[7,1,8,1],[7,6,8,6]]}
[null,"LTA Transitional Tax Free Amount Tool\nbuilt by M&G",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"2025-04-05"]
[]
[null,"Important information\nThis tool is designed to calculate the available Lump Sum Allowance and Lump Sum and Death Benefit Allowance as at 6th April 2024 for those who have crystallised benefits against the Lifetime Allowance between 6th April 2006 and 5th April 2024.\nIt compares the allowances available under the standard default transitional rules and if a Transitional Tax Free Amount Certificate was attained. \nThe tool does not deal with those who have Primary or Enhanced Protection or who are deceased. \nThe amount of tax free cash payable will be subject to individual client's circumstances and HMRC rules all of which may change. \nResponsibility for the use of any information derived from this tool rests solely with the user.\n",null,null,null,null,null,null,null,null,null,null,null,null,null,"Kill",{"$f":"AND(Q1<TODAY())","v":false}]
[]
[null,"A","LTA (must be £1,073,100 to £1,800,000)",null,1073100,null,null,null,null,null,"Permitted Maximum as at 6th April 2024"]
[]
[null,null,null,null,"LTA used ",null,"Actual/Deemed tax free amounts paid",null,null,null,"Lump Sum Allowance Available",null,"Value to support LSA"]
[null,"B","Deemed BCE on pre 2006 pension income",null,0,null,{"$f":"E8*E9/4","v":0},null,null,null,"Standard Default (L-J)",{"$f":"MAX(0,E19-E17)","v":268275},{"$f":"L8*4","v":1073100},null,{"$f":"AND(E9>0,E8=0%)","v":false},{"$f":"AND(E8>0%,E9=0)","v":false},{"$f":"COUNTIF(O8:P8,TRUE)","v":0}]
[null,null,"LTA at Deemed BCE",null,0,null,null,null,null,null,"With Transitional Tax Free Amount Certificate (L-C) ",{"$f":"MAX(0,E19-G12)","v":268275},{"$f":"L9*4","v":1073100},null,{"$f":"AND(E10=0%,G10>0)","v":false},{"$f":"AND(E10>0,G10=0)","v":false},{"$f":"COUNTIF(O9:P9,TRUE)","v":0}]
[null,"C","Serious Ill Health Lump Sums ",null,0,"E",0,null,null,null,null,null,null,null,{"$f":"AND(E11=0%,G11>0)","v":false},null,{"$f":"COUNTIF(O10:P10,TRUE)","v":0}]
[null,"D","Other BCEs",null,0,"F",0,null,null,null,"Lump Sum and Death Benefit Allowance Available"]
[null,null,null,"G",{"$f":"E8+E10+E11","v":0},"H",{"$f":"SUM(G8:G11)","v":0},null,null,null,"Standard Default (£0 where G is 100% +, or A - K)",{"$f":"IF(E12>=100%,0,MAX(0,E5-E17))","v":1073100}]
[null,null,null,null,null,null,null,null,null,null,"With Transitional Tax Free Amount Certificate (A-H)",{"$f":"MAX(0,E5-G12)","v":1073100}]
[null,"I","LTA previously used amount (A x G)",null,{"$f":"E5*E12","v":0},null,null,null,null,null,"Instructions for use"]
[null,null,null,null,null,null,null,null,null,null,"A - enter individuals personal LTA e.g. if no protection then £1,073,100, if FP12 then £1,800,000 etc\nB - enter deemed BCE percentage and relevant LTA at time of BCE\nC,D - enter percentage of personal LTA used for each category\nE - enter actual tax free amount of SIHLS paid\nF - enter actual tax free amounts paid through PCLS or UFPLS\n\nSee red tags for further information.\n"]
[null,null,null,null,"LSA",null,"LSDBA"]
[null,"J","Standard Transitional deduction ",null,{"$f":"E14/4","v":0},"K",{"$f":"IF(E12>=100%,\"SET TO £0\",IF(E10>0,E14,E17))","v":0}]
[]
[null,"L","LSA (A x 25%)",null,{"$f":"E5/4","v":268275}]

{"sheet":"Printer Friendly Output","index":1,"n_rows":19,"n_cols":17,"rows_kept":19,"cut":false,"hidden":false,"merged":[[14,10,18,12],[0,1,0,12],[2,1,2,12],[4,10,4,11],[7,1,8,1],[7,6,8,6],[10,10,10,11]]}
[null,"LTA Transitional Tax Free Amount Tool\nbuilt by M&G"]
[]
[null,"Important information\nThis tool is designed to calculate the available Lump Sum Allowance and Lump Sum and Death Benefit Allowance as at 6th April 2024 for those who have crystallised benefits against the Lifetime Allowance between 6th April 2006 and 5th April 2024.\nIt compares the allowances available under the standard default transitional rules and if a Transitional Tax Free Amount Certificate was attained. \nThe tool does not deal with those who have Primary or Enhanced Protection or who are deceased. \nThe amount of tax free cash payable will be subject to individual client's circumstances and HMRC rules all of which may change. \nResponsibility for the use of any information derived from this tool rests solely with the user.\n"]
[]
[null,"A","LTA (must be £1,073,100 to £1,800,000)",null,{"$f":"Tool!E5","v":1073100},null,null,null,null,null,"Permitted Maximum as at 6th April 2024",null,null,null,null,null,"2025-04-05"]
[]
[null,null,null,null,"LTA used ",null,"Actual/Deemed tax free amounts paid",null,null,null,"Lump Sum Allowance Available",null,"Value to support LSA",null,null,"Kill",{"$f":"AND(Q5<TODAY())","v":false}]
[null,"B","Deemed BCE on pre 2006 pension income",null,{"$f":"Tool!E8","v":0},null,{"$f":"Tool!G8","v":0},null,null,null,"Standard Default (L-J)",{"$f":"MAX(0,E19-E17)","v":268275},{"$f":"L8*4","v":1073100}]
[null,null,"LTA at Deemed BCE",null,{"$f":"Tool!E9","v":0},null,null,null,null,null,"With Transitional Tax Free Amount Certificate (L-C) ",{"$f":"MAX(0,E19-G12)","v":268275},{"$f":"L9*4","v":1073100},null,{"$f":"AND(E10=0%,G10>0)","v":false},{"$f":"AND(E10>0,G10=0)","v":false},{"$f":"COUNTIF(O9:P9,TRUE)","v":0}]
[null,"C","Serious Ill Health Lump Sums ",null,{"$f":"Tool!E10","v":0},"E",{"$f":"Tool!G10","v":0},null,null,null,null,null,null,null,{"$f":"AND(E11=0%,G11>0)","v":false},null,{"$f":"COUNTIF(O10:P10,TRUE)","v":0}]
[null,"D","Other BCEs",null,{"$f":"Tool!E11","v":0},"F",{"$f":"Tool!G11","v":0},null,null,null,"Lump Sum and Death Benefit Allowance Available"]
[null,null,null,"G",{"$f":"E8+E10+E11","v":0},"H",{"$f":"SUM(G8:G11)","v":0},null,null,null,"Standard Default (£0 where G is 100% +, or A - K)",{"$f":"IF(E12>=100%,0,MAX(0,E5-E17))","v":1073100}]
[null,null,null,null,null,null,null,null,null,null,"With Transitional Tax Free Amount Certificate (A-H)",{"$f":"MAX(0,E5-G12)","v":1073100}]
[null,"I","LTA previously used amount (A x G)",null,{"$f":"E5*E12","v":0},null,null,null,null,null,"Instructions for use"]
[null,null,null,null,null,null,null,null,null,null,"A - enter individuals personal LTA e.g. if no protection then £1,073,100, if FP12 then £1,800,000 etc\nB - enter deemed BCE percentage and relevant LTA at time of BCE\nC,D - enter percentage of personal LTA used for each category\nE - enter actual tax free amount of SIHLS paid\nF - enter actual tax free amounts paid through PCLS or UFPLS\n\nSee red tags for further information.\n"]
[null,null,null,null,"LSA",null,"LSDBA"]
[null,"J","Standard Transitional deduction ",null,{"$f":"E14/4","v":0},"K",{"$f":"IF(E12>=100%,\"SET TO £0\",IF(E10>0,E14,E17))","v":0}]
[]
[null,"L","LSA (A x 25%)",null,{"$f":"E5/4","v":268275}]