|
Directions for the Investment Comparator - and/or - Buy Term Life Insurance and Invest the Difference into Mutual Funds vs. VUL Whole Insurance Life Insurance Comparator |
|
Site Information Confused? It Makes More Sense if You Start at the Home Page Why We're Better at Financial Software Price List of All Personal Finance Software Modules How to Buy Money Calculator Software in General Huge Discounts for Financial Planning Advisers and Money Managers New Financial Planner Starter Kit Professional Investment Portfolio Building Kit Financial Plan Software Support Personal Finance Software Updates Site Info, History, Ordering Security, Privacy, FAQs Questions About Investment Software? Call (800) 658-1824 or Send E-mail Free Downloads and Money Tools Free Sample Comprehensive Financial Plans Free Downloads, Investing Tips, and Tutorials All Financial Plan Software Module Demos Listed on One Page Links to Other Personal Finance Websites The World's Best Free Retirement Calculator Other Free Retirement Calculators Free Bond Yield to Maturity Calculator Our Free Financial Calculators Other Free Online Financial Calculators Free Business Owner Calculators Miscellaneous Pages of Interest How to Get Your Brokerage Account Data to Download into Our Financial Tools About Financial Plan Software Integration About Portfolio Management Software and CRM Software About Using Monte Carlo with Personal Finance Software About Investment Risk Tolerance About Using a Discount Broker to Manage Your Own Money (and about custodians for advisors) About Long-term Care Insurance Software Fixed Annuities - Exposed, and Variable Annuity Optimization |
First, some Generic Directions that Apply to all the Financial Software on this Site If you're still shopping, the best evaluation results are obtained by looking at the demo, while following along as you read these directions. The "demo" is the actual financial spreadsheet without the formulas, so you won't be able to do anything but look at it. You'll receive a working program after purchase. If you try to print the demo directly from your browser, or you just click Print in Excel, it probably won't work well. Tips for working with and printing in Excel are here, here, and here. When you're using the program, things go better if you first print out these directions. 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). Before getting started, save an unused copy of the workbook in a separate folder, so you'll always have the original unaltered file. Then if you do something like delete a formula, you can easily fix it by copying it from the original files. Then save files you've worked on using a different file name (and into a different folder). Tips for saving and organizing files are here. 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. How to turn the zipped file attached in your e-mail into an Excel spreadsheet is explained in the text of the delivery e-mail. 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. More generic free PC tips are here. If you're seeing this: ####, then either increase your Zoom magnification number (using the slider at the bottom right), or make the column width wider. If you see this: #REF! or #DIV/0! after completing your input, please respond to get a new program immediately. This 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 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). Read about financial planning software support How to send financial plans to clients via e-mail, without sending the whole program is explained on the Excel help page Basic input concept for most all of these Excel spreadsheets: Input your data into the green-shaded cells of the input sheets (usually grouped in the middle of the workbook). This will make data flow to the calculations sheets, which are on the far right group of sheets. This data flows through the calculation sheets (or just areas if there are no separate calculation sheets), which will then populate the Results and Charts sheets (usually grouped in the left of the workbook), where you look at the results. After inputting, 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. This prevents users from 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 gray when a non-zero value is entered into them. You can copy the input sheet to the far left unprotected Results and Charts sheet, and then make notes there, so you'll know why you did what you did in the future. 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 occur in the same logical order as shown on the calculation sheets (left to right then down again the next year). 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. The tax deduction on traditional IRAs are added as a contribution. Then the annual rate of growth is applied. Then all fees and expenses are subtracted. Then dividend and capital gains distributions are calculated and dealt with. Then taxes on these two distributions are deducted. When you input taxes, you're inputting the average of all taxes (federal, state, local, FICA, etc.), so don't use marginal rates (and account for all four types of taxes). Also, be sure to lower taxes during the withdrawal period because there's no more FICA (7.65% or 15.3%) to pay, and investors are also usually in lower brackets just because they have less earned income. Then the net dividends and capital gains are first used to fund withdrawals. If they're insufficient to meet the withdrawal amounts, then more money is taken from the investment fund. In non-qualified investments, to be as unbiased as possible, these amounts are deducted pro-rata from basis and the balance of unrealized capital gains (for example, columns BI & BL on the Non-Qual MF A-Shares sheet). If these two distributions are not used for withdrawals, then they're added back. 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 could be a never-ending cycle, so it only happens once (the amounts are insignificant on the second round). 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. 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 inputted last year to show numbers is reached. Then everything displays zeros. 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, into the same underlying investments, 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 (or has the most money left over at the end) 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 traditional tax-qualified plans. However, results are very close to the true no-load mutual funds into a Roth IRA. 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. This difference is just profit for life insurance company, agents, and shareholders (not you, the policyholder). 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 IC demos. 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. You think it's a no-brainer now, adding this reality into the mix turns the whole concept of whole life insurance upside down. Results will vary depending on dozens of factors. For example, just minor tinkering with tickets will make one method of investing beat or lose to another. 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 and renaming. "Non-Qual" stands for Non Qualified, or in other words, not any kind of IRA, Roth IRA, nor 401(k) nor other tax-qualified retirement account. So this would be just a personal non-IRA Schwab-like brokerage account. Here, Non-Qual, 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 (basis). 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, when compared to tax-qualified investing (traditional IRA or Roth). "Qual" stands for tax-qualified, like traditional IRAs / 401(k) / 403b / TSA / Keogh / SEP / etc. You'll get a tax deduction on contributions, the growth and reinvested distributions are tax-free along the way, but you'll have to pay ordinary the highest income tax rates 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 stiff 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. You basically just ignore all taxes going in and out with Roths. 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 also not taxed, and then you don't pay any taxes on any withdrawals. This sounds great, but the reality is that paying taxes on dividends / interest / capital gains along the way doesn't add up to being near as significant as traditional wisdom would have you believe. Why is explained in detail below. 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'll read below, you can use this investing software to simulate most every type of actual investment vehicle used in the Real World. So investment strategies input are not limited to just mutual funds. "NL" stands for a true no-load mutual fund. This is one with no front- or back-end commissions, and low (under 0.275%), 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 an initial 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 media something to whine 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 more and more as time goes by. So over time, paying an initial load is going to make you more money than getting more and more sucked away every year in escalating 12b-1 fees. The moral of the story is that if you're going to pay commissions 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 (early) withdrawals. These are called redemption fees, back end loads, or deferred sales charges (DSC or DFSC or CDSC - the F stands for Fund and the first C stands for Contingent. American Funds calls them CDSD). They are renamed different things here and there so investors will think they're not paying the same old redemption fees everyone else has. They usually decline annually and eventually go away altogether after a decade or so. B-shares usually also charge a higher 12b-1 fee, to make the money needed to pay the salesperson their up-front commission. So yes, the salesperson made an up-front commission, just as if they sold the A-share. 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 much higher annual 12b-1 fee. Since knowing the money will be there one way or another eventually, the fund company can "loan" the salesperson this initial commission, and then the 12b-1 fees will pay if back over time, with interest of course. 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). Investors fall for this because they don't understand, they think they won't any 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. So basically, the B-share investor was just lured into a carefully-planned spider web with the bait of "no load," where they're unknowingly stuck letting the spider suck the life out of them more and more each year. Things like this are what's known as "financial innovation" on Wall Street. "C" stands for the C-share class of mutual funds. This is where the mutual fund does not charge a front- nor 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 (like a Registered Investment Advisor, or RIA). 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. So if your money manager is charging 1% of assets, and then using C-shares, then you have a problem. "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 usually 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 which share class it really is. There's only "three knobs to turn" on the mutual fund money-making machine, so where these knobs are set to determine which share class it 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 planner to help you, because they're not getting paid. "ETF" stands for Electronic Traded Fund. Here there are initial commissions / tickets to buy, then the ETF will have internal management fees just like a mutual fund, then there are selling commissions / tickets (but no 12b-1 fees). All about ETFs are discussed in the Money eBook. "Ind Sec" stands for individual securities. Think brokerage account, where you pay commissions every time you buy or sell anything (stocks and bonds). 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). There are no internal management nor 12b-1 fees here. IC Program Directions Step 1: Once you've unzipped the file Investment Comparator.zip, open Investment Comparator.xlsx with MS Excel. Go to the Input sheet tab. Input all of the generic data into cells B4 - B10. 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 (B69). 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 B12. This will automatically be reduced by whatever initial front-end load and/or sales commissions are paid, but only if you input "Y" to the switch in cell B13. This percentage amount is input into cell B47 of the Mutual Fund Input section or B82 of the ETF / stock section. This amount is deducted only from the initial balance in cell F4 of the MF A-Share, ETF, and Ind Sec sheets. To account for any differences between this automatic reduction and the Real World, you can use the Annual Contribution Manual Override in cell L4 (usually) for the initial year's load. 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 (usually) shown in column O on the tax-qualified calculation sheets. This advantage is negated somewhat when withdrawals come, because EVERYTHING is taxed at one's highest ordinary income rate when withdrawn. 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 will probably be probably lower, which means you'll 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 all ignored along the way (which also happen in traditional tax-qualified plans on the unrealized capital gains) when withdrawn. Also when you pay taxes on dividends / interest / capital gains along the way on ("unwanted") distributions in a non-qualified account, these amounts are nowhere as large nor significant as people postulate, because you're paying them in the early years, when the account balance and distributions, are relatively small. Then when these get reinvested, it increases basis. For example, please see columns BJ - BN on the Non-Qual No-Load MF sheet. Not knowing this is one of the biggest "mistakes" people make when touting the need to invest your money mostly into tax-qualified plans. Now that numbers are being correctly calculated, you can see how wrong they are for yourself. The bottom-line is that their biggest complaint (paying taxes on interest / dividends / capital gains distributions along the way in non-tax-qualified accounts) ends up being about one-fifth of the amount as they thought. There was no way to determine this until a program like this was created (so everyone was just guessing and using "gut instinct" instead of actual numbers). When you reinvest these taxable distributions 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). Basis is good - the more the better. This also explains why there isn't such a dramatic difference in non-qualified and qualified as you've probably been brainwashed to believe 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'll have more money than you did in the past, and it's ALL taxable. Whereas in taxable accounts, there is a lot more basis than everyone thinks, which is not taxable. Basis is just getting the money you put into the investment back out, which is not profit, so it's not taxed. Then tax rates are very different. In traditional IRAs, everything is taxed at your highest ordinary income rates. In non-qualified accounts, tax rates on dividends and capital gains are much less (sometimes much less than half as much). Back to the moral of the story again - when you're paying a percentage of your investments for something, in this case taxes (or loads), you want to do it early on, when the account is the smallest, and not later on, when the account is the biggest. This is where the biggest difference comes in when comparing tax-qualified and non-tax-qualified methods of investing. So when you hear the words "tax deferral," most of the time it's safe to think that you'll be paying way more later in life for the same thing, just to get immediate satisfaction today. In non-tax-qualified accounts, the amounts you pay in taxes every year (from those annoying and mostly unwanted 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. 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. This is repeated on purpose so you'll get it. 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. Back to the directions. 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." You can also use Excel's built-in Goal Seek function in addition to them to do any What-if scenario you want. 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. and then you have to remember to account for this: This is an important thing to keep in mind when you run / input 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). So when you input withdrawal amounts into the program, do this: Assume the insurance ledger says you're withdrawing $1,000 per month. There are no taxes taken out of this, so you're really only withdrawing $800, assuming you set the ordinary income tax rate in the program to be 20% (because you have to pay $200 in taxes, which is not accounted for anywhere in either the ledger nor this program). So when you input withdrawals into this program, you'll need to enter $800 and NOT $1,000, to make a true apples-to-apples comparison (because all withdrawals input into the program are after-tax spendable money. The IC program automatically takes the $200 out in taxes from the investment account withdrawals). If you don't do this correctly, then you'll just be using the same logic / math that the insurance industry hype uses, and this will make the insurance contract look way way way better than it actually is in the Real World. Another way to look at this example is that if the IC is spending $1,000 per month, then the insurance ledger withdrawals need to be set to $1,250 ($1,250 - 20% = $1,000). Step 3: Contributions: Input the amount of annual contributions into cell B14. Input the age when they start into cell B15 and the age they stop into cell B16. Then input the percentage amount of annual growth into cell B17. This is the gross rate of return on the whole investment account, AKA annual profit. Keep in mind that all total returns numbers reported by mutual funds are AFTER both internal management fees, and the 12b-1 fees. So if you're modeling a mutual fund that has a reported 10% total return, and 1% in total fees, then input 11% here. These two fees will be automatically deducted later. WE DID NOT DO THIS ON THE BUY TERM AND INVEST THE DIFFERENCE DEMO - so the non-whole life investment accounts would last years longer if we did that. We just input 10% on everything, when 11.25% should have been input as the gross return (because the ledger used 10% gross). The way the demo looks now, the internal management and 12b-1 fees (1.25%) are being double counted in the buy term demo. This was done to stop all of the arguments from people that this is too complex for. Everything still beat VUL by a large margin, so this was a pragmatic compromise. Front-end loads and initial commissions will be deducted from them automatically in the A-share and Brokerage Calculation sheets, but only if you input "Y" in cell B13. If you don't, then the number input into cell B12 is the account's starting value. 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 (unless you duplicate the manual override input into the other sheets as well, so remember to do that). 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 (retirement) withdrawals begin into cell B19 and the age they end in cell B20. Input the annual withdrawal amounts into cell B21. Input the percentage amount of annual increases into cell B22. 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 ordinary income tax rates. One for the period of time before withdrawals occur (accumulation phase) and one starting the year withdrawals begin (distribution phase). This is because withdrawals usually start to occur in the years of retirement, and most people are in lower tax brackets then. Yes, you can make them the same if you want to, and/or .you can manually override them in any year. Some life insurance product general ledger illustration software have these features, so they were implemented here as well by manually overriding tax rates in any year. Inflation: Cell B33. This does nothing to any of the calculations. All it does is show the inflation adjustments on second table of the IC Charts sheet. Mutual Fund Input: Input the annual rate of return (profit) into cell B38. You can manually override this in any year on the calculation sheets. Keep in mind that all total returns numbers reported by mutual funds are AFTER both internal management fees, and the 12b-1 fees. So if you're modeling a mutual fund that has a reported 10% total return, and 1% in total fees, then input 11% here. The fees will be automatically deducted later. Input the percentage amount of money subtracted from all contributions (A-share front-end loads and/or sales charges / commissions) into cell B47. These are in addition to the $5 to $30 trading ticket charges that are charged when you make an investment trade through a custodian, like Schwab. So if you're paying these, then you'll need to account for them here too later. Input the percentage amount of money that is subtracted from liquidating shares into the B-share rate schedule into cells B49 - 58. If there's no withdrawals in the first decade, then there will be B-share fees unless you manually account for them in the manual overrides. Input the annual percentage amount of mutual fund management fee into cell B42. 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 B43. Input the annual 12-b1 fee for the B-share mutual fund into cell B44, and B45 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 B39. You can call the fund to get a past average. Input the annual percentage amount distributed as capital gains into cell B40. Again this varies, so call the fund and ask for an average. The two items above come out of the gross rate of return input into cell B38. 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 B32. This is usually a nominal amount around $25 to $100 annually. Choosing Trading Costs: There are three ways of accounting for trading costs and commissions on the three ETF and brokerage account sheets. This is done by using the drop-down menu in cell B79 of the Input sheet. First, you can set an overall wrap fee, and all of the trading commissions and ticket charges are ignored. Next, you can select an average percentage commission rate. Here all buy and sell trades are charges this set commission. Lastly, you can set ticket charges per trade. Then after you set the cost per trade, set the average number of trades made per year. With mutual funds, you can also set the periodic reinvestment ticket charge to be less than initial buy tickets. You can usually invest (contribute) more money into an existing mutual fund at a much lower ticket, compared to what it costs when you initially buy it. Plus be aware that tickets can vary depending on if the account is qualified or not too. Ticket charges are not turned off on the mutual fund sheets, like they are when another method is selected like in ETF and brokerage sheets. You can manually override most of this on the calculation sheets. Also, there is a switch to select if you want to deduct the initial commission from the initial account balance too in cell B13. The Bank Rate of Return Input Cell: The rate of return in cell B69 is to account for the much lower rates of return in Bank CD or money market investments. Generally speaking, these three calculation sheets are for the types of investing where there is only interest, and no possibility for capital gains nor losses. In other words, FDIC guaranteed CDs, money market funds, savings accounts, and other short-term money market instruments (AKA cash). 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 (unless it's a Roth IRA), and there are never any dividends, realized or unrealized capital 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 these sheets 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: The numbers for annuities and life insurance cannot be accurately generated with generic investment software. They need to come from state-approved insurance 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 insurance agent that sells that carrier's products. So this program just guesstimates everything on the insurance company product side. But the numbers work out to be very close to actual ledgers if enough attention is paid to all of the policy expense inputs. 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. Sorry, but we can't give advice on how best to obtain these figures, other than to use a relative or friend that's a licensed agent. Try your P&C agent - the people you get your car insurance from. Here's how to 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'll 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 have been deducted. In general you'll 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. Please read all of the bold critical items below before inputting! Now for the Critical Differences in the Term Comparisons 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. Column J doesn't do anything and is just FYI. 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 B38 of the Mutual Fund section of the Input sheet. If you do it this way, then input 0% in cell B42 and B43. You then input estimated mutual fund fees and expenses into cells B42 - B45. If you're confused here and bought support, please ask what to do and you'll get an answer on how to most accurately model buy term vs. whole life. The difference between gross and net returns shown 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). 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: When doing a Buy Term & Invest the Difference analysis, you must account for the costs of the term life insurance in all of the mutual fund investment calculation sheets. The whole point is to compare the two ways of maintaining the needed amount of life insurance. 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. The face amount is the amount of death benefit. You'll 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 into them (every type of tax-qualified plan has annual contribution maximums, and you'll usually exceed these within a few years). If you could, then the life insurance product would look several times worse than it already will (buying term and investing the difference would win way bigger than it already is). Ensure the IRS PS58 costs are included in these term costs, if appropriate. About term life insurance PS58 costs: This is a tax table used by the Internal Revenue Service (IRS) in evaluating Split Dollar Life Insurance plans as to the extent of the economic benefit that is considered taxable ordinary income to the employee. The taxable ordinary income to the employee is the premium cost of one-year term insurance on the life of the employee minus that portion of the premium paid by the employee. If the employee pays that portion of the premium that is in excess of the economic benefit, the employee incurs no ordinary income tax liability. The premium cost of one-year term insurance at each age is listed in this IRS table. You basically need to calculate them yourself, and then add them to the term costs when doing a buy term comparison. So the only columns on the Buy Term Results sheet have meaningful comparison with insurance products. Most of the IC results can be ignored. If you want to do it anyway, all you need to do is use the Withdrawal Manual Overrides to add the term costs into the qualified sheets. Be sure to also add them to any existing withdrawals. You will not get any errors saying that you cannot contribute this much to a qualified plan. 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 in the early years. 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). Because of this, most life insurance companies don't even offer true ART anymore. The next critical item: Set the A-Share and No-Load Funds' internal management fee and 12b-1 fee both to 0% (cells B42 & B43 of the Input sheet). If you don't then it's not a fair comparison, because the exact same fees in the VUL subaccount are not deducted (or another way to look at it is that they're double-counted on the IC investment accounts). Only the fees and expenses that the life insurance company charges is deducted from gross returns. Another way to look at it is that you'll account for them twice on the mutual fund side if you input them here, because the gross rate of return is always after these two fees (the same with the gross return on the insurance product). The next critical item (if you want to do this, most don't): 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 read about this on the life insurance calculator 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. So if you're doing a serious analysis for yourself, then you'll 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, then create both another term life insurance ledger, and 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 (and/or contributions and withdrawals) every year. This is called "Universal Life" (UL). These days, it's mostly all "Variable Universal Life" or just "VUL." The ability to easily make these annual changes is why it's called "universal." It's called "variable" because you're able to fund it with non-fixed investments, called subaccounts (traded mutual funds), so the account values "vary" with the markets. All life insurance that is not term is automatically called "whole life" insurance. Term is just paying for nothing but pure life insurance over a specified term (usually one year). Term has no "investment account" attached to it. When you add any kind of an investment account to term it's then called "whole." Whole life can be universal (UL), and when this happens, the only difference is that the funding vehicles are all only the insurance company's "general fixed account," and thus don't vary with market conditions (you just get a paltry amount of interest, like a very lame CD, which is why nobody does that anymore, so it's been all VUL since that came out). If this is not possible (we're still on inputting declining future face amounts), 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 Buy Term Results 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 whole life 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 refunded 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 GROSS / 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. This is a night and day difference. So if you're inputting $1,000 annual withdrawals with the IC, 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 (always use identical tax rates on both sides), 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). Okay that's it for the buy term and invest the difference directions. If you bought support, then it's highly recommended that you now zip the program up with your input in it, and e-mail to us so we can check it for you at no charge. We want to see what actual people are doing so we can make these directions better. Accounting for policy loans: For this, you run a separate insurance ledger showing the long-term effects of taking policy loans. There are no provisions in the software to account for this because each policy is different. The basic concept is that loans are tax-free, so this is another "advantage" of the life policy. Instead of withdrawing the money, and paying ordinary income tax rates, you just borrow it from the policy's cash value. In reality, this money is just borrowed from the cash value, so there's two ways to go here: 1) Don't pay if back. Then your cash value will deplete faster because the insurance company will just deduct the interest from the cash value one way or another (along with fees and penalties as there's no free lunch, especially with life insurance companies). Then your money will run out much faster than you expected. 2) Pay the loan back. The money spent in interest is usually more than the taxes saved, so you're just robbing Peter to pay Paul here. Even though you're paying yourself instead of the government, the bottom line on the policy is the same - it's going to run out of money around the same time as if you never borrowed if you pay it back. So using policy loans to fund withdrawals are NOT an effective like insurance strategy. It's a great naive sales gimmick, but it doesn't work in reality unless you only care about the short-term. In the long run, you're either going to come out the same or behind if you pay if back. Here's more generic info about life insurance policy loans: You pay no income tax if you borrow cash value from the policy through loans. As a general rule, loans are treated as debts, not taxable distributions. This can give you virtually unlimited access to cash value on a tax-advantaged basis. After a sizable amount of cash value has built up, it can be borrowed against systematically to help supplement retirement income and in many cases, never pay one cent of income tax on the gain. Several cautions regarding policy loans: First, loans are charged interest and policy loans reduce the death benefit and cash value. Second, if a policy lapses or is surrendered with an outstanding loan, and the amount of the loan plus the cash surrender value is more than the sum of premiums paid, the excess will be taxable. Third, if the policy is a modified endowment contract, the loan and interest on the loan paid from cash value may be taxable and, if you are under age 59 1/2, may also be subject to a 10% penalty tax.) Pros: The money you borrow from your life insurance policy is tax free. A loan does not have to be repaid, and if you want to reimburse your account, there is no set timetable to do so. Unlike a 401(k), you don't get penalized for early and frequent withdrawals. The interest rate can be higher than the traditional savings account in banks offers. There are no hassles about asking for your money, nor are there questions about when you are going to pay it back to the insurance company. Cons: If you decide not to repay the loan, it will drop the face and cash value of your life insurance policy. The loan amount will be subtracted from the amount to be paid out upon your death. For instance, if you have a $100,000 policy and you borrowed $10,000 and didn't pay it back, your beneficiaries will only receive $90,000 from the insurance company. If you decide to surrender your policy the same process will happen. Some companies may tack on steep fees or service costs for taking out a policy loan. Misconceptions: A policy loan is vastly different from a loan from a bank. There are no penalties for not making payments on time and you cannot on a policy loan. Another huge misconception is that the money you borrow is your money. Technically it isn't. The amount in your cash value account is yours, but the loan is from the insurer. Your money is used as collateral. It may seem confusing but it justifies why your loan amount grows with interest over time. Borrowing your own money shouldn't have an interest rate. Warning: Not paying your policy loan back is your decision, however, there are dire consequences if one does not. If your loan plus interest exceeds the cash value of the policy, it is possible that your insurance company will bill you for the difference. It is also possible that the money that was received tax free may become taxable income in those rare situations. Besides the fact that such a loan over time can wipe out the benefit amount you wanted to pay your loved ones, you could be forced to pay for something that ends up being worthless, which could be worse than having no life insurance policy. For Fixed Annuities: Input the end-of-year values into column AC of the Insurance Input sheet. When it is annuitized (starts paying income), the market value disappears, so input all zeros starting in that year into column AC (as shown in the demo). 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). Important: 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 inputted. 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 input fields 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 (by deducting the sales charges / commissions from the before inputting). Annual Rate of Return Manual Overrides: Use these input fields 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 these input fields to change the amount of annual dividends and capital gains distributions on the non-tax-qualified accounts. Tax Rate Manual Overrides: Use these input fields to change the tax rates to be whatever you want them to be in any year. Annual Withdrawal Manual Overrides: Use these input fields 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 ten years. Use these input fields to account for this by inputting the schedule obtained from the mutual fund family, website, 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 comparisons. 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-1 fees 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 (usually the same as B-shares). 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. You'd do the account the same for the accumulation years too, by adding these amounts into the contribution manual overrides. 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 annual 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 Results and Charts sheets. The dollar amounts are only on the last columns of the calculation sheets. These columns on the calculation sheets don't affect any calculations. 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. If you bought support, then it's highly recommended that you now zip the program up with your input in it, and e-mail to us so we can check it for you at no charge. We want to see what actual people are doing so we can make these directions better. |
Personal Finance Software Modules For Sale (are listed below) Fully-Integrated Financial Planning Software Menu of Retirement Planning Software Asset Allocation Overview with Historical Portfolio Performance Comprehensive Asset Allocation Calculator Asset Allocation Models with Investment Track Record Monthly Updated Mutual Fund Picks TVM Financial Tools and Free Financial Calculators Financial Planning Fact Finders Investment Policy Statement Software Life Insurance Needs Calculator Rental Real Estate Analysis Software Money eBook for DIY Investors and Financial Advisers Financial Planning Marketing Tools Our Unique Financial Services Fee-only Hourly Consulting on Anything You're Willing to Pay For Using Custom Investment Benchmark Portfolios to Compare Performance Free Financial Planner Directory Coaching for Financial Planners Pay Listings for Buying or Selling an Investment Management or Financial Planning Practice Personalized Optimized Allocations for 401k / 403b and Similar Retirement Plans |