 Personal finance practice problem using Excel. Life insurance calculation based on child age. 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 and blank, example and essence dean and answer key. Let's take a look at it now. Information's on the left-hand side. Calculations for the life insurance needed on the right practice tab has some pre-formatted cells. So you can work the practice problem with less Excel formatting. The blank tab just has the information on the left and we'll build it from a blank tab. If you don't have any of this, you could just put the information on the left-hand side and start from there. I would first start if you had a blank sheet by selecting the entire sheet, putting the baseline Excel formatting by right clicking format the cells. I generally start with the currency brackets and then no symbol here and no decimals. I'm not gonna hit okay, but just close it out and then put the information on the left-hand side adjusting any formatting as needed, such as the percentages down below, make a skinny C column and then we're ready to go. So the information on the left says we got a married couple with two children ages three and nine. One spouse is not working, taking care of the home. Support needed per year, we're gonna say is 15,000 and we're gonna use these returns down below the return inflation and return after inflation for part of our calculation. So here we wanna focus in on how many years, for example, we might be thinking that somebody needs insurance. Again, you can get into a lot more depth in terms of different ways that you can calculate the need for the life insurance, but when children are involved, we might use that as kind of a baseline and say, okay, well we would need the life insurance if one spouse was to die to support at least the childcare of the children, possibly childcare needs for basically either spouse that would then need to be working and doing the childcare for example. So we might use that as the kind of years as part of our calculation for the total life insurance we might need. So for example, we might do a calculation to something like this or as part of our overall life insurance calculation, insurance needed, and let's put some, let's put some blue or some header formatting. I'm gonna select from D to F1. I'm gonna go to the home tab font group and make this black and white. And so we're gonna say, let's say, we're gonna start with the years until the youngest child is 18 and colon. Now you could get more complicated than this. You could say, well, if there were two children, it would cost more for the childcare and so on and so forth. But we're gonna say, well, we're gonna try to say until the youngest child is to a point where they might be able to have their own earnings would be the idea that being 18, that number also you can adjust that. You can say 18's kind of low these days. You might say that they might need more support given these current circumstances beyond 18 so you can increase that number or decrease that number. But the general idea would be there's gonna be needed more support up until they can support themselves would be the idea. So we're gonna say starting with 18 in the inner column. So the age kids are out, are on their own, are on their own age. And then the age we're gonna say of youngest child. And we're gonna say that age is three. So we'll say years until youngest child is 18 is gonna be the subtraction of these two. So we're gonna say 18 minus three. And so we got 15 years. I'll put an underline here, hometown, font group, underline. Now, oftentimes when you look at these kind of online tools that we looked at before online life insurance calculators, we often get the question in terms of how many years. So like this one is one of the more basic ones but oftentimes they've got how much money will you need it for burial. So I won't include that right now. You could take care of possibly your own burial before you die. You could think about trying to cover those costs and prepay them for example or something like that. But that might be a component. How many years of income will you need to cover? So this is often kind of like a generic number. One way, there's multiple ways we can think about that. We might just say, I'm just gonna use a generic like seven to 10 years, which is basically kind of like a baseline that people might need life insurance for that amount of years is just kind of a general number. We might then say, well, here's how many years I would have been working until retirement is another way or I might say this is how many years my spouse would work until retirement or I might say, hey look, I want the life insurance to cover how many years the youngest kid is going to be at least on their own, at least to some degree and not need like childcare support for example. And that's the calculation that we're taking a look at here and we came up to the 15, so the 15 years and then how much annual income will your survivors need would be the other kind of calculation. Again, you can base this on many different things. You might say, well, what's my current income? You might try to take a percentage of your current income. You might say, hey, whatever my income is now, I'm gonna then take 70% of it, 60 to 80% of it for example because I won't be around for example and I wanna just be covering the needs of it. That's one way you can do it. You might try to figure out what the childcare costs are that you think are gonna be over and above possibly what would be needed if you had a spouse that was actually taking care of the child to cover working and childcare for example and basically use a calculation such as that or we can get into a whole needs calculation and try to figure out what the current needs calculation would be. So we'll talk about more of those options so that needs-based approach at least in future presentations but here we're gonna use kind of a generic number the 15,000 oftentimes they use 10,000 it's kind of like a generic number I'm gonna put the 15 here. So how much money do you have in the savings? I'm gonna say no monies in the savings at this point so I'll keep it there. Obviously we could get into more detail in terms of how much debt do we have versus the income but we're kind of focusing on the age here and then the children, we're gonna say the children. I've got three and nine here and then you can see their estimate on the calculator so you can use these tools kind of in alignment with each other if you so choose but let's jump back on over to Excel I would typically use those other tools as kind of like a double check to see if you're in the bull park doing best practices with Excel and then go back on over here I'm gonna do some indentation I'm gonna select these three home tab alignment indent I'm gonna double indent this one home tab alignment indent it again and so then I'm gonna say the support needed per year I'm saying is 15 once again we might calculate that based on some percentage of our earnings or we might calculate that on the needs-based approach or based on childcare needs if one spouse wasn't there for example I'm gonna go to the home tab font group and underline it also note that we can use this as part of our calculation and add on top of it things like college and stuff if we wanted to have specific calculations related to college this would cover them up until they're 18 and then we might say I might have a separate college kind of calculation for that if I wanted to do that as well to tack on to it so then the insurance needed we would say would be equal to the 15 times the 15,000 the 225 so I'm gonna go ahead and make this blue and bordered home tab font group border blue so it's a fairly straightforward calculation we could get much more complex but again we're just basically trying to get how can we get to this number a couple different ways we can get to that how many years we would need now notice you also might say well I don't really need 15,000 times 15 what I really need is the lump sum to be paying out the equivalent of 15,000 for that 15 years for example so in other words if I put this in I might have earnings if they were to invest that they might have earnings over 15 years averaging like 6% for example and inflation let's say is 2.5% so we can say okay the return after inflation is about 3.5% so in other words how much would they need in a lump sum insurance if they were to invest it and basically get a real return of 3.5% and for that we can say okay let's try to do and cause you can see that this number by the way is substantially larger than what they've got here not that their calculation is perfect here this is a fairly basic online calculator but we could see their insurance their calculations assuming the rates of return down below and we could try to make a similar assumption and say okay well what if we did a present value present value of annuity annuity calculation annuity I'm gonna put this over here and let's make this a little wider and say this is gonna be negative I'll put a negative instead of an equal probably not the most proper way to do it but it's the easiest way to do it to get a positive number present value brackets and then I'm gonna pick this net or real return of the 3.5% comma and the number of periods I'm gonna say is 15 that we're gonna need comma and then the payment we will have a payment we're gonna say it's gonna be a payment of 15,000 so if there's a payment of 15,000 for that frame how much money would that be? That would be 172,761 so in other words if we had that much in place and they started taking out the 15,000 per year or net equivalent then we would only need 172,761 because of the earnings so let's try to break that down a little bit more I'm gonna go to the home tab, font group, brackets and blue and so let's try to visualize that and see what I'm saying here so I'm gonna put my cursor on the skinny column, home tab and format painter let's put that in column G, making column G skinny and let's try to break this out into a table to see if I can understand what we're saying and say years, increase, that's our gains and the payment and then the balance so let's go ahead and make this our headers we're gonna say this is gonna be font group it's gonna be black and white I'm gonna alignment and center it I'm gonna make the years a little bit smaller and call them H and let's put from zero, one, two I'm gonna select those three cells put my cursor on the fill handle drag down to 15 years center that home tab alignment and center and so then I'm gonna start with my balance over here and say okay what does this mean if I had 172, 761 and then there was an increase I'm gonna say a net increase here of this the real return 172, 761 times the return of the 3.5 and I'm gonna say F4 on the keyboard so I can copy that down and enter we would get a return of 6,047 and then we're gonna have a payment which you could put as a negative negative of the payment which is gonna be 15,000 if I was to die they're gonna have to pay out 15,000 each year that's the idea I'm gonna say F4 on the keyboard so I can copy that down that means the balance then would be the prior balance plus the increase if they were to invest that balance minus the amount they're gonna have to take out I'm gonna say plus because it's a negative number here plus the negative 15 subtracting it out so 172, 761 plus the 6,047 gain minus the 15,000 would bring us to the 163,808 if I was to copy that down I'm just gonna select these three and copy it down then we should have down to zero at the end so that's the idea so if they were to take out that much we should get down to zero and we should be okay at the 172,761 instead of the 225,000 let's try to analyze that a little bit more in depth here I'm gonna make this blue and border gonna go up top and say font group make that blue and border this won't work out exactly but this will give us hopefully a little bit of better understanding of it so I'm gonna hide from column C to column F so I can see this I'm gonna right click and hide not delete but hide I'm gonna make a skinny L column by going to the skinny G column home tab, clipboard, format painter skinny L okay so then I'm gonna do another table here a little bit different it's gonna be years increase and then payment and then the balance and let's put this are black and white header selecting home tab, font group making this black and white I'm gonna make them centered alignment and center we'll make the years a little bit smaller on the years same with the zero, one, two I'm gonna select those three numbers and copy it down to 15 years 15 years and then I'm gonna say home tab alignment and center that so then we've got the balance over here where we're gonna start off once again with the 172.761 now again this won't be exact but you might say what's really happening is we're gonna say that there's a 6% return but then we're going to be paying out we're gonna be paying out 15,000 but there's gonna be inflation on the 15,000 so they're gonna need more than 15,000 to spend the same kind of money due to inflation in terms of purchasing power so if I was to say okay there's an increase I'm gonna say I got this 172.761 times an increase of the 6% return but really the real returns over only 3.5 because there's inflation but I'm gonna say okay in future value terms there's gonna be a 6% return so I'm gonna say okay F4 F4 absolute reference we can copy it down and enter but when I make the payment it's not just 15,000 isn't gonna cover it anymore because there's inflation so I'm gonna need to increase it by 2.5% because of the purchasing power of the dollar went down so I'm gonna say this is gonna be equal to the 15,000 over here or let's do it this way I've gotta do it with a future value calculation so I'm gonna say this is gonna be equal to I'll say negative future value brackets I'm gonna pick up the rate over here which is the 2.5 and that just it's just inflation I'm gonna say F4 on the keyboard to make it absolute comma the number of periods is going to be I'm just gonna pick up this one right there so it's gonna be one year out comma this is not an annuity so we're not gonna have a payment so we're gonna have two commas and the present value is gonna be this 15,000 here and then I'm gonna say F4 on that one because I'd like to copy it down close up the brackets so the future value we're going to need is the 15,375 to equal the same 15,000 if it was in time period zero due to inflation so then if I double click on this let's actually get rid of the negative sign and make that a negative number like that and this is gonna be equal then the prior balance plus the increase plus the negative payment which is a decrease so 172,761 plus the increase of the 10,366 minus the payment which would have to be 15,375 to be equivalent to the 15,000 that we looked at in year zero dollars so if I was to copy that down I'm gonna select these three and just double click on the fill handle button then it's not exact here because we used a different number on these two but you can see between year 14 and 15 it goes below zero so that last year it's at 18 instead of what we would like it to be at the 21,724 you could adjust the payments for that the full amount for that 2005,25 but that's the general idea, right? You're gonna say okay it's gonna increase by the 10,366 but I'm not gonna pull out the 15,000 anymore because of inflation we had to increase it for the inflation at the 2.5% that would mean they would need to pull out for equivalent purchasing power dollars 15,375 and so now we've got this ending balance in the future value in the future value terms so you can see these numbers are different here because this one is kind of netting out the values here the net real return and this one is showing the actual payments we're trying to see what the actual payments might have to be in future time frames for the same purchasing power okay let's make this one blue and bordered I'm gonna go up top and say home tab font group make that blue and put some borders around it so that is that now the other thing you might look you could obviously do with this if you had a calculation like this you might be saying okay is there a way I can set up my life insurance policy if this is what it looks like so that each year the life insurance policy possibly goes down because you've got less years that you need to cover over that 18 right we had the 18 year time frame once they get to year 18 once the youngest child gets to the year of 18 so you might try to think about a life insurance policy that has the balance that kind of reduces over time for example as the years that you need to cover goes down and you could think about that calculation in more complex ways or kind of like a simple kind of way such as this so I'm gonna then unhide some cells put in my cursor on column B to column H those hidden cells between them right click and unhide there let's do a spell check I'm gonna go down here and say review spell check see if everything's lined up young guest young guest out I've got that twice I don't need it twice there it is