Free Microsoft Excel 2013 Quick Reference

Prorated Salary Formula

I need to build out a salary budgeting formula that looks at start date and end date within a given fiscal year (April 1st - March 31st) and populates each month with the correct salary value given the start and end dates

Extra Challenge:

We have 26 pay periods in the year (3 in April, 3 in October, 2 in the rest of the months)

Desired outcome:

As an example, if I enter a start date of 5/1/2010 and an end date of 3/31/2011, I am looking for the month by month values to populate in cells D-O for the amount of salary for that month. In this example, the month of April would return a value of $0 and the months of May - March would return values equivalent to the pay per period multiplied by the number of pay periods in that month (listed in bullet point #2 above).

My current column headings are:

A: Start Date
B: End Date
C: Proposed Salary
D-O: Each month of the fiscal year, listed April through March

Any help would be greatly appreciated!


Post your answer or comment

comments powered by Disqus
Hi everyone,

I need help with my excel file.

GOAL: to compute monthly prorated salary based on the inputs placed on the budgeted salary, start date and end date. The worksheet should automatically compute the monthly salary and should be based on the # of days of that month until the end date and put 0 on the months when employee is no longer employed (not within the start and end date).

I need this file to compute salary per month pro rated based on # of days and taking into account the exact start date and end date.

Attached is my file.

PROBLEM: The problem with my file is although it computes the monthly salary and puts in 0 in months employee is not employed, the salary calculation is not "to the dot". It does not calculate based on the number of days within the month. the budgeted salary only gets spread evenly thoughout the year and does not take into account the exact start date and end dates.

I know that my formula is using 12 month spreading, I cannot figure out what to use when I want it daily (probably salary divided by 365??)

Thanks and more power to this forum!!

I have budget spreadsheet template that took for ever to develop. Now I need to add formulas for salaries and fringes to be prorated. Please help! I have a deadline by which I must do budgets. Thank you. T

I need a formula to calculate pro-rated costs. I would like my employees to enter the current month (in the format of 'January', 'February' from a drop-down box) in A1 and the annual price in A2 and get what the prorated cost for the year is. However, this cost is not done by calendar year. The year used is March-February. So the cost for March thru March would be the whole year ($32.00), the cost for June thru March would be 9/12ths of the year or $24.00. I thought about an if/then statement {ie: =IF(a1="June",(9/12)*A2,IF(A1="July",(8/12)*A2,0))} but you can only nest up to 7 arguements, right? I'm sure there is a way, I'm just not sure of it. Any suggestions are apprecated!

Can you help ?

I hope so!

Thanks for whoever reads this.

My problem is this.

I have two spreadsheets

The First has the following information
A B C D E
Name Salary UK USA ROW
Paul Baker £50,000
Paul Moody £50,000

The second has the following information

Name UK USA ROW
Paul Baker 100%
Paul Moody 50% 25% 25%

What I want is the first spreadheet to have a formulae written in C, D and E to do the following.

I want to look the Salary in Spreadheet 1 and allocate the split which is shown in spreadsheet 2.

So the results are as follows:

A B C D E
Name Salary UK USA ROW
Paul Baker £50,000 £50,000
Paul Moody £50,000 £25,000 £12,500 £12,500

Can this be done I have to do this to 1,000 people!

Your input would be valuable.

Regards

Simon

Alright, here's the deal, the wonderful people at my college bookstore
didn't manage to get my textbook in until 1 1/2 weeks left in the
semester and I need some help putting together a formula in Excel 2003
for the final that will determine the salary of multiple employees, at
different hours, pay per hour and overtime.

i.e.
employee 1: 40 hours at $9
emp. 2: 48 at 10
emp. 3: 42 at $8
emp. 4: 44 at $9.50

All hours after 40 hours are time and a half. If someone could help
construct a formula for this it would be hugely appreciated because my
efforts to do so have only resulted in profanity.

We'll also have to determine pay after 8% taxes, so I'm assuming that
can be done with a forumla sum=gross pay*8% followed by the auto sum
feature for all columns in that field?

--
sockmodel7
------------------------------------------------------------------------
sockmodel7's Profile: http://www.excelforum.com/member.php...o&userid=29366
View this thread: http://www.excelforum.com/showthread...hreadid=490771

Hello,

I have attached a worksheet that lays out what I'm trying to do. The "data" worksheet will contain payroll data. In my example, you have the following columns: Name, Salary, Dept, Status, Termination Date, and Paid Through Date. The next worksheet is one called "Acctg." In the actual file that I have, there be multiple worksheets for each department, but in the example I only provided one, "Acctg." Basically, I need to create an IF(ISERROR formula, or maybe soemthing completely different, that will lookup the name on the "Acctg" worksheet (I have already created a formula that will bring over the people on the "Acctg" worksheet), and calculate if the month is actual, then 0, but if the month is forecast, AND less than the "Paid Through Date", then bring over the salary /12 or a weighted % times 12. Also, the formula must recognize that when the current month exceeds the paid through date, then it should be 0. I hope that made sense, I would appreciate any help. THANKS!!!

I need help with calculating a prorated percentage:

at 80% of plan pay 5% of salary
at 100% of plan pay 10% of salary
at 120% of plan pay 15% of salary

I have to prorate % of salary to pay based on the % of plan achieved.
(ie: 90% of plan = 7.50% of salary)

Is there a formula I can use to do this easily?

Thanks again!

Alright, here's the deal, the wonderful people at my college bookstore didn't manage to get my textbook in until 1 1/2 weeks left in the semester and I need some help putting together a formula in Excel 2003 for the final that will determine the salary of multiple employees, at different hours, pay per hour and overtime.

i.e.
employee 1: 40 hours at $9
emp. 2: 48 at 10
emp. 3: 42 at $8
emp. 4: 44 at $9.50

All hours after 40 hours are time and a half. If someone could help construct a formula for this it would be hugely appreciated because my efforts to do so have only resulted in profanity.

We'll also have to determine pay after 8% taxes, so I'm assuming that can be done with a forumla sum=gross pay*8% followed by the auto sum feature for all columns in that field?

I often have data sets that run on a weekly basis, but I need to prorate the weekly data into an estimated monthly total. I will take the number of days that fall in each month from the first week of the month and apply X number of days of that week's data into the current month, with the remaining portion going to the previous month. For example, if I have a weekly number of 700 for a week that ended October 4, 4 days of that value, or 400would be applied to October, while the other 3 days that were in September will add 300 to the September total. All of the full weeks that are fall in the month are simply added.

Below is a sample of data I use. The first week would have 479 applied to June (839/7*4 - for 4 days in June), while the last week would have 611 added to June (855/7*5), with June being a total of 3607.

6/4/10 839
6/11/10 839
6/18/10 846
6/25/10 832
7/2/10 855

Is there a ongoing formula I could use to copy down for it to always calculate the splits across the first weeks of each month and provide a running monthly total as the formula is copied down?

Thanks

I am trying to make a daily time sheet. I want to list the employees down the left side and at the top of my spreadsheet list the hour column along with job number. Sometimes we may work three different jobs, so above the hour column I will list a job number so I know the hours applied to a specific job.
At the right side of the spreadsheet I want the total hours to add up to the total cost for that person on that day. And at the bottom of the spreadsheet I will have total hours for the job.
So let say John had 9 hours Monday. John gets paid $13 an hour and the last column to the right will have the total (9x13) $113.
I can work this formula, my dilema is Pete. I pay Pete a salary, even though he worked three different jobs today totaling 10 hours he still makes $150 that day.Even though Pete is salaried if we do not work he does not receive any pay for that day. It is more like piece work salary.
In other words when I type a number of hours I still want the end column to read $150 whether he works 3 hours or 10 hours.
How do I type in Pete's hours and still keep his total costs at $150.
The reason this is important to me is I track my costs and hours on the same sheet (if possible). I do a lot of time and material jobs and have to keep an accurate count of hours for billing purposes. Can anyone help me with this formula? Thank you in advance.

Hello
Dears

I need a formula in excel for my Salary slip.
here is the details

If Total Salary >= 25000 and = 29999 then - Income Tax 6% of Total Salary
If Total Salary >= 30000 and = 34999 then - Income Tax 7% of Total Salary
If Total Salary >= 35000 and = 39999 then - Income Tax 8% of Total Salary

Thanks
Abdullah

Hi everyone,

First time poster, long time reader.

I am doing some New Year personal finance housekeeping and I have created a Google Docs workbook with 2 worksheets:2012 BUDGET - A yearly budget with my income and expenditure for the next 12 months. Rows are the individual items (income: salary, expenditure: rent etc), columns are my pay days (28th day of each month, or nearest week day). Expenditure is split up into different groups of rows: recurring expenditure (rent, rates etc.) and discretionary expenditure (holidays, big purchases that I am saving for).SAVINGS PLANNER - A savings planner, which contains data about what I am saving for (holidays etc) in the following year, including: target savings, number of months between saving start date and date the money is requiredWhat I want to do is automatically insert the amount I need to save per month, in the relevant row, into the 2012 BUDGET for only the number of months necessary i.e. for Thing F in SAVINGS PLANNER, I have a target of £200 and, having 6 months in which to save the cash (based on starting with this pay packet), I would like the figure of £33.33 to appear in the row for Thing F for the next 6 months automatically in 2012 BUDGET.

I've been messing around with array formulas and IF statements and have got a toehold, but further progress eludes me for now. Sorry for sharing the Google Doc link, but I use GD across multiple locations and I couldn't export the array formulas into Excel (also the Google Docs forum here appears to be quite quiet).

Huge thanks in advance as this is causing me considerable brain ache!

Cheers,

rsfx

ESB Calculation days Dept DOJ amount 1095 GMM 01.10.84 3300 9450 FALSE I should get Answer is 79385.7759 Dear Sirs,
I want to calculate end of service benefit our formula is ISt 3 year ie 1095 days 15days salary for per year (3Years) from 4 th year onwards 30 days salary for Py. I want to do th formula. please help me.
Thanks
Suresh

Hello,

I am calculating the budget on increment % per month and total for full year. Calculation on pro data basis. Means if a staff joined in jan his salary calculate full year from jan-dec. If the staff joined in august then the calculation will be from aug-dec. i am calculating with a formula but getting error if the row or column is blank. Could you please help me to setup my requirement in proper and professional way. sheet is attached for your reference.

Your rapid response is highly appreciable.

Thanks in advance.

Best Regards

HECGroups

I am looking for a formula. Our employees earn 1.5333333333 hours per week if they are hourly employees and 1.933333333 hours a week if they are salary and I am looking for a way to set that up in an excel spreadsheet so that i can calculate how much is accrued based on the week, then have the next column show pto used and the next colum to show available pto. Is this enough information for someone to help me? Thanks in advance!

I'm trying to find the easiest way to calculate a prorated bonus percentage.

Example:

at 90% of Plan - pay 10% of salary
at 95% of Plan - pay 15% of salary
at 100% of Plan - pay 20% of salary

If they achieve 97% of plan then I would have pay them 12% of salary. That's the easy part, the hard part is figuring out what to pay them at 97.57% of plan.

Can anyone help?

Thank you.

I am an HR professional working on new salary recommendations for employees. I am trying to put together a formula based on a two-dimensional matrix. The dimensions are: a) listed in colum 1 are: "range of years in the job" b) listed in columns 2-4 are % increases based on the salary quartile.

