If you're still shopping, the best evaluation results are obtained by looking at the life insurance software demo, while following along as you read these directions.

When you're using the program, things go better if you first print out these directions.

Click "Enable Editing" to make it so you can change inputs. If you don't do this, then "nothing will happen" when you input data. Any change is editing (according to MS). This occurs because the program is not in a "Trusted Location." Go to File, Options, Trust Center, to fix that.

The "demo" is the actual financial spreadsheet without the formulas, 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. The pages on the free sample comprehensive financial plan print better than the demo.

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.

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 spreadsheet in a separate folder, so you'll always have an original unaltered file. Then if you do something like delete a formula, you can easily fix it by copying it back using these original files. Then save files you've worked on using a different file name (and into a different folder).

Programs usually come without sample input data, but be sure to delete all of the sample / client input (everything in green-shaded cells) before inputting your data.

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).

About financial planning software support.

About getting custodian brokerage account data to download into spreadsheets.

About financial plan software updates.

About financial planning software integration (sharing data between modules)

Read how to present sample reports to prospects and clients (and it's the place to start if you're a financial adviser wanting to learn what's going on at the highest level).

How to send financial plans to clients via e-mail, without sending the whole program.

Basic input concept: Input your data into the green-shaded cells of the input sheets (in the middle of the workbook). This will make data flow to the calculations sheet(s), which are on the right. This will populate the presentation sheets (on 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 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). Anything red is usually bad (input error).

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.

You can copy the input sheet to the far left unprotected Scratch Pad sheet, and then make notes there, so you'll know why you did what you did in the future.

The Future Needs and Current Needs sheets do not have any password protection, so you can do anything you want to with them. Also, the white cells on the far right of the calculation sheet are not protected so you can tinker with the graph data.

Life Insurance Needs Software Instructions

Open the Excel spreadsheet, Life Insurance Calculator.xlsx with Excel (2007 of later).

• The program will open up to the Input sheet. This is where you'll input most personal data. You can only input into green-shaded cells on this sheet. Input cells change from green to gray when non-zero input is entered.

• Input your name, or the clients' names, and other personal data into cells B6 - B11.

Cells B8 & B9 are for the person that's assumed to be passing away today.

Cells B10 & B11 are for the survivor.

• Input children's data (just their first name and year of birth) into cells B12 - B23.

• Input other people that are dependent on the passing away breadwinner (e.g., parents) into a child's input area to account for lost support income.

• Input final expenses into the input area starting in cell B153.

The total shown in cell B168 goes into cell B31 (which cannot be changed). If it's not what you want, use the Miscellaneous input field (cell B162) to tweak until the final number is what you want.

• Enter the lump sums you want life insurance proceeds to pay for upon death into cells B28 - B34.

• Input amounts you want to have for each child's college education (and all other needs you want to fund for) into cells B35 - B40. The input questions regarding the child's ages will appear after you type in their names above.

You can use exact figures from the College Calculator spreadsheet by integrating / linking them so they share data with each other. To integrate:

- First run the college funding report
- Go to a cell from B35 to B40 of the life insurance software's Input sheet
- Input the equal sign in the cell for the right child
- Flip to the College Calculator's results sheet (any sheet that's has the word Presentation in it)
- Go to cell E or F or N or Q 21 or 27 (whichever one you want, as long as it says inflated PV)
- Press enter

Now college needs are automatically linked and input into the life insurance needs software, and will reflect changes made in the college calculator. If there is more than one child, then repeat using cells B35 to B40 of the life insurance software Input sheet.

• Input sources of funds available now, like existing life insurance, into cells B46 - B53. These are also things you would sell to raise cash to pay for living expenses. If you don't want to sell something to raise this cash, then don't input them. This will decrease the amount of life insurance needed by the same amounts.

You'll only get $255 from Social Security, so cell B45 can't be changed.

Read the text in cell B75 about Social Security benefits to help with the income replacement input section below.

If you input dollar amounts into cells B46 - B53 then you'll also need to input the percentage these funds will change annually into cells D46 - D53. If you don't, you'll see a red Error! message, and the program will not calculate correctly.

