{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Introduction","Drawdown Calculator","Input and calc","LIST"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":39,"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":"Introduction","index":0,"n_rows":14,"n_cols":2,"rows_kept":14,"cut":false,"hidden":false,"merged":[[13,1,13,7],[11,1,11,7],[10,1,10,7],[12,1,12,7],[2,1,2,7],[8,1,8,7],[9,1,9,7],[3,1,3,7],[4,1,4,7],[5,1,5,7],[6,1,6,7],[7,1,7,7]]}
[]
[]
[null,"COVID-19 LIVING ANNUITY DRAWDOWN RELIEF CALCULATOR"]
[]
[null,"Terms and Conditions."]
[null,"Conditions of use"]
[null,"This calculator is provided as a tool to assist you as financial adviser. It does not constitute advice by Liberty/STANLIB to the client and you are urged to ensure that you verify the content and are comfortable with utilising it as your personal advice. The results are an estimate based on the information provided and should not be relied on in isolation."]
[null,"What does this calculator do?"]
[null,"In order to address the financial hardship that living annuitants may face as a consequence of the COVID-19 pandemic, living annuitants will be entitled to change the drawdown percentage on their living annuity for a relief period of four months from 1 June 2020 to 30 September 2020.  They can choose a relief drawdown rate from 0.5% to 20% .  Annuities paid after the relief period will automatically revert to the previous drawdown rates applicable."]
[null,"This calculator determines the value of current annuities payable  to a client during the relief period, based on their selected relief drawdown percentage and the investment value of their policy at the last policy anniversary date.  If the policy anniversary falls within the relief period, you will need to recalculate the value of the annuity paid during the relief period based on the client's relief drawdown rate and the new investment value at the policy anniversary date.  The calculator also determines what the value of a client's annuities will be after the relief period up to December 2020,  based on the client's current (or selected) percentages within the existing drawdown relief rates of 2.5% to 17.5% (or 5%to 20% for annuities commencing prior to 21 February 2007).  The relief provided in this calculator may be apportioned in accordance with the four month relief period and the client's applicable annuity payment frequency.  It also provides for top up relief for annual and bi-annual annuities not payable during the four month relief period."]
[null,"Disclaimer"]
[null,"In formulating the information in this tool, Liberty/STANLIB has taken due care to ensure that the information provided is accurate and that any views and opinions given are based on information which is relevant and accurate. Any figures shown on this tool are solely for illustrative purposes only. These values should not be considered as a quote for any of Liberty/STANLIB products. Every effort has been made to ensure that these figures are as accurate as possible however, in the case of a discrepancy from any quoted figures from Liberty; the quoted value should always be used. Liberty/STANLIB accepts no responsibility or liability for any decisions made regarding the estimated figures and the values shown do not constitute any form of financial advice. Any recommendations made must take into account your client's specific needs and personal circumstances."]
[null,"Liberty Group Ltd is an Insurer and an Authorised Financial Services Provider (no 2409). "]
[null,"STANLIB Wealth Management (Pty) Limited is an authorised Financial Services Provider in terms of the Financial Advisory and Intermediary Services Act 37 of 2002 (License No. 590)."]

