Directions for the
Investment Comparator
- and / or -
Buy Term Life Insurance and Invest the Difference into Mutual Funds vs. Whole Life Insurance Comparator

Go to the main Investing Comparitor software product page

Site Information

Read About the Current Sale!

Confused? It Makes Sense If You Start at the Home Page

First, some generic directions for all of the financial software on this site

Investing Comparator Directions

First a disclaimer: The results are just estimates based upon your input. The only thing we can guarantee is that they WILL NOT be the same as what you'll see in the Real World!

Next, General Methodology

The flow of calculations occurs in the same logical order as shown on the calculation sheets.

The first thing that happens is that net annual contributions (after loads/commissions) are added to the value of the account at the beginning of the year.

Then the annual rate of growth is applied.

Then mutual fund management and 12b-1 fees are subtracted.

Then dividend and capital gains distributions are calculated and dealt with.

Then taxes on them are deducted.

Then the net dividends and capital gains are used to fund withdrawals. If they're insufficient to meet the withdrawal amounts, more money is taken from the fund. In non-qualified investments, these amounts are deducted pro-rata from basis and the balance of unrealized capital gains (for example, columns BG & BJ on the NQ MF A Calculations sheet).

The more basis there is, the more withdrawals come from tax-free returns of principle, and the more unrealized capital gains (profits) there are, the more is taken from there, taxed, and used to make up any withdrawal shortfalls. Capital gains taxes are calculated on amounts needed to be sold from the account to pay capital gains taxes. If you understand that, then you'll realize that this is a never-ending process, and so it only happens once.

The goal is to provide the withdrawal amounts in after-tax spendable dollars. So if you input $1,000 per year of income needed, then this is how much you'll get to spend, after all taxes have been paid.

The program withdrawals enough money to pay all taxes due, so that you'll have $1,000 net to spend. This is an important thing to keep in mind when you run insurance ledgers, because you'll have to adjust the withdrawals there to reach the same amount in after-tax spendable money (because all of the income withdrawal numbers shown on insurance ledgers are before-tax).

Then after all of that is calculated, the end-of-year balance of the account is then referenced back to the next year's beginning year balance. The process continues until the account has no money left, or the last year to show numbers is reached.

The overall goal is to see which method of investing will result in the most number of years of withdrawals, given the fact that each method has the same amount of money being contributed to them, with the same tax and growth rates, and the same amount of annual withdrawals. The investing method that results in the most number of years of withdrawals is usually the best method of investing your money.

Given Real World input, the traditional tax-qualified account (IRA/401k) is usually going to win. The problem with that is you can only invest so much money per year into tax-qualified plans. However, results are very close if you input true no-load mutual funds.

When it comes to deciding which way of obtaining life insurance coverage is best, careful input will usually result in buying term and investing the difference.

In the Real World, the amount of life insurance one needs declines rapidly every year, as loans are paid and there's one less year of the breadwinner's income to replace (see the life insurance software page for details).

This annual decline in life insurance need was not illustrated in the demo. If it was, then how much the difference of buying term life insurance and investing the difference into no-load mutual funds vs. any form of whole life insurance would be magnified ten-fold.

The annual escalations in term life insurance costs are the biggest item insurance agents point to when selling whole life insurance policies. When the results of subtracting one year of the breadwinner's income that needs to be replaced annually are properly accounted for, this escalation in term costs are mostly negated.

Results will vary depending on dozens of factors.

Sheet Tab Names

Abbreviations were needed because of the character limitations on sheet tabs. You can change the sheet tab names by right clicking on them.

"NQ" stands for Non Qualified, or in other words, not an IRA or 401(k) nor other tax-qualified account.

You don't get a tax deduction on contributions, you pay taxes every year on distributions (dividends/interest/realized capital gains), and money you invest adds to the tax basis. This means you can withdraw money you contributed, and didn't profit on, without paying taxes on it. As it turns out, this tax-free return of principal is one of the biggest reasons why non-qualified accounts fare way better long-term than traditional wisdom has postulated for decades. Also because reinvested dividends/interest/realized capital gains add to basis, and is not taxed when withdrawn also helps make regular old investing not such a bad deal as everyone says compared to qualified investing.

