Retirement Planning in Excel (Easy and Effective method)

Retirement Planning in Excel using Functions, Dynamic Arrays, and Power Query

To get the best out of Excel, you need to combine the different functionalities together in achieving your goal.

In this project, We have a long list of employees, along with their Hire Date and Date of Birth.

Employees hired prior to 1 January 2012, retire at the age of 60 years, while those hired on or after 1 January 2012, retire at the age of 65 years.

What we need to do is to create a report showing the names of all employees retiring at 65 and their retirement date.

For this project I’ll be using some classic functions, Advanced Filter, Dynamic Array Function and Power Query.

You can Download the Exercise File by clicking here below

The Setup:

In this workbook, we have 3 identical sheets, one for each method.

• In Column A, we have a list of employee names
• In Column B, we have their Birthdates
• In Column C, we have their Hire Date
• In Cell A2 we have the break point at which the retirement age changes.

Employees hired prior to 1 January 2012, retire at the age of 60 years, while those hired on or after 1 January 2012, retire at the age of 65 years.

For the first 2 methods (first 2 sheets) we need to start creating the same set of functions. Instead of redoing the steps, I’ll be grouping the 2 sheets, so whatever I do in one of them, will be done in the other as well.

Grouping Sheets:

Click on the worksheet “Adv Filter” then press SHIFT and click on the second Sheet “Dynamic Array”. Now both sheets are grouped and their tab color changes.

Creating a Label in Cell D4:

Click on Cell D4 and Type: “Retirement Date” then Hit Enter.

Creating a Function:

In Cell D5 Create an IF function that compares the Hire date of each employee to the Date in Cell A2:

=IF(logical_test,value_if_true,value_if_false)

=IF(C5<$A$2, retirement at 60, retirement at 65)

Note: Cell A2 is an absolute reference to be able to copy the function down. We lock it by hitting F4.

For the value if true (and the value if False) we calculate the retirement date by using an EDATE function:

=EDATE(start_date,months)

The Start Date is the “Birthdate” while the number of months to retire at 60 is 60*12, and to retire at 65 is 65*12.

Let’s put them all together:

=IF(C5<$A$2,EDATE(B5,60*12),EDATE(B5,65*12))

When we hit Enter, the retirement date is returned as a number, since a date is stored in Excel as a number (Day one is the first of January 1900 and since then every day increments by one)

We need before copying our function down, to format this number to look like a Date. I can do that by selecting an option from the drop list in the Number Group of the Home Tab. I can also Right click the cell and use the Format Cell Dialog Box and select the Date Category.

But I’ll be using the Shortcut: CTRL + SHIFT + 3

I can now copy the formula all the way down by double clicking on the lower right corner of cell D5.

After creating our functions in the grouped sheets, we need to Ungroup them to start working on each sheet separately. To do that Right click on one of the grouped sheets an select “Ungroup Sheets”

Select the First sheet and we want to prepare for creating an Advanced Filter that will extract the records for employees retiring at 65.

Method One: Using Advanced Filter

Advanced Filter is based on a Model Formula that runs in memory. If the formula returns a TRUE, the record will be extracted. If it returns a FALSE, the record will be skipped.

In Cell E2, let’s create a logical Test:

=C5>=$A$2

This function is evaluating only the first record (as a model) and is returning TRUE but in the advanced filter, it will run in memory for each record, on condition that the cell above is left Blank. That’s the programmatic way by which Excel understands what to do.

Now let’s create the Advanced Filter by Formula:

1. Select any single cell in the source list of employees.
2. Click on the Data Tab and select “Advanced” (alternatively, you can hit the shortcut ALT, A, Q)
3. The Advanced Filter Dialog box opens and the List Range is Selected.
4. We need to Select the Criteria Range which is Cells E1:E2 (You must select the Blank cell as well)
5. Select the radio button for “Copy To another Location”
6. In the Copy To box select cell G4
7. Then Hit OK
8. All records with a hire date >= 1 January 2012 are extracted and the report is created.

Method Two: Using Dynamic Arrays

Select the second sheet “Dynamic Arrays”

Select Cell G5 and start typing a FILTER Function

=FILTER(array,include,if_empty)

=FILTER(A5:D161,C5:C161>=A2)

Please note:

1. Since this is a Dynamic Array Function it is created in one single cell G5 and it spills onto the adjacent cells.
2. If you look in the Formula Bar, G5 is black in color while all other cells are grey.
3. Locking is not necessary for this function.
4. To delete all the records, just delete cell G5
5. If any of the cells where the function will spill has contents then a Spill error will be returned.
6. Dynamic array functions are only available in Office 365 with Office Insider.

Method Three: Using Power Query

Convert the source list into a Table by hitting CTRL +T followed by Enter

Name the Table “Employees” on the Table Design Tab

Click on the Data Tab and select From Table/Range

The Query Editor Opens and the Applied Steps are to the right.

A Changed Type step was added automatically but it is not totally correct

Select the 2 right columns (while pressing SHIFT, then Right Click >> Change Type >> Select Date

Now the Date format is correct. A message box will appear asking you to Replace Current >> Yes

We need to filter the Hire Date and keep only records >= 1 January 2012

Click on the down pointing arrow of the “Hire Date” column >> Select Date Filters >> After or Equal To >> 1 January 2012

The records remaining are for those employees retiring at 65

Now we need to calculate the Retirement Date:

1. Click on the ADD Column Tab >> Click Custom Column
2. Name the new column Retirement Date
3. For the Formula type:

=Date.AddYears([Birthdate],65)

then Hit OK

After calculating the Retirement Date, Change the Data Type for the new column to Date

Look at the applied steps where all the steps have been recorded.

We are ready to send our data back to Excel

Click on the Home Tab >> Close and Load >> Close and Load To

Select Existing Worksheet >> Cell G4 >> OK

Now The report has been created

We achieved the same result using 3 different methods

1. Functions and Advanced Filter
2. Functions and Dynamic Arrays
3. Power Query

If you looking for a template, you can also download the retirement planner spreadsheet template from Microsoft

Let me know in a comment which method you prefer.

You can watch the Video Tutorial Here