For example: If Jane has been in her job for 2 years and falls within the 2nd quartile of the salary range, then she is entitled to an 5% increase based on the two-dimensional matrix.

How do I complete the formula to compute the new salaries?

If this sounds confusion, I have attached a sample of the database + matrix.

I desperately need to complete this project over the weekend.

Please help!

Hello,
I have a question about a formula for 401k. This is the setup:

F4=401k costs to the company which is 3%
C13= the number of years the employee has been there
*the employee will only receive money into their 401k if they have been at the company for 1 year+
D13=their salary

I need to first deterimine the number of years the employee has worked there. IF 1 year or more, then they receive their 401K which is 3% (F4) multiplied by their salary(D13) multiplied by the number of years they have worked there(C13).

If the employee has worked their for less than a year, then the result is "0".

Thank you in advance,
David747

I have a question related to employee salary calculation. We are changing to a July 1 effective date for all. I have an employee whose anniversary date is November 6. The boss wants to pay them so their check increases on July 1 by an amount adjusted so that their total salary for 2011 will be the same as they would have been if their salary increase had begun on their effective date.

What formula can I use if I have the following data to use:
Current Bi-weekly: $1000
Salary Increase Percentage: 2%
New Bi-weekly: $1020
Anniversary date: 11/06/2011
Effective date: 07/01/2011

