I have a spreadsheet with a list of employees where
column I contains a person’s age as of that date,
column L contains their salary,
column M contains 4% of column L (invested on year 1), and
columns N through AP represent years 2 through 30, and equal the future value of the original investment at 4% interest, plus
another 4% investment. Row 7 has the numbers 2 through 30 on each of the columns N through AP.
The number of
employees may vary from list to list, however each list starts on row 8, and goes from the lowest age up to the highest age.
This particular list has 141 employees, (manually filled in on cell A3). Row 8 age is 26 and row 148 is 65. The average age
(cell I3) is 47.
Here’s my dilemma. I had initially assumed that once a person reached 65, they would continue
to accrue interest but wouldn’t have any more investments. So, (using row 71 as an example where the person is 47), my
formulas are as follows:
L = 95,966
And so on
Underneath the final row is a total of each column (year).
However, I now need to assume that each year that:
1. As long as a person is working, they will continue to receive 4% investment plus interest. That part is simple,
but here is where it gets complicated.
2. Each year, starting at year 2, 2% (rounded to the nearest whole person –
reflected as formula =ROUND(A3*2%,0) in cell B3) of the total number of employees of average age terminate employment. They
are then replaced by the same number of people at same age, same salary, but starting at the initial investment year 1.
Issue 1: what if there are no people of that exact age? Or what if there are more than 2% of people of that age?
3. In my scenario, there are 4 people age 46 in year 1, so with an average age of 47, on year 2, 3 people (2% of
141) age 47 would terminate, so the cells N67, 68 and 69 would need to reflect year 1 (column M) totals, and each year
(columns O-AP) after that build upon those amounts.
4. 2% (also rounded) of the oldest age people retire. They
are replaced by the same number of youngest people on the list, also starting at year 1. Since I know that the employees
start on row 8 and cell A3 tells me the number of employees, that should help.
I first thought I could change
the totals to a formula instead of simply a sum, (and not knowing how many employees might be in each list keep the totals on
row 2 instead of a row underneath the list.
However, regardless of which row the totals are in, the formulas in
cells N through AP definitely need to be changed to eliminate the current “if true, then they only get interest” portion.
That part is easy, but they also need to take into account if they fall in the bracket where they would be one who would
retire or terminate, and then have to start using a different formula back at year one and the subsequent years added to
that. Which means that for example, someone who is 55 in year 1 turns 65 in year 10, and the formula in that cell suddenly
equals true that they retire, and change to year 1 investment of age 26. Then their year 11 gets 4% added to that plus
So I am pretty sure those cells would require quite the multi-nested IF formula, but how I would
go about doing it is over my head. Any help would be greatly appreciated, if I didn’t give you a headache already reading