• Input into cell B58, how much monthly after-tax income the survivor will need to maintain their standard of living if the breadwinner were to pass away today, when there is more than one minor child in the family.

This calculates how much income needs to be replaced given the fact that Social Security will pay benefits when there is more than one child under age 16, or under age 18 and still in High School. To determine these needs in detail, please see the Family Budget Tool / Cash Flow Projector.

• Input into cell B59, how much Social Security will pay to a surviving family with two or more children. You can download free retirement planning software from the Social Security website.

• Input into cell B60, how much the surviving spouse will earn from work when there is more than one minor child in the family. Social Security still pays a benefit, but it's less than when there is more than one minor child.

• Input into cell B61, how much monthly after-tax income the surviving spouse and family will need when there is only one minor child in the family.

Cells B61 - 63 are the same as above, but for the time frame when there is only one minor child.

• Input into cell B64 how much monthly after-tax income the surviving spouse and family will need when there are no more minor children in the family.

Social Security stops paying the surviving spouse when there are no minor children. This is called the "Social Security Blackout Period." Social Security will not pay the surviving spouse again until age 60, when they can collect Survivor's Benefits.

When the survivor reaches age 60, Social Security will offer the option to continue getting the survivor's PIA or the PIA the survivor would be receiving if the deceased were still living. PIA stands for Primary Insurance Amount, and is their name for how much your check will be. Usually the latter is larger, so people usually do that. Rules change, but basically a surviving spouse can collect survivor's benefits at age 60, not 62.

You account for this jump in benefits by using the Social Security Manual Overrides on the Income Replacement Calculations sheet.

In the year the survivor turns 62, input the increased monthly benefit amounts into that year's manual override in column AB. Be sure to populate the remaining years with an appropriate Social Security inflation rate - like 2%.

For example using the demo (which is a bad example because the sample financial plan has John and Mary five years apart), say that Mary figured this out when she turned 62, and now wants to get the higher benefit of $1,700 a month. In cell BB30, input $1,700.

Then in cell BB31, input this Excel formula to inflate it by 2%: =AB30*1.02 Then in cell BB31, use the cell's drag handle (the little box at the bottom right of the cell - it will turn into a cross when you're on it), and drag this down to the bottom. This will inflate it every year at 2% in every year you dragged a formula down.

• Back to the Input sheet: Input into cell B65, how much the surviving spouse will earn from work during this Social Security Blackout Period.

• Input the age the survivor will retire (stop earning income) into cell B66.

• Input into cell B67, how much monthly after-tax income the surviving spouse will get from Social Security's Survivor's Benefit at age 60.

• Input into cell B68, how much monthly after-tax income the surviving spouse will need during retirement.

The survivor's earned income stops at the age input into cell B66. However, you can use any of the five income generators starting in cell B82 to feed incomes into the picture after retirement age.

If Social Security is not showing up past the first year, this is because you need to input an retirement age into cell B66 - even if you did not input retirement income.

• Input an assumed rate of inflation (COLA - cost of living allowances) into cell B69. This is the default rate applied to all incomes. You can use the manual override columns on the Income Replacement Calculations sheet to change these figures in any year.

• Input an assumed rate of inflation (cost of living increases) into cell B70. This rate is applied to all of the expenses and income needs input into cells B58 - 68. You can use the manual override columns on the Income Replacement Calculations sheet to change these figures in every year.

• Input the assumed Social Security benefit inflation rate (AKA COLA - or cost of living allowance) into cell B71. You'll probably want to make this a percent lower than overall inflation to be on the safe side, as benefits may be cut in the future.

• Enter the assumed age the surviving spouse will pass away into cell B72. The program will stop calculating retirement income need numbers at this age. The longer the surviving spouse is alive, the higher the life insurance need because more money is needed to pay income for every year of life.

• Enter the assumed rate of return the program needs to calculate the present value of all of the monthly income needs into cell B73.

