Free Microsoft Excel 2013 Quick Reference

salary calculation

Dears,

I want to develop a salary calculator based of basic salary, length of service & fixed annual increment.

example:

I have a candidate holding an MBA degree & the basic salary for MBA holders is USD 2000. if he has 5 years experience. I want a formula that calculate the basic salary form him considering annual increment to be 12%. to make it easier to you,

attracting candidate has 1year experience
= 2000 + %12(2000) = USD 2240

attracting candidate has 2year experience
= 2240 + %12(2240) = USD 2508.8

attracting candidate has 3year experience
=2508.8 + %12(2508.8) = USD 2809.85

etc...

Is there any solution for this so I can enter the number of years & the basic salary only?

thanks in advanced.

regards,


Post your answer or comment

comments powered by Disqus
Dear Admin/Moderators/Seniors

I need help for salary calculation also how to get inforamtion from sheet1 to sheet2 if i enter in sheet1 (Attendance Register) it should automatically comes in sheet2 (salary Calcuation).

Also i want to select month in attendance register if i change the month the days and dates should comes automatically and if sunday it should hilighted in red colour.

Please find attached file and go through the formulas and calculate same like that.

VBA password is : Carrot

Pls help me.

Thanks in advance.

Regards,
Logu

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

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 have the worksheet of the employees.

e.g Mary

Date Time start Time finish
12-7-2006 9:00am 6:00pm
13-7-2006 12:00pm 7:00pm
15-7-2006 9:00pm 6:00am

...

etc

I need to calculate the wages. However, it is not that simple because
different working time at different time has rate. For example working from
9am - 6pm will be paid $20 per hour
6pm - 10pm will be paid $22 per hour
10pm - 6pm will be paid $30 per hour

Saturday afternoon, Sunday and public holiday will be at different rates
too.

I have all these rates at another excel sheet.

At the moment, I need to manually allocate the time slot and it is very time
consuming.

Is there any other better way?

Thanks

How can i do a simple hours (and minutes) x hourly rate calculation and have
it display the correct amount in local currency

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!!

Hello Again,

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.

Thanks in advance.

your rapid response is highly appreciable.

Best Regards
HECGroups

I am trying to create a salary calculator based on particular pay bands and points within each band. Each pay band has a different number of points and each pay band along with a particular point has a specific salary. I want to have a drop down list of the different pay bands. In another cell I want to create a drop down list of points available within that band (remember bands do not have the same number of points within them so whichever band selected will determine the number of points in the drop down list). Based on the band and point chosen I want to display the salary in another cell. I have attached a simplified version. All the data is on the "DATA" sheet and I want to have the drop down lists and results on the "SUMMARY" sheet

Verbally it would be something like "If I was on band 3 and point 5 I would earn £15700". You couldn't have band 3 point 7 because it doesn't exist.

I'm trying to create a simple final salary calculator.

Current salary is in cell A1
Percentage salary increase per annum in cell A2
Number of years until retirement in cell A3.

So I need to multiply A1 * A2 and the multiply the result by A2 again and
keep repeating that action until the numbe of repeat operations equals the
value in A3.

Any suggestions?

Stephen Brown

I'm hoping someone can help me. I am trying to build a formula that will determine an hourly wage. For example. If I make $1000.00 a week, and work 73.5 hours during that week, how can I create a formula that will determine for me the hourly rate up to 40 hours, and the overtime rate at 1.5X thereafter.....keeping taxes completely out of the picture. The only constant in the formula would be the $1000.00 per week, the hourly wage and overtime wage would constantly change depending on the hours worked for the week. Any help at all would definately be appreciated!

Hi All,
This may be an ubernoob question; and for that I apologize.....if possible kindly assist?

...on the other hand this query may need an Excel Pro to figure out....hmmm...

I am trying to prepare a salary worksheet for our staff here at school. I have a column labeled "Years Experience" which I need to actually refer to a tabulated salary scale which will go into a final formula for salary calculation.

In particular, I'm looking for a way to enter in a number (from 1 to 20 in this case) in a cell (under the "Yrs.Experience" heading) which in fact defines the cell with a different value taken from another cell in the worksheet (the salary data).

I need, for example when I enter in "13" (thats years of experience) into say, Cell O5 ("O" being the "Yrs.Experience" column), to then in fact define Cell O5 to another set of tabulated data which in this case is in Cell G55 (which happens to be 17500).

However I need the "13" to remain typed in Cell O5 as such (I dont want it to switch over to 17500).

So I type in "13" into O5 but the formula bar actually reads "17500" (or "G55"). From this other calculations are straightforward.

Please see attachment as a visual example..

cheers,
Robin

Hi!

I'm sitting here doing a simple salary calculations sheet in Excel and since I'm including a print function, I was wondering if it was possible doing a "save a copy as PDF" function.

The spreadsheet's going to have a simple frontpage (1. sheet) and a calculation sheet (2. sheet).
The frontpage is going to have the basis for the calculation, the print function of the salary sheet and I was hoping for the "save a copy as PDF" function.
It should have the option that I can choose where to save to.

