 Personal finance practice problem using Excel. Life insurance calculation tools, part number one. Prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you don't have access to it, that's okay because we'll basically build this from a blank sheet. But if you do have access, three tabs down below. Example, practice blank, example in essence being an answer key. Let's take a look at it now. Information on the left calculations on the right as we've seen in prior presentations. When we think about how much life insurance we need, we can approach that calculation from many different angles. We can use many different calculations. We're gonna look at some tools and try to put those tools in isolation so that if you wanted to customize your own kind of calculations to fit your personal needs, you might be able to put these tools together in a future presentations. We will put them together in a comprehensive problem. The second tab is a practice tab that has some pre-formatted worksheets so that you could work through the practice problems with less Excel formatting. The third tab is a blank tab where we will be putting this together. In essence, from a blank tab, we've got the data on the left-hand side. If you don't have the data, you could just add the data. So if you had a blank worksheet, you could just select the whole worksheet. I would start by putting the underlying or baseline formatting down, right-clicking on it, format the cells. I would go to currency, brackets, and negatives, and then no dollar sign and no decimals. That's usually my underlying starting point. I'm not gonna hit okay, but just X out of it because I already have it set up here. Then add your data on the left-hand side, which is good practice for a practice problem and make the skinny C column, then we're ready to go. So the information on the left-hand side, we've got the wages, we've got the earnings rate, we've got the years. We're gonna say that the college costs that we're gonna be saving for will be 40,000. The kids' age that we're gonna save the college costs for is five, and then inflation, we're gonna say is at the 3% mortgage, and we've got our mortgage information down below. So this is just some tools. In practice, if we were doing a really formal kind of calculations for life insurance, we'd want our whole budget and whatnot and possibly some of these tools so that we can piece together our thought process on how much might be needed. So now we'll use these tools for a couple different approaches on how we might calculate life insurance. Now, first of all, you've got the component of life insurance that you might be looking at for somebody that would be dependent upon you. So if you were the wage earner for the family, for example, you might be saying, well, I would like to basically have my earnings potential to be supporting people for some amount of time. The first method would be like a generic type of method based on your wages for a certain time period. And so we've seen that in a prior presentation. So we could say, well, my wages for 10 years, this would be like a generic kind of calculation. We might say, okay, what I would like to do is say, I'm gonna go to the home tab, font group, make this black and make this white. And then I'm gonna say, okay, my wages are 60,000 equals the 60,000. And so there we have that. And the years, we're gonna say 10. Now, this year's calculation, how many years might you want this to cover? That's, you know, it will depend on the circumstances. We could say, well, if the child is five, we could see how many years it would take them to be 18. We could use a generic number anywhere from seven to 10. It's kind of a generic number of just, you know, a standard rubric or a type of number that you could basically use a recommended type of number or a heuristic type of number, if you will. Or we could say, how long would my working years be? Or we could think about how many years it would take for, say, my spouse to reach retirement, for example. So those are some kind of count. You might count, you might tie it to the mortgage and say how long until my mortgage is paid off and basically say my income will be sufficient until that point. I'm gonna use a generic 10, just the generic 10 here. So the insurance needed and we'll calculate this out. So this is gonna be the 60,000 times the 10 and that's gonna be the 600,000. Now you might take some fraction of that because you might say, well, I'm not gonna be there. So I won't have any of my particular costs and the 60,000 that I'm earning possibly covers more than simply the necessities. Meaning I might, you know, if we were trying to say I need life insurance for the necessities, I might say, okay, I'll take some fraction of that. I'm gonna say percent, 70% 0.7. And this is a heuristic type of number. We're gonna say seven. I'll put an underline here, a font group underline. We'll put an underline here. And so then we're just gonna say insurance needed. We'll be the 600,000 times the 70. Now, so you could use the 600,000, right? You could use the times 70 to 420. You can also, depending on your circumstances, you might say, hey, look, I've been making commitments to the mortgage and whatnot based on my income going up, right? I've been quite aggressive. We've got a mortgage based on the idea that my income was gonna go up. So you might say this 60,000 isn't enough. I should try to index it upwards because I was counting on my earnings potential going up and I've committed myself possibly to debt payments in alignment with that increase in wages. So it kind of depends on your circumstances, of course, but this is the first kind of heuristic kind of method. You can also use a similar method where I'm not gonna look at the wages side of things, but I'm gonna work, look at instead the expense side of things and try to calculate what the expenses that we're having or at least the cash flows that we'll have, which might include some debt calculations. Now notice that once we have this, we could of course tack on other kind of things that we would need to be saving for, such as possibly if we wanna have the spouse's retirement that we're gonna contribute to or possibly college or possibly a parent that would need help and their later years possibly or something like that. We can add that on top of this calculation. So this is one component or one piece or one tool that we can use in our overall calculation or you can use it in and of itself. So I'm gonna go ahead and make this blue and bordered and let's take a look at another way we might see this. Here, if you don't have that blue, it's in this little color wheel standard. We're using that blue right there. And so another way you might see it is you might say, well, what would be nice is if I can have enough in an investment, if I was to die so that they can invest it in a stock market or something and get earnings equivalent to my wages. So that's a much more aggressive kind of strategy, right? So I could say, well, how much life insurance would they have to get in a lump sum so that they would get 60,000 a year on average from it if they were to just invest it and get an average of a 5% return? We're gonna say 5%, yeah, earnings of 5%. So let's take about that. We could say, okay, I'm gonna make this skinny C. I'm gonna go to the home tab and make a format painter and make that into a skinny F over here. And so I'm gonna say this is gonna be the earnings, earnings on life insurance proceeds that we'll call that. And I'll make this a little bit wider and I'm gonna select these three and we'll say home tab, font group. Let's make this black and white. And so we could say, okay, what if I died and we had life? This is how I would normally think of it. You could adjust the math, but I usually would think, well, if I had the life insurance proceeds, well, there's a little algebra calculation and that was to get earnings per year of 5%. Earnings at a rate of 5%. I'm gonna equal the 5%. I'm gonna percentify that cell by going to the home tab, number group, percentify it. Let's underline it, font group underline. Then the annual earnings, I'm trying to get the annual earnings to be 60,000, right? Equal to my wages. So how much would I need in my life insurance if they were to invest that to basically get 60,000 if they were gonna average 5% earnings on it? So we could do the calculation like that. I could say, okay, it's basically an algebra calculation. I could say, well, this would be equal to the 60,000 divided by the 5%. So that would mean 1,200,000, which is obviously a lot more aggressive than the 420 because you're trying to basically say, how much can I get in a lump sum? I'm gonna recalculate it so we could do it this way so we could check our number. I'm gonna go over here and say font group and let's underline it and go to the number group, percentify it and then multiply this out. This equals the 1,200, 1,200,000 times the 5%. And so there's the 60,000 and that's 60,000 if they got 5% on it would always be in the principle as well, which would be nice. So if that was doable, then that's another calculation you could look at on more of a high end, of course, calculation. We're gonna say font group, do it this, we'll make it blue and bordered. Now of course you could just adjust the algebra on that too. You could say, okay, if I had earnings on life insurance proceeds and I'm looking for the unknown, I'm gonna make this blue and bordered home tab. I'm not blue and bordered, I'm gonna make it black and white because it's a header, it's a header thing. So you could say, okay, my earnings that I want annual earnings are 60,000 and that in the rate that I've got on the earnings is 5%, 5% and so I'm just working the algebra here. We're gonna go number group, percent, underline it, font group, underline it and that would give us our life insurance proceeds or insurance needed, let's say insurance needed would be equal to the 60,000 divided by the 5% once again at 1,000,000 too. So let's make that blue and bordered, I'm gonna select these, I'm gonna go font group and border blue on that one. So then you might say, well, they don't really, what I'd like them is to be able to get 60,000 per year for basically 10 years. So I could do this calculation to get to the 600,000 but really if they can earn, if they can earn 5% on it, then how much would I have to get in the life insurance for them to be able to pull out 60,000 a year for 10 years? So that's a different kind of calculation. It's gonna be a little bit lower than the 600,000 and certainly lower than the 1,002, lower than the 600,000, lower than this. So let's try that. We're gonna say, okay, well, that's too much life insurance for me. Let's try another calculation. So we're gonna go to the home tab, let's format paint this and make a skinny J over here and skinny J. And so this time I'm gonna say, let's use our present value calculations, present value. We're gonna take into consideration time value of money because I'm gonna make this K a little bit wider because that'll help us to take into consideration the earnings. So in other words, what I wanna look at is say, well, if I had the 60,000 for 10 years and it was an annuity of 10 years, what would the current value be at current time period? Which would be the life insurance proceeds that someone would need in order to get 60,000 annuity payments for 10 years out of 5% return. So let me see if I can explain that. We'll say, okay, let's do a present value calculation, which would be I started with a negative instead of an equals, present value, shift nine. And let's say we take the rate, so we're gonna say that it's gonna be 5%, comma, and then I can't see, I can't see. It's the number of periods is the next argument. The number of periods is the next argument, which is 10, we're doing the generic 10 here, comma. Okay, and then it wants the payment. So it's an annuity we're looking at because we're looking at a series of payments of 60,000 for 10 years that we want a present value. That comes out to 463.304. Okay, so let's think about that. So we're gonna say, let's say that, and right here, let's say that we're doing this with, I'm gonna put the rate down here just so we could see it. The earnings rate is 5%. That's what we're basing this on. Let's make that 5% right here. Number group 5%. Let's make this, I'll make this one black and white. I'll make this one blue and bordered, bordered and blue. And let's make this one black or blue and bordered, blue and bordered. Now let's see if we can understand that a little bit more so we'll say, okay, how does that work then? Let's take the skinny J and make a skinny M. Gonna take the skinny J, home tab, format pane it, make a skinny M. And then I'll do my little calculation to see how this is gonna play out. So I'm gonna say year, earnings, earnings, decrease, or let's call this the payment amount or balance, let's say balance, probably better. Okay, and then I'm gonna select these up top. We're gonna make that black and white, home tab, font group, making it black and white, black and white. Let's center it to alignment and center. Let's make this a little bit skinnier, skinnerizing it, thinning it up. It needs to thin it up, thin it up. Zero, one, two. We'll bring that down to 10 years. I'm gonna select these three, use the auto fill handle, put my cursor on the auto fill handle, drag it down to 10. Let's center that home tab alignment and center that out. And I'm gonna say, okay, if I start off with my balance here of the 463, 304, if I died and then I got 10 years of payments that are gonna happen, that means that they're gonna get payment. Let's put the payments here too. I'm gonna say payments are gonna be equal to the 60,000 just so we have everything right next door. Right next door to our working process. We'll say that's gonna be 60,000. Okay, so the earnings then on the first year is gonna be that 463. If I died and they got the 463, 304, put it into some investments and they got earnings of 5% on it, then they would be earning 23, 165. And then they're gonna take out 60,000. I'll make it a negative instead of equals I hit negative of this number. They take out 60,000. So that means the prior balance plus this earnings plus the negative number, which is minus the negative number is 426, 469. So let's do that this way. I'm gonna say the prior number plus the sum of these two and close up the brackets. Okay, and this earnings number, this 5% is outside of our table. So that's an L. So I wanna make that one L3 absolute because I don't want it to move down when I copy down. So I'm gonna select F4. You only need a mixed reference but this is an absolute reference and that will do. I'm gonna hit enter. We'll select these three. I'm gonna select the fill handle and drag that on down, drag it down and hold on a second problem. This 60,000 also doesn't, we don't want it to move down. So I gotta fix that. I'm gonna select double click on that one. That one needs to be absolutized. So I'm gonna say absolute reference F4, dollar sign L, dollar sign five, enter, put the cursor back on it. I'll just double click the fill handle button this time and bring that on down and you can see it goes down to zero, right? So the earnings go down each year because the balance is going down but the earnings help us out to maintain that 60,000 per period. That being the idea. So now we've got this 463 to try to maintain that income of the 60,000 that we're earning over 10 years. Again, we can add other kind of methods or calculations on top of this like college tuition, retirement, helping out for a parent or something that might need help in the future or something like that. But this is our starting point that we could do this way, 600,420. If we just take like 70% of that, trying to estimate what the actual spending needs are of it, we could try to calculate the actual 1,200 for them to earn 600,000 and have the principle of the 1,002, which would be great. Or we could try to have this annuity calculation that we could think about. Now, let's do another kind of, we could get a little bit, do another one. Let's make this border blue font, border and blue. Now you might say, well, that's great. But like each year here, you would think that they need less money, right? Because if I'm estimated that they only need money for 10 years because, say that child is growing or that was my full working years and then they're gonna be in retirement or something like that, then I would need, you might think of a life insurance that you could tailor and basically say I would like it to be higher during this point of my life and then basically go down possibly as we get the needs go down, at least for this portion of the life insurance for the income structure. You could tie it to how long you're gonna live, how long your working career was, how old your child is or something like that. But you might say it should be going down as I get closer to retirement or whatever that goal is. And you could see that happen here with this calculation, right? You could see it kind of going down. And so you could say, well, if I'm doing it out here four years out, maybe I only need 304, 542 at that point and you could try to tailor your life insurance to kind of possibly move down as you get closer to that end goal, whatever is the 10 years out. You might do this calculation another way, which is a little bit more simple than this table, although I think this table is really easy to look at. So let's just do that calculation another way. If I, and we'll use our time value of money to practice that, I'll put my cursor on column M. I'm gonna make that, that we're gonna copy the formatting and put that here, lay it down on R. And let's say that we just have the years and then the, or let's say insurance, insurance needed. Let's make that black and white. We'll make that font group and black and white. We're gonna center it, alignment and center. And then I'll do the same thing here, zero, one, two. The years, select those three, auto fill handle, copy it down to 10, alignment, center it. I'm gonna make column S a little smaller. So there we have it. And then what I'll do is the calculation for a present value calculation that we could kind of copy down here. So I'll start off with, in essence, the same present value calculation, but we'll use our tools so that I can copy it down. So I'm gonna say negative instead of equals present value, shift nine. Now first we need the rate. The rate is gonna be this 5%. And I don't want that to move when I copy it down. So I'm gonna select F4 on the keyboard. You only need a mixed reference, but an absolute one that works, dollar sign before the L and the three, comma, number of periods. Here's where we get a little bit fancy. What I wanna say is 10 because we got 10 periods, but I wanna be able to copy it down. So what I'm gonna do is say I want this 10 and then minus this one, whatever this is. And then I don't want the 10 to move down because that comes out to 10, obviously, 10 minus zero. If I move it down, I want it to be 10 minus one or nine, 10 minus two or eight. So this one I don't want to move. This one I do want to move. Therefore, S12, that cell needs to be absolute. So I'm gonna select F4 on the keyboard, dollar sign before the S and the 12. And then I'll say, comma, and then the payment. The payment is going to be the 60,000, 60,000 here. I don't want that to move down. So I'm gonna select F4 on the keyboard. So there is that, I think that does it. Let's close it up, enter. So we got that same calculation we have over here, but now we could copy it down, practicing our present value calculations, copying that down. So then you could say, okay, if whatever goal I have after 10 years, they only need this happening for 10 years, I might be able to say, can I buy some kind of life insurance, a term life insurance, for example, that basically tapers down per year based on these calculations or something approximating them so that you might get no more reasonable policy that covers the needs that you need. So in future presentations, we'll continue with this. And we might say, well, that's fine, but the 60,000, maybe I wanna like increase that for inflation and consider this 5%. So we'll think about that. And then we'll also get into how we might think about other things that we could tack on top of that, such as retirement help for our spouse or something like that, college savings that we could tack on top of it, for example, and we might use our mortgage, for example, in multiple different ways that we can work into our life insurance calculations. And these are just tools, remember, that we can then fit in to whatever calculation we think is the most appropriate for our particular circumstances. So I'm gonna then select these, we're gonna go to the thought group, I'll make this bordered and blue. So just to recap, this one part of the calculation, which would be the cash flows that you're trying to support someone with who might be dependent upon you, you can base it off of the wages or possibly you could try to base it off the expenses instead of the wages so that you could try to figure out what the actual costs are that are being covered as opposed to the wage calculation. You might do a generic calculation that would then say just multiply it times 10 years. You might try to take a percentage of that 70% of it, for example, to cover just the actual needs that are there, minus you in the picture. Or you might actually say I need to increase that due to the fact that I've taken on a lot of debt because I'm hoping my income goes up and if I die, then I've kind of made some estimates that it would go up and that might not be the case. You could try then to base, once you know what that cash flow is, you could try to say, well, and you could get it whether it's gonna be based on your income or expenses. You could try to say, well, how much could I invest in order to get a return of that much money to cover? And that would be nice because then you'd have the principal and the earnings on it, which they could basically live on. And that would be, if they continue the 5%, they could continue getting that each year, which would be great. But that would be a lot higher of a value, of course. You could also say, what if I did this annuity payment and say however you base the calculation, whether it be an expense calculation or based on the income, you could then say, well, what if I think about that annuity stream and they get kind of a return on it, then how much would that lump sum have to be at the point of death? And then you can also, of course, think about how it might be affected over time so that you could think about possibly tapering down the life insurance, which could make a more affordable life insurance that hopefully could still cover the needs.