Multi nested IF formula help needed

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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi

If I am understanding your question correctly, this spreadsheet has a dual purpose. The first is to track individual contributions to the fund and the second is to project the future values based on the %'s you supplied.

I recommend you leave the individual records alone and keep your existing totals at the bottom of the spreadsheet. This satisfies the first purpose/objective.

To get a valuation for future years, I recommend you introduce a new section beneath the existing totals that makes adjustments to the value per year based on the assumptions you outlined. However, rather than extracting the values for the employees of an average age, I would use average figures instead over the entire data set. You could refine those values by calculating the retirement adjuster over the oldest 10% of the data, and the resigning adjuster would be based on an average of the other 90%.

Just my thoughts...
Andrew
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top