 Personal finance practice problem using Excel life insurance using personal financial statements part number five Expenses and liabilities 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 basically built this from a blank sheet starting in prior presentations Continuing now, but if you do have access three tabs down below Example practice and blank example tab in essence and answer key. Let's take a look at it now Information of left calculations on the right We've started by creating the financial statements balance sheet and income statement two kinds of income statements We look at in essence and accrual and cash flow type of basis Which will become important as we think about our life insurance calculations, which we then did next we did this method last Time this is the next method. We will use let's recap the last method We thought about basically what the yearly needs would be and then multiply it by the number of years We would need it but instead of using an expenses approach We basically used the income of the individual that we're buying the life insurance for We also might add other needs to it such as added costs like the a nanny for example Which might not be a current expense, but we might add it We probably should have added that up top here and multiplied it basically by ten years But by taking the income approach and using the income as opposed to the expenses We might kind of assume that's going to be part of the the full income here that we're replacing Then in this section we in essence looked at those goal oriented items such as college tuition Retirement which would be out into the future would basically be saving for those so the calculation of them might be a bit different Then we looked at the emergency fund Which is a one-time kind of calculation as opposed to what would be needed on a year-by-year basis and another one-time Calculation such as the funeral expenses and then we subtracted out the current assets that are currently in place They could be used to meet those goals. So now we're going to start off with a balance sheet kind of approach We're going to look at the balance sheet and say hey look I would like to start off by clearing or getting rid of these liabilities Upfront if we could and how could we how could we think about that starting point? Well, notice the net assets down here is the assets minus the total liabilities But as we can see that includes the home and the fixed assets Which we can't really sell those in order to pay off, you know the liabilities So you could start and say okay, how much life insurance would I need if I say the total liabilities? minus The assets if we're talking liquid assets which would current which would be the checking the savings the emergency fund and then possibly the IRA if they can have access to it if there's no tax or or death benefit kind of implications related death taxes implications related to it and whose IRA is it do they have access to it We're gonna say minus that we could say then you can start off with the 97 8 and try to think about first Taking care of the liabilities removing basically the liabilities, which means that when we think about the Cash flow that will be needed instead of using the income side of things We might try to look at the expenses after having paid off the liabilities So we don't have the cash flow related to the mortgage and possibly the car loan and possibly the credit card as Kind of our starting point right so we would need 97 8 to basically take care of that And then start to think about our our cash flow needs instead of using the income We'll start looking at the cash flow that will be that will be necessary And then we can add on top of that once again our our goal oriented items And then we can look at the emergency fund and the funeral costs So that's what we'll do this time and then a future presentations. We'll add the mortgage Interest and then we'll do a more complex calculation based on a year-by-year Calculation insurance needed on a year-by-year method. Okay, the practice tab has some pre-formatted worksheets So you can work the practice problem with less Excel formatting the blank tab is where we're gonna be so we started with the balance sheet and then we made the income statement in two Formats here's our prior calculation now. We're gonna do the next method So I'm gonna make a skinny W to start off with a skinny S I'm gonna make it the same skinny as the S. We're gonna go to the home tab and paint brush it and paint brush the W skinny W and I'm gonna call this life insurance needed and we'll just say method 2 method 2 and So I'm gonna scroll over a bit and let's make X a little wider X needs to be a little wider Maybe like around let's make it all the way over to here And then I'm gonna highlight from X1 to Z1 and we'll make that our header home tab font group and let's say this is gonna be black and White black and white Okay, so we're gonna start off. I'm gonna start off with the balance sheet information So let's hide some cells so we can we can see the side by side Let's start off with the balance sheet. I'm gonna go from column K And I'm gonna drag on over to column V and then right click and hide them So I can see side by side I can practice moving around on my worksheet So I'm gonna look at the balance sheet and I'm gonna say hey look I what I'd like to do is have enough enough To pay off basically the liabilities But they already have the current assets So what I'm gonna think about is is how much I could pay off after the current assets to pay off the liabilities And then add the other the other necessities that would be there some instead of starting with the net assets Which is assets minus liabilities because these fixed assets are Items that they can't we can't use for cash flow basically I'm gonna say okay the starting point would be nice if we could say let's take the liabilities Liabilities less I'm gonna call it liquid assets assets colon and then we'll say okay the liabilities I'm gonna take the total liabilities now and That's gonna be include not the mortgage all liabilities all the liabilities And then this is gonna be equal to the one sixty one three and Then I'm gonna take the liquid assets so meaning I'm certainly not gonna add the fixed assets But I have this IRA and the question is on the IRA is one other any tax implications like a state taxes and To whose IRA is it do they have access to it at the point of once person's death or they're gonna Okay, they take it out at a later point in time or the restrictions to taking it out But I'm gonna say I'm gonna include it. So I'm gonna call this the liquid liquid assets And I'm gonna say this equals then the 395 plus the 24,000 395 plus the 24,000 and let's underline that font group and underline and then I'm gonna call this the Liabilities less liquid assets assets and Then we'll put this in the outer column. This will equal the one sixty one three minus the sixty three five That'll be our starting point. Okay, and then I'm gonna I'm gonna indent these I'm gonna select these three Alignment and indent. I'll select this one hometown alignment and indent again And so then we're gonna be adding to that the the yearly cash flows. So then we're gonna say yearly cash flows Cash flows needs Colon so once again, we're gonna kind of assume that whatever the lump sum is they pay off basically the liabilities and Then we're gonna add to it what the yearly cash flows would be on a needs basis on a foot on a Expenses basis in essence instead of just recording the income. So let's go ahead and say alright, so then we've got we've got the current yearly cash flow with no debt So I'm gonna unhide some cells I'm gonna go from column J and go on over to column X right-click and unhide those cells so we can see the income statements again and So on the income statements last time when we looked at at our calculation We use basically the income For the wage earner who were assuming is dot would die right the wage earner that we're buying the life insurance for Instead of doing that this time Let's look at the expenses side of things on the needs basis approach, which we made two two statements for One over here we included the cash flow for like the mortgage and the loans Which means that we would be so we're recording more of a cash flow kind of item But if we assume that we're paying off The mortgage and the loan if we can try to clear the liabilities Then we would have only the cash flow needs over here not including those big payments So we're now we're gonna say okay. Well, then the cash flow needs would be the 21 840 So instead of me using the income up up top. I'm gonna try to use the 21 840 down below Now notice that as time passes you might have inflation that factors in so you could try to try to factor that in For inflation we'll talk about that in future presentations, but for now. I'm just gonna use that 21 840 so we're gonna say okay 21 840 for the for that So let's make this a little bit wider here, and I'll put that in here. We're gonna say that's equal to The 21 840 cash flow not including the paydown of the debt because we're gonna pay that off And then we also I'm gonna tack on the nanny fees and the way that would be more appropriate here I'm gonna say the nanny fees are gonna be in here now notice over here I included the nanny feeds kind of down below where to be more appropriate We should have said the nanny fees 3600 times maybe 10 years if we wanted to add them on but again up top because we use the income up top And we're replacing the full income instead of the needs approach which would be on the expenses You may not need you know the added nanny calculation Down below because you would assume if you're in a situation where your income is already clearing the expenses But here we're gonna say that if I'm on a needs basis approach looking at the expenses I'm gonna have to add the nanny costs and then multiply times the number of years. We're gonna need that cash flow So I'm gonna say that this is gonna be equal to The I'm gonna say it's 3600 a year we're gonna say on the nanny. So that's gonna be estimated yearly cash Flow and the idea with the nanny would be that if if one parent dies there might be more costs related to basically child care Costs for example, so you can try to estimate what those would be basically on a yearly basis and you could try to then To calculate them out into the future based on you know different factors including the child's age Which I'm gonna include in the know in the ARF overall kind of calculation But you could get more nuanced about it and try to think about how many years you would need the nanny cost Versus how many years you would need to be paying the 21 840 for example And we'll get a little bit more nuanced in future calculations But for now, I'm just gonna underline this home tab font group and underline Let's sum that up equals the SUM of these two. So that's gonna be our yearly needs of 25 for 40 and then the years that we need them years needed So again, you could try to think about well What's gonna happen in the future as the children age or something am I gonna need as much cost or and get more nuance? But and when we think about the years needed if we're just using one number we could use the years until The spouse hits retirement the years that we would have hit retirement if we were still working for example We might use the the Years until the youngest kid I gets to age 18 for example And then we'll add on the goal-based items which would be retirement planning goals and Possibly college tuition and those kind of things or we might use a generic like seven to ten years for example So I'm gonna I'm gonna pretend we're calculating it based on the youngest kid Going to college Which we said is gonna be ten ten years out now again. You might not need obviously Nanny fees up until say say the kid is 18 Maybe it right but but so you could get more nuanced on those calculations in terms of what you think that the budget Costs will be on a year-by-year basis and separate that out But we're just gonna use the ten for the whole yearly costs yearly expenses home tab font group and underline so then We're gonna say and also note that you could get more nuanced too in terms of well the spouse is earning Income as well So you could try to try to subtract the spouses basically earnings or something like that But if you were to pass away in this case, they had five kids So it might you know that might she might be spending a lot more time doing doing that the Housework that you know the house upkeep and whatnot taking care of the kids, but That's another option that could be you know could be relevant there in terms of This expense here you could try to factor in the wages of the spouse if if the life insurance cost is you know tight For example, so I'm gonna say there it is and so let's do an indentation here Alignment indent I'll multiply that times 10 and I'm gonna I'm just gonna copy this one up top control C paste that down below Get rid of the colon double-click remove the colon and dent two times alignment and dent and dent And then we'll multiply that out and put it in the outer column This equals the 25 440 times the 10 years to get to the 254 4 okay, so then we're gonna add just like we did before the estimated estimated costs above daily living living Costs and we might Call it, you know the the goal oriented costs, right that we're basically looking into at this point And so you might call it something like goal Ori oriented costs instead of maybe for dependence in retirement So because I'm because these are gonna be treated differently because of the behavior of them We're trying to save up until some point in time in the future as opposed to trying to calculate what the yearly Expenses would be in trying to think about how many years we have to cover over so we've got the college tuition college Tuition and again, I think we said we had five there at five kids here But we're just we're just calculating it one time to practice it for that one golden child that youngest kids going to college The rest of them are lost causes, but that last one he's going to college I don't know it doesn't you don't have to go to college college seems kind of crazy these days anyway But whatever we're gonna say the college cost. I'm gonna say is the thirty five thousand thirty five thousand and again Multiple ways you can look at the college cost. You might say well, it's thirty five thousand today If you research it, I'm just pick that number up You could it's you know, that's just we just using that as an estimate But if you found that that's the cost today, you might say well, yeah But ten years from now it's gonna be more than that But if you died today, then you would think that they that they can invest it and basically Hopefully get at least inflation for That would hopefully Cover the cost of college, but in the future every year that passes the college will be more expensive So if we wanted to get more complex on this calculation We could certainly kind of do so because you would think the college cost would go up each year And so we'll think about that in a bit more nuanced way in future presentations So then we've got you might also have the name the nanny We already included the nanny costs, and then you might have then the retirement Retirement and so again, I'm just gonna pick a number here of 500,000 a goal a goal oriented calculation Just to just to set one so you might think about you know, who's we you know How much you're gonna you're gonna be contributing to the retirement? So notice that this cost up here We're thinking about how much they need for the year to year stuff and then this one down here We're looking at the goal oriented stuff, which again We might say well if we died they can invest and then save up to hit some goal or something like that But we'd have to be more nuanced about it because we could you know, we don't know which year We're gonna die in right and how much time the earnings would would go over So I'm gonna use the 500,000 for now and we'll dig into that more detail in future presentations You might also have something like care for parent Like elderly care or something like that which could could be costly that you might have a goal oriented item for as well And you treat it in a similar fashion We'll talk more about them in future presentations if we want to get more nuanced about it I'm gonna copy the goal oriented stuff put that here double-click on it delete the colon go to the outer column equals the sum of those three So there we have it. Let's do some indentations. I'm gonna select these four We'll go to the home tab alignment indent and then goal oriented will alignment and indent again And so then we've got the one-time cost. We're gonna call this the emergency funds six months Expenses So we got the emergency fund. So we said the yearly expenses yearly expenses We saw at the 41 right wasn't that I could just pull it from up here Cash flow with the no debt was the was the 21 840 and then I'm gonna add on to that then the nanny cost Which I calculated up top to right there the nanny costs We could have just taken the estimated yearly flow, but we'll recalculate that and that's gonna be The estimated yearly cash flow needed Again, this equals the sum of Those two I'm gonna put an underline here We'll go to the font group and underline it and then I need six months So I'm gonna take half of that half of that dividing it by two So I'll put an underline here home tab font group underline And so then that's gonna be I'll copy this up top control C Put that down below double click on it get rid of the colon and then put this in the outside This is gonna be equal to the 25 440 divided by two for six months. That'll be the 12 720 so notice that this is just like a one-time Calculation as opposed to this up top which we said was the yearly calculation, which we're gonna need for 10 years That's why it's it's we want it in a separate category because it's kind of a different it behaves different these expenses Gotta calculate up there. So we're gonna select these five Home tab alignment indent. I'll double indent this one double indent alignment indent again And then we've gonna have the funeral costs so that the funeral I'm gonna say equals and go all the way to the left Where we had the one-time cost of the funeral Just burn me in a bonfire and throw me in the ocean This is outrageous For crying out loud Just because i'm grieving doesn't mean i'm a sucker We're gonna Put an underline here Underline and then this is gonna be the life insurance needed Method two And i'm gonna say okay. Let's sum this up equals the sum And so now we started with this this net Uh so that we can pay off pay off the liabilities and then we added The cash flow for the yearly cash flow for 10 years And then we added the goal-oriented cost that may be over and beyond the 10 years And then we added the one-time emergency fund and the one-time cost of the funeral expenses to get to the 908 for 20 Now notice that that number is less than The 1000 the 1 million 106 10 we got to last time because basically one reason is basically We started by looking at the the 60 000 income number as opposed to the expenses number I mean so notice that you could you might when you use this method over here This first method you might base it on for example the expenses On a cash flow method, so I didn't pay off the liabilities So instead of picking up the 60 000 here you might base it on the cash flow Which includes the mortgage payment and the and the loan payments of the 41 to 20 and so that's one One difference that would be more on a needs-based approach as opposed to the income and you get a closer A closer range between the two there And also note if I did that the the 3600 should probably be up top For the 10 years that that added nanny costs up top, but I'm going to bring it back to where we were before Doing the 60 the 60 and so there we have it and then I'm going to select These items. Let's make that blue and bordered Home tab we're going to go font group border blue borders and then blue drop down if you don't have that blue It's right here Standard there's the blue. We need making it blue. Let's do a checking on the spelling review spell check it emergency emergency Okay, that's spelled right so there we have it now next time We'll try to get a little bit more nuanced and we'll add like the the mortgage And then we'll we'll do some calculations where we can think about well, what's going to be the cash flow Over over a certain time frame and could we have a life insurance that kind of declines over time for example?