Hope someone can help. TIA.

Kim

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 am on salary (weekly) but always work alot more than 40 hrs per week.
How can I make a spreadsheet that will calculate my rate of pay on a weekly
basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime)

Please Help,

Thanks in advance.

The following data as an example (excluding the desired result column)
comes from an Access database. I need for excel to calculate what
employees actually salary recieved during the financial year of April
1 to 31 March.

What I want is to be able to get a user to click a button on Access
which will take them to a pivot table which displays what employees
actually received each financial year. This pivot table will be linked
to a (hidden) excel sheet where the figure has already been calculated.
As it will be a hidden sheet, I need for excel to do this
automatically but I can't figure out how to do get this figure without
manually manipulating the excel sheet cells.

I also have two things that need to be looked into ... How do we
calculate a total figure if someone received numerous salary increases
in the same financial year (see Steve Bilington 1998/1999). Also, what
do we do if there has been no salary increase one year (see Steve
Billington is missing a figure for the 2002/2003 financial year as he
received no increase that financial year. I need for excel to
recognise this but calculate a figure for that missing financial year
as I still need it to appear in the pivot table.

I dunno what to do!!!! HELP!!!!!!!!!!!! K/

FirstName LastName Salary£ FinYear Date Inc DESIRED RESULT
Steve Billington 75000 1998/1999 1-Apr-98 0
Steve Billington 100000 1998/1999 1-Jun-98 0
Steve Billington 130000 1998/1999 1-Oct-98 Sum of £ received in
1998/1999 fin yr
Steve Billington 132500 1999/2000 1-Oct-99 £ received in fin year
Steve Billington 136000 2000/2001 1-Oct-00 £ received in fin year
Steve Billington 151000 2001/2002 1-Oct-01 £ received in fin year
Steve Billington 160000 2003/2004 1-Oct-03 £ received in fin year &
figure for 02/33
Steve Billington 165000 2004/2005 1-Oct-04 £ received in fin year
Steve Billington 175000 2005/2006 1-Oct-05 £ received in fin year
Bob Anderson 70000 2005/2006 1-Apr-05 £ received in fin year
David Downes 100000 2002/2003 1-Oct-02 £ received in fin year
David Downes 114500 2003/2004 1-Oct-03 £ received in fin year
David Downes 127500 2004/2005 1-Oct-04 £ received in fin year

Hello =)
i used to have a job that paid a certain salary per day, but sometimes the boss told me to leave after 4 hours, and sometimes after 8... so in order to see how much i make per hour all all i did was make make a "Worked from" and a "Worked untill" columns, Subtracted the "From" from the "Untill" and got the number of hours.

i used to write the times like this "15.75" (for 3:45pm/15:45) and just ignore any extra minutes by rounding up to down. and assuming i worked till 9:45pm, Excel did 21.75 - 15.75 = 6 hours.

Now i have a job that is 24 hours around the clock.
meaning i could work from 9pm, untill 2am. but calculating 2.0 minus 9.0 gives me -7, and no only is it wrong, since 9pm to 2am is 5 hours, it gives me negative hours.

id like for a way/formula to simlpy be able to write the exact time with mins too, Even if it 15:12, without having to convert 30 mins to half etc, and have excel give me the result in how many hours + mins i've worked that day.

TY!

I'd appreciate help on a formula to calculate pension contributions based on annual salary where contributions are based on 4.5% of the first $41,100 of salary PLUS 6% on the balance of the salary. For example annual salary $50,000 (I wish!!!) Contributions of $1,849.50 (4.5% of $41,100) PLUS $534.00 (6% of $8,900) equal $2,383.50 NOTE While this is example is for a salary > $41,100 the formula who also need to calculate for salaries below $41,100.

Thanks for any help Peter

I am on salary (weekly) but always work alot more than 40 hrs per week.
How can I make a spreadsheet that will calculate my rate of pay on a weekly
basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime)

Please Help,

Thanks in advance.

Hi,

I am preapring a salary computation programme for my company. Now I am facing problem to calculate automatically CPF contribution payable and recoverable according to salary. I am looking for a VB programme or relevant formula to solve.

Please find attached file and advice me how to solve this calculation.

Thanks
jvlkerala

The following data as an example (excluding the desired result column)
comes from an Access database. I need for excel to calculate what
employees actually salary recieved during the financial year of April
1 to 31 March.

What I want is to be able to get a user to click a button on Access
which will take them to a pivot table which displays what employees
actually received each financial year. This pivot table will be linked
to a (hidden) excel sheet where the figure has already been calculated.
As it will be a hidden sheet, I need for excel to do this
automatically but I can't figure out how to do get this figure without
manually manipulating the excel sheet cells.

