 Personal finance practice problem using Excel. Life insurance using personal financial statements, part number four, gross income method. 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've basically been building this from a blank sheet in prior presentation starting with the balance sheet and then two formats of the income statement. This time, we're gonna look at method number one for the life insurance needs calculation. If you do have access to this worksheet, there's three tabs down below. Example, practice and blank. Example tab in essence being an answer key. Let's take a look at it now. Information's on the left-hand side. We constructed a balance sheet and an income statement, our personal financial statements to help us out with our calculations for the life insurance. The income statement we had constructed on an accrual method and more of a cash flow type of method. Now we're going to use that to construct our life insurance calculations. We'll start off with kind of a more basic life insurance calculation. We'll get into a little bit more complex calculation. Then we will add our mortgage loan calculations for the amortization table, breaking it down on a year by year basis. This will be in future presentations. And then we'll use that to get to a more complex life insurance calculation that we might use for more complex, say, term life insurance that might like decline over time. So that's gonna be where we're gonna go. The second tab, practice tab, has a pre-formatted worksheet so you could work through the practice problem with less Excel formatting. The third tab, the blank tab, we've been building from scratch here. So we made our financial statements, the balance sheet, the income statement, two formats of the income statement. We're gonna use that to do our life insurance calculation. And we'll show this in a couple different ways. So first I want a skinny S column, skinny S, that's where we need to start here. So I'm gonna go to the home tab. Let's go to the skinny O. I'm gonna paint brush the skinny O to the skinny S. Home tab, paint brush, and skinny S. So we got the same skininess for the O and the S. And so this is gonna be the insurance out needed. This is gonna be method, let's just call it method number one, method one. And I'll make this a little bit wider for column T. Let's make that a little bit wider here, something like that. And then I'm gonna select from T1 to V1 and make that our header format, which is in the home tab, font group, bucket, drop down, black and white has been our format. Now we're gonna start off with basically, the first part is gonna be related to what we think our needs are from a cash flow standpoint from year to year. So if we were to die and someone was dependent import on our income, how would we first think about how much income they would need, for example, for the years going forward on a cash flow basis? And then we could tack on top of that goals that we're looking to save for, such as college tuition, retirement, for example, and that kind of stuff. So first we're gonna start with this method using the times yearly income method. So I'm gonna say, we'll base this on the income. So this first kind of component, there's multiple ways we could do this, right? We could say, okay, I'm gonna take a look at this spouse, the spouse that makes more money, that's the one that's going to die. So if that one died, they would be losing that income. We could base then this yearly needs for the other spouse that would be living based on that income. We could use that income to calculate some fraction of that income to calculate. Or we might use the expenses that we got down here on an accrual basis method, or we might use more of a cash basis method for the expenses. And that will depend in part, if I scroll back on over and we see that we have the assets minus the liabilities, we might first decide, could I just pay off the liabilities? Maybe I could set up my life insurance to be enough that we could pay off the liabilities. And then I might say simply base my calculation for the yearly needs after that point based on the expenses, not including the cash flow for any liability payments. Or I might say, hey, look, I wanna make my year by year calculation based on the total expenses, including those liability payments, assuming that they're just gonna keep the things as they are and pay off the liabilities as they come due according to the current loan structures. Or we might say, hey, look, we're good right now with the 60,000, I would like to keep the spouse at the same basic income level or threshold and just base the calculation based on the 60,000 on the earnings or possibly some fraction of the earnings. So we'll look at a couple of different methods as we think about different methods. The first one, we're gonna base on the gross income. So I'm gonna say the yearly income, yearly income of the spouse that would pass away, we're gonna say is this 60,000 spouse number two. That's who we're concerned about. Then we have the number of years that they're gonna need that income for. Again, multiple ways we can get this calculation. We might say, how many years would I have that income in practice until retirement, for example? And then past that, think about the retirement needs for the spouse as a separate goal oriented component. Or I might think about how many years my spouse has till retirement. Or I might think about how many years until the children, the youngest child possibly reaches age 18. And then after that point, I could think about other things targeted goals like tuition, like retirement for the spouse, for example. Or I can use a generic heuristic, which would be like seven to 10 years, for example. So I'm gonna say that we'll base it on the youngest child here, we're gonna say, okay, the youngest child has 10 years before they're 18 and out of the house, or at least go into college. And then we'll figure out the college calculations if we're gonna add that. So we're gonna say there's 10 years. So I'm gonna say years, years, I'm gonna say is equal to 10. So we'll just say 10 down here on the years. And there we have it, we're gonna underline that. I'm gonna put an underline, bracket and underline. And I'll multiply that out. This is gonna be the 60,000 times 10. And that's gonna be the 600,000. And this is gonna be, I'll say this is just the times or copy it down. Let's just copy that, control C and paste it down here. Get rid of the colon, do some indentation, selecting these three, home tab, alignment indent. I could select this item, alignment and indent again. Now notice in prior presentations, we also thought that you could take a fraction of that 600,000 and say, well, I'm not gonna be around. Therefore, maybe I could take a heuristic fraction like a 70% and or notice that this is saying that they would need 600,000 in order to get 60,000 for the next 10 years. But that's not necessarily the case because if they got 600,000 in a lump sum, they could invest it and basically get possibly a return on that. So we could get into more complex calculations in terms of what would be the annuity or what lump sum would we need in order for them to draw out 60,000 per year. So this is kind of a more of a generic calculation. And so we'll get into more depth on it in future presentations to get into some of those questions, but that's the first part. And then we've got the estimated cost. So we've got the estimated costs. We'll say above daily, daily living costs, costs. And so then we might do a calculation. I'm gonna have a sub-calculation. I'm gonna call this for dependence and retirement, retirement, for example. So we might say for the dependence, we might say we have something like college tuition. So college tuition might be a targeted goal. Now we had five kids, we listed out here, but I'm just gonna do it on one child, one calculation. For one special child, we're gonna say that's gonna be that last child's gonna go to college. But I'm gonna say it's 35,000. So 35, this is gonna be equal to, I'm gonna go to the left here and say this is for the special last child that had that 10 years till they get to college. And we're gonna assume that it's 35,000. Now the college tuition gets a little tricky too, because we could say, hey, look, if it costs 35,000 now, how much is it gonna cost 10 years? I could use a future value calculation to get to how much it's gonna cost 10 years from now. But I don't necessarily need to put in the college tuition for that future cost because if they got the money at this point in time and I died, they could save it possibly, put it into a savings account and get a return, hopefully that's increasing over inflation to reach that targeted goal. But it gets, so it's a little, there's gonna be some confusion in terms of when I would die and how much money we would have to put in in order to reach the targeted goal of the college tuition. So a generic number might be, say, simply today's college costs so that when I die, they can invest it, hopefully at least getting the rate of inflation in order to pay for the college when you need it. But we could get into more in-depth calculations about that, which we'll talk about in future presentations. And we might have added childcare. So if we die, then they might need more childcare, for example, over and above the expenses. So let's say that we added the nanny cost of the 3,600 a month, let's say, or 3,600, that was, well, we're gonna say that's for the year. So I'm gonna say that's for the year. So nanny costs, I'll just say that's gonna be 3,600s that we'll tack on as well. And then we might have a retirement, retirement that we're gonna, we wanna help out with as well. So retirement, retirement. So again, this calculation up top represents the cash flow needed for that year's possibly up till retirement or possibly up until the child reaches 18 or whatever. And then we might say, okay, I'd like to help with my spouse's retirement in some way. And we might have multiple methods. We can think about how we would do that or how we would get to that. It would be a same kind of targeted approach that we might use for retirement. We might just, or we might say, I would like to basically be able to contribute. I'm gonna come up with just a random number, which is gonna be 500,000 for the retirement to put that in place to help with the retirement. But the point is here that it would be a targeted type of thing that we might be able to put in over and above the cash flow that would be needed for the day-to-day operations up top that we would tack on. So I won't get into that in more detail here. We'll talk more about like this college tuition as a targeted kind of component that we could treat in a similar way in future presentations. So let's go to the home tab. Let's go to the font group, let's underline. So this is gonna be the total added costs. I'm gonna sum that up in the outer column equals the sum of these and enter. So there we have that, okay. Actually, we'll talk about the retirement as a targeted goal too when we get into the more complex calculation of it. But in any case, there we go. So then we've got the emergency fund. I'm gonna say emergency funds, six months of expenses, six months expenses. Now notice that because we base this on the 60,000 and not really a need spaces, the expenses method, then they might already have enough kind of to clear an emergency fund in that case because we're having the full income here. But we might then add a six month emergency fund for six months would be a general heuristic type of number. Let's do some indentation here, by the way. Let's select these three, alignment and dent. Let's indent this one again, alignment and dent again. So we might say that I would say, I would wanna take my yearly expenses, yearly expenses. So let's not base it on the income this time, our income. Let's say we wanna take our yearly expenses and I'll do it on the cash flow basis method. So that would include the mortgage and the credit card. So the cash flow that would be needed for a year, for example, and then I'm gonna say that we have the added nanny costs for the year, nanny costs that are over and above what we're currently paying, which I'm gonna assume that might be in place if I was to pass away in order to keep the job going. And so then that would be the total yearly, yearly expenses equals the sum of those two. And then I'm gonna take half of that, half of that for the emergency fund. And that's gonna be our emergency fund. I'm gonna copy this top number, paste it down here, double click on this cell, get rid of the colon at the end. And then we'll take this to the outer column. This will be equal to the 44,820 divided by the two to get us to the 22,410. Okay, so let's do an indentation here. So I'm gonna say indent on these. Alignment indent, I'll indent this one again. And so on top of that, we also might have the funeral costs, funeral expenses, expenses estimated. Now this is something that we could possibly take care of some of this and preset our funeral kind of arrangements up beforehand, right? And then maybe we could take care of it possibly that way, but, or we could try to estimate how much our funeral expenses would be. Just burn me up and throw me in a coffee can for crying out loud, how much can it be for? So but in any case, this is gonna be 3,800 is gonna be over here, so here's the 8,500. We're gonna assume that's gonna be a one-time cost. So notice it's not gonna be up here because these are the yearly costs. These are our targeted goal costs. These are the added costs for the emergency fund, which is kind of a one-time kind of calculation. And then we've got our one-time cost of the 8,500 just to get rid of my stinky corpse so that you can move on. So we're gonna say then this is gonna be, that's gonna be the 8,500. So the estimated families financial needs, financial needs, then it's gonna be the sum of all these, the sum of this outer column, some of this outer column. So we've got the income needs, the cash flows they'll need, and then the targeted goals, and then the emergency fund and that one-time need for our funeral expenses. So then you might say, okay, but we already have some liquid assets over here. So if I look at the balance sheet, we might say that's what they would need, but I already have these assets that could pay for part of that. And so we could say that, and notice the way we did this, we're saying we're not really calculating the pain off of all the liabilities at the time of death, but rather we're kind of calculated on a cash flow basis, imagining that we could still continue to kind of pay those items off. And we already have on the cash flow side of things this 39,500 and possibly have access to this 24,000. Notice we don't really have access to these assets down here. We could possibly tap into them by taking equity on the home or something like that, but these assets are kind of not gonna be helpful to us to take care of those cash flow needs for the most part. So considering these liabilities, we kind of took into consideration by mapping out and thinking they're gonna be part of the payment structure for our income that we added. So now we can say, well, we already have these assets on the books, so maybe I can subtract those out. Now remember the current assets are something that they have access to at this point in time. The IRA and the something that under the 401k plan, there could be tax implications if there's death benefits on it. And you have to determine, is this your IRA or the spouse's IRA? I mean, do they have access to it at the point of retirement? You know, how much access do they have? But we're gonna assume that they're gonna have access to these two at least at some point. So they got a cash flow of this 63,500. So if we're saying that this is the total cash flow, the total cash flow they have, then we could say, okay, let's we're gonna take out the liquid assets that they already have access to. So I'm gonna just add them one at a time here. I'm gonna say, we got the checking account. We got the checking account currently. And then I'm gonna copy that down. And so like this emergency funds, you might be saying, hey, look, when I calculated this, you might say, but yeah, they already have an emergency fund set up. That's right, I'm kind of calculating the full, you know, cash flow needs. And then we'll subtract out what's already on the balance sheet, right? That's kind of the idea. And then I'll also include the IRA, assuming they either have access to the IRA at the point of death, possibly depending on whose IRA it is in tax consequences for estate planning and age at death and so on. So then we're gonna go to the alignment and indent. I need the numbers too. You need the numbers too. You can't just pull over the name. So we'll pick up the number, which is gonna be that 3000. And so I'll copy that down on the 3000, put my cursor on the fill handle, copy it down. I can't copy it to the IRA because there's another, I think I can take the IRA and copy it to the right, auto fill it to the right. And no, I can't do that either. Whatever, I have to do it this way. I was trying to make it easy, but no, the IRA has to be a problem. It's over here in the 24th in the outer column. That's why. So there it is. Let's go ahead and format paint this down because I messed up the formatting too. Let's put an underline here, underline. And this is gonna be then the total liquid assets. I'm gonna copy this up top, put it down below and just put a total in front of total liquid assets. And again, remember that that last category, the IRA is the one you might wanna put a little bit more thought into, but alignment and dent twice, outer column equals the sum of those four. So we got the 65, 63, five already in place. So then we're gonna say the life insurance needed is gonna be equal to the 1,169 total minus the 63, five. So that's one method that we can kind of use font group underline. Notice that this method is kind of calculating things as of this point in time, right? And so you would think that as I get closer to retirement or the kid, the youngest child reaches college, that my life insurance needs would go down, right? And that's why some term policies might have lowering life insurance as time passes. So we could think about that in a little bit more depth. We thought about the fact that this 600,000 is based on the 10 year factor, and we could calculate that multiple different ways and think about what would happen as time passes. We talked about these targeted goals being, these are pretty, just an estimate of the target goals, but we could get more detailed into those calculations. And we could also think about this as if, what if I tried to first pay off the liabilities, right? Pay off the liabilities, which would be nice, and then think about what my cash flow needs would be afterwards, and or think about the cash flow needs on an expense basis rather than an income basis. So there's a whole lot of different ways we can imagine this, and we'll talk a little bit more about them in future presentations for now. Let's just wrap this up. Wrap it up, put a bow on it. You can't wrap it up without having a bow on it. If you're gonna wrap it, you might as well go all the way. We're gonna put the brackets around it. We're gonna put the bucket, if you don't have that blue right there, it's in the more, it's in standard, and that blue right there, blue, that's the one I typically use, and then we'll put a double underline maybe down here, home tab and font group double underline. Let's then go to the review and spell check it, families, okay. If that's what you say, spell checker, retirement, emergency, emergency, all right. I'm cool with those spell checks. So that looks good. So we'll continue on with another method next time.