🏆 Paycheck Deduction Transaction Generator

[MidnightPrzm]

I figured out what the issue was. I left the first row blank and somehow that messed up the entire formula. I added just a date to the top row and the formula works now. I originally left the top row empty so I could group and minimize past jobs by time frame, and I can’t minimize for better organization if there is info on the top row. In case anyone runs into any issues in the future.

[Ugochukwu]

I just finished setting up my deductions. Thank you so much for this! This must have been a herculean effort

[mssethi]

Which numbers do you use for budgeting? A little confused on that.

[jpfieber]

I’m not doing much budgeting these days, but when I did I primarily did the net pay. Depending on the types of deductions you have, there could be reasons to want to budget others I would imagine.

[mssethi]

Thank you for your response. If you budget netpay and budget the deductions, then the cash flow reflected is off…anyone have any thoughts on how to set it up? for example all of these deductions would be against a budget that shows net pay as income and cash flow would be understated:

[dmetiller]

I budget off of gross pay, and I use this sheet to track any paycheck deductions from that gross pay. Not sure if that answers your question, but I’ve become a fan of budgeting off of gross pay. Gives me a greater appreciation of just how much of our income goes to various benefits and taxes.

[mssethi]

Thank you for your response…can you please provide guidance on how you have it setup for gross pay…for example…

Gross Pay Budget - 100/week
Pay Actual Deposit - $75/week

You will always have a shortage against the budgeted amount??
The copied entry for gross pay from the generator does not add to the actual column for some reason?

How do you have that setup?

[dmetiller]

Not sure that I completely understand the issue that you’re raising. Maybe it’s because I use the Savings Budget sheet to do my budgeting? But my goal every month is to have my Actual Income equal my Budgeted Expenses. Where Actual Income equals my Gross Income and where Budgeted Expenses includes any paycheck deductions, incurred expenses and money that I allocate to savings. So:

Gross Income: $100 (Actual Income)

Budgeted Expenses ($100):
Payroll Deductions: $20
Actual Expenses: $70
Savings: $10

Hope that makes sense.

[mssethi]

Yes, thank you…that helped…also figured out that there was an issue with the copy paste from the generator date format so the gross income entry was not coming through.

[m00]

OK I gave in and tried this after looking at it last year.
I think I like having total Paycheck values, and I also like tracking taxes and other deductions. So now I am sold.

Nice work!

I wanted to automate/dummy-proof inserting the values into the Transactions sheet, so I wrote a script for it.
Don’t want to hijack your thread, so it is here.

[m00]

@Chad_George

@jpfieber

Columns I through L hold the standard deductions that most people have taken from their Gross Pay. Enter them as negative values, since that’s how we want them to show up on our transaction sheet. If any of them don’t apply to you, leave it blank.

Is there any way to format the cells so that these are automatically negative values?

So I have started using this, and for some reason typing negative numbers seems annoying to me.
I changed a couple of formulas, and added a Deductions? checkbox (TRUE or FALSE) on the second row to tell if that value should be taken as a negative or a positive. So now I can just enter all of the numbers as positive, and it takes care of it.

Here are the steps I did. It is kind of hacky, but it works for me:

1. Un-merge C2:AF2

2. I2:AF2: make each cell TRUE or FALSE for a deduction or not (use Data Validation check boxes if you want it fancy).
   I leave any unused columns blank.

3. H3 replace with:

={"PayNet"; ARRAYFORMULA(IF(G4:G99<>"", -(G4:G99 + MMULT(IF(I4:AF99<>"", I4:AF99, 0), TRANSPOSE(IF(I2:AF2=TRUE, -1, 1)))), ""))}

4. AI4 replace with:

=ARRAYFORMULA(IFNA(TRANSPOSE(IF($F4<>"", IF(VLOOKUP(AO4, $F4:$AF, {2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27}, TRUE) <> "", VLOOKUP(AO4, $F4:$AF, {2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27}, TRUE) * IF(G$2:AF$2=TRUE, -1, 1), ""), ""))))

5. (optional) Conditional formatting range I4:AF99
   Custom Formula =IF(I$2=TRUE,AND(ISNUMBER(I4), I4 < 0))
   Background: Bright Fuscia

6. (optional) Conditional formatting range C3:AF3
   Custom Formula =C2=TRUE
   Background: Reddish

7. (optional) Conditional formatting range C3:AF3
   Custom Formula =AND(C2=FALSE, NOT(ISBLANK(C2)))
   Background: Greenish

The output:

[jpfieber]

Nice option! For anyone doing this, keep in mind that not all ‘deductions’ are negative. On a couple rare instances (a bonus and an adjustment to a deduction) I’ve had positive line items listed on my paycheck ‘deductions’. You’ll want to make sure this change works if something like that happens.