{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Entry","Oct2015_Tables","xref Tabs","Sheet1"],"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":"Entry","index":0,"n_rows":68,"n_cols":18,"rows_kept":68,"cut":false,"hidden":false,"merged":[[2,0,2,2],[5,0,5,2],[6,0,6,2],[7,0,7,2],[21,1,21,2],[39,0,39,3],[25,1,25,2],[26,1,26,2],[27,1,27,2],[28,1,28,2]]}
["Frmi"]
["Pension Cost Estimates required by the Local Government Wage Increase Transparency Act"]
["These calculations are estimates only, based on the information you enter. The actual costs will be calculated  at the time of retirement using the employee's IMRF record ."]
[]
["Instructions"]
["1. Enter your information in the blue shaded cells only (over the existing sample text). Cells without shading will be recalculated and repopulated for you."]
["2. Wages entered should include all items that are IMRF reportable earnings. (For example, bonuses or the value of personal use of employer-provided automobiles.)"]
["3. Service should include service under all employers. If unsure of the total service credit, please contact IMRF.",null,null,null,null,null,null,null,null,null,null,{"$f":"DATEVALUE(VLOOKUP(B17,N57:O68,2,FALSE)&\"/01/\"&C17)","v":"2017-01-01"}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"\"Age: \"&O9","v":"Age: 65.1320000000001"},null,null,null,{"$f":"Age_IMRF(B12,L8)","v":65.132000000000062}]
[]
["Employee name: ","Mary Member"]
["Date of birth: ","1951-11-14"]
["Gender: ","Female"]
["Is the employee married or in a civil union? \n(if unknown, check with employee or choose \"yes\" for calculation purposes): ","No"]
["Termination Date: ","2016-12-31",null,null,null,null,null,null,null,null,null,null,"Regular","Yes","Male",4.4999999999999998E-2,{"$f":"Reg_Pen(M20,B31*12,O9,M20,1)","v":371.92},{"$f":"Reg_Pen(M20,C31*12,O9,M20,1)","v":418.42}]
["IMRF Plan: ","Regular",{"$f":"IF(B20<20, \"Does not Qualify for a SLEP Pension\",\"\")","v":""},null,null,null,null,null,null,null,null,null,"SLEP","No","Female",7.4999999999999997E-2,{"$f":"SLEP_Pen(M20,B31*12,1,O9)","v":516.92999999999995},{"$f":"SLEP_Pen(M20,C31*12,1,O9)","v":581.54999999999995}]
["Announced retirement date (1st of the month): ","January",2017]
[null,{"$f":"IF(L8<B15,\"Invalid retirement date (must be after termination date)\",\"\")","v":""}]
[null,"Years","Months"]
["Amount of service at termination date: ",24,10,null,null,null,null,null,null,null,null,null,{"$f":"B20+VLOOKUP(C20,M42:O53,3,FALSE)","v":24.812999999999999}]
[]
[null,"IMRF Wages"]
[null,"Wages without increase","Wages with increase",null,null,null,null,null,null,null,null,{"$f":"MONTH(B15)","v":12},{"$f":"YEAR(B15)","v":2016},{"$f":"IF(L23=12,1,L23+1)","v":1},{"$f":"IF(L23=12,M23,M23-1)","v":2016}]
[{"$f":"VLOOKUP(N23,M$43:N$54,2,FALSE) & \" \" & O23 & \" to \" & VLOOKUP(L23,M$43:N$54,2,FALSE) & \" \" & M23","v":"Jan 2016 to Dec 2016"},10000,15000,null,null,null,null,null,null,null,null,{"$f":"L23","v":12},{"$f":"M23-1","v":2015},{"$f":"N23","v":1},{"$f":"IF(L24=12,M24,M24-1)","v":2015}]
[]
[null,"Additional Final Rate of Earnings period wages"]
[{"$f":"VLOOKUP(N24,M$43:N$54,2,FALSE) & \" \" & O24 & \" to \" & VLOOKUP(L24,M$43:N$54,2,FALSE) & \" \" & M24","v":"Jan 2015 to Dec 2015"},10000,null,null,null,null,null,null,null,null,null,{"$f":"L24","v":12},{"$f":"M24-1","v":2014},{"$f":"N24","v":1},{"$f":"IF(L27=12,M27,M27-1)","v":2014}]
[{"$f":"VLOOKUP(N27,M$43:N$54,2,FALSE) & \" \" & O27 & \" to \" & VLOOKUP(L27,M$43:N$54,2,FALSE) & \" \" & M27","v":"Jan 2014 to Dec 2014"},10000,null,null,null,null,null,null,null,null,null,{"$f":"L27","v":12},{"$f":"M27-1","v":2013},1,{"$f":"IF(L28=12,M28,M28-1)","v":2013}]
[{"$f":"VLOOKUP(N28,M$43:N$54,2,FALSE) & \" \" & O28 & \" to \" & VLOOKUP(L28,M$43:N$54,2,FALSE) & \" \" & M28","v":"Jan 2013 to Dec 2013"},10000]
[]
["Monthly Final Rate of Earnings",{"$f":"ROUND(SUM(B24:B29)/48,2)","v":833.33},{"$f":"ROUND(SUM(C24+B27+B28+B29)/48,2)","v":937.5}]
[]
["PV Factor",{"$f":"Oct2015_Tables!C81","v":154.53572},{"$f":"B33","v":154.53572}]
["Present Value of pension",{"$f":"ROUND(B36*B33,2)","v":57474.92},{"$f":"ROUND(C36*C33,2)","v":64660.84}]
["Less member contributions on increase in wages",null,{"$f":"ROUND((SUM(C24+B27+B28+B29)-SUM(B24:B29))*VLOOKUP(B16,M15:P19,4,FALSE),2)*-1","v":-225}]
["Monthly  pension amount:",{"$f":"IF(AND($B16=\"SLEP\",$B20>=20),Q19,Q15)","v":371.92},{"$f":"IF(AND($B16=\"SLEP\",$B20>=20),R19,R15)","v":418.42},null,null,null,null,null,null,null,null,null,"Curr Year",{"$f":"YEAR(NOW())","v":2016}]
["Increase in monthly pension amount:",null,{"$f":"C36-B36","v":46.5},null,null,null,null,null,null,null,null,null,null,{"$f":"N36+1","v":2017}]
["Increase in employer's pension cost:",null,{"$f":"C34-B34+C35","v":6960.9199999999983},null,null,null,null,null,null,null,null,null,null,{"$f":"N37+1","v":2018}]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,0,null,0]
[null,null,null,null,null,null,null,null,null,null,null,null,1,"Jan",8.3000000000000004E-2,2,{"$f":"M23","v":2016}]
[null,null,null,null,null,null,null,null,null,null,null,null,2,"Feb",0.16700000000000001,3,{"$f":"Q43","v":2016}]
[null,null,null,null,null,null,null,null,null,null,null,null,3,"Mar",0.25,4,{"$f":"Q44","v":2016}]
[null,null,null,null,null,null,null,null,null,null,null,null,4,"Apr",0.33300000000000002,5,2016]
[null,null,null,null,null,null,null,null,null,null,null,null,5,"May",0.41699999999999998,6,2016]
[null,null,null,null,null,null,null,null,null,null,null,null,6,"Jun",0.5,7,2016]
[null,null,null,null,null,null,null,null,null,null,null,null,7,"Jul",0.58299999999999996,8,2016]
[null,null,null,null,null,null,null,null,null,null,null,null,8,"Aug",0.66700000000000004,9,2016]
[null,null,null,null,null,null,null,null,null,null,null,null,9,"Sep",0.75,10,2016]
[null,null,null,null,null,null,null,null,null,null,null,null,10,"Oct",0.81299999999999994,11,2016]
[null,null,null,null,null,null,null,null,null,null,null,null,11,"Nov",0.91700000000000004,12,2016]
[null,null,null,null,null,null,null,null,null,null,null,null,12,"Dec",null,1,{"$f":"Q53+1","v":2017}]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"January","April",4]
[null,null,null,null,null,null,null,null,null,null,null,null,"February","August",8]
[null,null,null,null,null,null,null,null,null,null,null,null,"March","December",12]
[null,null,null,null,null,null,null,null,null,null,null,null,"April","February",2]
[null,null,null,null,null,null,null,null,null,null,null,null,"May","January",1]
[null,null,null,null,null,null,null,null,null,null,null,null,"June","July",7]
[null,null,null,null,null,null,null,null,null,null,null,null,"July","June",6]
[null,null,null,null,null,null,null,null,null,null,null,null,"August","March",3]
[null,null,null,null,null,null,null,null,null,null,null,null,"September","May",5]
[null,null,null,null,null,null,null,null,null,null,null,null,"October","November",11]
[null,null,null,null,null,null,null,null,null,null,null,null,"November","October",10]
[null,null,null,null,null,null,null,null,null,null,null,null,"December","September",9]

