{"text_format":"1","workbook":"xlsx","n_sheets":13,"sheets":["DISCLAIMER","Administrator's Guidance Notes","LIMITS","COST","Normal Retirement Age"," TABLE 1","TABLE 2","TABLE 3","TABLE 4","TABLE 5","TABLE 6","TABLE 7","TABLE 8"],"not_rendered":{"charts":0,"images":3,"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":"DISCLAIMER","index":0,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,11],[2,1,2,11],[3,1,3,11],[7,1,7,11],[9,1,9,11]]}
[]
[null,"Please read the Disclaimer below"]
[]
[null,"Disclaimer\nThe data, information or results contained in, furnished by or obtained from the use of this Calculation Tool is for the general use of pension administrators with respect to the administration of the Single Scheme Purchase and Transfer Facility. The outputs of this calculation tool are based on inputs by a pensions administrator.\nNo responsibility is accepted by or on behalf of the Department of Public Expenditure and Reform for any errors, omissions, or misleading statements obtained through the use of this Calculation Tool. \nThe data, information or results obtained through the use of this Calculation Tool have no legal standing. \nThe legislation, policies and terms applicable to the Single Public Service Pension Scheme at all times govern any entitlements to benefits under the Scheme."]
[]
[null,"By continuing to use this Calculation Tool, you are deemed to have accepted and agreed to this Disclaimer"]
[]
[null,"You should now read the Administrators' Guidance Notes Tab prior to accessing the Calculation Tool"]
[]
[null,"If you notice any errors in this calculation tool please contact singleschemequeries@per.gov.ie with \"Purchase and Transfer Calculation Tool\" in the subject line."]

{"sheet":"Administrator's Guidance Notes","index":1,"n_rows":11,"n_cols":2,"rows_kept":11,"cut":false,"hidden":false,"merged":[[6,1,6,20],[7,1,7,20],[8,1,9,20],[3,1,3,20],[0,1,0,20],[1,1,1,20],[2,1,2,20],[4,1,4,20],[5,1,5,20]]}
[null,"Single Public Service Pension Scheme "]
[null,"Administrators' Calculation Tool - Purchase of Additional Pension/Lump Sum Referable Amounts - June 2019"]
[null,"Administrators' Guidance Notes"]
[null,"Who is this Calculation Tool for?\nThis workbook has been developed for use by pension administrators in the public service who have read Circular 15/2019 and who need to process applications and queries about the purchase and transfer facility set out in that Circular. It may also assist pension administrators to resolve queries from Scheme members.\n"]
[null,"What does this Calculation Tool do?\nThis tool is designed for use in conjunction with Circular 15/2019. It assists pension administrators to calculate:\n\n* the limits that apply to the purchase of additional pension and/or lump sum referable amounts by a Single Scheme member at a particular point in time; and\n* the cost of purchasing pension and/or lump sum referable amounts for that Single Scheme member at that point in time\n"]
[null,"What can the results of this Calculation Tool be used for?\nThe results may help pension administrators in calculating the maximum amount of pension and lump sum referable amounts that may be purchased by an eligible, active scheme member who wishes to avail of the Single Scheme Purchase and Transfer Facility.  It may also assist pension administrators to resolve queries from members of the Single Scheme that are employed by their organisation.  \n\n\n\n\n"]
[null,"What information should be reviewed before using the Calculation Tool?\nBefore using this calculation tool, administrators should review Circular 15/2019, and establish with certainty the relevant information about the Scheme member that is required to process an application for purchase or transfer under the Singe Scheme."]
[null,"What information needs to be inserted on this Calculation Tool?\nAdministrators need to insert relevant information in the YELLOW FIELDS indicated with yellow arrows on the LIMITS and COST tabs of the workbook.\n                                                                                                                 \n                 LIMITS tab                                                                                                                                       COST tab\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\nDetails of referable amounts should be available on a member's pension record/ Annual Benefit Statement. If this information is not available, referable amounts can be calculated using the \"Administrators’ Contributions and Referable Amounts Calculation Tool\" available on the Single Scheme website."]
[null,"What outputs are displayed by the Calculation Tool?\nOutputs are displayed in the BLUE FIELDS.\n\nThe LIMITS tab calculates the maximum amount of pension and lump referable amounts that can be purchased by the Scheme member - by way of \"actual purchase\" or by \"purchase by way of transfer\".\n\nThe COST tab calculates the cost of purchasing the desired amount for pension and lump sum referable amounts for the Scheme member. The cost is the same regardless of whether the Scheme member is availing of \"actual purchase\" or \"purchase by way of transfer\"."]
[]
[null,"This calculation tool may be updated from time to time. Administrators should check the website to make sure they are using the up-to-date version."]