The lower this rate of return, the more money will be needed to fund a future need, and vice versa. For example, if you'll need $1,000 in five years for something, then you'll need to put away about $784 now at a 5% rate of return. At 10%, you would only need $621.

Lower numbers are more conservative, meaning it will increase the amount of insurance needed, which is safer, but will make buying the extra life insurance more expensive.

• If you want to manually manipulate the amounts of assumed incomes or expenses, you can use any of the green-shaded manual override columns on the Income Replacement Calculations sheet.

About Calculating Future Life Insurance Needs

The present value of the income replacement needs automatically project into the future (shown in column AF of the Future Needs sheet). This will almost always show a decline every year, relative to the amount of money being needed going down as every year passes. As each year passes, and the insured doesn't pass away, that's one more year of income you don't need to replace, nor insure for. So the present value of this need declines by around that much annually.

Also, you'll probably want to stop accounting for children's educations when they turn a certain age. In the demo, all three children's college funding needs stop when they turn 25. These ages are set in cells F35 - F40 on the Input sheet.

Then investments usually increase over time, making more funds available for the survivor as years pass. This is because investment funds usually grow over time. Account for this using cells D46 - D53 of the Input sheet.

Also other major expenses usually decline over time too. For example, debts are usually eventually paid off. Account for this using cells D28 - D34 on the Input sheet.

You can also use the manual overrides on both the Lump Sum Projectors and the Income Replacement Calculations sheets to input detailed data.

What the section below describes is how to project both the lump sum needs, and the amount of funds available to meet them, into the future.

After the income replacement needs and the future lump sum needs are added together, they are offset by the amounts of resources available (as shown in columns AH and AI of the Future Needs sheet). The resulting amounts are the net lump sum life insurance needs for each year (columns AJ and AL). Column AL shows how much life insurance you need to carry each year into the future.

Column AM is just the percentage change from a base year to the next year.

In most cases, the amount of life insurance one needs to carry declines each year. The life insurance industry doesn't want you to know that, because they're in the business of maximizing the face amount of insurance you carry, not have it decline every year.

So you want to call your agent and adjust your face amounts annually now that you know your true needs. This will save you a small fortune over time.

Directions for Calculating Future Life Insurance Needs

• Go to cell D28 of the Input sheet. Cells D28 to D40 are where you'll input the amount of annual growth or shrinkage of the lump sum needs (shown left).

Input an assumed rate of change for all cells that have text to the right of them. Each lump sum need will assume to stay the same in the future if nothing is input into column D.

• Now go to cell F28 - F40. This is where you'd tell the program to stop adding these needs to the calculations. In the demo, at Mary's age 55, the mortgage is assumed to be paid at her age 55 (cell F28). From now until age 54, the loan balance declines by 10%, as input into cell D28. At Mary's age 55, it's zero because it's assumed to be paid off.

Also, the demo illustrates how to input actual loan amortization schedules into the manual override columns. The input shows the mortgage being paid off at 10% a year (cell D28 of the Input sheet). This will never be correct, so instead, actual end-of-year loan balances were input into the manual override column, H on the Lump Sum Projectors sheet.

• Now go to the Lump Sum Projectors sheet.

This is where you can tinker with these future lump sum values to make them more exact. For example, you can get out your home mortgage amortization schedule, and input the estimated amount of each year's liability into the manual override column (as shown in the demo on column H).

Repeat this process for all lump sum needs. You can use exact figures from the children's College Calculator spreadsheet by integrating / linking them so they share data with each other.

To integrate total present values of college funding into the life insurance needs calculator:

Now the life insurance needs will reflect changes in the college calculator. If there is more than one child, then repeat using cells B36 - B40 of the life insurance software Input sheet.

• Now go to column G of the Lump Sum Projectors sheet. Input the estimated funds available to meet all needs, starting with cell H7. The current year's figure in cell H6 came from cell I22 of the Current Needs sheet.

Use standard Excel formulas to populate the rest of column G. For example, if you expect the amount of funds available to increase at 5% every year, then input this into cell H7: =H6*1.05 Then you can use the cell drag handle to drag this formula down to populate the rest of the column. Or you can input a different amount for each year.