"Q" stands for tax-qualified, like IRAs/401(k)/TSAs/SEP/etc. You get a tax deduction on contributions, the growth and reinvested distributions are tax-free along the way, but you have to pay ordinary income taxes on all money that comes out of them when you make withdrawals (and there are tons of rules about what you can and can't do and penalties if you break them).

"Roth" stands for Roth IRA or Roth 401(k). These are tax-qualified accounts too, and have similar annoying rules and tax penalties, but operate differently than traditional tax-qualified accounts.

Basically, you don't get the initial tax deduction on contributions, the reinvested distributions that would normally be taxable annually in a non-tax-qualified account are not taxed, and you don't pay any taxes on withdrawals.

This sounds great, but the reality is that paying taxes on dividends/interest/capital gains along the way don't add up to being near as significant as traditional wisdom would have you believe.

So they perform somewhere in between non-qualified accounts and traditional IRAs. Again, the bottom line is shown in the demo. Crunch the numbers and do an audit yourself and see.

"VA" stands for variable annuity.

"MF" stands for mutual fund. As you will read below, you can use this investing software to simulate most every type of actual investment vehicle used in the Real World.

"NL" stands for a true no-load mutual fund. This is one with no front- or back-end commissions, and low, or no, annual 12b-1 fees.

"A" stands for the A-share class of mutual fund.

This is the type of mutual fund where you pay a front-end load / sales charge /  commission every time you contribute money to the mutual fund.

A-shares have a bad rap because the initial commission is the most obvious and painful to investors (and so it gives the press something to write about). But as you can see when you crunch the numbers with this investment software, they have better long-term results than B or C shares.

That's because you're paying commissions on the smallest amounts of money possible - the initial contributions. The big money happens years later when the account grows, and then when the higher annual B & C share 12b-1 fees are applied as a percentage of the total account balance, which are way bigger, it eats away at your money like cancer.

So over time, paying an initial load is going to make you way more money than getting more and more sucked away every year in 12b-1 fees.

The moral of the story is that if you're going to pay on a percentage of money, you want to do it with the smallest amount, to get it over with ASAP. Doing it up-front, when the account is the smallest, is the best way. See the demo and compare.

"B" stands for the B-share class of mutual funds. This is where there is no up-front sales commission (load) on contributions to the mutual fund, but the mutual fund family will deduct this percentage from withdrawals.

These are also called redemption fees or back-end loads. They usually decline annually and eventually go away altogether.

B-shares usually also charge a higher 12b-1 fee, to make the money needed to pay the salesperson their up-front commission. This money has to come from somewhere, and so if you don't sell shares while redemption fees are in place, then there is nowhere to get the money to pay the salesperson. So they get it along the way by dinging you a higher annual 12b-1 fee.

They are either getting the money to pay the salesperson's commission through the higher 12b-1 fee if you don't redeem, AND through the higher 12b-1 and the redemption fee if you do redeem. So you can't win either way with B-shares (which is why there's much stricter regulations and scrutiny on salespeople that sell a lot of them).

People fall for this because they don't understand, they think they won't be selling while the redemption fees are in force, and they don't want the initial pain of paying the A-share class front-end load.

"C" stands for the C-share class of mutual funds. This is where the mutual fund does not charge a front- or back-end load, but charges up to several times more in annual 12b-1 fees than on A-shares.

This money goes to the investment advisor as an "annual investment management fee." This basically allows commission-based salespeople to charge clients an annual fee, and have it treated as commissions, so they won't have to do all of the regulatory work to be able to charge actual investment advisor fees.

As long as everyone is okay with the advisor making money like this, there really isn't anything wrong with it. But it's a common abuse when it's not disclosed and/or advisers charge additional investment advisory fees on top of C-shares fees. So it's important to add up all fees and then decide if you think you're getting your money's worth.

"LW" stands for Load Waived, which means one buys the A-share class without paying the initial sales charge. It's the same as NL or No Load. In most cases, these are not available to the individual investor.

Some mutual fund families change these share class letters around to try to trick you into thinking they're not dinging you fees and commissions. The vast majority of mutual funds are one of these four types, even though they may be called their "Y" share class. All you need to do is evaluate the front-end load, back-end load, and the size of the 12b-1 fee to see share class it really is.

The bottom line on all of this is that you don't have to pay too much in any of these fees. All you need to do is buy a true no-load fund, and you won't get dinged any of this. Of course, you won't have a financial professional to help you because they're not getting paid.

"Ind Sec" stands for individual securities. Think brokerage account, where you pay commissions every time you buy or sell anything. This is sort of the same thing as having to pay both the A- and B-shares loads, but little to no annual fees of any kind (like 12b-1 fees).

