Excel Busn Math 42: Federal Income Tax Payroll Deduction

I own a tax prep firm and trying to make a spreadsheet to post on my website to give my clients an idea of what they are due back or owe. I have gotten stuck. I set up match and index functions for Earned income credit and tax table, calc for exemptions, and others to tie everything in. My problem is having excel calculate credit like child tax credit and addl child tax credit w/out calculating both. I setup a few if statements and still can't get it right. Can anyone send me an example if they have one and I can send mine out to critic. Thanks

I am trying to come up with the best way (formula not vba) to lookup & calculate income tax from the following two columns A & B when the taxable amount is entered in C1:


ColA Col B
1 10% 14,300 (1st 14,300 taxed at 10%)
2 15% 58,100 (1st 14,300@10% + over 14,300 to 58,100 taxed at 15%)
3 25% 117,250 (1st 14,300@10% + 43,800@15% + 58,100 to 117,250@25%)
4 28% 178,650 (1st 14,300@10% + 43,800@15%+120,550@25%+over 117,250 to 178,650@28%)
5 33% 319,100 (1st 14,300@10%+43,800@15%+120,550@25%+ 61,400@28%+over 178,650 to 319,100@33%)
6 35% over 319,100 (1st 14,300@10%+43,800@15%+120,550@25%+61,400@28%+140,450@33%+all over 319,100@35%)
Using only two columns A & B are preferred and have the ability to change the column A percents & the column B amounts without having to change the formula.

Example: The tax on 49,000 entered in cell C1 would be 6,635 (14300 X 10% + (49000-14300 X 15%)).

Any ideas?

Thank you for your help. mikeburg

Hello, I am trying to create a worksheet that will calculate new payroll deductions if a change is made mid year. Ex. An employee makes an annual election of $500 to be deposited in an FSA account. Say mid year he has a qualifying event and would like to change his election to $1000. The sheet I am trying to create has the following required inputs: date of 1st pay, original election, date of change, new election, & pay cycle (weekly, biweekly, etc). I am really strugling on finding the best way to calculate the remaining number of pays based on the "date of change". Any suggestions??

Thank you!

Does anyone know of a way to calculate federal tax withholding that should be taken out of your paychecks? What i have is the most current tax tables from the IRS and then some general information such as pay frequency and filing status. Then based on those entered parameters i need to have the federal taxes calculated.

Here is what my spreadsheet looks like.

SalaryPaycheck

  B C D 2 General Information 3 Gross Pay $65,520.00 Annually 4 Pay Period Bi-Weekly   5 Filing Status Married   6 # of Allowances 0   7 Additional Fed Withholding $0.00   8      

Excel tables to the web >> Excel Jeanie HTML 4

Cells C4 and C5 are pull down cells that include the different options as listed here.

C4 - Daily, Weekly, Bi-Weekly, Monthly, Semi-Monthly
C5 - Single or Married

Then i have these tax tables created as per IRS

