 Personal finance practice problem using Excel. Life insurance calculation tools part number two. 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. We started this worksheet in a prior presentation so you could go back there and work the first part of it, but it's actually not required to move forward from this point. If you do have access to the sheet, there's three tabs down below. Example, practice, blank. Example, in essence, and answer key. We've got the information on the left-hand side. We're looking at tools we could use to customize our life insurance needs, types of calculations, noting that as we've seen in prior presentations, you can approach the question of how much life insurance do we need from many different angles and you could try to customize or use these tools to customize your individual calculation. So the second tab, the practice tab has some pre-formatted worksheets so you could work through the practice problem without as much Excel formatting. The third tab then is gonna be where we're gonna work it from a blank sheet. Quick recap of what we saw in the prior presentation when we're thinking about the life insurance calculation. The first thing we would most likely think of is, is someone dependent on me for my wages? If they are and I was to die, how much would they need on a yearly basis to support them for how many years and then what's the lump sum that they would need at the time of my death in order to accommodate those needs? Now again, we can get to that baseline number a few different ways. We might just use our wages for example or we might use an expense calculation. We try to think about the cash outflows as how much they would need on a yearly basis. Once we have that number, we multiplied it by 10, which is kind of a heuristic type of number in the prior presentation. We could use the number of years till we're in retirement. We could use the child until the child reaches 18 until the spouse is in retirement in order to get that year's number. So there's multiple kinds of ways we can think about it and then we can tack on other things after those earning years that we would typically have if we were alive, such as saving for things like the college tuition for example or spouse's retirement or something like that. So then we came out to the insurance needed of the 600,000 with the generic 10. And so that could be a number that we could use right there or we might say, I'm gonna take some percentage of it heuristically, 70% for example, to get down to the 420, which you might reason that the 60,000 for example may not be something that they really need that's possibly covering the needs and over above the needs at that point. And you're not gonna be there of course. So you're gonna have less costs for yourself within the expensive side of things. And this 420,000 if they were to invest it, if they got a lump sum of it, you would also have earnings so that they can support themselves with the earnings that they would get on the investment as well. So that's kind of a heuristic approach that we could use. And then we talked about another thing we might say, well, what if I want them to be able to get 60,000 on top of on just the earnings of the proceeds that they would have from the life insurance. So we said, well, if they were to have 1,200,000 at a 5% return, they could get 60,000 a year and still have the principal. So that would of course be a much higher level of life insurance and then you'd have the principal. So that's another way you could think about it. We also said, okay, well, wait, we thought about this idea over here where the 420 could actually earn some money and so they would be only drawing out the 60,000 each year. So we said, okay, well, what if we thought about a stream of annuity payments? How much would they need invested to have a stream of annuity payments to pull out 60,000 each year? And we could do a present value calculation which we did last time based on earnings of 5%. And so that would mean that they would need this 463,304 in order to get an annuity stream of 60,000 out each year if they were able to earn the 5% on it. That also allows us to possibly look at term insurance that might have a decreasing factor to it because as we get closer to that retirement age, there would really be less of that cash flow need that they would might have. And then we can tack onto it retirement savings calculations, for example, or college tuition calculations and so on so that you might be able to look at term insurance that has a decreases over time. Now, we can get a little bit more detailed on that calculation just to add a little bit more complexity. We might say, well, yeah, but if 60,000 we determine is their expense needs in the current year, we also have to deal with inflation. So because that means that in order to have the same spending in the future year, there would be inflation involved. So we might say, okay, well, how can I take into consideration the inflation component? That's what we'll take a look at now. So let's do a skinny, let's make this skinny M over here. I'm going to take that skinny M and format paint it. I'm going to make a skinny U, skinny U. This is going to, we're going to do a present value calculation again, present value. I'll make this a little bit larger. And then I'm going to say, let's bring in our rates. We're going to have the estimated rate or the earnings rate is still going to be the 5%. But then I also want the inflation rate. So I'm going to say inflation rate is going to equal, I have this in my information on the left. Inflation rate is going to be this one, the 3%. So 3% inflation. Inflation, you would typically calculate anywhere between like two and 5% really. And usually historically the Fed wants it to be under 2% really, but it looks like it's going to get higher possibly at this point in time. So you might calculate a higher inflation than that. So in any case it depends on the timeframe. So you might just try, we're trying to do an average rate of inflation which we would think three would be, you would think high over the long run. But in any case, I'll subtract out the two of them. We got three minus five. So that means the real rate of return would be the 2% if that was the case, right? Because we're going to be losing earning potential on the 60,000 that they pull out because of inflation, the decline in the value of the dollar. So if I earn 5%, but three of it's eaten up by inflation, I'm really only earning in real terms the 2%. So this would be a more conservative kind of calculation. So let's widen this up again because I got to do, I'll do the calculation up top. So based on that information, we're going to do a present value annuity stream, but this time instead of basing it on the 5%, we're going to base it on the real rate of the 2%. So I'm going to say negative present value, shift nine, the rate is going to be the 2%, the real rate, comma, and then the number of periods. Let's actually add a little bit more data down here. Let's say the periods, periods, I'm going to say this equals, scroll all the way to the left, it's going to be 10. And then we're going to say that this will be, the payments are going to come out are 60,000. So there's our data, there's our data. Okay, so now let's make this, put some borders around it. Let's make it borders and blue, borders and blue. If you don't have that blue, it's down here. Standard, we'll make it that blue. And then this one will make it border blue. That's not right, that's white. And then this one, let's make it black and white because we did that last time, black and white. Okay, so now we'll see, let's do our annuity payment. If they were to take out 60,000 each year, but at the real rate of return, and we'll see how this kind of plays out when we'll expand the calculation of 2%, what would that look like? So I'm going to do negative instead of equals, present value, so it turns out to be a positive number, which probably isn't the most proper way to do it, but I think it's the fastest. The rate is going to be the 2%, comma, the number of periods now is going to be 10 years, comma, and then the payment that we're gonna make is gonna be the 60,000 and enter. So now we're at the 538955 instead of the 463, right? So it's a more conservative kind of calculation based on the 2%. Now, how does that actually work? Cause I did an annuity based on 60,000 at 2%, like what is actually going on here? Let's try to figure this out in a little bit more depth. I'm gonna make this blue and borders. I'm gonna make a skinny X here. I'm gonna put my U here. I'm gonna go to the format panel and put that on the X. And let's first do the same kind of thing we did over here where we did this calculation right here. So I'll basically copy the headers. Let's copy the headers. So you've got year earnings payment balance and the year is gonna be zero, one, two. And then we'll select those three, select the fill handle and drag it down to 10. Let's center it, alignment and center. We'll make the column Y a bit skinnier. Thinning up column number Y. The payment on the outside is gonna be equal to the 538955, 538955. And then I'm gonna do the earnings the same way here. I'm gonna say this is gonna be equal to the 538955 times, but I'm gonna just use the 2%. Really the earnings are gonna be 5%, right? So I'll switch it up in a second, but let's just do it the same way we did it before so we can see what's going on in a similar fashion with this calculation. So let's say it was 2% earnings and then this is in cell W5. So I'm gonna say F4 on the keyboard because I don't want that to move down. That's an absolute reference. You only need a mixed reference, but an absolute one works and enter. And then the payment is going to be, I'm just gonna keep it at the 60,000. I'm gonna say negative 60,000. F4, because I don't want that one to move down. Now remember, the outflow, once they start taking the money out, you're gonna say, but yeah, but it needs to go up by because there's gonna be inflation. They're gonna need more than 60,000 each year. So we'll think about that in the following calculation. Let's just do this one the same way first. So I'm gonna say enter, and this equals the prior one plus the SUM of these items and close up the brackets and enter. So it's gonna be this plus this minus this, the prior balance plus the earnings that they're making on it minus the payment. Let's select those three, copy it down with a fill handle. And you can see that gets us down to that nice zero down below. So given that, now we're gonna alter it and say, well, yeah, but the earnings should be 5%. And then the payments need to go up by 3%. So we won't get exact, it won't be exact doing it this way, but we'll get another, we'll get an idea of what actually we're trying to do here. Let's take, let's make this blue and bordered first. Blue and bordered. So now I'm gonna copy this whole thing. Let's copy from column X and drag on over to column AB. I'm gonna say control C and I'm gonna put that in AC1 and control V. So now let's just, let's delete the data and let's do it again. And let's say, okay, well, the balance is actually gonna be the 538, 955. And then I'm gonna say the earnings are gonna be at the 5%. We're gonna say the earnings are at the 5%, inflation's at the 3%. So earnings will actually be the 538, 955 times the 5%. I'm gonna select F4 on the keyboard because when I copy that down, I don't want it to move down. We only need a mixed reference, but an absolute one works. And then the payments are gonna be, if it's a year out, so I started a year later, if it was currently 60,000 that I'm having, then a year later it's gonna have to increase by that 30%. So this is gonna be equal to, we could say the 60,000, 60,000 times, and we could say the one plus 3%, or 103%. That's one way we can think about it to get us to that 61.8. The other way that we can do it to kind of be able to copy this down a little bit more easily, would say I wanna take the future value one year out. I'm gonna take a future value one year out at that 3%. So let's try it that way. I'm gonna say negative future value, brackets, the rate is gonna be the 3%. And I'm gonna say F4 on the keyboard because I wanna copy that down and not move that cell down, number of periods is gonna be one, which I'm gonna pick up right here so that when I copy that down, it will move down, comma, it's not a payment because we don't want an annuity this time. We just want the value of one. So therefore, two commas to get us over to the present value and then we're gonna be picking up that 60,000, the 60,000 being outside of our data. Therefore, I don't want it to move it down, F4 on the keyboard to put a dollar sign before the W and the eight brackets. And so there we have it. And I could make it negative to match this. I'm gonna double click on it. I'm gonna get rid of that negative sign, get rid of the negative sign, so it's now a negative. All right, so then I'm gonna say this equals the prior balance plus the sum of these two, the sum meaning the 26,948 minus the 61,800. So 538,955 plus 26,948 minus 6,1800, close up the brackets, gets us to the 504,103. So this is the actual amount that we would think about kind of in future value terms when we're considering it this way. So this calculation would kind of be necessary for us to figure how much would be there like as the years go by so that we can have our possibly a declining amount of the life insurance. So let's select these three and let's copy it down then using my auto fill, copy that down. So there we have it. Notice it doesn't get to it perfectly here and that's why so it's not as neat that way but we can see between years nine and 10 that basically it goes negative. So if we've got this amount, we're gonna say the earnings on it are gonna be the 26,948 but now we're not gonna be paying out 60,000 because we wanna pay out the enough amount to cover the same expenses that were 60,000 before, right? So now they're gonna have to pull out in future value terms the 61,800. And so that means that in future value terms we're now at the 504,103 and if they pull out the same amount we earn on that at 5% 25,205 and now they're pulling out not 60,000, not 61,8 but in order to meet the same needs because of inflation, 63,654 and that brings us to our new balance here and so on and so forth. Let's try now to think about doing this one more way to practice our present value calculations and maybe clarify this a little bit more. So I'm gonna select this skinny column, I'm gonna make another skinny column in AH, I'm gonna select this one, grab the paintbrush and paintbrush AH and so then I'm gonna say, I'm gonna call this one years and then we're gonna say this is the payments and then I'm gonna call this the insurance. So what I'm trying to do now is calculate the insurance needs in insurance, insurance. I'm gonna try to calculate the insurance needs based on each year into the future so we can see that declining balance. I'm looking to get to basically these same numbers here and a little bit different formatting. All right, so let's select these three, let's make this our header font group black and white. Let's center it, center it with the alignment. I'm gonna make AI a little bit thinner, skinny up AI and so this is gonna be from zero, one, two. I'm gonna select those three, grab the fail handle, drag it down to 10 and then go to the alignment and center it. So now I wanna think about the payments and again I want the payments to be increasing so that we can see them what the payments need to be in the next period. Let's do it the other way this time. So this time we did it with the future value calculation. You might see it this way. You might say, well, a year from now we're gonna need a payment which is gonna be the 60,000 in year zero times an increase of the 3%, which I'm gonna say brackets and do it this way, one plus the 3% close the brackets. You could see it basically up here. So W8 times one plus W4. You could see it here, W8 times one plus the W4. That's gonna give us our 61,000. Then in the next year, it's gonna be equal to the prior period, the 61, eight times brackets. One, we have to have the brackets because of the order of the numbers. So one plus the 3% or 103% or an increase of 3%. So 103% and enter. So that increased, you could see here, one plus 3%. Now this number right here is outside of our table and I want it to stay the same when I copy it down. So I'm gonna select F4 on the keyboard, dollar sign before the W and the four, enter. And so now we've got the same kind of numbers we have over here. I'm gonna just copy that down. So this is the amounts that we're thinking has to be paid out if we want the cash flow to be having the same purchasing power to the person that's pulling out the cash. So then we're gonna say that the life insurance started in year zero at the 60,000. Let's just put the 60,000 starting point here, 60,000. So how much insurance is needed at the beginning? We'll do our present value calculation, negative present value brackets, the rate we're gonna use is going to be the same, the real rate, the real rate. And so I'm gonna say that's the 5% minus the 3%. And then I'll say F4 on the keyboard because I wanna copy that down, comma, the number of periods. We'll do this in our tricky fashion, saying 10 minus the zero, which comes out to 10, but then I don't want this 10 to move down. So that one in AI 12, I'm gonna put my cursor in it, say F4, the dollar sign before the AI dollar sign before the 12. And then I'm gonna say comma, the payment. When I'm at zero, it's gonna be that 60,000 because that real rate of return will accommodate that as we go down and then enter. So we get to that same 538. Now it gets a little tricky, I could copy it down but let's calculate it a couple more times. Negative present value brackets, the rate is gonna be once again, the real rate of 2%, F4 on the keyboard. So I can copy it down, comma, number of periods is now gonna be the 10 minus the one, 10, F4, because I don't want that to move down, minus the one, which is nine, nine periods, comma. But then the payment, if I'm in year one, I can't start at the 60,000. I have to look at the annuity of the 61,800 using that increase from that point in time. That's why it's a little bit tricky. And so I'm gonna say, okay, and that gives us, that's what gives us that 504, 30, 103. Let's do it again. I could say negative present value, shift nine, rate. We'll do it one more time here, then we'll copy it down. The real rate, F4 on the keyboard, comma, number of periods is going to be now, I'm gonna say the 10, F4 minus the two, which would be eight, of course, comma. And then the payment now is, I gotta start at the 63. So if it's three years out, it needs to be the 63, 654, which I imagine then increasing for the remaining eight years, right? That's where it gets a little tricky. And then I can copy that down. If I copy that down, just double click in the fill handle. It should copy down properly. And you can see we've got a similar calculation here, not quite exactly the same, because again, this one gets a little skewed because of the earnings and the payout. So this one's probably a little bit more exact, but this minus this, you could see scrolling down. So it's pretty close to this calculation. Again, because we're breaking it out a little bit this way rather than using the real rate, you get a little bit of a difference there. But that's the idea. So then I can say, okay, let's make this border and blue. And also just be careful in what you're talking about with this 10. So you can say like at the end of 10 years, we won't need anything, right? So we could say, okay, at the starting point, we'd need the 538, 955. And then you might say, well, then I might want insurance policy that decreases at least with this component of it as the time passes, because my years till the kid reaches 18 or the years till my work life would be over would be there anyways, or the years till my spouse is in retirement. And then we can add on top of this possibly other things we might be aiming for, like targeted goals like tuition for school or retirement help for the spouse or something like that on top. So that's what we'll talk about next time. Once we get this baseline down, which we can then kind of possibly taper as we might need it over our earning years or whatever calculation we're going to do, then we can start to think how could I add on top of it specific goals that might be there if I was to die like college or like life like retirement help or something.