{"sheet":"Oct2015_Tables","index":1,"n_rows":81,"n_cols":5,"rows_kept":81,"cut":false,"hidden":true,"merged":[]}
[{"$f":"Entry!O9","v":65.132000000000062},{"$f":"INT(A1)","v":65},{"$f":"A1-B1","v":0.13200000000006185}]
[null,"FemaleNo","FemaleYes","MaleNo","MaleYes"]
[null,"Tier 1","Tier 1","Tier 1","Tier 1"]
[null,"Female W/O","Female with","Male W/O","Male with"]
[null,"Elig Sps","Elig Sps","Elig Sps","Elig Sps"]
[35,213.74,217.64,206.77,215.14]
[36,212.71,216.77,205.46,214.19]
[37,211.63,215.85,204.1,213.2]
[38,210.5,214.89,202.69,212.16]
[39,209.32,213.88,201.22,211.07]
[40,208.09,212.82,199.69,209.93]
[41,206.8,211.71,198.11,208.75]
[42,205.46,210.54,196.47,207.51]
[43,204.05,209.32,194.77,206.22]
[44,202.59,208.04,193,204.87]
[45,201.07,206.69,191.17,203.46]
[46,199.49,205.29,189.27,202]
[47,197.84,203.83,187.3,200.46]
[48,196.13,202.29,185.25,198.87]
[49,194.36,200.69,183.12,197.2]
[50,192.51,199.02,180.92,195.45]
[51,190.61,197.28,178.62,193.63]
[52,188.63,195.46,176.24,191.74]
[53,186.58,193.57,173.76,189.76]
[54,184.46,191.6,171.2,187.7]
[55,182.25,189.54,168.55,185.55]
[56,179.97,187.39,165.8,183.32]
[57,177.59,185.15,162.94999999999999,180.99]
[58,175.11,182.81,160,178.57]
[59,172.54,180.37,156.94999999999999,176.06]
[60,169.87,177.83,153.81,173.45]
[61,167.09,175.18,150.57,170.74]
[62,164.21,172.42,147.25,167.95]
[63,161.22999999999999,169.55,143.85,165.06]
[64,158.15,166.58,140.38,162.09]
[65,154.97,163.5,136.84,159.03]
[66,151.68,160.31,133.25,155.88999999999999]
[67,148.30000000000001,157.02000000000001,129.61000000000001,152.66999999999999]
[68,144.83000000000001,153.62,125.91,149.36000000000001]
[69,141.27000000000001,150.13,122.16,145.97]
[70,137.63,146.53,118.37,142.5]
[71,133.91,142.85,114.53,138.94999999999999]
[72,130.11000000000001,139.07,110.64,135.32]
[73,126.26,135.21,106.73,131.62]
[74,122.34,131.28,102.8,127.86]
[75,118.38,127.27,98.85,124.04]
[76,114.37,123.21,94.89,120.16]
[77,110.33,119.08,90.94,116.24]
[78,106.25,114.91,87.01,112.28]
[79,102.16,110.69,83.1,108.3]
[80,98.05,106.45,79.22,104.29]
[81,93.93,102.18,75.39,100.28]
[82,89.83,97.91,71.62,96.26]
[83,85.75,93.64,67.91,92.25]
[84,81.7,89.4,64.28,88.26]
[85,77.69,85.19,60.74,84.31]
[86,73.75,81.03,57.31,80.400000000000006]
[87,69.88,76.95,54,76.56]
[88,66.11,72.95,50.82,72.8]
[89,62.44,69.06,47.81,69.13]
[90,58.9,65.31,44.97,65.59]
[91,55.52,61.72,42.35,62.2]
[92,52.3,58.3,39.950000000000003,58.96]
[93,49.25,55.05,37.729999999999997,55.87]
[94,46.35,51.96,35.69,52.94]
[95,43.6,49.02,33.79,50.13]
[96,40.99,46.22,31.98,47.45]
[97,38.520000000000003,43.56,30.29,44.9]
[98,36.18,41.04,28.71,42.49]
[99,33.979999999999997,38.659999999999997,27.21,40.19]
[]
["Whole Age",{"$f":"VLOOKUP($B1,$A6:$E70,2,FALSE)","v":154.97},{"$f":"VLOOKUP($B1,$A6:$E70,3,FALSE)","v":163.5},{"$f":"VLOOKUP($B1,$A6:$E70,4,FALSE)","v":136.84},{"$f":"VLOOKUP($B1,$A6:$E70,5,FALSE)","v":159.03}]
["Next Age",{"$f":"VLOOKUP($B1+1,$A7:$E71,2,FALSE)","v":151.68},{"$f":"VLOOKUP($B1+1,$A7:$E71,3,FALSE)","v":160.31},{"$f":"VLOOKUP($B1+1,$A7:$E71,4,FALSE)","v":133.25},{"$f":"VLOOKUP($B1+1,$A7:$E71,5,FALSE)","v":155.88999999999999}]
["Diff",{"$f":"B72-B73","v":3.289999999999992},{"$f":"C72-C73","v":3.1899999999999977},3.5900000000000034,3.1400000000000148]
["Factor",{"$f":"B72-(ROUND(B74*$C$1,5))","v":154.53572},{"$f":"C72-(ROUND(C74*$C$1,5))","v":163.07892000000001},136.36612,158.61552]
[]
[]
[]
[]
[{"$f":"Entry!B13 & Entry!B14","v":"FemaleNo"}]
["Selected Factor",null,{"$f":"HLOOKUP(A80,B2:E75,74,FALSE)","v":154.53572}]

{"sheet":"xref Tabs","index":2,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Sheet1","index":3,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}