401(k) Calculator: Product Information and Directions
|Download the 401k Calculator Demo||List of Most All Free 401k Calculators on the Web||Go to the main 401(k) Self-management Information Page||Menu of Retirement Planning Software|
(is listed below. The financial planning software modules for sale are on the right-side column)
Confused? It Makes More Sense if You Start at the Home Page
Discounts for Financial Advisers
Questions about Personal Finance Software? Call (503) 309-1369 or Send E-mail to email@example.com
Free Downloads and Money Tools
|401k calculator prices and ordering information are about a third the way down this page
Put your 401(k) plan under the microscope of reality to see what's really going on, and how it may affect your future retirement plans.
Some of the free calculators (we found, evaluated, and listed most of them in one place here) are just about gathering your information to see if it's worth having a broker or insurance agent contact you about buying something, or rolling your 401k over to their management.
So in addition to being a futile market for advisors to canvass, the usual financial services shenanigans are always going on too.
This 401(k) investing calculator is unique, because it performs all of the following functions, in addition to performing all of the usual basic functions of every other 401k software program:
• It calculates how long your 401(k) will last more accurately than most programs. If you're interested in accuracy, then having it based in Excel is the only way to see it, because just about every number, where it came from, and the overall flow, is all transparent. If a financial calculator is not in Excel, then it's basically a "black box" where you'll input data, and then the box spits out an answer - with zero clues on how it was calculated. So you have no way of knowing how anything was calculated with those types of code-based money calculators.
All of our financial software calculations flow from left to right, just like reading, with headings on the columns to tell you exactly what's going on. Little-to-nothing is left out, so you can trace most all numbers back to their input, and/or verify the calculations with a hand calculator each step of the way. You can only do this via Excel. There's also an unprotected Scratch Pad sheet for you to do this work on.
• It works like the usual retirement planner does, where you'd input annual withdrawals to see how long investment money will, last given various assumptions (AKA gap funding).
Most 401(k) software just shows the accumulation phase, and ignores the distribution phase. This does it all.
• You can choose to input contributions automatically (by just inputting base salary and contribution rate), or you can bypass all of that and manually input your actual bi-weekly contribution amount.
• It automatically limits contributions to the annual maximum amounts.
• Employee participant contribution and employer matching contribution calculations are always kept separate, so you can see the true value of your matches in every year.
There's also a column and graph showing the annual percentage the participant's account is of the whole combined account, and how much the matches are to the whole account.
If you don't want to deal with two separate accounts, or if you don't receive matching money, then you can ignore the whole matching section and everything will work just like the usual 401(k) software.
• You can set the tax rates to be whatever you want them to be in every year for maximum control and accuracy.
• You have total control over employee participant and employer matches and contributions (so they can be different in every year). So it allows you to model all of the different participant and employer contribution modes.
So between all of the "switches" you can model any combination of automatic mode contributions. The different match combinations vary because you have all of the following working together: Employer matches less than 100%, equal to 100%, then there's a two-tiered compensation matching option, then there's the whole Catch Up Provisions layer on top of that. Then this is all constrained by the annual maximums imposed by the IRS.
Or you can just set everything to manual contributions, and input exactly what you want. Then this 401(k) calculator is unique because it allows you to change that in any year using the manual override columns on the Calculations sheets.
So you'll have total control over modeling all forms of savings contributions going into your 401(k) plan.
All of this allows you to model most all 401(k) plans as closely as possible, to get a realistic picture of what will probably happen in the future (up to 75 years).
• It has two-tier levels of employer matches in automatic mode to help account for compensation plans that use this contribution strategy.
• Contributions work bi-weekly, just like life in the Real World (in manual input mode).
• It will automatically deal with Catch-up Provisions.
• It will automatically deduct investment front-end loads from contributions.
• You can account for two types of fees, charges, and expenses: Annual fees as a percentage of assets, and then set dollar amounts of annual charges and expenses. Between these two inputs, you can account for most all money being siphoned off by all of the players.
• It calculates and displays all annual 401(k) fees and expenses, and then gives many meaningful totals and ratios on the Results sheets.
Then it shows what the annual running cumulative expenses are, then again as a percentage of both money accumulated, and money contributed.
So if you're interested in putting your 401(k) fees, commissions, charges, and expenses all under the microscope of truth for comparison purposes, this 401(k) analysis tool performs all of these functions.
• It automatically calculates and displays premature withdrawal taxes and the resulting tax penalties.
• You can automatically inflate annual withdrawals to account for COLA inflation, and/or you can have a different withdrawal amount in every year.
• It has a Minimum Required Distribution mode (AKA MRD, RMD, or MDIB) where you can set distributions to pay out like this, so you can estimate how much money you'd have at your life expectancy if you'd only withdraw the minimum amounts annually after age 70½.
When the Traditional section is set to MRD, the 401k Roth calculator still calculates withdrawals normally, so you can make those comparisons too.
• It displays all of the information other 401(k) software does, and much more (like calculating normal withdraw taxes, early withdrawal penalties and taxes, accounting for most loans, and tax savings on contributions).
It displays annual numbers for all years both in table and graphical form. It displays what percentage the dollar amount of employer matches are to the whole account, and then to just the participant contributions. It also displays most all of these numbers on a cumulative basis too. Everything you want to see is there.
• It will automatically deduct taxes from annual withdrawals, so when you input $X amount of money you plan to spend in a certain year, then that's how much you'll get to spend, because more than that is actually withdrawn to pay the taxes. So the actual Real World amount is withdrawn that's needed to fund your income goal withdrawal.
Most 401k software doesn't do this, because they assume you're going to get the money to pay these taxes from somewhere else. So they incorrectly overestimate your 401k balance during the withdrawal phase. This 401k calculator more correctly forecasts how long your 401(k) nest egg may last during retirement because of these functions.
If you don't want to account for it all like this, then you can just use the manual overrides in conjunction with the tax inputs fields. So you can make it work like most other retirement calculators too.
• It estimates and displays the costs of borrowing from your 401k plan. You can even model different loan interest rates in every year, and making additional annual principal payments to your 401(k) loans in every year.
All loan repayments, with interest, get added back to the 401k balance for five years. The remaining balance in year six is then automatically treated (taxed and penalized) as a premature distribution. So it accounts for and shows all annual loan cash flows affecting the account balance.
You can also model using 401k loans for a down payment on a principal residence using the manual overrides (these types of loans don't need to be paid back in five years).
• It automatically performs a side-by-side Roth 401(k comparison, so you can see if it's better to go Traditional or Roth. It does this with and without the value of the additional "taxes saved" account in the traditional 401(k).
In other words, the value of the traditional 401(k)'s initial tax savings on participant contributions is accounted for and added back to it, so you can make a more realistic comparison.
Most retirement calculators just omit all of this because it's too complex to program, so it makes it look like going Roth will always win.
This does it more accurately, so you'll see Roth losing out much more often, depending on your input.
• It also has a switch to allow you to control the usage of the Traditional 401(k) tax savings account. If you choose not to use it to fund withdrawals, then you can turn it off. Then the only place it's accounted for is on the Roth vs. Trad Comparisons sheet.
But if you turn it on, then it gets added to the Traditional 401k account balance, and thus is available to be part of the three accounts used to fund withdrawals (your money, match money, and your tax savings account).
So you can choose to include the tax savings account to fund withdrawals. Or you can choose not to, and it will grow separately.
If you choose to, then you'd also choose the percentage of withdrawals that come from the tax savings account (then the rest come from the participant's account). These inputs give you total control over how the tax savings account is used.
Plus, if you don't think it's correct to apply a growth rate to the tax savings account, because you've spent all of that money, then you can set the rate of return for it to be 0%.
This performs Roth comparisons automatically, and allows you to account for the contribution tax savings. Most retirement software just ignore the value of the tax savings from the Traditional, so they're inaccurate when it comes to accounting for the true value of a traditional 401k retirement plan. If you don't account for its true value in the first place, then all comparisons are meaningless.
• It allows you to stop showing numbers at any age to keep it simple and real. For estate planning, you can use this in conjunction with the inputted life expectancy, to show having so much capital left over at an assumed ending age (to pass on to heirs).
• It allows you to simulate starting retirement in any year, not just at age 65 like some 401(k) software does. Its only limitation is that you can't start taking withdrawals in the first year, so you'll have to wait until year two. If you're that close to retirement, then using 401(k) analysis software is the wrong thing to do in the first place, because You should be using actual retirement software.
• It has three separate non-protected Results sheets, so you can have total control over printing and presenting the results. You can also make all of the new charts and graphs you want to. Try printing and/or trying to change anything on any of the code-based rinky-Dinkytown retirement calculators, and then you'll see its power.
• It also has a non-protected "Scratch Pad" sheet to takes notes on and copy data to if needed.
• It has complete transparency. This means you can trace where all of the annual numbers came from. This is in contrast to it being the usual "black box," that just displays the resulting numbers, and hides everything else in code.
• It has two sets of inputs for everything important - one set for the accumulation phase (when working and making contributions) and one for the distribution phase (when retired and taking withdrawals).
Being able to accurately model your post-employment 401(k) is the only way to determine how much better off you'll be when you roll it over to an IRA and manage it yourself using a discount broker.
• It performs the usual Monte Carlo simulations, by iterating the rate of return, COLA inflation, and taxes. Most others only perform the most rudimentary simulation by only iterating the investment return rate. So this will do a good job of "stress-testing" your 401(k) plan.
• It displays both calculated life expectancy using IRS mortality tables, plus you'd also input a maximum life expectancy age separately. Then both are used for comparison purposes on the Results sheets. This is needed because you may live longer than you planned.
• It displays more relevant information than other 401(k) calculators on the three Results sheets. There's too much to list here, see the free demo.
It displays how much participant contributions and employer matches are in daily, weekly, bi-weekly, monthly, and annual dollar amounts.
On each of the two main Results sheets, there's around six small tables, one large table, and a couple dozen graphs. These sheets are not protected so you can change anything you want and make all of the new tables and graphs you want to.
Then it has another results sheet solely for comparing Traditional 401k vs. Roth 401(k) investing, with two big tables and five charts.
• The main inputs can be copied and pasted in one block so it's easy to deal with expired versions (or make an unlimited number of new versions, like current vs. proposed retirement plans). In other words, it's only one step to copy your input from an old version into a new one.
• It has a 75-year window, which is longer than most other 401k software (so it will take a 25-year old to age 100).
• It also has an input where you tell it to stop displaying numbers, so everything will go blank after a certain year. This cleans up the clutter.
• You can manually override all of the following in every year (both on the participant side and on the match side separately): Contributions, rate of investment return, fees and expenses, average tax rate, loan interest rates and additional principal repayments, and annual withdrawals.
This unique function allows you to control almost every number in every year. So you can do things like model not making contributions, having much higher or lower contributions, realizing 0% or -10% growth in any year, taking a huge withdrawal in one-year to take a world cruise, being in a high or low tax bracket in any year, or not taking any distributions at all. Then you can do all of these things independently of all of the other things going on.
Using the manual overrides on the participant side doesn't affect anything on the matching side, nor using it on the Traditional 401k calculation sheet does not affect anything on the Roth 401k calculation sheet. This allows you to model just about anything.
• You can also use it offline in complete security, privacy, and nobody will ever know what information you're inputting into it. Some free 401k software are all about using your input as sales leads. Putting your personal financial information in the clouds is a security risk.
So yes, you can do a detailed 401(k) analysis, and then integrate just those bottom-line results into the IFP and all of our other retirement software for the accuracy, power, flexibility, control, and functionality.
The far right sheet is all for integrating with the IFP, so you can control as much or as little as you want to.
• Also because it's Excel based, you can easily set it up so it will automatically update when you do a "download" from your 401(k) custodian software.
So with a few minutes of initial setup, then a few minutes here and there as maintenance, you can just click a button, and this 401(k) retirement software will update with your current 401k values.
Buy the 401(k) calculator now with your Visa or MasterCard by calling (503) 309-1369
401(k) Calculator Directions
First, here are some generic directions that apply to most of the personal finance software on this site:
When you're using the program, things go better if you first print out these directions.
Click "Enable Editing" to make it so you can change inputs. If you don't do this, then "nothing will happen" when you input data. Any change is editing (according to MS). This occurs because the program is not in a "Trusted Location." Go to File, Options, Trust Center, to fix that.
The "demo" is the actual financial spreadsheet without the formulas. It doesn't function, so you won't be able to do anything but look at it.
You switch between the sheets (pages) that make up the workbook (AKA spreadsheet) by either clicking on the sheet tab names at the bottom left of the Excel window, or by pressing Control Page Up or Down (pressing the Control key and either the Page Up or Page Down key at the same time).
Basic input concept for most all of these Excel spreadsheets: Input your data into the green-shaded cells of the input sheets (or just the input areas if there are no separate input sheets).
This data flows through the calculation sheets (or just areas if there are no separate calculation sheets), which will then populate the presentation areas (or the sheets to the left of the input sheets), where you look at the results. Then you fix mistakes, repeat, format, and print. If a cell is not colored green, then it's not an input cell, so you won't be able to do anything with it (if it's not an input sheet). If you could, then you may end up damaging the spreadsheet. Don't input, or type over, any formulas on the presentation sheets because then they will no longer change or function when you change your input data. Input cells usually turn a light-purplish-gray when a non-zero value is entered into them.
Before getting started, save an unused copy of all the programs in a separate folder, so you'll always have the original unaltered file somewhere. Then if you do something like delete a formula, you can easily fix it by copying it back using these original files. Then save files you've worked on using a different file name (and into a different folder).
Programs usually come without sample input data, but be sure to delete all of the sample / client input (everything in green-shaded cells) before inputting your data.
If you're seeing this: ####, then either change your Zoom magnification number (using the slider at the bottom right), or make the column width wider. If you see this: #REF! after completing your input, please respond to get a new program immediately. This usually means data was lost either via e-mail, unzipping, or while making a CD.
If a red Error! dialog box comes up saying your input must be confined to a range of values, and your input was within that range, then the problem is that the input cell doesn't accept pennies. Try again rounding the value to the nearest dollar. If it still happens, then send e-mail with the spreadsheet name, sheet tab name, and exact cell number, so it can be changed.
If you're an investing consumer (not a professional financial planner working with clients), then when you read "client, prospect, or they," just think "you." You would be both the advisor and the client (or spouse).
About financial planning software support, where there's more generic directions like above.
About financial planning software integration (sharing data between the different financial planning software modules).
How to send financial plans to clients via e-mail, without sending the whole program is explained on the Excel help page.
You should make CD (or at least USB flash drive) backups of all of your work at least on a monthly basis, and store the CD in a safe location, but not where your computer is. Flash drives are not safe because they're vulnerable to EMP, eventually wear out, and are in general, too flakey. It's critical to back up your data, but not programs. More generic free PC tips are here.
Beginning of the 401(k) Calculator Directions
First a tip if you want to play around with it: Download the 401(k) calculator demo with the program open. Go to the Input sheet on both. On the demo, copy everything from B4 to B57 and paste it into the working version. Then the working version will be the same as the demo, and you can tinker from there.
Most of the inputs are self-explanatory, as the text to the right of them tells what and how to input. Then it uses standard Excel input validation to control the ranges of valid input to help prevent user errors (this is the dialog box that pops up and says, "Error!").
Go to the Input sheet.
Start your input in cell B4, and continue to B57, if needed.
If everything went well and there were no red input errors, then that's all there is to it. Just review the number tables and the graphs on the three Results sheets to the left.
• B4: Input the current year. Calculated ages are based off of this input. You can input an earlier year for 401(k) analysis purposes.
• B5: Input your name the way you want to see it on the other sheets.
• B6: Input your year of birth. If this makes your age not show the way you like, then just change this year until it does.
Note: Both cells B4 & B5 need to have something input into them for the Results sheets to display information.
• B7: Input the last year that you want numbers to display on the Results sheets. So if you input "100," then starting at age of 101, everything will be blank. This is to reduce clutter and the usual long list of meaningless zeros and/or negative numbers other programs display past life expectancy.
• B10: Now's the point in the program to mention that if you don't want to deal with two separate accounts, or if you don't get match money, then you can ignore the whole matching section and everything will work just like regular 401k analyzing software.
Just don't input anything into the following cells, and then you can ignore everything that has to do with matching money: Input sheet: B11 and B23 - 27.
Input the amount you either have now in your 401(k) that's just money that you've contributed yourself. It's fine if this is a new 401k plan and you want to input $0 into cell B10.
B10 is just how much money you have saved up now that's from money you've contributed. It goes to cell F6 of both Calculation sheets.
• B11: Input the amount of money you have in your 401k now that came from employer (match) money. It's okay to leave this $0 too.
If you want to separate these two accounts, and you don't know the exact numbers, then just guess. One thing all financial software can guarantee is that everything shown will not match reality. Because all you're doing is making assumptions about future events that cannot be predicted, it's all just a huge guess anyway. So feel free to guess.
It just goes to cell F84 of both Calculation sheets.
• B12: Input the AVERAGE tax rate you're in right now. Note that most financial advisors want to tell you to use marginal tax rates, because they're much higher. That is very incorrect, so just don't do that.
If you're making about the same amount of money as last year, then your average tax bracket can be estimated by taking last year's amount of tax paid (state, FICA, and Federal combined) and divide that by your gross income on your tax form (this is not your AGI).
For example, if your gross income is $100,000 and you paid $15,000 in federal taxes, $1,000 in state taxes, and $5,000 in FICA taxes, then your average tax bracket was 21%. This is so even though your marginal federal tax rate was over 30%. So use 21% and not 30%+.
So you're more than likely going to end up with a total number less than 20%. If you used marginal, then this will be much higher. Nobody pays marginal rates on all of their income, so just don't be fooled by all of that marginal nonsense.
• B13: Input the gross amount of annual investment return you think you'll average in your 401(k) over the accumulation phase.
If you've read the Main information page about 401(k) management, then you'll know why the growth rate on your 401(k) will be much less than if you did a good job of managing your own money using a discount brokerage account.
So if you don't know, and you're used to getting 7% from self-managing your own investments, then input 5% to 6% here. This is because even if you did manage it well, your 401(k) investment choices are usually so limited, that there will be no way to get the results you realized on your own, having access to most all mutual funds.
If you're still confused, then just input 6% here and move on.
• B14: Input the front-end sales load commission on mutual funds and other investments.
FYI: First, just don't do that. If you're stuck using only loaded mutual funds, and your fund options are bad, and your employer is not matching, then it's our stupid little opinion that you should not invest in your 401k in the first place.
Next, just don't buy loaded mutual funds if your list of options has no-load funds - especially no-load index funds (but not ETFs).
So if you're buying loaded mutual funds for whatever reason, and every time you invest $1 you pay five cents in sales load commission, then just input 5% here.
These can all be manually changed to be whatever you want them to be using column L on the Calculation sheets. So if not all of your investment purchases are loaded, you can reduce them here.
• B15: Input all combined 401(k) fees, charges, and expenses that are a percentage of assets (AUM - assets under management) during the accumulation phase. So if you're getting charged a flat 1% for something, 0.5% for something else, then this is where you account for all of that by inputting 1.5% here.
Note that when you start withdrawing funds, this is when the "accumulation phase" ends and the "withdrawal or distribution phase" begins.
There's another input cell so you can account for fees during the withdrawal phase, because fees usually go down when you terminate employment and no new money is added.
FYI: The "accumulation phase" is AKA as other things, but it's all the same thing: Savings phase, contributing phase, during employment, working, etc.
The "withdrawal phase" is AKA the distribution phase, retirement, spending phase, etc.
The most "technically correct" terms are accumulation phase and distribution phase.
• B16: Input the current year's dollar amount of all fees, charges, and expenses that are a set dollar amount. For example, every custodian will charge you somewhere between $25 and $100 annually just to hold your IRA money - regardless if you have $1,000, or millions in it.
So if there's any set fees that are not a percentage of AUM, then this is where they are accounted for.
There's another input cell so you can account for these fees during the withdrawal phase, because they usually go down or go away when you terminate employment and no new money is contributed.
Note that column AJ on the Traditional 401k Calculations sheet and column AA on the Roth 401k Calculations sheet are where you can manually override annual combined fees, charges, and expenses to make them be whatever you want them to be in any year - and/or for the participant account vs. the match account separately.
Overall, there are two ways to input and account for contributions: Manual mode and Automatic mode.
• B18: This is a drop-down menu where you'd select between the two modes of inputting contributions: Manual mode and Automatic mode. You should decide how you want to account for new money coming into the 401k plan before inputting now.
If you choose Manual mode, then you'll just input the bi-weekly dollar amount of contribution into cell B19, and then you'll be done (other than dealing with the contribution growth rate and matches). This is just the dollar amount of your 401k contribution if you get paid every two weeks. Don't input anything into cells B20 & B21 if in Manual mode, as these values are not used.
In Automatic mode, you'll input your gross salary (compensation base) into cell B20 and then a percentage of that into cell B21, and everything but the contribution growth rate and matches are automatically accounted for. Don't input anything into cells B19 if in Automatic mode, as this value is not used.
• B23: Now to confuse things even more, B23 is a drop-down menu where you select between using just one level of employer matches, or you can use the two-level mode, usually called a "two-tier" contribution system. Most 401k plans do not use a tiered system.
If your plan does not have tiers, then this is simple, just select "N" in cell B23. Then don't input anything into cells B24 through B27 if in Manual mode, as these values are not used.
If your 401(k) plan has two tiers, then select "Y". If your 401k plan has more than more than two tiers, then send e-mail to us saying that, as we need to add that feature. Few plans do, so it wasn't programmed.
Here's how to operate the two-tiered contribution mode: First select "Y" in B23 so that section of the program will activate.
Input the two compensation level dollar amounts into cells B25 & B27. If your 401(k) plan does not two levels (like shown in the demo), then your plan does not have a tiered system, so choose "N" in B23.
Then input the two matching percentage numbers into their respective cells - B24 & B26.
The demo shows the best way to explain this: For gross compensation amounts of $75,000 (B25) or less, the company will match your contributions up to 50% (the percentage you input into cell B24). So if you make $50,000, and contribute 5%, and get a 50% match, your match amount will be around $1,250.
Then for gross compensation amounts above $75,000 but not more than $25,000 (B27), the company will match your contributions up to 25% (B26). So if you make $90,000, then tier 1 will have the employer contribute $1,875, and tier 2 will add another $188.
The bottom line is that you can play with the inputs until the contributions that show in column I of the Calculation sheets display the correct numbers.
If you don't like what's there based on this input, then just input annual contribution amounts into the manual override column L. Just remember that if you're paying a front-end commission on contributions, then these loads need to be deducted from the gross amounts before inputting.
• B29: Input the annual growth rate of contributions. If you think you're going to average a 1% annual raise throughout your career, then input 1% (like the demo shows). Then all contributions will automatically increase by 1% annually until they stop.
Note that both employee and employer contributions are automatically reduced to the annual IRS-set maximums, even if you input more than that. So don't worry about inputting too much, as the program automatically limits both to the maximum amounts.
• B30: Input the first year that you'll make contributions to the 401(k) plan. If you're working and contributing now, then set this to be how old you are now (or how old it says you are in the text to the left in C30).
This is set up like this because some people come to work for a new employer, roll over their old work 401k into the new work plan, then can't or don't want to contribute for a while, then start later. So this allows you the flexibility to control when they start.
• B31: Input the last year of when you estimate you'll stop contributing. Normally this is the year before retirement, so in the demo, retirement is age 65, so the last year of contributions is 64.
• B33: This section is for using the Catch Up Provisions. In summary, if you're over age 50, the IRS says it's okay to add more than the generic maximum contributions, because you're old (and screwed because you should have been rich enough by age 50 to not need to do this, so now you'll have to "run faster to catch up to where you should be").
All this does is this: If your contributions are set so high that they're above the annual maximums, and you're over 50, and you 401k plan has this feature built into to it, then you can add up to another $5,500 annually to your 401k plan.
All of these maximum amounts usually increase by inflation annually (so they'll allow you to model contributing more than the max in years past year 1, even though the IRS may be slow to actually allow you to do that).
So if you're already maxed out with total annual contributions plus matches at $17,500, then by using Catch Ups, you can contribute up to another $5,500, for a total of $23,000.
So choose "Y" from the drop-down menu in cell B33 to activate this function. "N" deactivates it everywhere in the program, so all of these inputs are ignored.
The demo shows how this works - it is activated, and the whole $5,500 is input into cell B34. You can input amounts less than $5,500 into B34, but not more.
All this does is add the amount of B34 to gross contributions annually. Note that it will only do this if you do ALL of the following:
1) B33 has to be set to "Y".
Inputting Into the Distribution / Withdrawal Section
The input section starting in cell B36 is for when you start to take out and spend money from the 401k plan. Nothing you do here effects anything before the year input into cell B38.
• B37: This switch is for modeling what's called minimum distribution mode. This is also a drop-down menu choice. If you want to do this, then choose "Y," else choose "N" here.
If you choose, "N" then everything to do with this is deactivated and thus ignored.
What this is about: For investors that have other income or investment assets that can be uses for living expenses until age 70, you can choose to let the tax advantages ride as long as possible by not withdrawing money from your 401(k). The IRS caught on to this long ago, and set a maximum age for this trick, because it wants its tax money back that it "loaned" to you when you got payroll tax deductions when you contributed.
So starting at age 70½, the IRS requires you to withdraw a set minimum amount annually, so it can get its taxes back from you.
So if you really want to let your money sit for as long as possible, because you don't want nor need it, and you want to pass it all on to your heirs, then you can only do that up until age 70½.
At this year, you must start withdrawing a minimum amount annually. This is why it goes by several different names, but they're all the same thing: MDIB stands for Minimum Distribution Incidental Benefit, MRD stands for Minimum Required Distribution, and RMD stands for Required Minimum Distribution. There's no difference at all between any of these terms, so don't be confused by that.
These minimum annual amounts are just your account balance divided by the inverse of your life expectancy. So if you're 70, the divisor is 27.4, and if you're 100 it's 6.1. It goes down until age 115, where it's 1. The inverse of a divisor of 6.1 is 93.9, so when you're age 100 and in MDIB mode, then 93.9% of your account balance must be withdrawn and taxes in that year. It's that simple.
These tables were different for male and female, but were replaced by a unisex life expectancy table around 2004. So if you see MRD tables for male and female, they're very obsolete, and shouldn't be used for anything.
These future MDIB amounts are just estimates, so even though it calculates far into the future, the actual annual minimum distribution has to be determined using actual values on an actual date. The future numbers are just to calculate estimates for input into retirement planning software to estimate how much annual income goal it can consume. These estimated amounts are shown for each year up until an advanced age that the IRS assumes you will pass away at (currently age 115).
Most qualified plan custodians will do this for you, but only when it actually happens. Errors in their work do occur. This calculator can serve as a check on their work (so you won't get penalized for not taking enough out).
So just remember, numbers shown in financial software are just estimates. When you fill out your taxes, you'll need to use Real World account balances on actual valuation dates.
• B38: Input your retirement age, or age when you plan to start withdrawing money to spend from your 401(k). This is the first year when distributions start being applied on the Calculations sheets.
• B39: Input your estimate of your maximum life expectancy. It was calculated using IRS Unisex Tables automatically, but that's just the average for the U.S. around this time. So if you plan to only live that long, then you'll more than likely live for another decade or two.
Then you'll be in a no-win scenario called "superannuitization." Avoiding this trap is the most important reason to buy and use retirement planning software. The last thing you want to happen when you're old and feeble, and can't work, is to run out of money because of poor prior planning.
The point is that if you've spent all of your money because you thought you were going to croak around the "calculated life expectancy number," then you'll be out of money during these last two decades.
So just don't do that. Input a realistic maximum life expectancy age into cell B39. The usual conservative ages input are 90 or 100. Yes, even if you don't want to live that long, medical technology and the government may force you to, so you'd best plan for that.
So if you're an advisor using this 401(k) software for your clients, never input anything less than age 95 here, or you could end up in hot water many years later if your client outlives your financial plan (because they can and will sue you for that).
• B40: Input the gross rate of return on investments during the withdrawal phase. This is usually different than the return you input for the accumulation phase. But if it's the same, then just input the same number. This feature is there for many reasons, just to add power and flexibility.
Again, you can manually override the 401k growth rate in any year using the manual override column T or Q on the Calculation sheets.
• B41: Input the first year's gross withdrawal amount before taxes.
This is another example of how this program works differently than the free rinky-Dinkytown retirement calculators. They usually just withdraw the inputted amount the first year, then increase that by some amount for the subsequent years.
The main problem with that is, where are the withdrawal taxes going to come from? The rinky-Dinkytown calculators don't account for this, and thus assume that this money will just come from somewhere else. But in the Real World, there usually is no "somewhere else."
So we did this a better way - all of these taxes are also withdrawn at the same time from the same account (your 401k). Where it comes from are somewhat from the tax savings account.
When you input that you want to withdraw $25,000 in the first year, then this is how much you'll actually get to spend. Using rinky-Dinkytown math, you'd only get to spend around $20,000 because $5,000 will be used up paying taxes on this withdrawal. People don't realize this, then are shocked when they can't pay their bills during retirement. So this methodology solves all of those problems.
If you don't like the way this works, then just use the manual override columns to input $25,000, and it will work just like rinky-Dinkytown. Or you can just set the tax rate in cell B43 to be 0% too.
As you can see, this can become a never-ending cycle, so it had to stop somewhere. For this retirement software, it wasn't accounted for. But on the Investment Comparator, it's only accounted for once. The numbers are too small to account for more than once. So it's not perfect, but at least it gets you into the ballpark, whereas other 401(k) retirement calculators assume you're going to sell enough blood or whatever to pay your 401(k) withdrawal taxes when you're old with no earned income.
• B42: Input the annual percentage increase that withdrawals will be automatically increased at. So if you think there's going to be 2.5% annual CPI inflation, then you'll want to give yourself at least a 3% COLA (Cost Of Living Allowance) here.
If you input $10,000 into cell B41 and 1% into B42, then $10,000 will be withdrawn the first year, then $10,100 in year 2, then $10,201 in year 3, etc.
You can also manually override annual withdrawal amounts in every year to more closely match the reality of your life.
• B43: Input the average tax bracket you estimate you'll be in during retirement. See the text about this above where you input a rate for the savings phase.
You'll almost always be in a lower average tax bracket when you're retired and usually making less money, not paying FICA taxes, get another generic tax exemption for being 65, and may have moved to a state with lower state taxes.
Input something in the 13% to 18% range here. Very few people in the Real World have the same, or higher tax bracket, when retired.
• B44 & B45: Input fees and all like you did during the accumulation phase, but be aware that you probably would have wised up by then and rolled it over to a Rollover IRA and are managing it yourself using a discount broker.
Then most all of these leaks will be plugged if you're on the ball (see the image at the top of this page), so they will more than likely be lower (because you're not working and contributing too).
Using the Tax Savings Account to Fund Withdrawals
This input allows you to add the tax savings account to the participant's account on the Traditional 401k Calculations sheet when it comes to funding annual withdrawals.
• B55: If you input 0% or delete this input, then the tax savings account will not be used to fund withdrawals, and the graph on the Roth vs. Trad Comparisons sheet will look goofy.
If you input any number between 0.1% and 99%, then this is how much of the annual withdrawals will come from the tax savings account (relative to only the participant's account - the match account is not affected by any of this).
After it's depleted, then it will automatically start taking 100% from the participant's account until it's depleted too.
There's a "catch" if you want to use this function though: After inputting a number into B55, compare columns V with CA on the Traditional 401k Calculations sheet.
For the best Real World results, they should both deplete about the same year. If they're off by a lot, then change the inputted number. It's best to use decimals to get them to both end in the same year.
The best way to get a good guess initially, is to look at column CA. If you're seeing numbers in the 20% range, then that's where you start when it comes to inputting a number into B55.
In the demo, when 20% was input, then the participant's account is depleted way before the tax savings account, so the 401k plan "ran out of money," when there's still plenty of money left in the tax savings account.
If you input 5%, then the tax savings account depleted way before the participant's account. So with a few trial and errors, 11% was input into the demo, which put everything into its proper balance, making the number tables and charts and graphs all look right.
Because of the dreaded circular reference problems which can't be avoided without using an annoying macro, there's yet another catch that you may have to Deal with manually, if you set B55 to too small of a number.
If you set it too low, then the participant's account will run out, and everything will show depletion, but there will still be a huge pot of money left over in the tax savings account (more than likely growing into the bazillions over time). This is not how things should be, and there's no way to automatically "fix it" because it causes a circular.
To see the problem, first set B55 to 5%. Then look at columns V and X. If X has depleted, and V has not, then this is what you're fixing.
So here's how to bring it back into balance manually: Go to the column BD and see when zeros start to appear. The first year you see a zero, is when the participant's account ran out.
In this same year, column CD shows what's in column AV - the total amount of withdrawal needed in that year. This is what needs to be input manually into column CA in the same year.
So do this until column V also runs out in the same year. Then both accounts will run out at the same time, like they should.
The whole point is to bring both accounts to the point where they both deplete in the same year. Then all of the tables and charts will look good.
• B56 & B57: These are just the rates of return that the tax savings account grow at. B56 is for the accumulation phase, and B57 is for the distribution phase. If you're thinking that this account should not be growing, then just set both to 0%.
All of these are there if you want to use them, if not then just ignore them. The point is that all of these arcane functions working together make it so you can model any oddball thing that comes up in the Real World. So feel free to do all of these kinds of "What-ifs" you want to.
Which brings up a very important point, that since it's based in Excel, you can use the built-in Goal Seek function to do all and any What-if scenario you can think of (bazillions more than any other 401(k) calc can).
Just go to Data, What-If Analysis, Goal Seek, and the dialog box will come up, then it's somewhat self-explanatory what it does and how to use it.
Inputting Into the 401(k) Loan Section
• B48: This is a drop-down menu switch for activating and deactivating this function. If you choose "N" then this feature will be ignored throughout the program.
If you choose "Y" then the input fields will become active, and you can simulate one 401k loan. You can see all of this at work on the Calculation sheets in columns BK - BP or BD - BI.
• B49: Input the year in which you'll take out the loan.
• B50: Input the estimated amount of annual loan repayments.
• B51: Input the amount of annual loan interest (APR).
• B52: Input the amount of the loan.
Everything else is automatic. It automatically figures out the loan term in years, then these results are used to determine the outstanding balance at the end of year five.
These are estimates and are limited to Excel formulas, so on one hand they'll be more accurate than the rinky-Dinkytown retirement calculators, but it still won't be "right." There's no way to program loan stuff to work perfectly in the Real World, so these are all just gross estimates.
You can use the manual override columns to tinker with the 401k loan interest rates and additional principal payments made annually.
Note a few things about 401k loans:
First, loans are withdrawals, so they get deducted from the account balance on just the participant's part of 401(k) plan.
Next, all interest and principal repayments get added back to the account balance.
Next, the loan balance at the end of five years is automatically treated as a premature distribution (unless you're over 60), so all of those taxes and excise penalty taxes will appear too (see the demo).
Also note that if your loan is for the down payment on your personal residence, these five year max penalties do not apply. The program doesn't account for this (unless enough users say they want it to, then it will).
Finally, 401(k) loans are not applied to the matching balance just because it would add to the complexity without adding enough value. In the Real World, very few participants actually receive matching money from their employer anymore, so there would no money there to loan.
Directions for Using the Monte Carlo Simulator
First, note that the Monte Carlo simulation only runs on the Traditional 401(k) side, it does not do anything with the Roth 401k side. As you can see on the Roth vs. Trad Comparisons sheet, there's usually little-to-no difference, so the resulting probability number it generates is going to be the same, or one percent lower, on the Roth side compared to the Traditional side.
Go to the Monte Carlo sheet. You'll need to be on this sheet when you run it, or it won't work.
Go to View, Macros, View Macros, then select Monte.
Then click Run.
It’s always safer to save all of your work before running something as intense as Monte Carlo in case your computer dies or something, as it may take a long time even with a fast computer, if it's bogged down with parasitic programs.
The macro runs. Things on your screen will bounce around wildly, and this is all normal.
It then ends up on the Traditional 401k Results sheet, on the cell with the resulting probability number. The simulation is now over.
Note that these numbers will remain until you run the macro again, even when you subsequently change other input data. So as soon as you change something after the macro runs, the old simulation results will be “wrong.”
The rates of return on investments are both incremented (iterated) from getting an average rate of return from -10% to 10%. It then logs a true or false condition, based on if there are years where capital is depleted between retirement age and your inputting life expectancy age.
The proportion of times it's true is then used to display the bottom-line "probability of success" number. So if all years in retirement up to your inputting life expectancy age have capital left at the end of the year, then it will display 100%. If only half have capital, and half has no capital, then it will display 50%.
Our simulations are unique in that it increments inflation and taxes too. The first iteration, for example, uses a -10% average rate of return, a 2% inflation rate in all three input fields, and a 15% average tax bracket. The second uses -10% rate of return, 2% inflation, and 25% tax rate. The third uses -10%, 4% inflation, and 15% tax rate. The fourth uses -10%, 4% inflation, and 25% tax rate. The fifth uses -10%, 6% inflation, and 15% tax rate. The sixth uses -10%, 6% inflation, and 25% tax rate. Then it cycles in one half percent increments more rate of return until all combinations of the three variables are solved for. This is a total of 246 iterations. The maximum Excel can handle is 251, so we had to stop there.
This bottom-line probability of success number displayed is just telling you the chances of not needing any more capital until the inputted life expectancy age.
We feel this methodology paints a more realistic picture. Others just use average asset rates of return from -100% to 100% (and don't fiddle with taxes or inflation at all). In the Real World, the chances of losing all of your money in an investment portfolio in one-year are just about as negligible as doubling your money (plus when you totally run out, the next year of -100% is meaningless).
This 401(k) software iterates between the two and three ranges of both taxes and inflation that are most likely going to happen in the Real World. We don’t use rates of return over 10% because this is a long-term average rate of return, and few can get such a high average over the long-term (even a few years or more). Also, the chances of getting a negative 10% rate a return every year for over a decade is also just as unlikely.
Our Monte Carlo results will also probably show a much lower probability of success than what you may be used to seeing. This is because it paints a much less rosy scenario than other vendors. Our numbers are more realistic relative to what will probably happen in the future.
The inflation and tax iterations are also just three and two examples of good to worst-case scenarios.
So even though this is a different way of doing things, we feel that it makes the bottom line probability number much more meaningful, and projects what’s going to happen in the future, better than what everyone else does. Please note that all of these numbers are as good as computers can generate, but they will still all be wrong in the Real World.
Things to keep in mind:
• This will take a few minutes on a slow computer, and you can't stop it once it starts running.
• Rates of return input into the investment manual override columns will override simulated rates in those years.
• You'll need to keep the Workbook and the Traditional 401k Results sheet unprotected so the macro can write the results to it.
How to Link / Integrate the 401k Software's Results into the Other Retirement Planners
All you'll need to do is first get the 401k calc the way you want it (not running out of money at your inputted life expectancy), then use one of the six totally manual Miscellaneous Income and/or Expenses columns to account for just the net retirement income from the 401(k) calc.
So using the single RWR's current plan demo as an example, here's how to do it:
• Have both the 401k calc and RWR open at the same time.
• Get the 401k calculator input the way you want it. Lower the income goal withdrawals until you're not running out of money at your inputted life expectancy.
• Go to RWR's Summing & Input sheet.
• Go to cell BA18 and input, "401k" then press Enter.
• Go to cell BA20 and press the equal sign on your keyboard.
• Go to the 401k calc. Now decide whether you want to use the Traditional or Roth sheet.
• On the Traditional 401k Results sheet, go to cell V6, which is the first year of gross withdrawals. Click on V6 and press Enter. It goes back to RWR, showing a zero in that year (no withdrawals yet).
• Now you'll need to account for the taxes, as this manual input area of RWR is for NET incomes, after taxes.
So in RWR, cell BA20 of the Summing & Input sheet, now it should be linked to cell V6 of the 401k calc. Edit the end of this formula like this: Input the minus sign at the very end of the formula, then go back to the 401k calc.
Now click cell AB6 on the same sheet. Press Enter.
Now you should have the gross income in V6 minus those taxes in AB6. The result will still be zero in year one.
Now in RWR, click the drag handle - when you go to the little black box on the very bottom right of a cell, it will turn from a big white cross into a little black cross - now drag it down all the way to the bottom in row 90. Now all 75 years of 401k calc are accounted for in RWR and that net income will be added to the retirement scenario.
For Roth 401(k)s, go to the Roth 401k Results sheet. Do the same thing, except the column is just column V, because there are not taxes to pay in a Roth 401k plan.
For integrating the 401k calc results into the Investment Comparitor, just make a new column on IC's results sheet, then link the end of year market values column from the 401k calc. Then you can also insert that column into the charts fairly easily. This isn't "automatic" because if there's a match, then it won't be a fair comparison. So if you have the IC with support, just ask and you'll get help.
• Integration with the IFP: The whole far right sheet is for that because data from the 401k calculator goes vertical and the IFP files usually want it horizontal, so this makes it all easy.
There's several rows here so you can pick and choose what you want to account for in the IFP. If you want more data than what's there, then just ask and it may be added.
First make a title for what you're integrating into the IFP (in the Net Worth Projectors as an asset or the Cash Flow Projectors as an income or expense).
Then you basically just go to the CFP or NWP part of the IFP, then go to the annual manual override cell where you want to integrate into the projectors. Start with the first year by going to that cell on the projector sheet, then input the equal sign into that cell.
Now go to the 401k calc and go to the first year of the row of data you want to share on the IFP Integration sheet. Press Enter. Now the CFP or NWP data comes from the IFP.
Now you'll probably need to delete the dollar sign after the "!" in the formula, or everything will be stuck on year one during the next step.
Now just drag it across for all 75 years on the NWP or CFP projections sheet, and that's it. Now all of the changes you make on the 401k calc will flow to all years on the NWP and/or CFP.
Repeat for all of the other things you want to account for too.
The three main things to account for are the participant contributions, incomes, and end of year total account values. But the data is there if you also want to account for taxes, fees, and expenses.
Be aware that the 401k calc account for participant and match values separately, so when accounting for the incomes and account values, you'll need to do them twice to account for both.
The Results Sheets and Miscellaneous 401k Calc Information
The tax savings columns (O - R) on the Traditional 401k Calculations sheet are only shown in the participant's section because there are no tax savings from matches.
If you don't like having participant and matches in two different sections, then just don't input any employer matches. Just tinker with it until the amount in cell F6 on the Calculation sheets match your current 401k balance.
About the Roth vs. Trad Comparisons sheet: All this results sheet does is compare the annual ending combined account values between Traditional and Roth 401(k)s. This is all automatic, so you can see the differences without having to do any real work.
The big thing of interest is the "side account" where all of your tax savings on the Traditional 401(k)s contributions are accounted for. This is THE big advantage here, so these savings need to be accounted for and applied to the comparison. This is not even done on most of the rinky-Dinkytown retirement calculators.
These amounts are grown annually by the amount input into cell B13. The bottom line is that if you set the tax savings side account's growth rate to 0%, then there is little-to-no difference between Traditional and Roth (regardless of what anyone else says or writes. For more proof: Here is where you can read an analysis on when it's best to start collecting Social Security benefits.
Hopefully, all of the results information displayed on the Results sheets are self-explanatory. If not, then please let us know. Don't forget that's there's many graphs below the tables in column B.
Not only that, the Results sheets are not protected, so you can make any changes you want to there (and on the Scratch Pad sheet). To unprotect, click Review, Unprotect Sheet. If you have butterfingers, then it's wise to re-protect afterwards so you won't screw things up.
Last but never least, how to totally control printing in Excel with just a few clicks is here.
|Financial Planning Software Modules For Sale
(are listed below)
Financial Planning Software that's Fully-Integrated
Goals-Only "Financial Planning Software"
Retirement Planning Software Menu: Something for Everyone
Our Unique Financial Services
Mr. Market Timer's Unique Market-neutral Stock Market Timing Services
Miscellaneous Pages of Interest
© Copyright 1997 - 2017 Tools For Money, All Rights Reserved