This Post Is Part Of The Life Insurance Movement Hosted by Good Financial Cents
Today I wanted to create a small spreadsheet that would help you determine how much you need in terms of life insurance. Why? Not only because it’s the cheapest type of life insurance, but also because it’s something that most of us unfortunately have to consider at some point and I thought it would be a nice way to give a few more examples of different excel functions. Let’s start with the actual case.
In general, as individuals, we buy life insurance for 2 main reasons:
-to pay off any debt
-to replace part of our income (in order to help our family or other dependents) – we will assume 50% in this case
So let’s start off with a fairly simple spreadsheet. I will ask the user to:
“Enter your annual income here”
“Enter the amount of life insurance your have”
“Enter your total debts including your mortgage here”
Then, I would start off by seeing if all debt can be paid, you can see here:
=IF(C5>=C6,”YES”,”NO”)

I will even add conditional formatting to have the cell in green if that first condition is met or in red if not:

Then, I will determine how much will be left and how much of that can be paid out annually over 20 years. I will assume that this amount generates a return of 4%. How will I do it? The PMT function as follows:
=PMT(rate, nb periods, pv, fv)
=PMT(0.04,20,life insurance – debts)
=PMT(0.04,20,C6-C5)
As you can see, this gives me an amount of $51,507 per year for 20 years, which is not bad would not entirely replace my income. Let’s give the user the option to decide the proportion of his income to be replaced:

Then I will determine if it is enough:
=IF(C12>=C4*C14,”YES”,”NO”)
If it is not sufficient, I will use the PV function to determine how much is missing from the current policy:
I will display 0 if nothing is missing:
=IF(C15=”YES”,0,formula)
The formula I will use is:
=PV(0.04,20,-(C4*C14-C12))
You can see the new result here:

And as always, I invite you to download the spreadsheet here