Articles

Excel Prorated Salary Formula

IBM
September 24, 2008 at 10:12:04
Specs: Windows XP, P3 384MB RAM

I need to develop a formula which will determine an employee's earnings for the fiscal year, November 1 - October 31, based on a prorated salary, in order to determine a bonus amount.

Example: Joe's salary is $50,000/yr and he started June 7, 2008. He would be entitled to a bonus based on prorated earnings for June to October 31(because he worked more than half of June)

However, Carol's salary is also $50,000/yr but she started after June 16. She would be entitled to a bonus based on prorated earnings for July to October 31 (because she worked less than half of June).

What formula would I use? How should the dates appear in the 'Start Date' Column?

We also have employees who started in previous fiscal years, how do I exclude them from the prorated calculation.

Thank you for your time.


See More: Excel Prorated Salary Formula

Report •


#1
September 24, 2008 at 11:50:05

What makes this a bit complex is that your fiscal year does not match the calendar year. If they matched, anyone hired prior to this year would not need to have their salary prorated. However, we not only need to check what year they were hired in, but in what month. e.g December 2007 gets prorated (twice - once for the first half of the month and once for the second half) but October 2007 and earlier, doesn't.

So we have to check year, month and day of the month.

Now, obviously I don't know what columns you are using for what, so here are my assumptions:

Column A contains the Start Date
Column B contains the End Date (Oct 31, 2008)
Column C Contains the Annual Salary

Assuming your first Start Date is in A1, put this in D1:

=IF(YEAR(A1)<YEAR(TODAY())-1,C1,IF(YEAR(A1)<YEAR(TODAY()),IF(MONTH(A1)<11,C1,IF(DAY(A1)<15,(C1/12*(MONTH(B1)-MONTH(A1)+13)),(C1/12*(MONTH(B1)-MONTH(A1)+12)))),IF(DAY(A1)<15,(C1/12*(MONTH(B1)-MONTH(A1)+1)),(C1/12*(MONTH(B1)-MONTH(A1))))))

This should return:

- 5 months of salary from someone who started June 1 - 14, in the current year.
- 4 months of salary for someone who start June 15 - 30, in the current year.
- 12 months of salary for someone who started prior to November 1st of the prior year.
- A month's salary for someone who started prior to October 15th of the current year.
- etc.

Test it out and let me know what you think...


Report •

#2
September 24, 2008 at 13:55:12

That's very impressive DerbyDad, and much appreciated.

However, I come up with a value of 0 in Column D.

Does it matter what format the dates are in Column's A and B?


Report •

#3
September 24, 2008 at 15:14:18

The format of the date should not matter as long as it is a true date, with a month, a day and a year.

I put 6/6/08 in A1
I put 10/31/08 in B1
I put 50,000 in C1
I copied the formula directly from my post and pasted it into D1

It returned 20,833.33 which is 5 months salary - 50,000/12*5

I put 6/16/08 in A1

It returned 16,666.67 which is 4 months salary.

I put 11/16/07 in A1

It returned 45,833.33 which is 11 months salary.

I put 6/6/07 in A1

It returned 50,000 since the start date is prior to this fiscal year.

I can't say why it's not working for you.



Report •

Related Solutions

#4
September 25, 2008 at 10:30:13

It's working a treat now DerbyDad!

Thanks a lot.

Mulli


Report •

#5
September 25, 2008 at 11:36:36

Glad to have been of assitance.

Have you calculated my bonus yet?


Report •


Ask Question