401k Contributions / Calculating Projected Account Growth

[TheInsuranceMan]

Okay everyone, I'm trying to build an excel worksheet for my personal use, that will include my base pay, % of possible 401k contributions, employer match, and then also account for pre-tax deductions, which is my health insurance and hsa contributions.  I also want it to account for taxes taken out of paychecks, to equal what my pay stub does at the end of the month.
Why you may ask?  So I can see how the different 401k contribution elections that I may select affect my net pay. 
And then, I want it to be able to calculate compound interest to a defined date, say ages 45, 50, and 55.  I've plugged some numbers in and had it close to how I want to, but I cannot get the future value function to work correctly for me. 

[dandarc]

Post your formula, then tell us specifically what is wrong with the FV result.

[MDM]

You could copy and paste whatever you find useful from the case study spreadsheet: http://forum.mrmoneymustache.com/ask-a-mustachian/how-to-write-a-%27case-study%27-topic/msg274228/#msg274228

Much of what you describe is there in one form or another.

[TheInsuranceMan]

So, I have my base pay in cell B2
Column A runs down the % of the contribution made by me, 6% - 30%
Column B is contribution in dollars, this is base pay * contribution %
Column C is employer contribution, set at 3%, or .03 for calculation purposes
Column D is total contribution, mine + employers
Column E is Yearly contribution - column D * 12

So, I'm assuming my formula would be fv(1+.06)360,D4
That would be 6% interest, 360 months (30 years),D4 (total monthly contributions)
I'm getting a -6 trillion number :)
I'm usually very proficient in excel, so something isn't clicking in my head this morning.

I also have taxes/insurance, dependent care contribution, and HSA contribution, to help come to my actual net paycheck amount, but that isn't the issue at hand.

[TheInsuranceMan]

So, I have my base pay in cell B2
Column A runs down the % of the contribution made by me, 6% - 30%
Column B is contribution in dollars, this is base pay * contribution %
Column C is employer contribution, set at 3%, or .03 for calculation purposes
Column D is total contribution, mine + employers
Column E is Yearly contribution - column D * 12

So, I'm assuming my formula would be fv(1+.06)360,D4
That would be 6% interest, 360 months (30 years),D4 (total monthly contributions)
I'm getting a -6 trillion number :)
I'm usually very proficient in excel, so something isn't clicking in my head this morning.

I also have taxes/insurance, dependent care contribution, and HSA contribution, to help come to my actual net paycheck amount, but that isn't the issue at hand.

Appears if I change that to fv(.06/12)360,D4, I get -303,701.16.  Now, that amount looks correct, but the negative is throwing my for a loop.

[dandarc]

that's 106% interest - just enter .06.  Then PMT and PV should both be negative (or 0) for this purpose.

[Cromacster]

To make the final amount you receive accurate you will also need to take into account the IRS withholding calculations.  Read through IRS Pub 15, it contains the tables and methods for calculating payroll withholding.  I think the tables start on page 45 or 46.

[MDM]

See https://support.office.com/en-us/article/FV-function-2eef9f44-a084-4c61-bdd8-4fe4bb1b71b3 and https://support.office.com/en-us/article/PV-function-23879d31-0e02-4321-be01-da16e8168cbd for, respectively, FV syntax and the overview of all Excel financial function entries (including why some things get entered as negative values).

[MDM]

To make the final amount you receive accurate you will also need to take into account the IRS withholding calculations.  Read through IRS Pub 15, it contains the tables and methods for calculating payroll withholding.  I think the tables start on page 45 or 46.

+1

See http://forum.mrmoneymustache.com/taxes/best-way-to-calculate-w-4-exemptions-for-2016/ for some discussion on that topic.

[TheInsuranceMan]

that's 106% interest - just enter .06.  Then PMT and PV should both be negative (or 0) for this purpose.

Perfect.  The FV function I read about had it at 1+interest percent, so 1+.06, or whatever you wanted to use, which is why it was throwing it off.  So, that helped.
And I'm not really looking for exact numbers, just estimated.  I understand withholding will play with some of this, but if I can say at 10% contribution level, my estimated paycheck will =x, and by age xx, I will have approximately $xxx,xxx, I'm satisfied with that.  I suppose, I could go a step further and add in my current 401k balances that I have, as that will greatly affect the compound interest earned on the entire balance.

[dandarc]

There is a detailed explanation of the why behind the signs of the PV, PMT, and FV in spreadsheets and financial calculators at the below link:

https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=58261

Boils down to the equation that is being solved (with a 0 rate) is:

PV + PMT * NPER + FV = 0

So PV + PMT * NPER = -FV

Follows that for an investment, PV (current balance) and PMT (additions) should have the same sign, and both will be opposite the sign of FV.

For a loan, PV (current balance) and PMT (payments) should have opposite signs, and FV will be 0 if your PV / PMT / NPER pay the loan exactly, have the same sign as PMT if you have a remaining balance, or have the same sign as PV if you pay too much.