I also have two things that need to be looked into ... How do we
calculate a total figure if someone received numerous salary increases
in the same financial year (see Steve Bilington 1998/1999). Also, what
do we do if there has been no salary increase one year (see Steve
Billington is missing a figure for the 2002/2003 financial year as he
received no increase that financial year. I need for excel to
recognise this but calculate a figure for that missing financial year
as I still need it to appear in the pivot table.

I dunno what to do!!!! HELP!!!!!!!!!!!! K/

FirstName LastName Salary=A3 FinYear Date Inc DESIRED RESULT
Steve Billington 75000 1998/1999 1-Apr-98 0
Steve Billington 100000 1998/1999 1-Jun-98 0
Steve Billington 130000 1998/1999 1-Oct-98 Sum of =A3 received in
1998/1999 fin yr
Steve Billington 132500 1999/2000 1-Oct-99 =A3 received in fin year
Steve Billington 136000 2000/2001 1-Oct-00 =A3 received in fin year
Steve Billington 151000 2001/2002 1-Oct-01 =A3 received in fin year
Steve Billington 160000 2003/2004 1-Oct-03 =A3 received in fin year &
figure for 02/33
Steve Billington 165000 2004/2005 1-Oct-04 =A3 received in fin year
Steve Billington 175000 2005/2006 1-Oct-05 =A3 received in fin year
Bob Anderson 70000 2005/2006 1-Apr-05 =A3 received in fin year
David Downes 100000 2002/2003 1-Oct-02 =A3 received in fin year
David Downes 114500 2003/2004 1-Oct-03 =A3 received in fin year
David Downes 127500 2004/2005 1-Oct-04 =A3 received in fin year

This spreadsheet will be used to help calculate sales commissions. Attached is sample data and my efforts. Target payout is the amount of the base salary a salesman can earn if all goals are reached. Commissions are payed quarterly. Listed on the left are 7 goals, each with a weight of importance toward the target payout amount. The percentage achieved can only be 100% or 0%, nowhere in the middle, although they are allowed to make up a goal later if they do not make it at first. This is where I get stumped. For example, if they don't make a goal in quarter one but make the goal in quarter two plus what they missed in one, they get two quarters worth of commission in quarter 2 for that goal. I am having trouble figuring out the easiest way to go about this. Thanks in advance for any help.

Hi All,

I have created a workbook that shows several drop down menus. Depending on the drop down menu, i.e. the Name, the Year, the Max % of Salary Available for Incentive Compensation, the workbook will calculate a Payout Timeline.

There are two other tabs that require info fillin.. "Performance Inputs" and "Salary Inputs". Basically, everything I've highlighted in Yellow needs to be inputted. Now, my situation is that while this simple model works, my boss wants it EVEN SIMPLER for the user (his boss) to use.

This means, he wants a model that has a GUI interface (form like) that has everything that I am showing.. Drop Down menu of the name of the person, the Current Year it is, the Max % of Salary Available, and then, he wants to be able to enter the "Excess in Basis Points" Performance and then press ENTER. Once we press ENTER, the PAYOUT TIMELINE should show up calculating the bonuses based on my inputs.

Can someone please take a look at my spreadsheet and let me know if there is something that can be done to make it "SIMPLER"?

I can't do VBA.. and I think this calls for VBA.. I cannot upload the file because it is too big.. If there is anyone interested, please email me and I will send it to you via Private Email.

Thank you,

Hi,

I need to calculate the % of salary increase and bonus for staff based on a matrix.

1) The salary increase in based on the salary grid. Each staff is allocated a ratio as shown in cell H4 to H25. For example, for Abraham, he has obtained a rating of 2 and has a ratio of 78%. Looking at the salary grid, he should get a 13% salary increase.

2) The bonus is also based on a bonus grid. The bonus is based on the rating and job time.The date used for current date calculation is Jan 11 2008. For example, Abraham has a rating of 2 and a job time more than 18 months.Therefore, he should get a bonus of 24.99% based on the bonus grid.

I am trying to create a formula in salary increase% column (yellow) and the bonus % column (blue) to automatically update from the salary grid and bonus grid. The actual staff number is quite long and using a correct formula would ensure the data ia accurate based on the grid/matrix.

Appreciate any help.

I need some assistance in determining a commission calculation.

The following is assumed.

I have a base salary of $5k/month
My PROFIT on hardware sales must equal or exceed my monthly salary in order for me to earn a commission on hardware sales. If it does, my commission is then Profit beyond monthly salary X 25%
I also earn commission on software sales.
Total Commission is the sum of Software commission plus commission on hardware sales.

Example: HW Profit is $6k. Software commission is $3k.
$6k - $5k = $1k.
$1k * 25% = $250.
$250 + $3k = $3250However, there are times (unfortunately) when my total hardware profit does not equal or exceed my monthly salary. When that happens, I am now in a negative status regarding hardware commission. The negative status is simply salary minus hardware profit.

Total commission is then that negative number plus software commission.

Example: HW Profit is $3k. Software commission is $3k.
$5k - $3k = $-2k.
-$2k + $3k = $1kI need a formula in one cell to account for both possibilities.
Thank you in advance for any/all assistance!

Marty


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