I'm combining weekly and bi-weekly payroll amounts into a summary column on a third sheet. The summary sheet is bi-weekly, so it takes the bi-weekly salary from sheet a and the two column weekly hourly from sheet b and adds them together in sheet c. For example:

=Salary!ab2+Temp!cd1+Temp!ce1. This sums, for example the bi-weekly salary with the two weekly payrolls for the same period. My problem is, when coping the summary column in the to the next column for the next bi-weekly period, my Temp!xxx references move 1 column at a time and I need them to jump two columns while the Salary!xxx only jumps once.

In the above example, my next column in the summary sheet should be:

=Salary!ac2+Temp!cf1+Temp!cg1, but I'm getting =Salary!ac2+Temp!cd1+Temp!cf1.

How can I add additional columns for payperiods without manually adjusting the formulas?

Hi

I wonder if anyone can help - been struggling with this one for a while and only getting so far..

I'm trying to get a formula to work out salary increments that go up a spine point every year, but this increase can either be on a fixed date or on a start date anniversary. so my statement looks at the start date and end dates of each period and if it matches one of the dates specified in a range elsewhere it should increase the spine point - easy enough.

However this is complicated by the fact that we don't have a spine point 19, so I have to build in another IF statement along the lines of "if answer=19 then use previous cell plus 2 otherwise previous cell plus 1"