{"sheet":"LIMITS","index":2,"n_rows":44,"n_cols":5,"rows_kept":44,"cut":false,"hidden":false,"merged":[[40,2,40,3],[38,1,38,4],[36,2,36,3],[39,2,39,3],[33,2,33,3],[34,2,34,3],[35,2,35,3],[32,2,32,3],[24,2,24,3],[25,2,25,3],[16,1,16,2],[17,1,17,2],[21,2,21,3],[22,2,22,3],[23,2,23,3],[27,1,27,4],[29,2,29,3],[0,0,0,19],[1,0,1,5],[13,1,13,2],[14,1,14,2],[28,2,28,3],[8,1,8,2],[9,1,9,2],[10,1,10,2],[7,2,7,3],[11,1,11,2],[12,1,12,2]]}
["Administrators' Calculation Tool - Purchase of Additional Pension/Lump Sum Referable Amounts - June 2019"]
["PLEASE REVIEW DISCLAIMER AND ADMINISTRATOR GUIDANCE NOTES BEFORE USE"]
[]
[]
[null,"Information needed to calculate the limits"]
[]
[]
[null,"Figures A-G are at end of most recent year","Ref Para 7 of Circular"]
[null,"A. Scheme member’s annual pensionable remuneration, calculated on a full time basis (FTE)",null,0]
[null,"B. Annual maximum personal rate of the Contributory State Pension payable to a person who has no adult or child dependent",null,0]
[null,"C. Total of pension referable amounts (both accrued and previously purchased) by scheme member",null,0]
[null,"D. Pension referable amount accrued by scheme member in most recent year",null,0]
[null,"E. Total of lump sum referable amounts (both accrued and previously purchased) by scheme member",null,0]
[null,"F. Lump sum referable amount accrued by scheme member in most recent year",null,0]
[null,"G. Period of time from end of most recent year to the member’s NRA (whole years only) (not FTE)",null,0]
[null,"Purchase Multiplier "]
[null,"Total number of FTE years as Scheme member to NRA (including past years) (whole years only)",null,0]
[null,"Purchase Multiplier",null,{"$f":"IF(D17=9,\"1\",IF(D17=10,\"2\",IF(D17=11,\"3\",IF(D17=12,\"4\",IF(D17=13,\"5\",IF(D17=14,\"7\",IF(D17=15,\"9\",IF(D17=16,\"11\",IF(D17=17,\"13\",IF(D17=18,\"15\",IF(D17=19,\"17\",IF(D17=20,\"20\",IF(D17>20,\"20\",\"0\")))))))))))))","v":"0"}]
[]
[null,"Calculating the limits"]
[]
[null,"Pension"]
[]
[null,"Formula 1","A/2 - B - C - (D x G)",null,{"$f":"((D9/2)-D10-D11-(D12*D15))","v":0}]
[]
[null,"Formula 2","D x purchase multiplier",null,{"$f":"MAX(0,(D12*D18))","v":0}]
[]
[null,"Limits to be applied"]
[null,"Purchase Limit","Lower value produced by 1 & 2",null,{"$f":"MAX(0,MIN(E24,E26))","v":0}]
[null,"Transfer Limit","Value produced by formula 1",null,{"$f":"MAX(0,(E24))","v":0}]
[]
[]
[null,"Lump sum"]
[]
[null,"Formula 1","(A x 1.5) - E - (F x G)",null,{"$f":"((D9*1.5)-D13-(D14*D15))","v":0}]
[]
[null,"Formula 2","F x purchase multiplier",null,{"$f":"MAX(0,(D14*D18))","v":0}]
[]
[null,"Limits to be applied"]
[null,"Purchase Limit","Lower value produced by 1 & 2",null,{"$f":"MAX(0,(MIN(E35,E37)))","v":0}]
[null,"Transfer Limit","Value produced by formula 1",null,{"$f":"MAX(0,(E35))","v":0}]
[]
[]
[null,"IMPORTANT: Limits must be re-calculated each time the member enters into a purchase agreement"]

