Life Insurance Calculator Directions.

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 the directions.

The "demo" is the actual spreadsheet, password protected, without the formulas so it's much smaller. You won't be able to do anything but look at it. You'll receive a working non-protected file after you pay for it.

If you try to print the demo from Internet Explorer, or you just click Print in Excel, it won't work well. Tips for working and printing in Excel are here, here, and here. The pages on the free sample comprehensive financial plan print better than the demo.

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

If you have Excel 2007, then first save the workbook as an xlsx workbook.

You switch between the sheets by either clicking on the sheet tab names at the bottom left of the Excel window, or by pressing *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 from 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 backups of all of your work on a monthly basis, and store the CD in a safe location, but not where your computer is. More PC tips are here.

If you're seeing this: ####, then either increase your Zoom magnification number (View, Zoom), or make the column width wider. If you see this: #REF! or #DIV/0! after completing your input, please respond to get a new program immediately. This means data was lost either via e-mail, unzipping, or while making a CD.

If a red Error! dialog box comes up saying your input must be confined to a range of values, and your input was within that range, then the problem is that the input cell doesn't accept pennies. Round the value to the nearest dollar and try again.

If you're an investing consumer (not a professional financial planner working with clients), then when you read "client, prospect, or they" just think "you." You would be both the advisor and the client (or spouse).

Read how to present sample reports to prospects and clients.

How to send financial plans to clients via e-mail, without sending the whole program is explained on the Excel help page, here.

Read about financial planning software integration (sharing data between modules) here.

Read about financial planning software support here.

How to send financial plans to clients via e-mail, without sending the whole program is explained on the Excel help page, here.

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

You can copy the input sheet to the far left unprotected presentation 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 at 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 Needs Calculator.xls with Excel. It won't work with just MS Works.

º The program will open up to the Input sheet. This is where you 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 A6 - A11.

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

Cells A10 & A11 are for the survivor.

º Input children's data (just their first name and year of birth) into cells A12 - A23.

º Input other people that are dependent on you (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 A153.

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

º Enter the lump sums you want the life insurance proceeds to pay for upon death into cells A28 - A34.

º Input amounts you want to have for each child's college education (and all other needs you want to fund for) into cells A35 - A40. 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: Open the life insurance needs software, and click on cell A35 of the Input sheet. Input the equal sign, then open the college funding calculator and click on either cell F14, F13, or N14 or N13 of the Presentation sheet. Press enter.

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

º Input sources of funds available now, like existing life insurance, into cells A46 - A53. These are also things you 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 increase the amount of life insurance needed by the same amounts. Yet get a flat $255 from Social Security, so cell A45 can't be changed.

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

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

º Input into cell A58, how much 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 A59, how much Social Security will pay a surviving family with two or more children. You can download free financial planning software from the Social Security website, so you won't have to guess about the benefit numbers, by clicking here.

º Input into cell A60, 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 A61, how much monthly after-tax income the surviving spouse and family will need when there is only one minor child in the family.

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

º Input into cell A64 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 65, 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.

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 65, 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 per month. In cell AB30, input $1,700.

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

If you want to not use Social Security benefits (for every year or just one year), input $0.01 into column AB's manual override for that year. Inputting $0 will tell the program that there is no manual override amount, so there needs to be a tiny non-zero number input, or it will default to the calculated numbers.

º Back to the Input sheet: Input into cell A65, 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 A66. You can use the first decimal place to account for months.

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

º Input into cell A68, 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 A66. However, you can use any of the five income generators starting in cell A82 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 A66 - even if you did not input retirement income.

º Input an assumed rate of inflation (COLA - cost of living allowances) into cell A69. This is the default rate applied to all of 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 A70. This rate is applied to all of the expenses and income needs input into cells A58 - 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 A71. You 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 A72. 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 A73.

The lower this rate of return, the more money will be needed to fund a future need, and vice versa. For example, if you need $1,000 in five years for something, you will 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 needed 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, or 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. You set these ages in cells E35 - E40 on the Input sheet.

Then assets usually increase over time, making more funds available for the survivor as years pass. This is because investment funds grow over time. Account for this using cells C46 - C53 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 C28 - C34 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. You're going to get a lot of static from your life insurance agent about this, but you can just show them your spreadsheet, and they won't be able to object to any of it (if they know what they're doing and/or they're honest).

Directions for Calculating Future Life Insurance Needs

º Go to cell C28 of the Input sheet. Cells C28 to C40 are where you input the amount of annual growth or shrinkage of the lump sum needs (shown to the 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 C.

º Now go to cell E28 - E40. This is where you 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 E28). From now until age 54, the loan balance declines by 10%, as input into cell C28. 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 showed the mortgage being paid off at 10% per year (cell C28 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.

º 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 exact amount of each year's liability into the Manual Override column (as shown in the demo).

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: Open the life insurance needs software, and click on cell A35 of the Input sheet. Input the equal sign, then open the college funding calculator and click on either cell F14, F13, or N14 or N13 of the Presentation sheet. Press enter.

Now the life insurance needs will reflect changes in the college calculator. If there is more than one child, then repeat using cells A36 - A40 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 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. 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.

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 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. Don't be afraid to contact your agent to lower your face amounts, OR canceling policies altogether! The money you save is worth enduring their complaining.

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, in order to not show people how much the need for life insurance declines annually.

Excel's built-in Goal Seek function makes this Life Insurance Tool by far the most flexible, powerful, and functional family budgeting tool ever created.

For example, using the demo data, you can go to cell I27 of the Current Needs sheet, click Tools, Goal Seek, input $300,000, then go to the Calculation sheet and click a cell like AZ23, then press enter. This will increase the amount of money the survivor has to earn in 2023 to lower the life insurance needs from $348,219 to $300,000.

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

Product Information

Fully Integrated Financial Planning Software

Menu of Retirement Planning Software

Asset Allocation Overview with Our Historical Returns

Asset Allocation Software

Asset Allocation Models with Our Historical Returns

Our Monthly Updated Mutual Fund Picks

Financial Plan Tools

Family Budgeting Calculator

Financial Planning Fact Finders

Investment Policy Statement Software

Bond Calculators