 Personal finance practice problem using Excel Life Insurance using personal financial statements 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 and blank the example tab in essence being an answer key Let's take a look at it now Information is on the left-hand side. We're first going to think about putting together our financial statements personal financial statements That is the balance sheet the income statement Discussing different ways that you might compile this information in practice as we do then we'll use that information To do a couple different calculations for how much life insurance we would need then we'll factor in and create Our loan amortization table as well as breaking out our loan on a year-by-year basis And use all that information to put together some more Sophisticated life insurance needs calculations trying to break it out on a year-by-year basis Possibly helping us to then purchase life insurance that might say decline Over over the years or possibly having some more complex life insurance Calculations we are looking into so let's go back to the practice tab This has some pre-formatted worksheets so that you don't have to spend as much time on the formatting and then the last tab The blank tab we will spend the time with the formatting if you don't have any of this You could just put the information on the left-hand side and use that then to create The data that we're going to be putting together It will take some work because this will be a longer problem more data on the left-hand side But not too bad to get started off with so if you have a blank sheets You might want to put down or lay down the base or underline formatting first Which I would do by selecting the triangle and then right-click on the selected area go to the format cells And then I would typically go to the currency Brackets in red for the negative numbers no dollar sign no decimals. That's the starting point I'm not going to hit okay But just X out of it because we already have it here then add your data on the left-hand side Which again there is kind of a lot of data, but it's not too bad Just to do the data input for it good practice to do and then make a skinny C column and we're ready to go So let's just take a look at the data. We're gonna say we have a married couple the kids We have their five kids the funeral expenses are here nanny costs And we're gonna say those are added nanny costs that we're gonna assume if like one spouse dies for example We're looking at the life insurance for the spouse that earns more That's what we're gonna be focused in on and so we've got the assets This is basically what we're gonna create our balance sheet from and that's what our starting point will be You might get this information from say if you have like a quick books or something that you're working You might use that or you might have some software that can compile basically your balance sheet fairly easy something like a personal capital kind of thing which can pull in the ending balances from like your Financial institutions that can help you to put together some of this stuff That's another method you might use at least for the balance for the balance sheet side of things Or you could just use your financial statements and compile Basically the balance sheet and then we've got the income statement information This is information which is it kind of a little bit more difficult in some ways to get Because you can't just pull it from the ending balance of a statement, but you have to compile it in some way So if you don't have any of it you can use other tools to put it together That would be easier to use something like a quick books or something like that Or you might just basically go through your transactions in your bank account try to figure out basically your income statement Your expense needs and your income needs of course you could start also with the w2 form or with a With your with your wage stubs for example, and then we've got our expenses broken out by category They're currently on the monthly expenses and we're going to bring them to a yearly We've got inflation at 2.5. We'll assume that'll come into play later years until youngest child is 18 So we're going to assume it's it's 10 years until the youngest child is 18 We're going to say years left on the mortgage 20 the mortgage rate is 6 percent current college costs Estimated so we'll get into college intuition or specific goals later First let's put together the financial statement So we'll get the standard balance sheet and income statement We'll put them together starting with the balance sheet so we can understand their components And then use them in our life insurance calculations noting that the life insurance calculations could be custom to specific circumstances Okay, so we're going to start off with the balance sheet. I'm going to say balance sheet So there we have that I'm going to make that black and white. It's going to take a few Cells, so I'm going to go to the font group. Let's make that black and white black and white And then I'll expand the d column putting my cursor between d and e. Let's break that out We're going to start with the assets assets And then under the assets I typically break out current assets and long-term assets Which is kind of terminology for businesses But I think it works for individuals too because you might have current Such as financial assets like your savings account your cash and so on And then you might have long-term kind of assets which would be stuff I would say possibly under the umbrella of like an ira or 401k because you can't really get to it until retirement for example and then You might have other like fixed assets like the home for example in cars So I'm going to say let's say we have current Assets this would this would be the more liquid axed assets the ones that you can possibly get to In order to pay bills, you know, so this is going to be equal to I'm just going to pick up the data on the left Hand side in practice. You might get this information from your financial statements You might use personal capital software or something like that to pull the information Automatically for something like the financial statements. I'm going to say then in column e This is going to be equal to the number 3000 by the way, I'm not promoting personal capital or anything. That's just one one tool that you could use And then I'm going to copy that down so it goes to the savings and then the emergency fund So I'm going to put my cursor on these two items I'm going to put my cursor on the fill handle and copy that down a couple items down I'm going to scroll up a little bit. I'm holding down control and scrolling up so I can zoom in a bit So there we have that and then I'm going to say let's say this is going to be the total current assets So notice the structure of this. I've got a colon So now I'm going to do my indentation thing to make it looks nice I'm going to select these four items home tab alignment and increase the indentation And then I'm going to put my cursor on the total alignment and indent again I'll put an underline below the 20 font group underline Put this in the outer column in column f use the trusty sum equals the s to the um Brackets and sum up those three items giving us the 39 500 so then I'm going to put the other assets here Because I want I like to see the financial assets up top. So when I look at other assets I'm not looking at the home. Those are going to I'm going to call those fixed assets But other assets that are financial for example I might put into another category which would be something like the IRA or a 401k plan They're under the umbrella of an IRA therefore you can't really get to them until Retirement for example without being penalized on them. So I'm going to pick up the 24 000 there I'm just going to put it right in the outer column And I'm not going to say total other assets because there's only one category of one item in it So I'm just going to put that right into the outer column And I'll even indent that alignment and indent that here And then we got the fixed assets. We got the fixed Assets in business terms. You might be called property plants and equipment for example depreciable assets You might you might think of them as and that's going to include the big assets I'm not going to try to put every asset on there I'm not going to put like my tv on there oftentimes or like that kind of stuff you could But I'm going to put I'm going to put the big ones on there the things that that especially the home Because it could go up in value and it could be something that I might be able to use as collateral Quite readily on a loan or something like that helping me to get some liquidity in the event that I need it So the big one would be the home, right? So I'm going to value the home now notice that the home Is something that you can't get from something like a personal capital software or anything like that because the value of the home Might you might need like an appraisal So you might actually look up an appraisal online to get what you think the current value of the home is And you could try to project it forward to think what you think the current future values of the home will be Uh in your life insurance kind of calculations and trying to think about what the what the ramifications with that for that would be on your calculation So that's going to be I'm going to say it's 200 000 for the home And then we've got two cars now the cars you may or may not They're not as important to put on there. I'm going to copy down this one because because They're probably going to go down in value, right? So you've got that asset you could use it as collateral on a loan for example If you need to but that's kind of a last resort thing typically that you that you'd have to Do but there's still probably the next biggest thing that you might kind of put on there as your assets And you might also have the loans against the cars that you could put on there as well So I'm going to say this is going to be the car once again The value of the car is going to go down over time. You could use a depreciation method to make it go down But or you could just basically look up what you think the value appraised value of the car is At any given time and make periodic adjustments based on that information for example So there these are items that you can't get just from financial statements Or just from something like a personal capital pulling from your financial institutions Whereas all this stuff you could get it pretty much automatically Okay, so then I'm going to I'm going to indent this one these three home tab alignment indent This will be the total fixed assets total fixed assets Put that in the outer column equals the su m su m Of those three There it is it. Let's double indent the total fixed assets home tab Alignment indent indent double time two times the indent Okay, so then We'll say that's the total assets total assets Assets is in the outer column. So I'm just going to sum up the outer column these three items equals the su m of these three So the current assets the ira and the car now notice from a life insurance perspective In terms of meeting someone's needs this total assets, you know, it's a significant number here But this stuff right here is not very liquid We can't really get to it to reach the current needs and we also have to kind of compare that then to The liabilities, of course when we think about our our life insurance needs So so let's do that. I'm going to put an underline here. I'm going to go to the home tab font group underline and let's put an underline here home tab font group underline Let's put a double underline here getting crazy with the underlines two underlines the double Underline Okay, so now I'm going to make a skinny g column I'm going to put my cursor on skinny c because I like to have the same skininess of the skinny columns home tab Clipboard skinny column. All the skinny columns need to just be the same That's how it works. And then I'm going to put the liabilities down here in h2 h2 liabilities Liabilities Again, we're going to have like current liabilities, which I'm going to say are the liabilities roughly that are due Within the next year and then long term liabilities. So I'm going to say current Current liabilities Liabilities If I spell it wrong, I'll do the spell check later Forgive me for the misspellings poor five or credit card So we've got the credit card obviously the big one's going to be the mortgage But I'm going to put that in the long term section even though it has a short term component to it So I'm going to say so we'll talk about that in a second. Let's pick up the credit card And so I got credit card number one numero uno numero What no, I only got one credit card and then I got the loan the car loan I'm going to say that short term too Although you might have a car loan that's longer, but usually we're going to say car loans within a year I'm going to pay it off within a year. Let's say So car loan is short term I'll put that in the current liabilities on the car loan And then the mortgage is going to be long term. Let's make this a little bit larger column h Needs to be needs to fatten up h. It's too skinny. You're too skinny Okay So there it is and this is going to be the total current liabilities total current liabilities Put that in the outer column equals the sum Of those two the three nine the seven four gives us the 11 Three let's put an underline under the seven four home tab font group and drop down underline Let's do some indentations with these three And we'll go to the home tab alignment indent and then the total liabilities We will indent again alignment and indent again So then let's take a look at the long term liabilities And I'm just going to put the mortgage down here because it's going to be lasting multiple years into the future Now note that the mortgage has a current portion to it So to really do this properly we we should break out the mortgage and say Okay, I've got the next year is current that's going to be the current portion And then I'll have the long term portion So I might get into more detail when we we would need the actual amortization table To think about that in a bit more depth But for now I'm just going to put the whole balance in the long term portion Here's I'm going to say the mortgage because most of it is long term And that's kind of a little bit of a quirk with accounting which requires adjusting entries if you were to do this in practice For example this credit card information you could pull in just from the credit card statements as well as the loan Most likely you could also use something like a personal capital that can just pull in those ending balances Same with the mortgage. It's coming from a financial institution So you can pull in that ending balance But if there are loans that are extending beyond one year Then technically you should have a current and long term portion to it which takes adjusting entries You'd have to make adjusting entries periodically to pull out the current and long term portion So I'm just going to put it generically here into the long term portion because the mortgage is Primarily long term generally. Okay, so I'm going to end it alignment and end it Put this in the outer column because there's only one thing in this category. So I'm going to put that directly into the long term Liability and I'm not going to do any subcategory. I'll just keep it out there And then I'm going to say this is the total liabilities total liabilities Outer column. I'm going to sum up these two equals the sum of the current and the long term And I'll put an underline here font group and underline Now notice that if you were to construct your financial statements this way, you might then say, okay the bottom line here I will call net assets meaning assets minus liabilities If you were to use more of an accounting and kind of calculation Or if you were to get this from something like a quick books Then it would every transaction you make would have a double entry accounting system And therefore you would have something called equity, for example And then the balancing number would be called liabilities and equity So you want to note that that those two things are kind of the same It's not like we have a different balance sheet It's not like we're doing anything different for the personal side of things Then the then the corporate side of things or the business side of things But sometimes we just use different names and it kind of depends on how you're constructing something Notice that if I'm constructing something from the end results from the end financial statements instead of building it from the financial transactions Then it's more likely this bottom number is going to be I'm going to plug it in their net assets It will be the balancing number whereas if you're using your financial transactions to create the balance sheet And then reconciling to the financial statements like the checking account Then you're more likely to use something that's called equity or something like that on this side, so What I'll do is I'm going to call it net assets net assets here Which is going to be my my assets minus my liabilities So this is the assets of the 298 5 minus the liabilities of the 161 3 That's my net assets, which is equivalent to basically equity in like If you were like in a business calculation of it doing a corporate business balance sheet and then I'm going to say liabilities and net assets or what would be equivalent to equity is going to be the sum Of these two liabilities and net assets, which is kind of just recalculating this obviously That's the case because this one I calculated as being assets minus the liabilities So I kind of cheated to do that because in practice if you were doing this Uh, if you were doing this properly for your accounting system like with a quickbooks system Then your net assets would be calculated for you as you enter the transactions But instead what we're doing it basically is building the balance sheet from the ending balance as opposed to The uh as opposed to making them from the transactions Okay, so we might get into bookkeeping later, but that's the general Ideas I'm going to go then to the font group and underline it and so there it goes And then let's put a double underline here font group drop down Let's put the double underline There and there we have it. Let's do some let's make it look a little nicer I'm going to make this blackness go all the way to the end Let's go to the font group and make that black and white I'm going to make the the header go from the balance sheet from here to j I'm going to do that not by Going to the home tab We could like burge the cells like that, but I don't like doing that because then you got like one fat cell And there it just kind of messes everything up because no other cell like does that So what I prefer to do is undo that And then right click on it and then format the cells Format the cells like so and then I'm going to go to Alignment second tab and then I want to say center it across center across that whole Thing and I'll say okay So that's a little bit deceptive too because then people click on this and they're like but wait There's nothing in that cell and I go I got to go to this cell. That's where it is That's where it's located, but it doesn't kind of mess up your ability to To like move whole columns or anything like that with the other one kind of does so I like that better Anyways, uh, I think I'm going to then select this whole thing And then make that blue and bordered home tab. Let's go to the font group Let's make it blue if you don't have this blue right there I go to the more colors and then the standard and we'll go to that blue right there. That's the one You're the one blue. You're the one And then I'm going to go to the home tab font group and borderize it And so there we have it. Let's do a let's do a review on the spell check Did I spell everything right? I totally did. It's amazing So there's going to be our balance sheet now obviously when we think about this in terms of our life insurance Then this net asset becomes kind of important because notice that in some parts of our our life span We would expect like when we have a family and we're getting a mortgage or something that we might have more basically Liabilities than assets and so so but later after we pay down the mortgage for example We would expect then our assets to increase over our liabilities as our assets increase We're basically self-insuring for life insurance to some degree because if we were to die with a significant amount of assets over the liabilities Then our our loved ones would be taken care of to some degree if we die Well, we have a lot more liabilities than assets then the loved ones are in a problem So notice that the net assets here are at the 137 200. That's the assets minus the liabilities, which is good But a significant portion of that is in the fixed assets over here Meaning the home and the car so if you were to die you can't really use the fixed assets very easily to be paying off You know current obligations that are going to be going forward And so that's one thing that we want to keep in mind also the IRA if it was a 401k We have a similar situation it could be subject to taxes like estate taxes possibly if that was applicable And you also have to assume or determine whose IRA is it which spouse is it will they have access to it At the point of death or or they're going to have to wait until some future point in order to get access To the IRA. So once you consider those items you might say, okay If I was looking at from a life insurance perspective, you might try to say can I get enough life insurance Basically to pay off maybe the liabilities or at least possibly my portion of the liabilities my my component My part of the liabilities or something like that, which would be the 161 3 for example Or you might try to start thinking about the The cash flow which might be the current assets plus The the IRA if they have access to the IRA at the point of death Minus maybe the full liabilities here as a way that you can kind of think about the your Life insurance needs or you might try to say I'm going to take my current assets And if they don't have access to the IRA, maybe it's their IRA or something like minus The 11 3 the current liabilities which is short or low because I'm not including in here the mortgage or the First year of the mortgage for example You could try to start comparing Current assets to current liabilities to see if there's any kind of liquidity problems in that first In that first kind of year there, but obviously then going forward We have to think about the income side of things So we're going to look at our income statement and then use that to kind of project forward What we think our needs are going to be for expenses In the event that we were to die to think about that and we have to want to think about the liquidity of it You know the cash flow of it as well to meet the current bills Even though we could have a significant amount of basically net assets because again a significant portion of that might be Tied up in you know fixed assets, so we'll get into the income statement next time