2009TaxTable

  B C D E F G H 3 TABLE 1- WEEKLY Payroll Period 4               5 Weekly Single   Weekly Married 6 Amount Base Percent   Amount Base Percent 7 $138 $0.00 10%   $303 $0.00 10% 8 $200 $6.20 15%   $470 $16.70 15% 9 $696 $80.60 25%   $1,455 $164.45 25% 10 $1,279 $226.35 28%   $2,272 $368.70 28% 11 $3,338 $802.87 33%   $4,165 $898.74 33% 12 $7,212 $2,081.29 35%   $7,321 $1,940.22 35% 13               14 TABLE 2- BIWEEKLY Payroll Period 15               16 Bi-Weekly Single   Bi-Weekly Married 17 Amount Base Percent   Amount Base Percent 18 $276 $0.00 10%   $606 $0.00 10% 19 $400 $12.40 15%   $940 $33.40 15% 20 $1,392 $161.20 25%   $2,910 $328.90 25% 21 $2,559 $452.95 28%   $4,543 $737.15 28% 22 $6,677 $1,605.99 33%   $8,331 $1,797.79 33% 23 $14,423 $4,162.17 35%   $14,642 $3,880.42 35% 24               25 TABLE 3- SEMIMONTHLY Payroll Period 26               27 Semi-Monthly Single   Semi-Monthly Married 28 Amount Base Percent   Amount Base Percent 29 $299 $0.00 10%   $656 $0.00 10% 30 $433 $13.40 15%   $1,019 $36.30 15% 31 $1,508 $174.65 25%   $3,152 $356.25 25% 32 $2,772 $490.65 28%   $4,922 $798.75 28% 33 $7,233 $1,739.73 33%   $9,025 $1,947.59 33% 34 $15,625 $4,509.09 35%   $15,863 $4,204.13 35% 35               36 TABLE 4- MONTHLY Payroll Period 37               38 Monthly Single   Monthly Married 39 Amount Base Percent   Amount Base Percent 40 $598 $0.00 10%   $1,313 $0.00 10% 41 $867 $26.90 15%   $2,038 $72.50 15% 42 $3,017 $349.40 25%   $6,304 $712.40 25% 43 $5,544 $981.15 28%   $9,844 $1,597.40 28% 44 $14,467 $3,479.59 33%   $18,050 $3,895.08 33% 45 $31,250 $9,017.98 35%   $31,725 $8,407.83 35%

Excel tables to the web >> Excel Jeanie HTML 4

So in this example if an employee was being paid $65,520 per year which would be $2,520.00 Bi-Weekly. The federal tax would be $232.60 if the employee was Married with 0 allowances being paid Bi-Weekly. I would like to have this calculated with an excel formula using the tax tables in this example.

Any help would be highly appreciated.

In an effort to speed up the payroll process, I did up an Excel workbook with in/out time columns that calculates total hours worked in a 14-day period, and the Gross $ Total for the pay period based on the wage.

