Directions for the
|Go to the Main Investing Comparitor Software Page||Download the Free Investment Comparator Demo||Buy Term vs. Whole Life Analysis (BTID)||Download the Free Buy Term vs. Whole Life Demo||Using Discount Brokers to Manage Your Own Money|
(is listed below. The financial planning software modules for sale are on the right-side column)
Confused? It Makes More Sense if You Start at the Home Page
Discounts for Financial Advisers
Questions about Personal Finance Software? Call (503) 309-1369 or Send E-mail to email@example.com
Free Downloads and Money Tools
|First, here are some generic directions that apply to most of the personal finance software on this site:
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 need to print something on a password protected sheet, and you need to tinker with column and row sizes, and you bought support, and it's in Excel 2007 format, then you can get a version sent to you that does that.
When you're using the program, things go better if you first print out these directions.
Click "Enable Editing" to make it so you can change inputs. If you don't do this, then "nothing will happen" when you input data. Any change is editing (according to MS). This occurs because the program is not in a "Trusted Location." Go to File, Options, Trust Center, to fix that.
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).
About the colored sheet tabs: Blue is a presentation sheet. Light green is an input sheet. Green is both an input sheet, and a presentation sheet. Orange is a calculation sheet. Purple is something else.
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 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).
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.
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 change your Zoom magnification number (using the slider at the bottom right), or make the column width wider. If you see this: #REF! after completing your input, please respond to get a new program immediately. This usually means data was lost either via e-mail, unzipping, or while making a CD.
If a red Error! dialog box comes up saying your input must be confined to a range of values, and your input was within that range, then the problem is that the input cell doesn't accept pennies. Try again rounding the value to the nearest dollar. If it still happens, then send e-mail with the spreadsheet name, sheet tab name, and exact cell number, so it can be changed.
If you're an investing consumer (not a professional financial planner working with clients), then when you read "client, prospect, or they," just think "you." You would be both the advisor and the client (or spouse).
How to send financial plans to clients via e-mail, without sending the whole program.
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, 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 a light-purplish-gray when a non-zero value is entered into them.
About the colored cells: Medium or light gray is either a title heading, or an input cell after a non-zero value is input into it (it's green when either zero or empty). Peach (or pinkish, depending on your Excel version) is a (usually password protected) field that changes when input changes. Light gray cells are information-providing, non-input, cells that don't change with input. White cells usually don't have anything in them, except on the unprotected presentation sheets (and change when input changes).
You can copy the input sheet to the far left unprotected Scratch Pad, Results or Charts sheets, then make notes there, so you'll know why you did what you did in the future.
Benchmarking Software Directions
Open Benchmarking.xlsx with Excel.
It will open to the Asset Classes Bench Returns sheet. Bench should say Benchmarking, but the name is too long.
This is where inputting starts, unless you're only interested in asset-level vehicle comparisons. If so, then go to the Asset Benchmarking sheet, and jump ahead in these directions.
The Results and Graphs sheet is just what it says - it has the tables and graphs that show the results of the asset class comparisons (no data from the Asset Benchmarking sheet flows here).
In summary, what you're doing is inputting asset allocation weights of up to three investment portfolios. Just one can be input to play around with things, but at least two are required to make comparisons:
• The Current investment portfolio. In other words, the one in question (and usually held in the Real World).
• The Guideline portfolio. Any allocation mix that is essentially something a software "algorithm" or adviser thinks will better suit one's needs is recommended to replace the Current mix - we call Guideline. This is because, as you can read here, there is no such thing as a perfect mix. It's all just a guess just to go in the right, or in a better, direction (than Current).
• The Proposed portfolio. This is one an adviser recommends. If it's the same as the Guideline, then there is no need to input it, as it would be the same. This is here because in the Real World, situations where the investor is adamant about holding too much of one asset class, usually real estate, is common. So this allows the adviser to input a compromise investment portfolio showing this overweightedness. Then this can be compared to the Guideline, which should never reflect any "personal preferences" like this. The differences between Guideline and Proposed then serves as the wake up call to the risks of being overweighted (or underweighted).
Regardless of the naming, the point is to be able to compare three asset allocation mixes at once.
So the directions assume you'll be inputting all three, as shown in the demo.
The first thing you're probably going to think, is that you don't want to use the asset classes that come with it in row C. Just change them, the borders, and/or the colors. Name changes flow automatically throughout the rest of the program. Look at it as being able to work with fifteen asset classes of your choice.
First, you'll need to calculate the asset class weights. There is no free and easy calculator to perform this tedious function. The Comprehensive Asset Allocation software will do this, but it's not free (and integrating all of these programs together in the future will depend on how much users want (to pay for) it.
Input the Current investment portfolio's asset class weights into row C. If your input does not total up to 100%, then red numbers showing how much you're off by will display in column R.
Repeat with the other two portfolios in rows 9 and 13. That's it for that.
Now you'll need raw investment data for benchmark indices. This is always the hard part. Input that for each time frame of interest into row 6.
First, you'll have to find and choose the best-fitting ones. HA! Morningstar AWS has thousands to choose from. Just large-cap growth offers dozens of choices. Then when you see how pathetic they've laid all of this out in their crapware, your brain will explode. Then when you nail down a good benchmark index, the data will either be wrong, missing, or just lousy. Best of luck with all that! Now you feel my pain, haha!
That's all there is to it after repeating for all of the time frames of interest below. All numbers and results then display for evaluation.
Understanding the six tables and graphs: What you're doing is comparing three sets of asset allocation mixes, using benchmark index returns. Since there are three allocation mixes, and two ways to evaluate them, there are a total of six tables. The two graphs next to each table are the same things, just different types of chart layouts to help get it.
The first way to evaluate, is by comparing three sets of allocation mixes using benchmark returns over the eight time frames. It would be more, but Morningstar fails to provide easily accessed data for more than fifteen years.
The other way to look at it, is to compare returns of the allocation mixes. The data points are the same here, the only difference is how the numbers show up in the tables and charts. In other words, the two sets of three tables display the same data, but in a different order.
Another way to look at is, is that the top left number in the first three tables make up the first row in the first table of the second set. There's no good way to explain it, so you'll just have to stare at them until you get it. All of the formulas are unhidden, so you can trace everything back to its source.
That is it for that.
Next, the Asset Benchmarking sheet:
This is for comparing asset-level funding vehicles, so it's much easier to understand.
Input the name of the asset into the current input field in column C. Then input their return data from columns D through K.
The one example in the demo has data as of the end of March 2016. This is why Last Quarter and YTD are the same. Note you can compare up to 50 assets at once. No data flows beyond each asset's section.
The results display in the charts to the right, which again are the same, just a different chart type.
That's all there is to that too.
As you can see, now that a template was created, the hard part is data. This is why we offer to populate yours with data for $100 per hour.
Answer to the question: "Where are all of the portfolio statistics in these comparisons?" First, they're not there because calculating them requires inputting monthly returns over a long time frame.
Next, they're completely useless in making future predictions about how a portfolio may perform in the future.
So if this what you want, then you'll need to buy Morningstar, which is very expensive, and is one big bug plagued by missing data. Then it's not capable of making comparisons like this even on a good day.
So long story short, there is no investing benchmarking software that performs all of the functions you want.
The Portfolio Statistics Sheet
This sheet calculates most all of the popular investment ratios and statistics people like to see. It's more for investment professionals, but even the novice investor may get some use out of it.
It's our opinion that the vast majority of these things are completely useless in forecasting anything. It's sometimes interesting to know what happened in the past, but none of this stuff has any value at all when trying to predict the future.
After you've input a series of monthly rate of return numbers (all portfolio software like this works based on monthly returns) for the one investment you're interested in, it will calculate the investment's:
• Correlation coefficients (r) compared to the S&P 500, Barclays & S&P US Aggregate Bond Index, the MSCI EAFE Int'l stock index, and a custom benchmark index that's totally under your control. Just think U.S. stocks, bonds, and int'l stocks.
Calculating these numbers was the main reason for building the sheet - to let you input investments and see how they've moved compared to these three major asset classes over time frames that you can select (on a monthly basis going back to 1976).
This is the major factor that goes into deciding whether or not the adding the investment will make an investment portfolio better or worse (AKA more efficient or optimized). If it has low correlations, and decent returns, then adding the investment to the portfolio will reduce the portfolio's overall risk, while more than likely increasing its returns, at the same time.
• Average, median, minimum, maximum rates of return over the selected time frame, along with the standard deviation of monthly returns. The most important one here is the standard deviation. The higher the number, the more "risky" it is.
• Beta, Alpha (Jensen), R-squared, Treynor Ratio, and Sharpe Ratio (using the S&P 500 as the benchmark index and a rate of return for the risk-free investment that you input).
Data has already been input for the three major asset class benchmark indices going back to February 1976. But to keep in updated, you'll need to input current month's data into the green-shaded cells starting about F34 (this changes monthly). The numbers to input are the percentage change in the index or investment, from the prior month.
Sorry, but there's no reliable data source you can get for free online that can be recommended. There are hundreds of sources, like just taking the end of month values from the newspaper. We can do this for you at the consulting rates. These historical investment returns are updated monthly on the table here.
The investment you input into column Q is called the Custom Reference Asset (for lack of a better name). This is the investment that you're curious about. Most investors familiar with portfolio statistics know what they are and what they're for.
So they usually want to know things like, what the correlation coefficient and beta was compared to the S&P 500, and what the standard deviation was compared to bonds over X time frame.
If you're considering adding or deleting an investment (stock, bond, mutual fund, ETF, doesn't matter), to a portfolio, then it's good to know if it may go up more when the S&P 500 goes up, down more when it goes down, by how much, any combination of that, what the standard deviation / beta, of it is, etc.
This will calculate these basic statistics just as well as software costing dozens of times more, but you'll have to input all of the monthly returns going back to the start of your time horizon. Actually, when you buy expensive portfolio optimizing software, you'll need to input monthly returns for everything not included in its database, too.
First determine how much data (monthly rate of return numbers) you have or want to use for the reference asset. The actual Moderate or Aggressive Model Portfolio was used in the demo, and all of the data was input since its inception in January 1999. So if you're going to use this, then first delete all of that input in the green-shaded areas in columns O & R.
Next input the earliest month into cell D6 in this format: 1/1/99. The "1" in the middle represents the first day of the month, and has to be there because of Excel's limitation when it comes to working with dates. If you're getting errors it's probably because you didn't use the exact format required. Also, for years in the 21st century, you have to input the whole year like this 1/1/2014 or Excel may think it's 1914 if you input 1/1/14.
Next input the current T-Bill (money market or CD) rate, or whatever you want to use for the risk-free asset's rate of return into cell D5. If you don't know what this is, or what to use, just input 2.5%. This is AKA "rf."
Then input how much money you want to use as the starting value of all of the investment vehicles into cell D7. This is needed for the graphs sheet to the right.
This value is used as the first year's starting value for everything, and is then compounded by all of the subsequent monthly rates of return. So if you input 1/1/99 as the first month into cell D6, and $10,000 into cell D8, as shown in the demo, it will assume $10,000 was deposited into everything on 1/1/99, and that's what will compound in columns E / H / K / N / & Q. These are the columns of numbers used on the graph sheet.
Then input the ending month of your time frame into cell D7.
Now input your monthly return data into column R. It's best to start at the beginning month and work your way up. If you don't input anything into a month, then it will be treated as 0% for the month. Ensure to account for every month up to the current month. Input negative rates of return with a minus sign.
Once you've input your data, the statistics are automatically generated.
If you've input a date earlier than the first month of monthly returns, then you'll get an error saying not to do that. The data shown from F13 - N13, and all of column Q / R will be wrong, but everything else will be okay.
You can change the date input into cell D6 to any month after the first month of custom reference data you input, and it will work just fine. Going back more than five years is less meaningful the further you go back when it comes to correlation coefficients and other statistics, because the world changed too much since then. There is no perfect time to look back to, and even Bill Sharpe changes his mind periodically (he said seven years in the 90's then changed it to five in the 21st century).
The Custom Benchmark Index (column O): Use this if you want to input another benchmark in addition to the S&P 500, Bonds, and Int'l stocks.
All you'd need to do is find and input the data, and statistics will show in cells N / O13 - 18 just like the other three indices. Everything will be blank in the statistics cells until data is input. We used the same allocation as the Moderate or Aggressive Model, but funded with benchmark indices. This allows the proper calculation of Alpha of our actual Moderate Model.
Select which one of the four benchmarks you want to use to base the reference asset's statistics on by using the drop-down menu in cell L25 (the arrow for the drop-down will display when you click on cell L15). Then the data in cells Q21 - 24 will reflect the custom reference asset's stats based on that benchmark.
There's no long-winded explanations about what the statistics are, or what they're good for, because this page is already too long, and you can just do a search and enough information will come up to give you a headache. Here's a very old Word docx you can download with a summary of investment management terms here.
There's also more than enough explanation to understand correlation coefficients (r) and what this is used for in portfolio optimizers, in the text that explains the asset allocation reports.
The Investment Portfolio Statistics Graphs
The portfolio statistic's graphs were put on a separate sheet so the protection won't interfere with changing the date ranges and other alterations. The stats sheet requires a password, but the graphs sheet does not.
You can alter date ranges to see similar graphical information as the old Ibbotson's (now Morningstar) "Growth of $10,000 since a very long time ago" for the S&P 500, Barclays & S&P US Aggregate Bond Index, MSCI EAFE Int'l stock index, a custom benchmark that you can use, and the inputted custom reference asset.
The data table (that you can see on the main sheet) goes back to 1976 for them, so you can see how these markets have done compared to each other (and the custom reference asset, which is the actual Fee-Based Moderate Model Portfolio here (on the Portfolio Statistics sheet)).
To change the date ranges, click on one of the colored sections of the chart. A long formula will appear in the formula bar. The middle and last series of numbers are just the ranges of the month name, and the amount of money the index has grown to.
If you wanted to see the whole historical performance of something going back to 1976, just change the row range. Just remember to change the date ranges for all (five) graph series if you're tinkering with the top graph that shows all five together.
The title of the graphs say Growth of $10,000, so you'll probably want to change that. Just click on it and type what you want it to say.
The names on the little squares at the bottom can be changed by editing the far left part of the formula after the word, "SERIES( Just keep in mind that is has to be in quotes.
If you mess up editing a graph formula, you'll know because you'll get the red error window when you hit return. Just press the escape key and try again.
You're free to alter the graphs any way you want, as there is no protection.
IFP users: You can copy the latest month's data from the free demo here.
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 for 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 then 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). In general, don't input a tax rate more than 35%, as it's rare that someone would actually be paying 35% of their gross incomes in taxes. When people say they're paying over 50% in taxes, that's just adding up all of their highest marginal rates, which nobody pays. Always use average /effective in financial software.
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 realized 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 first from basis and then from the balance of unrealized capital gains (for example, columns BI & BL on the Non-Qual No-Load MF sheet).
If these two distributions are not used for withdrawals, then they're added back (which adds to basis).
The more basis there is, the more withdrawals will come from tax-free returns of original principal, 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 investment 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 a 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 or blanks.
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. In other words, everything is held the same, except for the investing strategy method and vehicle used, so comparisons are apples-to-apples.
The investing method and/or investment vehicle used 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.
Stocks, ETFs, and mutual funds are three examples of different types of investment vehicles.
Given Real World input, the traditional tax-qualified account (IRA / 401k) is usually going to win. This is usually because of the investor getting the initial tax deduction while they're in a higher tax bracket during their earnings years. The problem with that is you can only invest so much money a year into traditional tax-qualified plans. However, results are very close to the true no-load mutual funds invested into a Roth IRA.
When it comes to deciding which way of obtaining life insurance coverage is best, careful input and attention to detail will usually always result in buying term and investing the difference being a superior investment strategy than buying any form of permanent life insurance (AKA whole life, or VUL, which stands for variable universal whole life insurance). This difference is just profit for life insurance company, agents, and shareholders (not you, the policyholder).
Also, in the Real World, the amount of life insurance one needs declines rapidly every year, as assets grow, 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 Investment Comparitor 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 several times.
The annual escalations in term life insurance costs are the biggest item insurance agents point to when selling whole life insurance policies. When just 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.
The second fallacy the life insurance industry perpetuates, is that you can take withdrawals tax-free by using the policy loan feature. That scam doesn't work for the policyholder, but it works out great for the life industry. It doesn't work in the Real World because interest builds several times faster than the taxes that would have been paid. Little-to-nothing works out as advertised in the life insurance industry, when actual math is applied to correctly analyze scenarios.
Results will vary depending on dozens of factors. For example, just minor tinkering with tickets may make one method of investing beat or lose to another.
About the Sheet Tab Names
Abbreviations were needed because of the character limitations on sheet tabs. You cannot change the sheet tab names because the workbook is protected.
"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 personal or joint 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, reinvest, along with trading costs, all adds to 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 much 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. Much more about why you need to average over 11% to get any value from tax-qualified investing, given the current low tax rates, is below and also here.
"Qual" stands for tax-qualified, like traditional IRAs / 401(k) / 403b / TSA / Keogh / SEP / etc.
This is AKA a "tax wrapper," because you can buy most all investment vehicles using a qualified account. When you do this, you're just wrapping the vehicle up in a tax wrapper (just like wrapping a present). This tax wrapper then defers taxes until you withdraw money.
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 of the money when you make withdrawals (and there are tons of rules about what you can and can't do, and stiff tax 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 (using true no-load mutual funds). Again, the bottom-line is shown in the demo. Crunch the numbers and do an audit yourself and see.
"VA" stands for variable annuity. Variable means the value of the units (shares) inside the annuity "varies" daily with the markets.
"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.30%), 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, then when the higher annual B & C-share 12b-1 fees are applied as a percentage of the total account balance, which are much bigger, it eats away at your money more and more as time goes by.
So over time, paying an initial load just once is going to make you more money than getting more and more sucked away every year in escalating 12b-1 and insurance contract fees.
The overall 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. So doing it up-front, when the account is the smallest, is the best way of the three ways. 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 also 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, with B-shares, the salesperson made the exact same up-front commission, just as if they sold the A-share version of the same mutual fund.
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, 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 there won't any be selling while the redemption fees are in force, and they don't want the obvious 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.
"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 is using C-shares on top of that, then you have a problem.
"LW" stands for Load Waived, which means one buys the A-share class fund without paying the initial sales charge. It's almost the same as NL or No Load. In most cases, these are usually not available to the individual investor. This had promise, but then petered out (because it wasn't paying Wall Street enough).
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. So that's the great "DIY vs. Advisor" conundrum.
"ETF" stands for Exchange 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 usually no 12b-1 fees). About the advantages and disadvantages of using ETFs are discussed in the Money eBook.
"Ind Sec" stands for individual securities. Think Merrill Lynch 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: Open Investment Comparator.xlsx with MS Excel 2007 or later.
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 or blanks 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 J4 or 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 the main benefit of this method of investing (tax wrapper), so it needs to be accounted for.
So it's added back to the $1,000 contribution. Instead of contributing $1,000, you really contributed $1,200. This is (usually) shown in column M, N, or O on the tax-qualified calculation sheets.
The huge advantage in this tax wrapper 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, and not as good as the traditional wrapper.
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'll 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 the Roth tax wrapper - the fact that taxable events are all ignored along the way (which also happen in traditional tax-qualified plans on the unrealized capital gains) and then again 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.
As you can see, paying taxes on interest / dividends / capital gains distributions along the way in non-tax-qualified accounts ends up being about one-fourth of the amount expected. There was no way to determine this until a program like this was created.
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 principal 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.
In non-tax-qualified accounts, the amounts you'll 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 also because all of these amounts are the smallest in the early years, relative to having EVERYTHING taxed at your top marginal tax bracket when you start withdrawing from traditional tax-qualified accounts (when everything is the largest). 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. It's a new world - all relative to both long-term market returns and average tax rates (which are both lower now).
Back to the directions.
Important: If you use any of the manual overrides on any of the calculations sheets, then you must input the same thing into ALL of the other calculation sheets, or the purpose of comparing one method of investing with another apples-to-apples 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.
So just be sure that 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. Then you'll also 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 usually before-tax).
So when you input withdrawal amounts into the program, do this: Assume the insurance ledger says you're withdrawing $1,000 a month. There are no taxes taken out of this, so you're really only withdrawing $800, assuming you set the ordinary average 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 - whereas the insurance ledger ignores everything to do with taxes).
If you don't do this correctly, then you'll just be using the same incorrect facts, marketing logic, and sales math that the life insurance industry hype uses, and this will make the insurance contract look much better than it actually is in the Real World.
Another way to look at this example is that if the Investment Comparitor is spending $1,000 a month, then the insurance ledger withdrawals need to be set to $1,250 ($1,250 - 20% = $1,000 -or- $1,250 * 0.8 = $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 contribution growth into cell B17. So if you're investing $100 a month this year, and plan to invest $110 next year, then $121 the next, then input 10% here.
Also 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 it has 1% in total fees, then input 11% here. These two fees will be automatically deducted later.
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 on 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 initial year's annual withdrawal amount into cell B21.
Input the percentage amount of annual increases into cell B22.
So if you input $10,000 into B21 and 10% into B22, you'll get the withdrawals seen on column F of the IC Results sheet of the demo.
The amount of B-share back-end loads will be automatically deducted from withdrawals in those calculation sheets. You can manually override the withdrawal amounts 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 all of that.
Tax Rates: There are two sets of ordinary income tax rates.
One is for the period of time before withdrawals occur (AKA the accumulation phase) and the other one starts the year withdrawals begin (AKA the 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.
Annual Inflation: Input cell B33. This does nothing to any of the calculations. All it does is show the inflation adjustments on second table of the IC Results 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. These two 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. Leave this cell blank if you won't be modeling any A-shares.
These A-share front-end loads 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 deducted from withdrawals 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 12b-1 fee nor what an investment advisor charges clients.
Input the annual 12b-1 fee for the no-load and A-share mutual fund into cell B43.
Input the annual 12b-1 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%.
Use the column C text in these input cells as a guide. They'll tell you what to input to get the desired end result. For example, in the demo, 20% of the 10% input into B38 was input as a dividend yield. So the end of the text says you'll be realizing a 2% yield, which is the bottom line you wanted.
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. The drop-down menu doesn't function on the demo.
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 charged this set dollar amount of commission.
Lastly, you can set ticket charges per trade. Then after you set the cost per trade, set the average number of trades made a year.
With mutual funds, you can also set the A-share 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. This is because you usually can't get away from paying them even after paying A-share loads. Tickets go to pay for trading, which doesn't have anything to do with the actual mutual fund or mutual fund family (or their salespeople). A-share loads go to pay salespeople, internal management fees pay the actual money managers, and 12b-1 fees are sort of a free-for all that can pay several people at once. So tickets are different because the money goes to completely different firms.
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 (Bank) 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 principal, 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 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 insurance 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 (which is what was done to get actual numbers for both the BTID, and the fixed annuity evaluation).
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. You can try your P&C agent - the people you get your car and/or home insurance from.
Here's how to use the output from their 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 estimated 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 (death benefits) doesn't do anything, is not accounted for in the program, and is just FYI. 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.
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.
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.
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 (premiums).
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 much bigger than it already does).
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, then add them to the term costs when doing a buy term comparison.
So these are the only columns on the Buy Term Results sheet that have meaningful comparison with insurance products. Most of the IC other 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, then you'll 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 after the great financial meltdown (of their obsolete business model).
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 accurately project true capital needs, 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, 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" daily with the markets.
All life insurance that is not term is automatically called "whole life" insurance, or "permanent life insurance." They are the same things.
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 life insurance, it's then called whole or permanent life insurance.
Whole life can be universal (UL). When this happens, the only difference is that the funding vehicles are all only in 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). The "V" in VUL stands for variable, which basically means you can buy equities (stocks) in the account.
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 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, charges, 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 withdrawal 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, and in the Real World).
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).
That's it for the buy term and invest the difference directions. If you bought support, then it's highly recommended that you now e-mail the program 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'd 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'd 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 much 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, and/or your death benefit will be much lower.
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 life insurance strategy. It's one of the most effective naive sales gimmicks ever created, 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 (if you're lucky), or way behind if you pay it back (and if you die, then your estate will realize less death benefit).
Here's more generic 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 (AKA a MEC), 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% premature penalty tax.)
Advantages: 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.
Disadvantages: 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 default 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 just 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 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 at all.
Accounting 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 net after taxes it will pay annually into column AD.
On the IC Results sheet, the difference between what it pays out, and the needed income withdrawals will display in column AJ.
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 income amounts shown on your annuity ledger are before taxes. So they need to be adjusted so the same amount of spendable income is applied to 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 advisor that sold it to you.
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 Data, What-if Analysis, 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 A42) 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 $10 to $100 annually regardless of the size of the retirement account. Input this annual amount in cell A32 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 A79 - 88 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.
As you can see, this investment software is much 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 e-mail the spreadsheet to us with your input, 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 and the investing software better.
|Financial Planning Software Modules For Sale
(are listed below)
Financial Planning Software that's Fully-Integrated
Goals-Only "Financial Planning Software"
Retirement Planning Software Menu: Something for Everyone
Our Unique Financial Services
Mr. Market Timer's Unique Market-neutral Stock Market Timing Services
Miscellaneous Pages of Interest
© Copyright 1997 - 2017 Tools For Money, All Rights Reserved