Directions

Step 1: Once you've unzipped the file Investment Comparator.zip, open it with MS Excel.

Go to the Input sheet tab.

Input all of the generic data into cells A3 - A9.

The only two inputs here that affect calculations are the year of birth and the ending age to display numbers. If you set it to age 100, then only zeros will display after age 100. This minimizes clutter.

The rest of the input will flow through all of the calculation sheets equally, except for the Bank input cell (A46).

The adjustments for loads/commissions and fees will occur automatically in the appropriate calculation areas.

Step 2: Input the amount of money that is currently in the investment into cell A11.

This will be reduced by whatever initial front-end load and/or sales commissions are paid. This percentage amount is input into cell A34 of the Mutual Fund Input section. This amount is deducted from the initial balance in cell F4 of the A-share Calculation sheet.

To account for any differences between this automatic reduction and the Real World, you can use the Annual Contribution Manual Override in cell L4 for the initial year.

Note: For all of the tax-qualified calculation sheets: When you invest money into tax-qualified plans, like IRAs/401(k)/etc., you get an immediate tax deduction on the contributions.

The amount of this tax refund is added back to the contribution in that year.

There was a lot of controversy on how to handle this, and it was decided that this was the best way to account for it.

For example, if you contribute $1,000 to an IRA and your average tax bracket is 20%, then you get $200 back in tax savings. It would have been too complex, and not as correct, to assume this was spent or invested into another non-tax-qualified account, to be used later to fund withdrawals. This is a massive benefit of this method of investing, so it needs to be accounted for.

So it's added back to the $1,000 contribution, and so instead of contributing $1,000, you really contributed $1,200. This is shown in column O or M on the tax-qualified calculation sheets. This advantage is negated somewhat when withdrawals come, because they are all taxed at ordinary income rates.

This is a good time to also point out why Roth IRAs are only a little better than non-qualified taxable accounts.

As it turns out, this initial tax deduction (that is assumed to be invested into the account) is why traditional tax-qualified accounts do better than Roth accounts.

Here's the biggest reason why: Your tax bracket is most always higher when you're working and making contributions, so this helps a lot (because the higher your tax bracket when you make deductible contributions, the more you get back immediately in tax savings per dollar of contributions).

Then when you withdraw money in retirement to spend on living expenses, your tax bracket is lower, which means you spend less in income taxes. These two forces end up overwhelming the relatively small, and only, advantage of Roth IRAs - the fact that taxable events are not taxed along the way (which also happen in traditional tax-qualified plans).

Also when you pay taxes on dividends/interest/capital gains along the way in a non-qualified account, these amounts are not as big as people postulate, because you're paying them in the early years, when the account balance and distributions, are relatively small. See columns AE & AH on the NQ MF Calculation sheets.