{"sheet":"COST","index":3,"n_rows":33,"n_cols":10,"rows_kept":33,"cut":false,"hidden":false,"merged":[[30,1,30,3],[23,1,23,3],[24,1,24,3],[12,1,12,4],[24,6,24,8],[13,1,13,3],[14,1,14,3],[17,1,17,3],[18,1,18,3],[22,6,22,8],[20,6,21,9],[23,6,23,8],[8,1,8,3],[29,1,29,3],[7,1,7,3],[5,1,5,3],[0,0,0,19],[1,0,1,11],[3,1,3,4]]}
["Administrators' Calculation Tool - Purchase of Additional Pension/Lump Sum Referable Amounts - June 2019"]
["PLEASE REVIEW DISCLAIMER AND ADMINISTRATOR GUIDANCE NOTES BEFORE USE"]
[]
[null,"Member Details"]
[]
[null,"Fast accrual?"]
[null,"Date of birth"]
[null,"Normal Retirement Age",null,null,{"$f":"IF(E6=\"Yes\",55,(IF(E7<'Normal Retirement Age'!C3,66,IF(AND(E7>='Normal Retirement Age'!C4,E7<='Normal Retirement Age'!D4),67,IF(E7>'Normal Retirement Age'!C5,68,\"INVALID\")))))","v":66}]
[null,"Age next birthday",null,null,{"$f":"DATEDIF(E7,TODAY(),\"Y\")+1","v":121}]
[]
[null,"Limits (calculated in LIMITS tab) "]
[]
[null,"Limits on \"Actual Purchase\""]
[null,"Pension referable amount",null,null,{"$f":"(LIMITS!E29)","v":0}]
[null,"Lump sum referable amount",null,null,{"$f":"(LIMITS!E40)","v":0}]
[]
[null,"Limits on Purchase by way of Transfer"]
[null,"Pension referable amount",null,null,{"$f":"(LIMITS!E30)","v":0}]
[null,"Lump sum referable amount",null,null,{"$f":"(LIMITS!E41)","v":0}]
[]
[null,"Cost of pension referable amounts",null,null,null,null,"Convert a sum of money into referable amounts"]
[]
[null,"€ pension referable amount",null,null,"Cost",null,"Money amount/ transfer value"]
[null,1,null,null,{"$f":"IF(E8=68,VLOOKUP(E9,' TABLE 1'!B2:C49,2,FALSE),IF(E8=67,VLOOKUP(E9,'TABLE 3'!B2:C48,2,FALSE),IF(E8=66,VLOOKUP(E9,'TABLE 5'!B2:C47,2,FALSE),IF(E8=55,VLOOKUP(E9,'TABLE 7'!B2:C36,2,FALSE)))))","v":{"$error":"#N/A"}},null,"Pension referable amounts; OR",null,null,{"$f":"(J23/E24)","v":{"$error":"#N/A"}}]
[null,null,null,null,{"$f":"(B25*E24)","v":{"$error":"#N/A"}},null,"Lump sum referable amounts",null,null,{"$f":"(J23/E30)","v":{"$error":"#N/A"}}]
[]
[null,"Cost of lump sum referable amounts"]
[]
[null,"€ lump sum referable amount",null,null,"Cost"]
[null,1,null,null,{"$f":"IF(E8=68,VLOOKUP(E9,'TABLE 2'!B2:C49,2,FALSE),IF(E8=67,VLOOKUP(E9,'TABLE 4'!B2:C48,2,FALSE),IF(E8=66,VLOOKUP(E9,'TABLE 6'!B2:C47,2,FALSE),IF(E8=55,VLOOKUP(E9,'TABLE 8'!B2:C36,2,FALSE)))))","v":{"$error":"#N/A"}}]
[null,null,null,null,{"$f":"(B31*E30)","v":{"$error":"#N/A"}}]
[]
[null,"Total cost",null,null,{"$f":"(E25+E31)","v":{"$error":"#N/A"}}]