Once input is finished, look at the data on the both the Current Needs and Future Needs sheets. There are two sheets because of formatting differences.

You can change how many years are shown on the two charts at the bottom of the Future Needs sheet.

It comes set up to see all years in the 75-year window. To see fewer years, do this: Click on one of the bars inside the chart. If nothing appears in the formula bar, then press the up or down arrow until something does. The number 79 and 80 in the middle, and at the end of the formula is the chart's data range.

The middle 79 (or 80) corresponds to the row list of year numbers. The last 80 is year 75 on the Income Replacement Calculations sheet. To change the chart from showing 75 years to 50, just subtract how much years difference there is, and change these numbers.

To change it to see 50 years, change the 80s to 55s (and 79s to 54s) in ALL of the charts data ranges. This means that after you change one, you'll need to press the up / down arrows until they are all changed. There's a way to do this automatically by right clicking on the chart and selecting Select Data. You'll know when you did it right when the charts finally look right. There are six sets of data to change in the top chart, and eight in the bottom chart. They all need to be changed in the exact same way.

The first year of both of these charts show the initial lump sums needed to recover from the loss of the breadwinner. The bottom chart's red bars show where life insurance is filling the gap in the income needs over time. As you can see, replacing income takes up much more money than the lump sum needs for most families.

Column CT of the Income Replacement Calculations sheet shows how a year of cash flow surpluses will be used to fund the next year's income needs.

If there is no income need in that year, then they will accumulate until they're used up in future years. No interest compounding is done here as most people won't invest the money correctly in the Real World - the money will usually sit in an interest-free checking account until used.

The bottom-line amount of life insurance that's currently needed is given in cell I27 of the Current Needs sheet. This is the face amount of life insurance that your family needs to buy today.

Repeat your input at least annually, and adjust the amount of life insurance your family needs to buy accordingly.

Financial planning professionals would print the Future Needs and Current Needs sheets to show their clients how much they need to buy to be fully covered for the worst happening now.

They can also choose to not print out the Future Needs report, or just hide or delete that data, or delete the whole sheet out of the workbook, in order to not show people how much the need for life insurance declines annually.

Excel's built-in Goal Seek function makes it so you can easily do any "What-If" scenario that any other life insurance program can do, plus dozens more they can't.

Using the demo as an example, you can go to cell J27 of the Current Needs sheet, click Data, What-If Analysis, Goal Seek, and then input $775,000 into the middle field, then go to the Income Replacement Calculations sheet and click a cell like BA26, then press enter.

This will automatically increase the monthly amount of money the survivor has to earn in 2030 to lower the current life insurance needs from $784,318 to $775,000. Any combination of cells can be used to do any What-If scenario.

Any combination of cells can be used to do any What-If scenario you want, even if they make no sense at all.

About input cells B78 & 79: Input current monthly income into cell B78 and then a percentage that needs to be replaced with life insurance proceeds into B79. All this does is multiply these two numbers together and the results display in cell B80.

If you bought support, then you can and should e-mail the program back with your input to have us check your work before making important purchase or face amount decisions.

Financial Planning Software Modules For Sale
(are listed below)

Financial Planning Software that's Fully-Integrated
(the IFP is the NaviPlan alternative for 1/6th the price)

Goals-Only "Financial Planning Software"
(the MoneyGuidePro alternative for 1% of their price)

Retirement Planning Software Menu: Something for Everyone
(the RWRs, RP, and SRP)

Comprehensive Asset Allocation Software

Model Portfolio Allocations with Historical Returns

Monthly-updated ETF and Mutual Fund Picks

DIY Investment Portfolio Benchmarking Program

Financial Planning Fact Finders for Financial Planners Gathering Data from Clients

Investment Policy Statement Software (IPS)

Life Insurance Calculator (AKA Capital Needs Analysis Software)

Bond Calculators for Duration, Convexity, YTM, Accretion, and Amortization