{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Tool","Output (printer friendly)"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":2,"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":"Tool","index":0,"n_rows":28,"n_cols":22,"rows_kept":28,"cut":false,"hidden":false,"merged":[[7,11,7,12],[15,1,15,2],[11,4,12,5],[17,1,17,6],[0,1,0,2],[8,1,8,2],[10,1,10,2],[4,1,4,5],[5,1,6,2],[5,4,6,5]]}
[null,"Protected TFC Tool\nBuilt by M&G",null,null,"Tax Year 2026/27"]
[]
[null,"This tool is for UK financial professionals only."]
[null,"Important information about this tool"]
[null,"This tool is designed to calculate the scheme specific protected tax free cash amount for money purchase arrangements on or after 6 April 2012.\nNOTE: as at 18/11/2024 we await clarification from HMRC as to how the calculations for those with Primary or Enhanced Protection and had lump sum rights below £375,000 and those with other LTA enhancements should be performed. The tool will work for those with no LTA protection or Fixed/Individual Protections. \nThe tool does not take account of anyone where a disqualifying pension credit exists. \nScheme specific tax free cash protection does not apply where tax free cash is protected through Enhanced or Primary Protection i.e. A-day cash was over £375,000.\nThe amount of tax free cash payable will be subject to individual client's circumstances and current HMRC rules all of which may change. In particular, the individual should have sufficient lump sum and death benefit allowance available for all the amount to be tax free.\nResponsibility for the use of any information derived from this tool rests solely with the user.\nPlease note that this tool will cease to operate at the end of the tax year displayed above. If you require a copy for your records we suggest you print a PDF of the pages required.",null,null,null,null,null,null,"Enhanced (no protected %)",{"$f":"MAX(1500000,J11)","v":1500000},1]
[null,"Enter relevant values below",null,null,"Results",null,null,null,"Fixed 2012",{"$f":"MAX(1800000,J11)","v":1800000},1]
[null,null,null,null,null,null,null,null,"Fixed 2014",{"$f":"MAX(1500000,J11)","v":1500000},1]
[null,"\"A-day TFC\" -  tax free cash entitlement from scheme at 5 April 2006",0,null,"A-day tax free cash amount x 1.2",{"$f":"IF(C8=0,\"\",C8*(J24/J25))","v":""},null,null,"Fixed 2016",{"$f":"MAX(1250000,J11)","v":1250000},1,"Kill IP",null,"Min","Max","Input","Active","Min met","Max Met","Count False","Warning Rqd"]
[null,null,null,null,"Additional Lump Sum Amount",{"$f":"IF(J20=FALSE,\"\",IF(AND(J22=2,J21=FALSE),\"\",MAX(0,((J26+(C12-((C10*(J28/J25)))))/4))))","v":""},null,null,"Individual 2014","Use input amount",2,"IP14",{"$f":"AND(J11>O9)","v":false},{"$f":"MAX(J8,J11)","v":1250000},1500000,{"$f":"C15","v":0},{"$f":"AND(C14=I9)","v":false},{"$f":"AND($P$9>=N9)","v":false},{"$f":"AND($P$9<=O9)","v":true},{"$f":"COUNTIF(Q9:S9,FALSE)","v":2},{"$f":"AND(Q9=TRUE,T9=1)","v":false},{"$f":"IF(N9=1250000,\"Amount should be between £1.25m and £1.5m inclusive\",\"Amount should be over the standard lifetime allowance and on or below £1.5m\")","v":"Amount should be between £1.25m and £1.5m inclusive"}]
[null,"\"A-day fund\" - amount of fund value on 5 April 2006",0,null,null,null,null,null,"Individual 2016","Use input amount",2,"IP16",{"$f":"AND(J11>O10)","v":false},{"$f":"J11","v":1073100},1250000,{"$f":"P9","v":0},{"$f":"AND(C14=I10)","v":false},{"$f":"AND($P$9>=N10)","v":false},{"$f":"AND($P$9<=O10)","v":true},{"$f":"COUNTIF(Q10:S10,FALSE)","v":2},{"$f":"AND(Q10=TRUE,T10=1)","v":false},"Amount should be over the standard lifetime allowance and on or below £1.25m"]
[null,null,null,null,"Protected TFC amount",{"$f":"IF(J20=FALSE,\"\",IF(F9=\"\",\"\",SUM(F8:F9)))","v":""},null,null,"No Protection",1073100,1]
[null,"Current Fund Value",0,null,{"$f":"IF(F11=\"\",\"\",IF(C12=0,\"\",IF(J15=TRUE,\"\",IF(J17=FALSE,K17,IF(J19=TRUE,K19,\"\")))))","v":""},null,null,null,"Primary (no protected Amount)",{"$f":"MAX(1500000,J11)","v":1500000},1,null,null,null,null,null,null,null,null,"Warning displayed",{"$f":"IF(U9=TRUE,V9,IF(U10=TRUE,V10,\"\"))","v":""}]
[]
[null,"Protection held","No Protection",null,"\"Standard\" TFC",{"$f":"IF(C12=0,\"\",C12/4)","v":""},null,null,"Hide B13 and C13",{"$f":"AND(J22=1)","v":true}]
[null,"Please enter Individual Protection Amount",0,null,"Additional TFC \ndue to protection",{"$f":"IF(F11=\"\",\"\",IF(J20=FALSE,\"\",MAX(0,F11-(C12/4))))","v":""},null,null,"Starting settings",{"$f":"AND(C8=0,C10=0)","v":true},null,null,"\"Suicide code\""]
[null,{"$f":"U12","v":""},null,null,null,null,null,null,"A-day TFC > 25%",{"$f":"AND(C8/C10>0.25)","v":{"$error":"#DIV/0!"}},"A-day TFC should be greater than 25%",null,{"$f":"TODAY()","v":"2026-04-16"}]
[null,"Lump Sum and Death Benefit Allowance used in calculation",0,null,null,null,null,null,"TFC > 25% now",{"$f":"AND(F11/C12>0.25)","v":{"$error":"#VALUE!"}},"Protected amount is lower than standard amount",null,"2027-04-05"]
[null,null,null,null,null,null,null,null,"A-day fund  > A day cash",{"$f":"AND(C10>C8)","v":false},"A-day fund should be higher than A-day cash",null,{"$f":"AND(M16>M17)","v":false}]
[null,null,null,null,null,null,null,null,"TFC now > Fund now",{"$f":"AND(F11>=C12)","v":true},"Protected amount is greater than or equal to current fund value"]
[null,null,null,null,null,null,null,null,"Can calc be done",{"$f":"AND(C8>0,C10>0,C12>0)","v":false}]
[null,null,null,null,null,null,null,null,"IP input",{"$f":"AND(J22=2,C15>0)","v":false}]
[null,null,null,null,null,null,null,null,"Protection #",{"$f":"VLOOKUP(C14,I5:K12,3)","v":1}]
[]
[null,null,null,null,null,null,null,null,"ULA",1800000]
[null,null,null,null,null,null,null,null,"FSLA",1500000]
[null,null,null,null,null,null,null,null,"LS",0]
[]
[null,null,null,null,null,null,null,null,"CSLA (calced)",{"$f":"IF(J22=2,C15,L28)","v":1073100},null,{"$f":"VLOOKUP(C14,I5:K12,2)","v":1073100}]