{"sheet":"Normal Retirement Age","index":4,"n_rows":5,"n_cols":5,"rows_kept":5,"cut":false,"hidden":true,"merged":[]}
[]
[null,"Date of birth",null,null,"Normal Retirement Age"]
[null,"Before","1955-01-01",null,66]
[null,"Between","1955-01-01","1960-12-31",67]
[null,"After","1961-01-01",null,68]

{"sheet":" TABLE 1","index":5,"n_rows":49,"n_cols":3,"rows_kept":49,"cut":false,"hidden":true,"merged":[]}
["NRA","Age Next Birthday","Cost of purchasing €1 Pension p.a. from age 68"]
[68,68,22.18]
[68,67,22.21]
[68,66,22.25]
[68,65,22.28]
[68,64,22.31]
[68,63,22.35]
[68,62,22.38]
[68,61,22.41]
[68,60,22.45]
[68,59,22.48]
[68,58,22.51]
[68,57,22.55]
[68,56,22.58]
[68,55,22.61]
[68,54,22.65]
[68,53,22.68]
[68,52,22.72]
[68,51,22.75]
[68,50,22.78]
[68,49,22.82]
[68,48,22.85]
[68,47,22.88]
[68,46,22.92]
[68,45,22.95]
[68,44,22.99]
[68,43,23.02]
[68,42,23.06]
[68,41,23.09]
[68,40,23.12]
[68,39,23.16]
[68,38,23.19]
[68,37,23.23]
[68,36,23.26]
[68,35,23.3]
[68,34,23.33]
[68,33,23.37]
[68,32,23.4]
[68,31,23.44]
[68,30,23.47]
[68,29,23.51]
[68,28,23.54]
[68,27,23.58]
[68,26,23.61]
[68,25,23.65]
[68,24,23.68]
[68,23,23.72]
[68,22,23.75]
[68,21,23.79]

{"sheet":"TABLE 2","index":6,"n_rows":49,"n_cols":6,"rows_kept":49,"cut":false,"hidden":true,"merged":[]}
["NRA","Age Next Birthday","Cost of purchasing €1 Lump-sum from age 68"]
[68,68,1]
[68,67,1]
[68,66,1]
[68,65,0.99]
[68,64,0.99]
[68,63,0.99]
[68,62,0.99]
[68,61,0.98]
[68,60,0.98]
[68,59,0.98]
[68,58,0.98]
[68,57,0.97]
[68,56,0.97]
[68,55,0.97]
[68,54,0.97]
[68,53,0.96]
[68,52,0.96]
[68,51,0.96]
[68,50,0.96]
[68,49,0.95]
[68,48,0.95]
[68,47,0.95]
[68,46,0.95]
[68,45,0.95]
[68,44,0.94]
[68,43,0.94]
[68,42,0.94]
[68,41,0.94]
[68,40,0.93]
[68,39,0.93]
[68,38,0.93]
[68,37,0.93]
[68,36,0.92]
[68,35,0.92]
[68,34,0.92]
[68,33,0.92]
[68,32,0.92]
[68,31,0.91]
[68,30,0.91]
[68,29,0.91,null,null,68]
[68,28,0.91]
[68,27,0.9]
[68,26,0.9]
[68,25,0.9]
[68,24,0.9]
[68,23,0.9]
[68,22,0.89]
[68,21,0.89]

