EOS Budgeted amount calculation till the previous year end!

Required a formula help to return a values in column D as of DOJ to 31-12-2022.

Conditions mentioned below:-

1. Consider 70% of the gross salary as the basic salary.
2. 21 days per year basic salary is to be calculated for the first 0 to 5 years,
3. After 5 years and before 24 years, 30 days of basic salary will be calculated,
4. No calculation after 24 years, only freeze the values up to 24 years.


Sample sheet with expected results attached.

Thanks,

thanks for the response,

Manual calculation for D6 is available in the attached sample sheet. Please see

Thanks,

Many thanks for your formula,

the results in D7 should come 396,936.00 instead of 480,362.00

The cumulative calculation last range is only 24 years from DOJ.

Thanks,

Yes correct,

I said about Post#06,

As stated in the attached sample sheet, both formulas produce inaccurate results from 2020 onwards any date.


Thanks,