However, I want to take the Gross Total and perform the calculations that the Canada Revenue Agency online calculator (http://www.cra-arc.gc.ca/esrvc-srvce.../pdoc-eng.html) does directly in the Excel workbook. Since these are hourly positions, rather than salaried ones, the hours may change from pay-period to pay-period. As such, it would be time consuming to need to go back to the on-line calculator every two weeks to redetermine deductions.

The following data is an example of what I have to work with...

Wage: $9.00/hr
Hours per Week: Variable, determined by timesheet input (max 40)

These are the rates (on the Gross) that I believe the CRA calculator is deducting (as of April 1, 2009)...
CPP rate: 4.95%
EI rate: 1.73%
Fed. Tax.: 15% (Minimum)
Prov. Tax.: New Brunswick?


Simplified Example: Considering a New Brunswick incorporated small business in the minimum bracket with no other deductions than mandatory... What would be the deductions from a $720.00 bi-weekly pay cheque (80 hrs @ 9.00/hr). This can be easily found on the website above. Now how do I make Excel do that?

Hope this wasn't too confusing...


Thanks!

In an effort to speed up the payroll process, I did up an Excel workbook with in/out time columns that calculates total hours worked in a 14-day period, and the Gross $ Total for the pay period based on the wage.

However, I want to take the Gross Total and perform the calculations that the Canada Revenue Agency online calculator (http://www.cra-arc.gc.ca/esrvc-srvce.../pdoc-eng.html) does directly in the Excel workbook. Since these are hourly positions, rather than salaried ones, the hours may change from pay-period to pay-period. As such, it would be time consuming to need to go back to the on-line calculator every two weeks to redetermine deductions.

The following data is an example of what I have to work with...

Wage: $9.00/hr
Hours per Week: Variable, determined by timesheet input (max 40)

These are the rates (on the Gross) that I believe the CRA calculator is deducting (as of April 1, 2009)...
CPP rate: 4.95%
EI rate: 1.73%
Fed. Tax.: 15% (Minimum)
Prov. Tax.: New Brunswick?


Simplified Example: Considering a New Brunswick incorporated small business in the minimum bracket with no other deductions than mandatory... What would be the deductions from a $720.00 bi-weekly pay cheque (80 hrs @ 9.00/hr). This can be easily found on the website above. Now how do I make Excel do that?

Hope this wasn't too confusing...


Thanks!

I have created a small spreadsheet to derive the Gross Pre-tax Income when all that is known is the Net Income, and the Tax Brackets and Rates.

In the example that follows, when the known Net Income is entered in D18, E18 displays the correct Gross Income.

The Tax Brackets and Rates are not static, but are in turn drawn from a set of 13 different Provincial rate groups using an INDIRECT function.

This was the best solution I could think of and it is accurate, but if possible, I'd like to condense this a bit more if there is a simple way to do it that I have overlooked and am very receptive to any ideas that may be obvious to others with more experience.

Thank you for your consideration,
FF.




Sheet1

* A B C D E F 1 Threshold Rate * Cummulative Tax Cummulative Net Gross Sums 2 * * * * $0.00 FALSE 3 $0 0.00% * $0.00 $10,320.00 FALSE 4 $10,320 15.00% * $1,011.15 $16,049.85 FALSE 5 $17,061 20.60% * $1,057.29 $16,227.71 FALSE 6 $17,285 23.26% * $3,884.78 $25,556.22 FALSE 7 $29,441 20.06% * $5,143.54 $30,572.46 FALSE 8 $35,716 22.70% * $6,280.81 $34,445.19 $56,230.71 9 $40,726 29.70% * $15,400.79 $56,032.21 FALSE 10 $71,433 32.50% * $18,656.97 $62,795.03 FALSE 11 $81,452 36.50% * $18,862.10 $63,151.90 FALSE 12 $82,014 38.29% * $25,591.18 $73,996.82 FALSE 13 $99,588 40.70% * $36,448.32 $89,815.68 FALSE 14 $126,264 43.70% * $2,166,270.95 $2,833,729.05 FALSE 15 $5,000,000 * * * * * 16 * * * * * * 17 * * * Net (Entered): Gross (calculated): * 18 * * * $45,345.00 $56,230.71 *
Spreadsheet Formulas Cell Formula F2 =IF(AND ($D$18>E2,$D$18< (E3+0.01) ) ,E2+D2+ ( ($D$18-E2) / (1-B3) ) ) D3 =( (A4-A3) *B3)+D2 E3 =( (A4-A3) - ( (A4-A3) *B3) )+E2 F3 =IF(AND ($D$18>E3,$D$18< (E4+0.01) ) ,E3+D3+ ( ($D$18-E3) / (1-B4) ) ) D4 =( (A5-A4) *B4)+D3 E4 =( (A5-A4) - ( (A5-A4) *B4) )+E3 F4 =IF(AND ($D$18>E4,$D$18< (E5+0.01) ) ,E4+D4+ ( ($D$18-E4) / (1-B5) ) ) D5 =( (A6-A5) *B5)+D4 E5 =( (A6-A5) - ( (A6-A5) *B5) )+E4 F5 =IF(AND ($D$18>E5,$D$18< (E6+0.01) ) ,E5+D5+ ( ($D$18-E5) / (1-B6) ) ) D6 =( (A7-A6) *B6)+D5 E6 =( (A7-A6) - ( (A7-A6) *B6) )+E5 F6 =IF(AND ($D$18>E6,$D$18< (E7+0.01) ) ,E6+D6+ ( ($D$18-E6) / (1-B7) ) ) D7 =( (A8-A7) *B7)+D6 E7 =( (A8-A7) - ( (A8-A7) *B7) )+E6 F7 =IF(AND ($D$18>E7,$D$18< (E8+0.01) ) ,E7+D7+ ( ($D$18-E7) / (1-B8) ) ) D8 =( (A9-A8) *B8)+D7 E8 =( (A9-A8) - ( (A9-A8) *B8) )+E7 F8 =IF(AND ($D$18>E8,$D$18< (E9+0.01) ) ,E8+D8+ ( ($D$18-E8) / (1-B9) ) ) D9 =( (A10-A9) *B9)+D8 E9 =( (A10-A9) - ( (A10-A9) *B9) )+E8 F9 =IF(AND ($D$18>E9,$D$18< (E10+0.01) ) ,E9+D9+ ( ($D$18-E9) / (1-B10) ) ) D10 =( (A11-A10) *B10)+D9 E10 =( (A11-A10) - ( (A11-A10) *B10) )+E9 F10 =IF(AND ($D$18>E10,$D$18< (E11+0.01) ) ,E10+D10+ ( ($D$18-E10) / (1-B11) ) ) D11 =( (A12-A11) *B11)+D10 E11 =( (A12-A11) - ( (A12-A11) *B11) )+E10 F11 =IF(AND ($D$18>E11,$D$18< (E12+0.01) ) ,E11+D11+ ( ($D$18-E11) / (1-B12) ) ) D12 =( (A13-A12) *B12)+D11 E12 =( (A13-A12) - ( (A13-A12) *B12) )+E11 F12 =IF(AND ($D$18>E12,$D$18< (E13+0.01) ) ,E12+D12+ ( ($D$18-E12) / (1-B13) ) ) D13 =( (A14-A13) *B13)+D12 E13 =( (A14-A13) - ( (A14-A13) *B13) )+E12 F13 =IF(AND ($D$18>E13,$D$18< (E14+0.01) ) ,E13+D13+ ( ($D$18-E13) / (1-B14) ) ) D14 =( (A15-A14) *B14)+D13 E14 =( (A15-A14) - ( (A15-A14) *B14) )+E13 F14 =IF(AND ($D$18>E14,$D$18< (E15+0.01) ) ,E14+D14+ ( ($D$18-E14) / (1-B15) ) ) E18 =ISNUMBER(F2)*F2+ISNUMBER(F3)*F3+ISNUMBER(F4)*F4+ ISNUMBER(F5)*F5+ISNUMBER(F6)*F6+ ISNUMBER(F7)*F7+ISNUMBER(F8)*F8+ ISNUMBER(F9)*F9+ISNUMBER(F10)*F10+ ISNUMBER(F11)*F11+ISNUMBER(F12)*F12+ ISNUMBER(F13)*F13+ ISNUMBER(F14)*F14

Excel tables to the web >> Excel Jeanie HTML 4

Say, we're paying a guy 20,010 a month. I have to withhold FICA at 7.65% up to $120,000 of his gross salary. After he hits 120,000 the rate goes down to 1.45%


A B C D E F G
1 20,010 20,010 20,010 20,010 20,010 20010 20010 etc.....
2 fica formula??

This was my formula on A2,but it is not correct.

=iif(sum($a1:a1)=

I need a formula for excel that would allow me to calculate the gross income required to produce a certain net income. I will know the net income and the marginal brackets and rates.

For example, here are a set of marginal rates (there is no tax below $10,320), and suppose my net income was $58,432. I know how to manually figure this out, but how can I phrase it so that it will be easy to deal with in a spreadsheet with multiple sets of tax rates and widely varying net incomes?


Threshold ||| Rate ||| dRate $10,320 ||| 15.00% ||| 15.00% $17,061 ||| 20.60% ||| 5.60% $17,285 ||| 23.26% ||| 2.66% $29,441 ||| 20.06% ||| -3.20% $35,716 ||| 22.70% ||| 2.64% $40,726 ||| 29.70% ||| 7.00% $71,433 ||| 32.50% ||| 2.80% $81,452 ||| 36.50% ||| 4.00% $82,014 ||| 38.29% ||| 1.79%