When you reinvest them back into the fund, this is the same as contributing more money, which increases basis, which results in less taxes being paid when you withdraw money (because it's more return of principle because of the higher tax basis).

This also explains why there isn't such a dramatic difference in non-qualified and qualified as you've probably been brainwashed to think over the decades.

Because the money grew so much over the years tax-free in qualified accounts, when you take it out, you're paying tons in taxes even though you're in a lower tax bracket. This is because you have more money than you did in the past, and it's ALL taxable.

Back to the moral of the story again - when you're paying a percentage of your assets for something, in this case taxes, you want to do it early on, when the account is the smallest, and not later on, when the account is the biggest.

In non-tax-qualified accounts, the amounts you pay in taxes every year (from those annoying interest, dividend, and capital gains distributions) ends up being much less significant than traditional wisdom would have you believe.

This is because these amounts are small in the early years, relative to having EVERYTHING taxed at your top marginal tax bracket when you start withdrawing from traditional tax-qualified accounts (IRA/401k/SEP/Keogh/etc.).

This is so even accounting for the fact that your tax bracket at retirement is probably much lower than when you were working and making contributions.

Add to that the fact that dividend and capital gains distributions are taxed at a lower rate than ordinary income taxes. So even when you're in the accumulation phase, and paying dividend and capital gains taxes at the highest bracket, this is still less money than paying ordinary income rates at your lower (retired) tax bracket.

As you can see when you crunch the numbers, traditional tax-qualified plans still end up with making the most money, which allows you to have a bigger retirement paycheck, but the bottom lines are not near as much as the financial services industry has been saying for decades.

Important: If you use any of the manual overrides on any of the calculations sheets, you must input the same thing into ALL of the other calculation sheets, or the purpose of comparing one method of investing with another will be defeated.

This investment software has many uses other than comparing ways of investing, and this is why the manual overrides are there - to do "What-Ifs."

If you're inputting any insurance general ledger information into the Insurance Input sheet, the comparison results will not be correct because you typically cannot illustrate odd or unequal cash flows into insurance ledger software. Just be sure all of the green-shaded cells in ALL of the calculations sheets are empty before attempting to compare life insurance company products with all of the other methods of investing.

Step 3: Contributions: Input the amount of annual contributions into cell A12.

Input the age when they start into cell A13 and the age they stop into cell A14.

Then input the percentage amount of annual growth into cell A15.

Front-end loads and initial commissions will be deducted from them automatically in the A-share and Brokerage Calculation sheets.

As usual, you can manually override these amounts in any calculation sheet, in any year, and all independent of each other. But if you do, you won't be making true comparisons with the others.

Also on the A-share and Brokerage Calculation sheets, if you use the annual contribution manual overrides, don't forget to deduct any front-end loads or commissions, and input the net figures, because there is no way to automatically account for them there.

Withdrawals: Input the age withdrawals begin into cell A17 and the age they end in cell A18.

Input the monthly amounts into cell A19.

Input the percentage amount of annual increases into cell A20.

The amount of B-share back-end loads will be automatically deducted from withdrawals in those calculation sheets. As usual, you can manually override the withdrawal amount in any year. Here, the redemption fees will be automatically accounted for. You'll almost always want to use the manual overrides here, because redemption fees decline and go away over the years. This is where you account for that.

Tax Rates: There are two sets of tax rates.

One for the period of time before withdrawals occur (accumulation phase) and one starting the year withdrawals begin.

This is because withdrawals usually start to occur in the years of retirement, and most people are in lower tax brackets then.

Some life insurance product general ledger illustration software has these features, so they were implemented here as well. In the year of the first withdrawal, the tax rates in cells A22 - A24 are replaced with those in cells A25 - A27.

As usual, you can manually override tax rates in any year on the calculation sheets.

Inflation: This does nothing to any of the calculations. All it does is show the inflation adjustments on lower table on the Presentation sheet.

Mutual Fund Input: Input the annual rate of return into cell A33. You can manually override this in any year using column Q on the calculation sheets.

Input the percentage amount of money subtracted from all contributions (front-end loads and/or sales charges/commissions) into cell A34.

Input the percentage amount of money that is subtracted from liquidating shares into cell A35 (B-share back-end redemption fees).

Input the annual percentage amount of mutual fund management fee into cell A36. This is what the fund managers get paid, and is not the 12-b1 fee nor what an investment advisor charges.

Input the annual 12-b1 fee for the no-load and A-share mutual fund into cell A37.

Input the annual 12-b1 fee for the B-share mutual fund into cell A38, and A39 for C-shares. These are both usually higher than no-load and A-shares.

Input the annual percentage amount that is distributed as dividends and interest into cell A40. You can call the fund to get a past average.

Input the annual percentage amount distributed as capital gains into cell A41.

The two items above come out of the gross rate of return input into cell A33. So if you input a 10% gross rate of return, a 1% dividend rate and a 2% realized capital gains rate, then the amount of unrealized (and un-taxed) capital gains in that year will be 7%.

Input the annual dollar amount of IRA/Retirement custodian fee into cell A42. This is usually a nominal amount around $100 annually.

The Bank Rate of Return Input Cell: The rate of return in cell A46 is to account for the much lower rates of return in Bank CD or money market investments.

Generally speaking, the last three calculation sheets are for the types of investing where there is only interest, and no possibility for capital gains or losses. In other words, CDs, money market funds, savings accounts, and other short-term money market instruments.

These sheets were added to show the long-term results of investing in them, given the fact that they are still popular and have three unique characteristics: Insured safety of principle, all interest is taxed annually at ordinary income rates, and so there are no realized or unrealized gains or losses to account for.

You can, as usual, manually override the rates of return in any year for maximum flexibility. It's only accurate to use this when the investment vehicle only earns interest, and has no possibility for any profit or loss (so don't use it for any kind of bonds, including zero coupon bonds, unless you're assuming they'll be held until maturity).

Step 4: Life Insurance Company Product Inputs:

