{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["OPL"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":0,"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":"OPL","index":0,"n_rows":56,"n_cols":19,"rows_kept":56,"cut":false,"hidden":false,"merged":[]}
["TPD Personal Cover Limits ",null,null,"All for personal covers only"]
["This calculator should only be used when the income supports a sum insured of more than $3m on a Multiples of Income + Additional Considerations",null,null,"Comments"]
["basis (please refer to the Adviser Guide for details)."]
[]
["Customer date of birth (dd/mm/yyyy):","1981-01-01",null,"ANB",{"$f":"DATEDIF(B5,TODAY(),\"Y\")+1","v":44}]
[null,null,null,"simplify duration to be based on current age (done)"]
["Annual income (incl. Super):",null,null,"income to specify: excl super (included in label)"]
["Years remaining to age 65:",{"$f":"IFERROR(66-E5,\"\")","v":22},null,"add row for super (Not necessary, as in income label)"]
["Derived income to age 65:",{"$f":"IF(B8=\"\",\"\",B7*B8)","v":0},null,"lifetime income to include both  (done, as in income label)"]
[]
[]
["Proposed working definition TPD (personal purpose only):"]
[null,null,null,null,null,null,null,null,null,null,null,null,"1 year"]
["Retained TPD (personal purpose only):",null,null,null,null,null,null,null,null,null,null,null,"2 years"]
[null,null,null,null,null,null,null,null,null,null,null,"2 years","5 years","Number of IP payments possible to end of benefit period for different WP and BP"]
["Proposed monthly IP (including any Super Contribution Option)",null,null,null,null,null,null,null,null,null,null,"6 years","6 years","BP/WP","30 days","60 days","90 days","1 year","2 years"]
["Benefit Period:","To Age 65",null,null,null,null,null,null,null,null,null,"To Age 65","To Age 65","2 years",2,2,2,2,2]
["Waiting period","2 years",null,"add row for IP super contribution option (in label)",null,null,null,null,null,null,null,null,null,"5 years",5,5,5,5,5]
["Total to expiry",{"$f":"IFERROR(IF(OR(B16=\"\",B16=0),\"$0\",IF(OR(B17=\"\",B18=\"\"),\"Require BP & WP to calculate\",B16*12*INDEX($O$17:$S$21,MATCH(B17,$N$17:$N$21,0),MATCH(B18,$O$16:$S$16,0)))),\"Error, please contact Zurich\")","v":"$0"},null,null,null,null,null,null,null,null,null,null,null,"1 year",1,1,1,1,1]
[null,null,null,null,null,null,null,null,null,null,null,"30 days","30 days","To Age 65",{"$f":"66-$E$5","v":22},{"$f":"66-$E$5","v":22},22,{"$f":"66-$E$5-1","v":21},{"$f":"66-$E$5-2","v":20}]
["Retained monthly IP (including any Super Contribution Option)",null,null,"leave this based on future available IP payments as at application date (a bit inconsistent with income but in customer's favour)  (not sure if this is desirable)",null,null,null,null,null,null,null,"60 days","60 days","6 years",6,6,6,6,6]
["Benefit period:","To Age 65",null,null,null,null,null,null,null,null,null,"90 days","90 days"]
["Waiting period","1 year",null,"add row for IP super contribution option (in label)",null,null,null,null,null,null,null,null,"1 year"]
["Total to expiry",{"$f":"IFERROR(IF(OR(B21=\"\",B21=0),\"$0\",IF(OR(B22=\"\",B23=\"\"),\"Require BP & WP to calculate\",B21*12*INDEX($O$17:$S$21,MATCH(B22,$N$17:$N$21,0),MATCH(B23,$O$16:$S$16,0)))),\"Error, please contact Zurich\")","v":"$0"},null,null,null,null,null,null,null,null,null,"2 years","2 years"]
[]
["Income to retirement",{"$f":"B9","v":0}]
["Insured amount (including TPD and IP)",{"$f":"IFERROR(B12+B14+B19+B24,\"Waiting for information above\")","v":0}]
[null,null,null,"(add in super)  (done, in label)",null,null,null,null,null,null,null,"Minimum","Maximum"]
["Income to retirement vs insured amount",{"$f":"IF(ISNUMBER(B27)=FALSE,\"Waiting for information above\",(IF(B7=\"\",\"\",IF(B12=0,\"Calculator is only required if TPD cover is being proposed\",_xlfn.XLOOKUP(M51,$D$54:$D$56,$M$54:$M$56,,0)))))","v":""},null,"(add in super contributions options)  (done, in label)",null,null,null,null,null,null,null,{"$f":"IF((3000000-B14-B12)<0,3000000-B14,B12)","v":0},{"$f":"(B12-(B27-B26))","v":0},null,"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      "]
["Action:",{"$f":"IF(OR(B19=\"Require BP & WP to calculate\",B24=\"Require BP & WP to calculate\"),\"Complete all fields above\",IF(B29=\"Complete all fields\",\"Complete all fields\",IF(B29=\"Calculator is only required if TPD cover is being proposed\",\"Please add a sum insured in the 'Proposed TPD' field above\",IF(B7=\"\",\"\",_xlfn.XLOOKUP(M51,$D$54:$D$56,$I$54:$I$56,,0)))))","v":""}]
[null,null,null,"(changed the message away from numbers to explanatory words)  (done)",null,null,null,null,null,null,null,{"$f":"ROUND(MAX(L29,M29),0)","v":0}]
[]
[null,null,null,"++ add requirement to tech spec to capture details of existing IP cover where the customer is applying for over $3m of TPD  (if this is an adviser tool, not sure how this is going to be done without something like VPMS.  Also, how is this at all possible for external IP cover)"]
[]
[]
["Today",{"$f":"TODAY()","v":"2024-11-04"}]
[]
[]
[null,null,null,"Step 1 - Cap the TPD sum insured to the greater of $3m and the amount of TPD needed to replace income to age 65",null,null,null,null,null,null,null,null,{"$f":"MAX(3000000-B14,B9-B14-B19-B24)","v":3000000},null," max(3m- retained personal TPD, income to retirement- retained personal TPD - retained IP)"]
[]
[null,null,null,"Step 2 - max $5m industry cap on personal TPD",null,null,null,null,null,null,null,null,{"$f":"MAX(MIN(M39,5000000-B14),0)","v":3000000},null,"min(step1, 5m - retained personal TPD)"]
[]
[null,null,null,"Cover requested and allowed ",null,null,null,null,null,null,null,null,{"$f":"MIN(M41,B12)","v":3000000}]
[]
[]
[null,null,null,"Cover requested and not allowed",null,null,null,null,null,null,null,null,{"$f":"MAX(0,B12-M43)","v":0}]
[]
[]
[null,null,null,"> 3m/income cap",null,null,null,null,null,null,null,null,{"$f":"B12>M39","v":false}]
[null,null,null,"> 5m industry cap",null,null,null,null,null,null,null,null,{"$f":"SUM(B12,B14)>5000000","v":false}]
[null,null,null,"Outcome",null,null,null,null,null,null,null,null,{"$f":"IF(M43=0,\"The maximum permitted amount of TPD is already in place\",IF(OR(M49=TRUE,M50=TRUE),\"Above cap\",\"Sums insured provided are within limits\"))","v":"Sums insured provided are within limits"}]
[]
[null,null,null,"Outcome",null,null,null,null,"Action",null,null,null,"Income to retirement vs insured amount"]
[null,null,null,"Sums insured provided are within limits",null,null,null,null,"No action",null,null,null,"Sums insured provided are within limits"]
[null,null,null,"Above cap",null,null,null,null,"Reduce the proposed TPD",null,null,null,{"$f":"\"Based on the information provided, the maximum allowable new TPD cover is \"&\"$\"&TEXT(M41,\"#,##0\")","v":"Based on the information provided, the maximum allowable new TPD cover is $3,000,000"}]
[null,null,null,"The maximum permitted amount of TPD is already in place",null,null,null,null,"No additional TPD may be proposed",null,null,null,"The maximum permitted amount of TPD is already in place"]