dbwiz
Active Member
- Joined
- Nov 20, 2007
- Messages
- 275
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:
I =FLOOR(DAYS360(G71,DATE(2007,12,1))/360,1)
L = 95,966
M =L71*0.04
N =IF($I71+N$7>65,M71*4%+M71,FV(4%,N$7,-$M71))
O =IF($I71+O$7>65,N71*4%+N71,FV(4%,O$7,-$M71))
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 interest… Yikes.
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 this!
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:
I =FLOOR(DAYS360(G71,DATE(2007,12,1))/360,1)
L = 95,966
M =L71*0.04
N =IF($I71+N$7>65,M71*4%+M71,FV(4%,N$7,-$M71))
O =IF($I71+O$7>65,N71*4%+N71,FV(4%,O$7,-$M71))
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 interest… Yikes.
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 this!