Most variable annuity input is straightforward except for one glitch. The qualified variable annuity using the exclusion ratio method of determining taxes due on non-annuitized payments. This causes a circular reference that cannot be fixed. So you have to manually input this ratio into cell AG1 of the VA calculation sheet.

The numbers for life insurance cannot be accurately generated with generic investment software. They need to come from state-approved software generically called "General Ledger Illustration" software.

Only life insurance companies produce such software, and they can only use it to make future assumptions regarding their own company's products. This is also why you can't just go online and find them without dealing with a licensed agent.

So you'll need to get life insurance cash value figures from a licensed life insurance agent or broker that has an insurance license in the state you live in. It's easy to do, but you'll have to deal with someone that wants to sell you life insurance company products to get the information you need to input into this software.

Here is how use the output from this proprietary software to input into the Investment Comparator. The point is to use it to compare against all of the other forms of investing:

First you need to run the numbers using the life insurance illustration software to generate a general ledger. This is just arcane terminology that means you get a report that estimates the projected future account values of the life insurance company product. What you're after are the annual account values after all of their premiums and fees are deducted.

In general you need to input the EXACT same parameters into both software (the life insurance illustration software and the Investment Comparator) for the results to have any meaning.

In the Investment Comparator, this means the following input cells should show the exact same input data that shows on the ledger: Input sheet cells A8 and A11 - A27. Any difference will make the comparison meaningless.

Please read all of the bold critical items listed below before inputting!

Now for the critical differences

For whole life insurance products: On the insurance ledger, there will usually be more than one column with (estimated) end of year market values.

Even if it's a guaranteed fixed-rate subaccount, the numbers will only be estimates - JUST LIKE THE RESULTS SHOWN IN THE INVESTMENT COMPARITOR. They will never be exactly what you'll actually get in the Real World.

It's critical to only input the NET end-of-year account values into column I of the Insurance Input sheet (also sometimes called Surrender Values). Do not input the Gross Account Value numbers.

These Net values are the estimated end-of-year account values AFTER all of the estimated life insurance company product fees and life insurance premiums are deducted.

Using end-of-year Surrender values are even more accurate, because that's much money you'd get back if you needed to withdraw it. Yes, you have to input them manually for every year (unless your ledger software allows you to copy and paste them into Excel).

Next, it's critical to input the same GROSS rate of return used on the insurance ledger into cell A33 of the Mutual Fund section of the Input sheet.

The difference between gross and net returns on the insurance ledger show the biggest reason why one should rarely "invest" in any kind of cash value life insurance product (whole life, or VUL - Variable Universal Life).

You then input estimated mutual fund fees and expenses into cells A34 - A39.

Use the exact fees and expenses that the mutual fund you're using has. You can find them online, via database software like Morningstar, the mutual fund prospectus, or by calling the fund's toll-free marketing/customer support/service phone number (or we can get them for you for $9 per mutual fund).

The net rates of return on the mutual fund will then flow through the calculation sheets to produce a true apples-to-apples comparison with the life insurance company products. It's normal for the life insurance company products to have fees and expense several times higher than mutual funds.

It's not necessary to input any of the Net Death Benefits (sometimes called Surrender Values) into column J of the Insurance Input sheet, because it doesn't affect any of the calculations. It's just for your information.

The next critical item: You must account for the costs of the term life insurance in all of the mutual fund investment calculation sheets. Here you'll need to also run a term life insurance ledger to get all of the annual term life insurance costs. Ensure you use the same annual life insurance face amounts as the whole life product in every year.