It works in the sense that it jumps from 18 - 20, but for some reason it then can't continue so rather than the next cell going to 21 it reverts to 0!

help - it must be something in my logic but i can't get my head round it any suggestion would be appreciated!

IF(AND($B$44"y",OR(H43=refer!$A$8,H43=refer!$C$8,H43=refer!$E$8,H43=refer!$G$8,H43=refer!$I$8,H43=refer!$K$8,H43=refer!$M$8,H43=refer!$O$8,H43=refer!$Q$8,H43=refer!$S$8,H43=refer!$U$8,H43=refer!$W$8,H43=refer!$Y$8)),IF(D43+1=19,D43+2,D43+1)))))
here is part of the formula where the error is found - The refer options are all to do with looking up dates (can't use a lookup as it contains other data that screws it up)..

Thanks
lins

Hi all,

I need help to create a formula to calculate pay stub. The calculation is based on a default start date of 1/1/2006. The end date will the pay period ending in the employee paystub.We would then use the Year To Date amount to calculate the monthly salary. I need to build this into an excel formula but could not find the correct formula. Appreciate your kind assistance.

I have attached a sample. Thanks.

I work in a large Public Service Payroll Department. I am trying to create a spreadsheet that will automatically calculate arrears payments for salaried staff. What I need is something that will make the calculation after I provide a start date an end date and old and new salary per annum. I'm self taught using excel but at a very very basic level.

My problem is that I don't know how to enter a formula which will give me the correct amount between 2 dates. For example

01/10/2005 to 16/09/2005 old salary PA 16000 new salary PA 17500. The manual formula would be:
((17500 - 16000)x1/12) + ((17500 - 16000) x 1/12 x 16/30)) = £191.67

Thanks

Brian

I’d like to write a conditional formatting formula for the following scenario:

I have a maximum and minimum salary boundary for 4 different paybands. For an individual’s salary (one row per individual) I want the cell to go ‘RED’ when a salary outside the range for that payband is entered in the appropriate cell. I need to enter the formula in one condition only as I have other conditions I need to apply.

Grateful for some advice.


No luck finding an answer? You could always try Google.