{"sheet":"Drawdown Calculator","index":1,"n_rows":49,"n_cols":7,"rows_kept":49,"cut":false,"hidden":false,"merged":[[1,1,1,7],[3,1,3,7],[4,1,4,7],[5,1,5,7]]}
[]
[null,"COVID-19 DRAWDOWN RELIEF CALCULATOR"]
[]
[null,"In order to address the financial hardship that living annuitants may face as a consequence of the COVID-19 pandemic, living annuitants will be entitled to change the drawdown percentage on their living annuity for a relief period of four months from 1 June 2020 to 30 September 2020. They can choose a relief drawdown rate from 0.5% to 20%."]
[null,"Annuities paid after the relief period will automatically revert to the previous drawdown rates applicable of 2.5% to 17.5% (or 5% to 20% for pre- 21 February 2007 annuities)."]
[null,"This calculator will determine the value of current annuities payable  to you during the relief period, based on your selected relief drawdown percentage and the investment value of your policy at your last policy anniversary date.  If your policy anniversary falls within the relief period, you will need to recaculate the value of your annuity paid during the relief perod based on your relief drawdown rate and the new investment value at the policy anniversary date.  The calculator will also determine what the value of your annuities will be after the relief period, based on your current (or selected) percentages within the drawdown relief of 2.5% to 17.5% (5% to 20% for pre-21 February 2007 annuities)"]
[]
[]
[null,null,"Current Policy / Details Outside of Relief Period",null,null,"Relief Details"]
[null,null,null,null,null,"Results"]
[null,null,"Did your policy start before 21 Feb 2007?",null,null,"Drawdown type"]
[null,null,"No",null,null,"Percentage"]
[]
[null,null,"Last Policy Anniversary",null,null,{"$f":"VLOOKUP(F12,DDSelRelief,2,FALSE)","v":"Annual drawdown at special rate"}]
[null,null,"2019-11-01",null,null,20,{"$f":"VLOOKUP(F12,DDSel,3,FALSE)","v":"Percent"}]
[]
[null,null,"Investment Value at Last Policy Anniversary"]
[null,null,2000000]
[]
[null,null,"Current date / Calculation date"]
[null,null,"2020-07-01"]
[]
[null,null,"Chosen frequency of payment"]
[null,null,"Monthly"]
[]
[null,null,"Drawdown type"]
[null,null,"Percentage"]
[]
[null,null,{"$f":"VLOOKUP(C27,DDSel,2,FALSE)","v":"Annual drawdown at standard rate"}]
[null,null,12,{"$f":"VLOOKUP(C27,DDSel,3,FALSE)","v":"Percent"}]
[]
[]
[]
[null,null,"Month","Year","Final annuity amounts"]
[null,null,"January",2020,0]
[null,null,"February",{"$f":"D35","v":2020},0]
[null,null,"March",{"$f":"D36","v":2020},0]
[null,null,"April",2020,0]
[null,null,"May",2020,0]
[null,null,"June",2020,0]
[null,null,"July",2020,33333.333333333336]
[null,null,"August",2020,33333.333333333336]
[null,null,"September",2020,33333.333333333336]
[null,null,"October",2020,20000]
[null,null,"November",2020,"Next review period"]
[null,null,"December",2020,"Next review period"]
[]
[null,null,"Relief top up - no normal pmts in relief period",null,0]
[null,null,"Allowed - Yes or No",null,"No top up"]

