Smart Asset Paycheck Calculator
Our take
I'm working on trying to create my own paycheck calculator in Excel by using the format of the calculator located at SmartAsset Paycheck Calculator. I have a cell with a drop-down list of the pay frequency with the options as, Daily, Weekly, Bi-Weekly, Semi-Monthly, Monthly, Quarterly, Semi-Annually, and Annually. I have another cell where I want to calculate the gross pay by multiplying the hourly pay rate by the amount of hours per pay frequency based upon the selection of the cell. What would be the best way to do this? I looked into the functions "CHOOSE" or "MATCH" or just the simple "IF" with nested if's and of course i would be using the SUM function in all of this. I also plan to use the cell with location info to take into consideration the state income taxes.
Basically, i'm asking for help on how to calculate values with information that contains text with the text giving the value based on the user's input of that specific cell.
| Flat Rate Tax States | Rate | |
|---|---|---|
| Arizona | 2.50% | |
| Colorado | 4.25% | |
| Georgia | 5.19% | |
| Idaho | 5.70% | |
| Illinois | 4.95% | |
| Indiana | 3.00% | |
| Iowa | 3.80% | |
| Kentucky | 4.00% | |
| Louisiana | 3.00% | |
| Massachusetts | 5.00% | |
| Michigan | 4.25% | |
| Mississippi: 4.4% (first $10,000 exempt) | 4.40% | |
| North Carolina | 3.99% | |
| Ohio: 2.75% (on income above $26,050) | 2.75% | |
| Pennsylvania | 3.07% | |
| Utah | 4.55% | |
| Graduated Tax Rates | Low | High |
| Alabama | 2.00% | 5.00% |
| Arkansas | 0.00% | 3.90% |
| California | 1.00% | 12.3% (plus 1% over $1M) |
| Connecticut | 2.00% | 6.99% |
| Delaware | 0.00% | 6.60% |
| District of Columbia | 4.00% | 10.75% |
| Hawaii | 1.40% | 11.00% |
| Kansas | 5.20% | 5.58% |
| Maine | 5.80% | 7.15% |
| Maryland | 2.00% | 5.75% |
| Minnesota | 5.35% | 9.85% |
| Missouri | 2.00% | 4.80% |
| Montana | 4.70% | 5.90% |
| Nebraska | 2.46% | 4.55% |
| New Jersey | 1.40% | 10.75% |
| New Mexico | 1.50% | 5.90% |
| New York | 4.00% | 10.90% |
| North Dakota | 0.00% | 2.50% |
| Oklahoma | 0.25% | 4.50% |
| Oregon | 4.75% | 9.90% |
| Rhode Island | 3.75% | 5.99% |
| South Carolina | 0.00% | 6.20% |
| Vermont | 3.35% | 8.75% |
| Virginia | 2.00% | 5.75% |
| West Virginia | 2.22% | 4.82% |
| Wisconsin | 3.54% | 7.65% |
| Washington | 7% on cap gains over $250,000 | |
| New Jersey | 4% on only interest and dividend income | |
| No income tax states | ||
| Alaska | ||
| Florida | ||
| Nevada | ||
| South Dakota | ||
| Texas | ||
| Washington | ||
| Wyoming |
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| Pay Frequency | Bi-Weekly | Type | Hourly | |||
| Dependents | 0 | Hourly Wage | $30.00 | |||
| Location | Sterling, VA | Hours (Per Pay Period | 80 | |||
| Allowances | Salary (Per Year) | $62,400.00 | ||||
| Federal | 1 | Overtime Hourly Wage | $45.00 | |||
| State | 1 | Overtime Hours (Per Pay Period) | 0 | |||
| Local | 1 | |||||
| Gross Paycheck | $2,400.00 | |||||
| Pre-Tax Deductions | Taxes | |||||
| Deduction Name | Calculation Method | Deduction Amount | Amount | Federal Income | ||
| Medical Insurance | $ Fixed Amount | $110.00 | $110.00 | State Income | ||
| Dental Coverage | $ Fixed Amount | $10.00 | $10.00 | Local Income | ||
| Vision Insurance | $ Fixed Amount | $10.00 | $10.00 | FICA and State Insurance Taxes | ||
| 401k | % of Gross Pay | $10.00 | $240.00 | Social Security | ||
| Long Term Disability Insurance | Medicare | |||||
| Life Insurance | State Disability Insurance | |||||
| Commuter Plan | State Unemployment | |||||
| FSA | State Family Leave Insurance | |||||
| HSA | State Workers Comp Insurance | |||||
| Total | $370.00 | Pre-Tax Deductions | ||||
| Medical Insurance | $110.00 | |||||
| Post-Tax Deductions | Dental Coverage | $10.00 | ||||
| Deduction Name | Calculation Method | Deduction Amount | Amount | Vision Insurance | $10.00 | |
| 401k | $240.00 | |||||
| Long Term Disability Insurance | $0.00 | |||||
| Total | Life Insurance | $0.00 | ||||
| Commuter Plan | $0.00 | |||||
| FSA | $0.00 | |||||
| HSA | $0.00 | |||||
| Post-Tax Deductions | $0.00 | |||||
| Net Total | $2,030.00 |
I've tried things such as the following but just couldn't seem to get it to work properly.
IF(B2="Daily", SUM(G3*8), IF(B2="Weekly", SUM(G3*40), IF(B2="Bi-Weekly", SUM(G3*80), IF(B2="Monthly", SUM(G3*160), IF(B2="Quarterly", SUM(G3*520), IF(B2="Semi-Annually", SUM(G3*1040), IF(B2="Annually", SUM(G3*2080)))))))
=CHOOSE(MATCH(B2, {"Daily", "Weekly", "Bi Weekly", "Semi Monthly", "Monthly", "Quarterly", "Semi-Annually", "Annually"}, 0), 26, 24, 52)
=CHOOSE(MATCH(G7, {"Bi Weekly", "Semi Monthly", "Weekly"}, 0), 26, 24, 52)
=IF(B2="Daily", G3*8, IF(B2="Weekly", G3*40, IF(B2="Bi-Weekly", G3*80, IF(B2="Monthly", G3*160, IF(B2="Quarterly", G3*520, IF(B2="Semi-Annually", G3*1040, G3*2080))))))
=if(B2="Daily", Choose(1, "Daily", "Weekly", "Bi-Weekly", "Semi-Monthly", "Monthly", "Quarterly", "Semi-Annually", "Annually"))
[link] [comments]
Read on the original site
Open the publisher's page for the full experience