{"sheet":"TABLE 3","index":7,"n_rows":48,"n_cols":3,"rows_kept":48,"cut":false,"hidden":true,"merged":[]}
["NRA","Age next birthday","Cost of purchasing €1 Pension p.a. from age 67"]
[67,67,23.03]
[67,66,23.07]
[67,65,23.1]
[67,64,23.13]
[67,63,23.17]
[67,62,23.2]
[67,61,23.24]
[67,60,23.27]
[67,59,23.31]
[67,58,23.34]
[67,57,23.38]
[67,56,23.41]
[67,55,23.45]
[67,54,23.48]
[67,53,23.52]
[67,52,23.55]
[67,51,23.59]
[67,50,23.62]
[67,49,23.66]
[67,48,23.69]
[67,47,23.73]
[67,46,23.76]
[67,45,23.8]
[67,44,23.83]
[67,43,23.87]
[67,42,23.9]
[67,41,23.94]
[67,40,23.98]
[67,39,24.01]
[67,38,24.05]
[67,37,24.08]
[67,36,24.12]
[67,35,24.15]
[67,34,24.19]
[67,33,24.23]
[67,32,24.26]
[67,31,24.3]
[67,30,24.33]
[67,29,24.37]
[67,28,24.41]
[67,27,24.44]
[67,26,24.48]
[67,25,24.52]
[67,24,24.55]
[67,23,24.59]
[67,22,24.63]
[67,21,24.66]

{"sheet":"TABLE 4","index":8,"n_rows":48,"n_cols":3,"rows_kept":48,"cut":false,"hidden":true,"merged":[]}
["NRA","Age next birthday","Cost of purchasing €1 lump sum p.a. from age 67"]
[67,67,1]
[67,66,1]
[67,65,1]
[67,64,0.99]
[67,63,0.99]
[67,62,0.99]
[67,61,0.99]
[67,60,0.98]
[67,59,0.98]
[67,58,0.98]
[67,57,0.98]
[67,56,0.97]
[67,55,0.97]
[67,54,0.97]
[67,53,0.97]
[67,52,0.96]
[67,51,0.96]
[67,50,0.96]
[67,49,0.96]
[67,48,0.95]
[67,47,0.95]
[67,46,0.95]
[67,45,0.95]
[67,44,0.95]
[67,43,0.94]
[67,42,0.94]
[67,41,0.94]
[67,40,0.94]
[67,39,0.93]
[67,38,0.93]
[67,37,0.93]
[67,36,0.93]
[67,35,0.92]
[67,34,0.92]
[67,33,0.92]
[67,32,0.92]
[67,31,0.92]
[67,30,0.91]
[67,29,0.91]
[67,28,0.91]
[67,27,0.91]
[67,26,0.9]
[67,25,0.9]
[67,24,0.9]
[67,23,0.9]
[67,22,0.9]
[67,21,0.89]

{"sheet":"TABLE 5","index":9,"n_rows":47,"n_cols":3,"rows_kept":47,"cut":false,"hidden":true,"merged":[]}
["NRA","Age next birthday","Cost of purchasing €1 pension p.a. from age 66"]
[66,66,23.89]
[66,65,23.92]
[66,64,23.96]
[66,63,24]
[66,62,24.03]
[66,61,24.07]
[66,60,24.1]
[66,59,24.14]
[66,58,24.17]
[66,57,24.21]
[66,56,24.25]
[66,55,24.28]
[66,54,24.32]
[66,53,24.36]
[66,52,24.39]
[66,51,24.43]
[66,50,24.46]
[66,49,24.5]
[66,48,24.54]
[66,47,24.57]
[66,46,24.61]
[66,45,24.65]
[66,44,24.68]
[66,43,24.72]
[66,42,24.76]
[66,41,24.79]
[66,40,24.83]
[66,39,24.87]
[66,38,24.91]
[66,37,24.94]
[66,36,24.98]
[66,35,25.02]
[66,34,25.05]
[66,33,25.09]
[66,32,25.13]
[66,31,25.17]
[66,30,25.2]
[66,29,25.24]
[66,28,25.28]
[66,27,25.32]
[66,26,25.35]
[66,25,25.39]
[66,24,25.43]
[66,23,25.47]
[66,22,25.51]
[66,21,25.54]