{"sheet":"Input and calc","index":2,"n_rows":29,"n_cols":17,"rows_kept":29,"cut":false,"hidden":true,"merged":[]}
["Key",null,null,null,null,null,null,null,"Calculations"]
["Input field",null,null,null,null,"Validation",null,null,null,null,"Conditions"]
["Calculated field",null,"Current inputs",null,null,null,null,null,"Year","Month","After current month?","Is it before next review dt?","Is it a PMT month?","Is month in the relief period?","Normal ann pmt","PMT in relief period and before pol ann","Final annuity amounts"]
["Static",null,"Inception before 21 Feb 2007","No",null,null,null,null,2020,1,{"$f":"MONTH(VandagDt)<=J4","v":false},{"$f":"IF( DATE(I4,J4,DAY(RevDtLast))  >=  RevDtNext, FALSE, TRUE)","v":true},{"$f":"MOD( 12 + PolAnnMonth - J4,  MonthlyIncCur) = 0","v":true},{"$f":"AND(J4>=StartMRelief, J4 <= EndMonthRelief)","v":false},{"$f":"IF(AND(K4,M4), PmtCur,0)","v":0},{"$f":"IF(AND(K4:N4), PmtRelief, 0)","v":0},{"$f":"IF(L4,IF(AND(K4, P4= 0), O4, P4), \"Next review period\")","v":0}]
["Results",null,"Last policy anniversary","2019-11-01",null,"must be before today",null,null,{"$f":"I4","v":2020},2,false,true,true,false,0,0,{"$f":"IF(L5,IF(P5= 0, O5, P5), \"Next review period\")","v":0}]
[null,null,"IV at Last Pol Ann",2000000,null,null,null,null,{"$f":"I5","v":2020},3,false,true,true,false,0,0,0]
[null,null,"Current Date","2020-07-01",null,null,null,null,2020,4,false,true,true,false,0,0,0]
[null,null,"Frequency of pmt","Monthly",null,null,null,null,2020,5,false,true,true,false,0,0,0]
[null,null,null,null,null,null,null,null,2020,6,false,true,true,true,0,0,0]
[null,null,"Drawdown ","Percentage",null,null,null,null,2020,7,true,true,true,true,20000,33333.333333333336,33333.333333333336]
[null,null,{"$f":"VLOOKUP(D10,DDSel,2,FALSE)","v":"Annual drawdown at standard rate"},12,{"$f":"VLOOKUP(D10,DDSel,3,FALSE)","v":"Percent"},null,null,null,2020,8,true,true,true,true,20000,33333.333333333336,33333.333333333336]
[null,null,"Relief inputs",null,null,null,null,null,2020,9,true,true,true,true,20000,33333.333333333336,33333.333333333336]
[null,null,"Relief Drawdown","Percentage",null,null,null,null,2020,10,true,true,true,false,20000,0,20000]
[null,null,{"$f":"VLOOKUP(D13,DDSel,2,FALSE)","v":"Annual drawdown at standard rate"},20,{"$f":"VLOOKUP(D13,DDSel,3,FALSE)","v":"Percent"},null,null,null,2020,11,true,false,true,false,20000,0,"Next review period"]
[null,null,"Static field",null,null,null,null,null,2020,12,true,false,true,false,20000,0,"Next review period"]
[null,null,"Relief start date","2020-06-01"]
[null,null,"Relief end date","2020-09-30",null,null,null,null,"Pro Rata Pmt - no normal pmts in relief period",null,null,null,null,null,null,null,{"$f":"IF(AND( ReliefStDt<=RevDtNext, RevDtNext<= ReliefEndDt), \"Anniversary is in Relief period\", IF(SUM(P4:P15) = 0, (PmtRelief - PmtCur)  *(EndMonthRelief + 1 - StartMRelief) / (MonthlyIncCur*0 + 12), 0))","v":0}]
[null,null,"Calculated fields",null,null,null,null,null,"Allowed - Yes or No",null,null,null,null,null,null,null,{"$f":"IF(Q17 = \"Anniversary is in relief period\", Q17, IF(Q17 > 0, \"Yes\", IF(Q17 = 0, \"No top up\", \"Cannot pay money back\")))","v":"No top up"}]
[null,null,"Next review date",{"$f":"DATE(YEAR(RevDtLast)+1,MONTH(RevDtLast),DAY(RevDtLast))","v":"2020-11-01"}]
[null,null,"Policy anniversary M",{"$f":"MONTH(RevDtNext)","v":11}]
[null,null,"Relief start month",{"$f":"MONTH(ReliefStDt)","v":6}]
[null,null,"Relief end month",{"$f":"MONTH(ReliefEndDt)","v":9}]
[null,null,"Pmt freq p.a.",{"$f":"VLOOKUP(D8,FreqTable,2,FALSE)","v":12}]
[null,null,"Pmt M increment",{"$f":"12/Freq","v":1}]
[null,null,"Current drawdown %",{"$f":"IF(D10 = LIST!B8,DDInput * Freq/RevValLast, DDInput/100)","v":0.12},"Min/Max",2.5000000000000001E-2,0.2]
[null,null,"Current Pmt per freq",{"$f":"DDPercCurr/Freq*RevValLast","v":20000}]
[null,null,"Relief drawdown",{"$f":"IF(D13 = LIST!B8,DDInputRelief * Freq/RevValLast, DDInputRelief/100)","v":0.2},"Min/Max",5.0000000000000001E-3,0.2]
[null,null,"New annual pmt",{"$f":"DDPercRelief*RevValLast","v":400000}]
[null,null,"New pmt during relief",{"$f":"D28/Freq","v":33333.333333333336}]

{"sheet":"LIST","index":3,"n_rows":15,"n_cols":4,"rows_kept":15,"cut":false,"hidden":true,"merged":[]}
["Name"]
[]
["FreqTable","Monthly",12]
[null,"Quarterly",4]
[null,"Bi-Annually",2]
[null,"Annually",1]
[]
["DDSel","Amount","Amount per frequency","Rand"]
[null,"Percentage","Annual drawdown at standard rate","Percent"]
[]
[null,"Amount","Amount per frequency","Rand"]
[null,"Percentage","Annual drawdown at special rate","Percent"]
[]
[null,"Yes"]
[null,"No"]