{"sheet":"Output (printer friendly)","index":1,"n_rows":17,"n_cols":6,"rows_kept":17,"cut":false,"hidden":false,"merged":[[11,4,12,5],[15,1,15,2],[0,1,0,2],[4,1,4,5],[6,1,6,2],[6,4,6,5],[8,1,8,2],[10,1,10,2]]}
[null,"Protected TFC Tool\nBuilt by M&G",null,null,{"$f":"Tool!E1","v":"Tax Year 2026/27"}]
[]
[null,{"$f":"Tool!B3","v":"This tool is for UK financial professionals only."}]
[null,{"$f":"Tool!B4","v":"Important information about this tool"}]
[null,{"$f":"Tool!B5","v":"This tool is designed to calculate the scheme specific protected tax free cash amount for money purchase arrangements on or after 6 April 2012.\nNOTE: as at 18/11/2024 we await clarification from HMRC as to how the calculations for those with Primary or Enhanced Protection and had lump sum rights below £375,000 and those with other LTA enhancements should be performed. The tool will work for those with no LTA protection or Fixed/Individual Protections. \nThe tool does not take account of anyone where a disqualifying pension credit exists. \nScheme specific tax free cash protection does not apply where tax free cash is protected through Enhanced or Primary Protection i.e. A-day cash was over £375,000.\nThe amount of tax free cash payable will be subject to individual client's circumstances and current HMRC rules all of which may change. In particular, the individual should have sufficient lump sum and death benefit allowance available for all the amount to be tax free.\nResponsibility for the use of any information derived from this tool rests solely with the user.\nPlease note that this tool will cease to operate at the end of the tax year displayed above. If you require a copy for your records we suggest you print a PDF of the pages required."}]
[]
[null,"Enter relevant values below",null,null,"Results"]
[null,"\"A-day TFC\" -  tax free cash entitlement from scheme at 5 April 2006",{"$f":"Tool!C8","v":0},null,{"$f":"Tool!E8","v":"A-day tax free cash amount x 1.2"},{"$f":"Tool!F8","v":""}]
[null,null,null,null,{"$f":"Tool!E9","v":"Additional Lump Sum Amount"},{"$f":"Tool!F9","v":""}]
[null,"\"A-day fund\" - amount of fund value on 5 April 2006",{"$f":"Tool!C10","v":0}]
[null,null,null,null,"Protected TFC amount",{"$f":"Tool!F11","v":""}]
[null,"Current Fund Value",{"$f":"Tool!C12","v":0},null,{"$f":"Tool!E12","v":""}]
[]
[null,"Protection held",{"$f":"Tool!C14","v":"No Protection"},null,{"$f":"Tool!E14","v":"\"Standard\" TFC"},{"$f":"Tool!F14","v":""}]
[null,{"$f":"Tool!B15","v":"Please enter Individual Protection Amount"},0,null,{"$f":"Tool!E15","v":"Additional TFC \ndue to protection"},{"$f":"Tool!F15","v":""}]
[null,{"$f":"Tool!B16","v":""}]
[null,{"$f":"Tool!B17","v":"Lump Sum and Death Benefit Allowance used in calculation"},{"$f":"Tool!C17","v":0}]