{"sheet":"TABLE 6","index":10,"n_rows":47,"n_cols":3,"rows_kept":47,"cut":false,"hidden":true,"merged":[]}
["NRA","Age next birthday","Cost of purchasing €1 lump sum p.a. from age 66"]
[66,66,1]
[66,65,1]
[66,64,1]
[66,63,0.99]
[66,62,0.99]
[66,61,0.99]
[66,60,0.99]
[66,59,0.98]
[66,58,0.98]
[66,57,0.98]
[66,56,0.98]
[66,55,0.97]
[66,54,0.97]
[66,53,0.97]
[66,52,0.97]
[66,51,0.96]
[66,50,0.96]
[66,49,0.96]
[66,48,0.96]
[66,47,0.95]
[66,46,0.95]
[66,45,0.95]
[66,44,0.95]
[66,43,0.95]
[66,42,0.94]
[66,41,0.94]
[66,40,0.94]
[66,39,0.94]
[66,38,0.93]
[66,37,0.93]
[66,36,0.93]
[66,35,0.93]
[66,34,0.92]
[66,33,0.92]
[66,32,0.92]
[66,31,0.92]
[66,30,0.92]
[66,29,0.91]
[66,28,0.91]
[66,27,0.91]
[66,26,0.91]
[66,25,0.9]
[66,24,0.9]
[66,23,0.9]
[66,22,0.9]
[66,21,0.9]

{"sheet":"TABLE 7","index":11,"n_rows":36,"n_cols":3,"rows_kept":36,"cut":false,"hidden":true,"merged":[]}
["NRA","Age next birthday","Cost of purchasing €1 pension p.a. from age 55"]
[55,55,33.68]
[55,54,33.729999999999997]
[55,53,33.78]
[55,52,33.83]
[55,51,33.880000000000003]
[55,50,33.93]
[55,49,33.979999999999997]
[55,48,34.03]
[55,47,34.08]
[55,46,34.14]
[55,45,34.19]
[55,44,34.24]
[55,43,34.29]
[55,42,34.340000000000003]
[55,41,34.39]
[55,40,34.44]
[55,39,34.49]
[55,38,34.54]
[55,37,34.6]
[55,36,34.65]
[55,35,34.700000000000003]
[55,34,34.75]
[55,33,34.799999999999997]
[55,32,34.86]
[55,31,34.909999999999997]
[55,30,34.96]
[55,29,35.01]
[55,28,35.06]
[55,27,35.119999999999997]
[55,26,35.17]
[55,25,35.22]
[55,24,35.270000000000003]
[55,23,35.33]
[55,22,35.380000000000003]
[55,21,35.43]

{"sheet":"TABLE 8","index":12,"n_rows":36,"n_cols":3,"rows_kept":36,"cut":false,"hidden":true,"merged":[]}
["NRA","Age next birthday","Cost of purchasing €1 lump sum p.a. from age 55"]
[55,55,1]
[55,54,1]
[55,53,1]
[55,52,0.99]
[55,51,0.99]
[55,50,0.99]
[55,49,0.99]
[55,48,0.98]
[55,47,0.98]
[55,46,0.98]
[55,45,0.98]
[55,44,0.97]
[55,43,0.97]
[55,42,0.97]
[55,41,0.97]
[55,40,0.96]
[55,39,0.96]
[55,38,0.96]
[55,37,0.96]
[55,36,0.95]
[55,35,0.95]
[55,34,0.95]
[55,33,0.95]
[55,32,0.95]
[55,31,0.94]
[55,30,0.94]
[55,29,0.94]
[55,28,0.94]
[55,27,0.93]
[55,26,0.93]
[55,25,0.93]
[55,24,0.93]
[55,23,0.92]
[55,22,0.92]
[55,21,0.92]