Formula To Calculate How Much Tax To Pay
Working from cell D19 I am trying to find a formula to calculate how much tax to pay.
I have a TAXABLE INCOME figure (in cell D18) , from this figure
The FIRST £1520 should be multiplied by 0.1 (in cell C19,)
The next £26880 should be multiplied by 0.22 (in cell C20, the answer to be put into D20)
Any remaining from the Taxable Income figure (put into cell C21), should be multiplied by 0.4 (the answer placed in cell D21)
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Formula To Calculate Pay Stub
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.
View Replies!
View Related
Formula To Calculate Pay Based On Hours Worked
i am trying to make an excel spreadsheet so i can keep track of my hours and pay at work... i know how to do mult and add but i wanted to know how to do the 2 together.... for example Hours ST OT Total Hrs Gross Pay 8 2 10 ? i would like help figuring out the gross pay if say for example my st time rated is 21.21 an hr, and the ot rate is 31.82 how can i get the 8 times 21.21 = 169.68 and the OT 2hrs times 63.64 to show up in the gross pay as a total of 233.32  what would the formula look like?
View Replies!
View Related
Formula To Calculate Sales Tax From Total Sales
I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and filling in the chart on excel but I would like to create a formula that automatically does it for me based on total sales.
View Replies!
View Related
Calculate The Tax On Any Income
I'm trying to write a function that takes a single argument grossIncome of type Currency. It should calculate the tax on any income using the following tax schedule 1) If income is less than or equal to $15000 there is no tax. 2) If income is greater than $15000 and less than or equal to $75000 the tax is 15% of all income greater than $15000 3) If income is greater than $75000 the tax is 15% of all income between $15000 and $75000 plus 20% of all income greater than $75000 Then I want to write a sub that asks for the user's income and have the above function calculate the tax on the income and say the tax in a message box.
View Replies!
View Related
Calculate BiWeekly Pay Periods
I have been working on solving this riddle since yesterday. What I'm trying to do is have Excel use today’s date on the computer to tell me when my Paydays are in a given month. Or have it tell me the next two or three Paydays based on Today's Date. I can get Excel to calculate Every Two Weeks by using the formula =A3 + 14 then have each sequential field add 14 days to the previous fields date. However what I end up with is just a column of Pay Dates. Not what I'm looking for. I want to have an active page that only shows 2 or 3 future Pay Days. I want to use the function =TODAY() in my formula so that every time I open up the file it can calculate the next Pay Dates automatically. I have been playing around with this formula:..... All this does is tells me the First Friday of every month. This won't work for me because the Pay Periods are separated by 14 days not the 2nd and 4th Friday of every month. Here is an example of my Pay Periods: Sep 19, 2008; Oct 3, 2008; Oct 17, 2008; Oct 31, 2008; Nov 14, 2008; Nov 28, 2008; Dec 12, 2008; Dec 26, 2008; etc.......
View Replies!
View Related
Calculate A Pay Back Period For A Certain Investment
I need to calculate a pay back period for a certain investment. Excel has options for NPV and IRR but not for the Pay back period. (PBP). I've tried finding a solution using google discussion groups and some books but nothing helped me out. attached you'll find an example of my sheet. I need the PBP to be calculated in e.g. 5,3 years. but when the figures change it should automaticly recalculate the PBP.
View Replies!
View Related
Calculate Pay For Mileage While Factoring In The Number Of Participants
I'm trying to do is calculate pay for mileage while factoring in the number of participants. In other words, if the mileage was between 0 and 50 miles, and there were 5 or less people, then this cell should be $35.00. But if it's between 0 and 50 and there were more than 5, then this cell should display 175/the number of people (175 being 35 * 5). In other words, more than five people would receive an equal amount of the total pay for five people (this is because five people are the standard, but there are instances in which six or seven could participate and I was hoping to avoid hardcoding that). The rest of the if statements simply follow that same formula, but for different mileage/pay rates.
View Replies!
View Related
Calculate Total Pay Based On Sliding Percentage Scale
I am trying to set up a spreadsheet where all the sales manager will have to do is input the number of cars sold to determine what the total amount of commission will be for a salesperson. I have something set up so far, but need the spread sheet to do an if/then type thing where it will search the table, and produce the dollar amount for the number of cars sold based on the scale and the number in the # cars sold cell. I've tried conditional sum, IF, goal seek, and all kinds of stuff to no avail. The spreadsheet is attached.
View Replies!
View Related
Lookup The City Tax And Add It In If The Customer Is Not On The Tax Exempt List
I am almost done with form I have been making and so far this forum has been invaluable. My last hurdle is this: I have a list of Tax Exempt Customers that when thier name is entered into the form, I would like it to not add tax. I have tried a forumula like this: =IF(CustName='CustTaxExemptList'!D2:D27,"Exempt",(=VLOOKUP(qtProjAddress3,'City Mileage and Tax Rates'!A2:C702,3)) The above formula does not work, but I dont know why. I want it to lookup the city tax and add it in if the customer is not on the tax exempt list.
View Replies!
View Related
Lookup Wage Calculations (calculate Pay Per Shift Dependant On The Type Of Shift)
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked. I have attached a screen shot of the current page, In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet. The shift pay is the column i am having trouble with. I would like it to change dependant on what is selected in the 'worked' column. For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
View Replies!
View Related
Calculate Pay For Shift Work With Different Rates Based On Shift Hours
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyymmdd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the ifthenelse statements for calculation seems to be wrong!! examples: start = 20080101 09:15 , end = 20080101 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333 start = 20080103 21:05 , end = 20080104 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000 start = 20080202 19:00 , end = 20080202 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000 Function prod(st As Date, en As Date) As Double Dim shour As Integer Dim smin As Integer Dim ehour As Integer Dim emin As Integer Dim stod As String Dim etod As String pday = 8 pnight = 12 shour = Hour(st) smin = Minute(st) + shour * 60 If (shour >= 8 & shour < 20) Then stod = "day" Else stod = "night" End If ehour = Hour(en) emin = Minute(en) + ehour * 60 If (ehour >= 8 & ehour < 20) Then.................
View Replies!
View Related
Bracket Tax Formula
I am working with a income statement in excel, and I need to calculate the tax rate on Net Income. I have to calculate this with both positive and negative net income, and insert this into the cell below net income. I also have to account for different tax rates. $0–$7,500 10.0% 7,50030,650 15.0 30,650 –74,200 25.0 74,200–154,800 28.0 154,800–336,500 33.0 336,500 and up 35.0
View Replies!
View Related
Formula In A Cell That Configures Sales Tax
I have a formula in a cell that configures Sales Tax. How do I add to my existing formula so that if someone is tax EXEMPT to not display or calculate ANYTHING in the Sales Tax Cell? I want to add this to my existing formula in my sales tax cell: IF(Y33="YES") and then I want it to override any existing formula and display nothing in that cell at all.
View Replies!
View Related
Holiday Pay
I need a formula to compute holiday pay. I am having problems when there are two holidays during the month and the hire date of the employee is after the first holiday. For instance in November, November 11 and November 26 are holidays. If the employee is hired before November 11, he gets paid for the two holidays. If he is hired after November 11, he only gets the second holiday. A B C 1 HOLIDAYS 2 HIRE DATE RATE/ 11Nov09 3 HOUR 26Nov09 4 5 10/15/2009 60.00 6 11/16/2009 60.00 7 11/28/2009 60.00 8
View Replies!
View Related
Tax IF Formulas
I need help figuring out an IF formula that would allow me to calculate the tax owed. The tax rates are 20%, 25% and 30% and the full bracket total for 20% is 4,000$ and for 25%, 11,500$. In D14, I have as a taxable income, 20,000$ and In E14, I would need a IF formula that calculates that... but I would need to copy only one formula down the E column to be used on varying taxable incomes...
View Replies!
View Related
Holiday Pay Calculation
I have a problem with a cell displaying the correct number of days. We work a 38 hour week and therefore each day is 7.6hrs I have a start date and current date and calculate the number of days employed. I know that each day employed is worth 24 minutes, so over the year it calculates to 20 days per annum. as I have not entered all the hours taken off yet, my current holiday entitlement is 767:04 (hh:mm). when I process this on the calculator it tells me I have nearly 101 days holiday due, but I can not get excel to display XX:XX:XX (DD:HH:MM) What would like to know is how many days that equates to using the 7.6 hour days
View Replies!
View Related
Trucking Company Pay
Here is the Scenario, A friend of mine who works for a Trucking company asked me this Question; This is How their company calculates His Pay for the Trips that he Makes. @ first 100 Meter + Loading A Value of 1 Which will be Multiplied by the weight of the Load “L” and then a Flat Fee of “X’, And Off course The weight is not of any Issue for us for time being. @ first 100 Meter + Loading A Value of 1 * X @ Next 500 Meter a value of Max. 500100=400 => 400/100= 4, then A Value of 4 * Y, Then if Total Traveling is 350 Meter, There we will have 1*X*L + 2.5*Y*L And again L is not important and for time Being Could left Out. @ Next 10 Kilometer A value of [Number of Kilometer  .5]*Z Then Max would be 9.5 * Z @ Traveling of Over 10 and Less Than 30 Kilometer would be [Number of Kilometer – 10] * W Then a Traveling of 27 would be 17 * W @ Over 30 Kilometer a Value of [Number of Kilometer – 30] * V And Finally all these would be added up accordingly. So far so good, and There is no Problem and Easily I can Use Excel To Calculate Every thing for me Just by Entering the Total travel amount and the Weigh of the Load, Using If, And Vlookup. But Here it gets Complicated, They have these Following additional Conditions; 1: If the Traveling Road is a High way or Major Paved Roads Then all the above Values would be multiply by .77 2: If the Road is a Semi Paved Road then all the Above Values would be multiplies by .9 3; If the Road is a Dirt Road then all above would be Multiply by 1 For Example if the Traveling is 17 Kilometer and 2 kilometer is Dirt at the Beginning, and 2 Kilometer is the semi Paved at the end and the Rest are Fine Paved Road. 1*X*1+ 4*Y*1 + 1.5*Z*1 + 8*Z*(.77) + 5*W*(.77) + 2*W(.9)
View Replies!
View Related
Progress Tax Calculator
I'm trying to come up with an efficient formulae or function to calculate tax The problem I have is that the tax is progressive. As below the first 20,000 is taxed at 5% the next 20,000 is taxed at 6% the next 20,000 is taxed at 7% the next 20,000 is taxed at 8% the next 20,000 is taxed at 9% more than 100,000 is taxed at 10% I'm trying to do a formula like below Cell B3 is my taxable amount Cell B5 =IF(B$3>20000,20000*0.05,B$3*0.05) Cell B6 =IF(B$3>40000,40000*0.05,(B$320000)*0.05) This gives me a problem in that for 35k say, I end up with a negative number for the second part in cell B6. My other issue is that each calculation will take up 6 rows on my spreadsheet. I was hoping to set up a function that could do this in a cell, but even the simple stage defeats me at the moment.
View Replies!
View Related
Sliding Tax Function
I want to make a function via macro to calculate tax During search I have found a code but the result is not coming correctly. Tax rate is: Upto 110000 > 0% More than 110000 but less than 150000 > 10% More than 150000 but less than 250000 > 4000 + 20% Above 250000 > 24000 + 30% For checking I am providing some solution: IncomeTax Payable 110000 > 0 130000 > 2000 150000 > 4000 160000 > 6000 250000 > 24000 490000 > 96000 The function is: Public Function tax(income As Single) Select Case income Case Is <= 110000 tax = 0 Case Is <= 150000 tax = (150000  110000) * 0.1 Case Is <= 250000 tax = (250000  150000) * 0.2 + 4000 Case Else tax = (income  250000) * 0.3 + 24000 End Select End Function
View Replies!
View Related
Filtering For Multiple Pay Types
I need to find the total $ collected on repair orders that contain customer pay and warranty repairs. The problem is that C and W show up on differerent rows, if an RO has 4 lines 3 may be c and 1 line w. I don't know how to use pivit tables to get the info I am looking for, or if that is the best way to fnd my answer.
View Replies!
View Related
Tax Year Date Splitting
I have a date that is save as a date variable. Is there any quick functions that can be used in VBA that will get a date split by tax year. For Example If I was wanting to look at tax year 06 this would be from April 06 to March 07. The year would have to be determined by a entered year Input box. After it has been split by year I then if it falls between the dates to split the date by Month. Is there any simple functions and comparitors I can use on a date defined variable.
View Replies!
View Related
Payroll Extract To Work Only On Certain Pay Period Dates.
I currently have an Excel payroll extract that populates a start date and end date via the calender control 11 user form. What I have found out is I need to inject some sanity to this application. I can not have users select days that are outside of a pay period. The users should only be able to select the first or the 16th of the month for a pay period start. Then they can only select the 15th or the 28th/31st for the pay period end. What I am trying to do is have them select the month and the pay period start date, then the end date would automaticly be selected. But I don't want to have to create a bunch of loops to counter for the differing month end dates or leap years.
View Replies!
View Related
Desperately Seeking Solution  Payroll Tax Withholding
I need to determine federal tax withholding from gross pay. If gross amount (for S0 = single no dependents) is Over.................But NOT Over............Then Subtract.........And multiply $0.00...............$195.00.....................$5 1.......................10% $195.00............$645.00.....................$99 .......................15% $645.00............$1,482.00..................$351 ......................20% $1,482.00..........$3,131.00..................$447 ......................25% $3,131.00..........$6,763.00..................$849 .....................30% If gross is $400 then what are the total taxes taken out? Could someone please provide a formula? I tried this one: =IF(M10
View Replies!
View Related
Formula To Calculate A %
I use this formula to calculate a %, but naturally when there is a 0 excel returns the #DIV/0! error. Can someone fix my formula so the error gets replaced with a 0 in the formula the 0 would be in cell W2 =IF(N2="yes",BN2/W2,0.5).
View Replies!
View Related
Formula To Calculate Discounts
I have attached an example .... If both of the discounts being offered are positive then I am ok with. In this example the first discount ofered is 25% and the second one is +50%. If the required charge is higher than the standard charge then, in this example, the final charge should give the value as 2,500. What would the formula be that added up the 2 discounts together and gave the correct value in cell B8?
View Replies!
View Related
Calculate A Baseline Formula
I am trying to calculate a baseline average sales formula skipping weeks were there was a promotion. The maximum weeks = 8. In my table, promotions are indicated by equal values in the promotion column (C) and weekly sales column (A). If there was no promotion a 0 is in column (C). Beginning the formula in cell B10, If A10<>C10, average A10:A2 If a11 = c11, average A10:A3 ( move up a cell in Col A and average that cell with the preceeding 7 numbers). =AVERAGE(IF(A10<>C10,A3:A10,A2:A9)) However, if another value in A equals the corresponding C values within the averaging range (i.e. A8=C8), I want to skip "A8" in the average calculation and include the next cell outside the range (A2 or A1). In other words, I want to average a maximum of 8 preceeding entries in Col A that meet the criteria A<>C.
View Replies!
View Related
Formula To Calculate Turnaround
I am trying to calculate the number of items that took more than 5 days to complete. The user enters the month and the year for which stats are required in G5 and G6, respectively (e.g., February and 2008). Both cells are formatted as General. Column D contains the start dates, while column Q contains the completed dates. I used the following formula but I am not getting the expected result: PHP =SUMPRODUCT((DATE(YEAR($D$10:$D$12),MONTH($D$10:$D$12),DAY($D$10:$D$12))>=DATE($G$6,CHOOSE(MATCH($G$5,{"January","February","March","April","May","June","July","August","September","October","November","December"},0),1,2,3,4,5,6,7,8,9,10,11,12),1)),(DATE(YEAR($D$10:$D$12),MONTH($D$10:$D$12),DAY($D$10:$D$12))<=DATE($G$6,CHOOSE(MATCH($G$5,{"January","February","March","April","May","June","July","August","September","October","November","December"},0),1,2,3,4,5,6,7,8,9,10,11,12)+1,0)),($D$10:$D$12<>""),($Q$10:$Q$12<>""),(DATE(YEAR($Q$10:$Q$12),MONTH($Q$10:$Q$12),DAY($Q$10:$Q$12))DATE(YEAR($D$10:$D$12),MONTH($D$10:$D$12),DAY($D$10:$D$12))>5))
View Replies!
View Related
Formula Calculate The Percentage
I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers 'chosen' at random by 500 unique people. I have another row of numbers representing the 'frequency' that the aforementioned number was chosen in cells A2 to Z2. Example: 1 2 3 4 5 6 7 8 9 10 11 ... 10 4 16 9 23 20 61 13 18 43 17 ... Is there a formula that I could use to calculate the percentage (0%100%) that each number was chosen?
View Replies!
View Related
Formula  How To Calculate For The 35 Min At Say 15.24
i just started with a company Royal Mail and they have some very strange timings for starting and finishing and i need a formula to help me work my money out and my hours to the precise min. for example i have a shift comming up this week and my start time is down for 22:45 and my calculated finishing time is 07:23. i need a formula that will show on a spread sheet my exact time of work. the other time i had was 22:35 to 06:35...it all seems prity simple to alot of people but this is my other problem. if i work from 06  18:00 i get one rate and if i work from 18  06:00 i get another rate. so i need to know how to calculate for the 35 min at say 15.24 ph.
View Replies!
View Related
Formula To Calculate Penalties
i am trying to make a wage sheet calculator using excel.. and the problem arised was that how to deduct late fine .. i have a sheet which says for example if he is late for 15min then 10$ and if he is late for 30 min then 15$ ..etc.. how to write the formular if the person of salay say 1000$ range comes late after 15min. 30min . 60min .etc...
View Replies!
View Related
Calculate Formula According To Date
I am creating a proforma income statement for a business plan and need the cell values to calculate (or not) depending on the startup date that is entered on another sheet. The following formula works if the value of Startup!$C64 is "March", but if it's "February" it returns 0. =IF(AND(Startup!$C64<="March",Startup!$D64=2008),Assumptions!$S17*P$3,0) What I'm trying to do is have the cell on the income statement only perform the calculation if the startup date is earlier or the same as the month referenced in the formula. Perhaps I can't use the "<" with text?
View Replies!
View Related
Formula To Calculate 2 Cells
I have conditional statement {=SUM(IF('name of sheet'!$H$2:$H$226=H8,1,0))} When ever I try to add a 2nd condition in using the wizard, I get incorrect results. H8 is the cell that I want the calculation based on. How do I put on there that I want the calculation based on H8 or H9. If the answer is an easy one I apologise in advance. I'm a newbie to excel.
View Replies!
View Related
Formula To Calculate The Value Over Limit
I need to work out a formula to calculate the “value over limit” in the following scenario: Cell D11 contains a total balance (input manually) Cell D1 is the total of cells E to H Cell A1 contains the formula to calculate the % of Cell D1 against the balance in Cell D11 Cell B1 will used to input a credit limit which will either be blank or the value of the limit In Cell C1 I need a formula that will return 0 or value over limit based on the following Formula to calculate the value over limit: If cell A1 >10% of Cell D11 return the value difference between 10% of Cell D11 and the value in Cell D1 example; cell D11 = £100,000 & cell D1 = £22,000 (cellA1 = 22%): 10% would be £10000 therefore, the result would be £12,000 over limit However, the comparison also has to take account of Cell B1. If this contains a balance then this needs to be taken into account e.g. in the example shown, if cell B1 contained a balance of £5000 then the result would be £7000 over limit or if the figure was =>£12000 the result would be 0. If the % in Cell A1 is <10% the value is always returned as zero in Cell C1 regardless of any balance in Cell B1.
View Replies!
View Related
Refuses To Calculate A Formula!
I have a spreadsheet that I was sent (with some resource calculations on it  nothing major or huge) and I have added a sheet to it. The issue is that, on the existing sheets, any formula I add works but on the new sheet  they don't!. I get on the new sheet is the formula written out in the cell: =vlookup(b2,table,2,false) rather than the answer I have  looked at the formula and it is correct  cut and paste all sheets (values only) into a new sheet  had a look in the VBA window  nothing stood out
View Replies!
View Related
Formula To Calculate Hours
im doing an excel spreadsheet. on 1st sheet i have a summary page for some clients, on sheet too i have worked out and done the formula so that it calculats values i put it. i want to take this total value i have worked out (which is a variable will change from day to day) and move this value to sheet one. from here i want the number that i show in the summary page to deduct from a fixed total in the cell nxt to it. and ideas ive tried several options coming close but no ciga
View Replies!
View Related
Formula To Calculate Values From Words
There are five levels of Performance. Not Evident(o), Emerging(1),Operational(2),Highly Functional(4),Exemplary(5) There are ten rows of Categories of Performance. There is one final column of Performance Summary for each category. There are seven columns that are report sources of Performance. There is one final row of Performance Summary for each report source. There is one cell that is a final Summary of Performance Overall. The cells have drop down lists of words only. I need the Summary cells to show the result of calculation of the average for each row and column.
View Replies!
View Related
Calculate A Formula Instead Of Returning Text
I am compiling data from several different, variable, worksheets. I am using concatenate to add the name of each worksheet into a formula to pull the appropriate data. However, it is returning the text of the formula instead of the results of the formula. How can I make it calculate the result instead of simply displaying the formula text?
View Replies!
View Related
Conditional Formula And Calculate Automatically
I am creating a sales per day/week worksheet for my reps to keep track of their sales. I have created the sheet that they only have to enter in what they have sold and it automatically calculates what they have sold per hour in the day, and what that makes their weekly total. The problem is sometimes they will work 5 days, sometimes 4, sometimes 6 or 7. im trying to fix the weekly average formula to divide by 7.5 for 1 day worked, 15 for 2, 22.5 for 3, etc all through 7... this is the formula I have now, obviously it doesnt work =IF(COUNT(B7:H7)=1,SUM(E42,E75,E110,E143,E175,E207,E238)/7.5,IF(COUNT(B7:H7)=2,SUM(E42,E75,E110,E143,E175,E207,E238)/15,IF(COUNT(B7:H7)=3,SUM(E42,E75,E110,E143,E175,E207,E238)/22.5,IF(COUNT(B7:H7)=4,SUM(E42,E75,E110,E143,E175,E207,E238)/30,IF(COUNT(B7:H7)=5,SUM(E42,E75,E110,E143,E175,E207,E238)/37.5,IF(COUNT(B7:H7)=6,SUM(E42,E75,E110,E143,E175,E207,E238)/45,IF(COUNT(B7:H7)=7,SUM(E42,E75,E110,E143,E175,E207,E238)/52.5)))))))
View Replies!
View Related
Formula To Calculate Staff Performance
I am trying to work out a % score for a telephone operator. To explain further, I would monitor a call that an agent takes. The agent starts at 100%. There are 20 points to be scored and they are scored like this 1  Yes 0  No left blank  n/a B3 counts the number of entries in the column B2 counts the number of 1's in the column B28 = B2/B3 Seems to work fine until the agent makes a fail which would enter a 0 in the column. Somewhere I think I need to take account that there are 20 possible fails or passes?
View Replies!
View Related
Logic Formula To Calculate Distance
I have a excel sheet with city names in Column A, Distances in Column B, and Distances in Column C. I wanted to write a formula so excel checks the distance between column C and column B and if there is less than a 10% difference between the two then it would copy the name of that city into another column D.
View Replies!
View Related
Formula To Calculate Sick Days
I am trying to put a time sheet together that will place the number 8 in a cell when the work "sick" or vacation is in another cell. For example, if I am out sick on monday, I would like to put "sick" in the cell next to monday and have the number 8 be placed in another place. Then If I am out "sick for three days I would like all of those "sick" days to add up to 24.
View Replies!
View Related