You only have to input them once, into column L of the Insurance Input sheet, and they flow into the five Non-Qualified Mutual Fund sheets. The reason they don't flow into the Tax-Qualified sheets is because after a few years, you won't be allowed to contribute that much money to them (every type of tax-qualified plan has annual contribution maximums, and you'll exceed these within a few years).

If you want to do it anyway, all you need to do is use the Withdrawal Manual Overrides to add the term costs. Be sure to also add them to any existing withdrawals. So the only columns on the presentation sheet with meaningful comparison information are the non-tax-qualified columns.

A tip is to try to use premium rates for Annually Renewable Term life insurance, or ART. Any kind of level term, or term periods, just ends up wasting money in commissions and expenses. If the annual term costs are different every year, and go up every year, and then you have the correct information to input. If they are the same in any year, then they are not ART. Agents will always try to get you to NOT buy ART, and will go on and on and on about why you should not. This is because they know this is the life insurance product that they make the least amount of money on (and they'll get griped at by their sales manager every time they sell it).

The next critical item: This is another good time to mention the fact that the vast majority of people's life insurance needs GO DOWN SUBSTANTIALLY EVERY YEAR.

So it's critical to be able to calculate your actual annual needs so you don't end up buying way too much death benefit (which is expensive). So please look at the life insurance calculator on this page.

This life insurance software will more accurately project true needs than anything ever created, and will both explain and show, why your death benefit face value needs decline every year (also see the Life Insurance Calculator's directions page for details).

So if you're doing a serious analysis for yourself, then you want to use the Life Insurance Calculator's annual results, before you input all of these insurance parameters into the Investment Comparator. This way you won't have to do it again once you see how much the face value needed decreases every year.

Once you do this critical first step, and then create both a term life insurance ledger, and a whole life insurance ledger. The point is to be able to calculate, and then input, the annual costs of term life insurance into column L of the Insurance Input sheet.

These annual costs will flow through all of the non-tax-qualified mutual fund calculation sheets, and will be added to the amounts of annual withdrawals amounts. If you account for these annual reductions in face amounts, the difference between term and whole life will stand out like a mountain over a molehill.

The point is to input the exact same amount of annual life insurance death benefit and PREMIUMS, for both the term and whole life products, in order to do a true: Buy term life insurance and invest the difference into an alternate investment vehicle (called a mutual fund in this software) vs. buying whole life and "investing" in the life insurance company's subaccounts.

In order to do that, the exact amount of annual death benefits must be identical in both the term life insurance product and the whole life insurance product. Most all modern whole life insurance products allow for having different/declining face amounts every year in their illustrations, and it's called "Universal Life" or these days, "Variable Universal Life" or just "VUL."

If this is not possible, and/or you just want to compare term vs. whole using a fixed death benefit, then just ignore all of this declining face value complexity. Just keep in mind to ensure that the amounts of annual death benefit you input are identical in EVERY YEAR in BOTH the term and whole life products.

The Net Account Value amounts you input into column I of the Insurance Input sheet will just flow through to the presentation sheet unaltered. These values are how much it's estimated that you could get back from the life insurance company if you choose to surrender your policy (which is why they may not be called Net Account Values on the ledger and may be called something like Net Surrender Values).

Inputting anything that sounds like "Gross Values" will not represent reality, because if you choose to withdraw your money, you won't be getting anything close to that much money after the life insurance company first deducts their enormous fees and commissions.

CRITICAL TAX ACCOUNTING INFORMATION: Another important thing to keep in mind - these Net Account Value amounts are also before any taxes you would owe if you surrendered the policy and/or took income withdrawals.

All of the numbers for the Bank CD, brokerage, and mutual fund withdrawals are AFTER TAXES are paid.

So if you're inputting $1,000 annual withdrawals, you're getting $1,000 in spendable after-tax money.

The $1,000 withdrawals you see in insurance ledgers are most always before tax. So if you're inputting $1,000 in annual withdrawals into the Investment Comparator, then you'll need to input the amount of withdrawals that will equal $1,000 in spendable money, after the taxes are paid on the insurance product withdrawals.

For example, if you have input $1,000 in annual withdrawals in the Investment Comparator, and the tax rate is 20%, and all money coming out of the insurance product is subject to 20% tax after you get it, then you'll need to adjust the amount of insurance product withdrawals up to also take taxes out of the balance (because that's how it works in the Investment Comparator calculations).

If so, then you'll need to input $1,250 in annual withdrawals into the insurance ledger ($1,000 / 0.8 = $1,250   The 0.8 comes from 100% - 20% = 80% or 0.8). This is critical if you want to compare apples to apples.

If you already have your insurance ledger, and don't want to have it redone, you can also do it this way: Again assuming a 20% tax rate, just multiply the mutual fund's income withdrawals by the reciprocal of the tax rate. For example, if your insurance ledger has $1,000 in gross withdrawals, then input $800 for the withdrawal amounts in the Investment Comparitor ($1,000 * 0.8 = $800     0.8 would be the reciprocal of the 20% tax rate).

For fixed annuities: Input the end-of-year values into column AC of the Insurance Input sheet.

When it is annuitized, the market value disappears, so input all zeros starting in that year.

Then input how much it will pay annually into column AD.

On the Presentation sheet, the difference between what it pays out, and the needed income withdrawals will display in column AD.

Negative numbers mean the annuity will not be paying enough to cover the needed income withdrawals. This is because the insurance company keeps a stiff part of it in exchange for the life income guarantee (and all of the enormous fees, of course).

The same accounting for taxes applies with annuities as life insurance - the amounts shown on your ledger are before tax. So it needs to be adjusted so the same amount of spendable income is the same as for all of the other investing methods.

Step 5: Usage of the manual overrides.

You'll need to input the exact same manual override data into all of the other sheets you're using to compare things with, or any comparisons will be meaningless.

The Annual Contribution Manual Overrides: Use these to input whatever you want annual contributions to be in any year. If you're using the A-share sheets, you'll need to first adjust them for front-end loads/commission deductions.

Annual Rate of Return Manual Overrides: Use this area to make the annual rate of growth be whatever you want it to be in any year. They can even be negative.

Dividend and Capital Gains Manual Overrides: Use to change the amount of annual dividends and capital gains distributions on the non-tax-qualified accounts.

Tax Rate Manual Overrides: Use to change the tax rates to be whatever you want them to be in any year.

Annual Withdrawal Manual Overrides: Use to make the annual withdrawals be whatever you want in any year.

B-share Redemption Fee Percentage Manual Overrides: For most all B-share mutual funds, the percentage of redemption fee declines annually after some point. Then most of them disappear altogether after about ten years. Use these input fields to account for this by inputting the schedule obtained from the mutual fund family or the salesperson that sold it to you.

Miscellaneous

You can use Excel's Goal Seek function to do hundreds of What-If scenarios, but just realize that it will throw off most all of the Comparator capabilities.

Go to Tools, Goal Seek, and input what you want to do into the three cells.

Why there are so many mutual fund 12b-1 fee input areas: A-shares get their money to pay salespeople mostly via the front-end load, so there is little reason to jack up the 12b-1 fee.

Generally speaking, 12b-1fees on A-shares are the lowest, and are about the same as true no-load funds. B-shares need to recoup the commissions paid to salespeople if no shares are redeemed during those years in which the redemption fees are applied, so they are higher than A-shares. The whole point of C-shares is to pay the investment advisor annual investment management fees for managing your money. So generally speaking, they are the highest.

Mutual fund management fees (cell A36) are the fees that go to the actual investment managers to manage the money. None of this goes to sales or marketing. That's the purpose of the 12b-1 fees.

So these fees are pretty much the same regardless of which of the three shares classes are used. This is why there is only one input area for them. If you want to increase or decrease these fees in one fund and not in others, then calculate the difference in annual fees, and use the Withdrawal Manual Overrides to account for the difference.

For example, if the annual withdrawal amount was $1,000, and you want to show using a no-load with 1% less in total annual fees, and the account balance is $10,000 in that year, then you would subtract $10 from the $1,000 and input $990 in the manual override column.

The Dollar Amount of Retirement Account Fees: Most all tax-qualified accounts have a nominal annual fee that goes to pay the custodian to keep your retirement money safe. This is usually in the order of $100 annually regardless of the size of the retirement account.

Input the amount in cell A42 on the Input sheet. If you want to make adjustments per method of investing, then use the Withdrawal Manual Overrides as explained in the paragraph above.

Brokerage accounts will have different commission schedules, and annual rates of dividends and capital gains, because you're dealing with individual securities. There are also commissions when you sell, which are usually not there when you use mutual funds. That's why the input cells A50 - 53 are there.

The percentage of costs to invest are shown both on the last several columns of the calculation sheets, and on the two bottom tables on the presentation sheet. The dollar amounts are only on the last columns of the calculation sheets.

The 401(k) and 403(b) sheet does not share input like the other sheets do. This is because the employer matches make it non-apples-to-apples. You can make it apples-to-apples with the other sheets, by setting the employer matches to be 0%. When you do this, the sheet functions the same as the Tax-Qualified A-share Mutual Fund sheet.

As you can see, this investment software is way more complex that it seems at first glance. So you may want to order support! The most complex sheets by far are the non-qualified mutual fund sheets. This is because of all of the basis and tax calculations.

Fully Integrated Financial Planning Software
 

Services

Fee-only Financial Planning and Investment Consulting for Consumers, Investors, and Financial Planners
 

Building Custom Investment Benchmark Portfolios

Financial Planner Directory

 

How to Become Your Own RIA

 

Coaching for Financial Planners

 

Asset Allocation for 401(k) / 403(b) and Similar Retirement Plans