 Personal finance practice problem using Excel life insurance using personal financial statements part number seven Decreasing balance method 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've basically been building this from a blank sheet and prior presentations Continuing on with it now, but if you do have access three tabs down below example practice blank example in essence being the answer key Let's take a look at it now Information on the left hand side calculations on the right we put together the personal financial statements balance sheet and Income statement two methods of the income statement in essence and accrual and cash flow method And then we started to think about different calculations for the life insurance that based on that information We have method number one method number two more static type of methods where we're not really taken into consideration Say more time value of money calculations We then added the loan amortization assuming this being the mortgage So we have the mortgage amortization table We broke that down to a year-by-year calculation to help us out with possibly a more complex type of calculation where we might be thinking of a term life insurance say that we can tailor to the To the year in which we die meaning you would think that you would might need less life insurance At some point in the life as you go forward because you'd have left people dependent upon you And hopefully your net assets would be grease increasing as you as you go at some point as well So now we'll try to think about the year will break out the years up top And then we'll think about basically the cash flow needs trying to take into consideration Time value of money as we do so and that'll be our method here We'll do kind of two methods here this one And then we'll also do a similar kind of calculation But then try to think about being able to pay off the liability upfront Keeping that in mind the second tab has a pre-formatted worksheets So that we can work through the practice problem with less Excel formatting the third tab is where we're going to do the Excel formatting So I'm going to go all the way to the right. This is where our new stuff is happening And so last time we made these we made these amortization tables ending in column AP So now I want an aq skinny column to start the new thing. So I'm going to make this whatever this cell is I'm going to make that skinny cell and copy it home page Format painter paint brush yet and the paintbrush aq row column column that is and then we're going to say this is going to be I'll call it the header is going to be called life insurance needed declining One or decreasing or declining. I'll call it decreasing because I think I'm so I'm thinking term life insurance typically that might have a Decreasing as time passes a decreasing balance or payout, okay? So let's make that a little bit wider Let's bring it out to here and then I'm going to put the years up top So I'm going to say this is going to be zero one two and I'm going to bring it out to ten years And I'm assuming kind of this ten-year calculation is Is similar to that could be until we hit retirement that could be until the youngest child hits 18 That could be until our spouse hits retirement for example, and so that's going to be that calculation So I'm going to bring this on over to ten Or you might just use an arbitrary seven to ten, which is kind of a heuristic Type of number so hold on a second Something went horribly horribly wrong selecting these going out to ten ten Why was that so difficult and then I'll center this we're going to go alignment center and Let's make that black and white to black and white as the header black and white. So there we go So that looks good. Let's make this black and white Black and white. So there's our starting point. Okay, so first we want to think about the yearly cash flows We got to break out our cash flows in a similar way We did before on the kind of the behavior of the expenses So we got to think about what's going to be the cash flow needs if someone's dependent on our income and then try To think about that with regards to time value of money and then those one-time things like the emergency fund might act You know a little bit differently Because it's a one-time cost instead of a year by year cost and then those goal-oriented things like college and possibly saving for Retirement are going to be treated a little bit differently, right? So we're going to break those things out in their own Categories so first we'll talk about the yearly cash flow needs So if we were to die then how much the cash flow needs would be would be needed by the people that are dependent upon us So multiple methods we can do on this we could look at our income statement We could look at our wages and start with our wages and use some kind of fraction of our yearly wages based on what we're currently earning Or we could use a needs approach and try to look at the expenses instead so we talked about this in a prior presentation, so I won't go over it in too much detail, but Here we're going to do more of an expense best. So here's our income statement So we could basically say if this is the person that's going to die We could just base it on the sixty thousand income or some fraction of it or we might take the expenses now on the expenses side of things We could try to do two methods. We could say hey look I'm going to keep the liabilities on the book. So if I look at my balance sheet We've got the major liability being the mortgage I could think about first trying to pay off that mortgage and then what would the expenses be? Or I can try to say okay I'm going to keep the mortgage on on the books and just continue imagine It's going to be paid off in a cash flow method across the term of the mortgage meaning that the payments of the mortgage would be included in my Expenses so I would say this is more of a cash flow method in that we've got the loan Cash flow obligations that we've committed to in place as well and that's where we get then this number So I'm going to try to break these out a little bit separate So I'm going to basically take this number right here that doesn't include the mortgage And then I'm going to put the mortgage into its own line item in another in another cell So what I'm going to do is I'm going to go back on over here and say all right my cash flow needs I according to my budget I'm going to be picking up then the expenses expenses not including Mortgage and the mortgage really isn't kind of an expense on a cruel basis Because it's a decrease of the of the principal, but it's a cash flow component So if we're not paying it off, we know that there's going to be a cash flow need So I'm going to go over here to my income statement. We're going to be picking up that 21 840 now We're also going to say that there's that nanny costs nanny costs meaning we're going to assume That if I if I die if we as the wage earner dies that the other spouse might have to hire a Nanny or something to try to to try to compensate for the care at least to some degree So I'm going to say equals and we estimated that all the way on the left to be 3600 3600 in our data So I'm going to pick that up and then we've got the mortgage So I'm going to say mortgage and the reason I'm going to break this out is because this is the key kind of Difference that we might do in another calculation to determine whether or not we can pay off the liabilities up front And then and then not have the mortgage component versus us imagining We're just going to be paying off the mortgage kind of as we go as per the mortgage arrangement So I'm going to say the mortgage is going to be equal to and we can pick this up from multiple areas We might pick it up. We just did a mortgage calculation here. So it's the yearly It's the yearly payment on the mortgage for my year by year calculation for The table and so there we have it now all of these are based on Year zero at this point in time when I made my when I did my finances or my financial statements, right? So going forward you would expect that they would go up at least with inflation So you would think they would increase with inflation. So let's go ahead and add these up. This is going to be let's call this the yearly cash flow needs I should have just copied the other one, but there it is and I'm going to sum this up equals to some So in today's dollars, you would think on a year by year They would need the 38 336 and then of course we could just simply multiply that by by 10 for that 10 year component But we also have kind of like the time value of money factor that we might want to add into this in some way shape or form Which of course will complicate things a bit. Let's go ahead and add a couple rows So I can put my time value of money right next to it. So I don't have to hide a bunch of cells I'm going to put my cursor on a r and scroll over to like a t a r r to at r to at right click And insert let's insert some stuff. I'm going to make these a bit wider These need to be wider actually at could have stayed the same So let's do it right there. I'll make at skinny again ads like what do you want for me? I was wide and now I'm skinny I'm going to go now. You're going to make me skinny again. I'll make it skinny with the paintbrusher And so let's take our Let's take our rates. So I'm going to say this equals I'm going to pull my rates all the way from the left to the left. So I don't have to keep on hiding cells or anything Down here. We said that inflation is that 2.5 percent. So let's pick that up. That's an important number I think I could just copy or take this cell And autofill it to the right and so I can pick up that number That's not the number, but I can just percentify it home tab Number percentify and then I got to add a decimal because it's 2.5 Okay, then I'm going to do another one like the rate of what I'm going to assume our earnings rate is Which I think we said was five percent if I was to invest on average I'm going to say we can get a rate of return Five percent five percent rate of return. Let's go ahead and take that one I'm going to autofill it to the right And so then I need to percentify if you want to recognize you've got to percentify Number percentify. Okay. Let's make this one blue and bordered home tab font group Bordered and blue drop down if you don't have that blue more colors standard blue. There you go There it is. Okay so then We've got we've got this this as our yearly cash flow I'm going to copy that across so I could try to make all of these like f4 on them Another way you could do it. I commonly do is just say equals the prior number, right? And if I copy that across it'll always equal the number in front of it So if I change this first number it'll change everything I could copy that down I'm not going to copy it to the total because I want to total it up with the total and then I'll select these And I'll copy those to the right with the autofill bring it on out to the right So we got the same numbers on on across And then I'll bring the total on across total And put my cursor on the autofill handle bring that across And then let's put an underline on this Column or row. That's a row rows columns Font group underline. So there we have it. So that's going across and so then I'm going to say, all right. So now let's think about the future value future Value so we're going to say, yeah, but if there's two percent inflation Let's do some indenting first. Let's don't get ahead of yourself. But I'm so slow. I hate waiting for myself I wish I was faster. I'm going to go to the home tab Uh alignment indent and then let's double indent this one home tab Alignment indent, okay, so I'm not ahead of myself now So then we're going to go to the future calculation. So I'm going to say like in period zero That's fine. But if I'm going a year out, there's going to be inflation So if my current costs are at the 38 336 then you would think in the future. I would need a 2.5 increase. We're going to say I'm going to make this sell a little skinnier to get to to get to the actual cash in future value terms That would be needed So we're going to do a future value calculation. So that would look something like this I'm going to say negative Future value negative to flip the sign, which probably isn't the most proper way to do it But the easiest way and then brackets We're going to pick up the rate, which is going to be that 2.5 I want that to not move when I move across because it's outside of my data range Therefore f4 on the keyboard dollar sign before the as and dollar sign before the two You only need a mixed reference, but an absolute one works comma number of periods. It gets a little bit Well, no, it doesn't we're just going to say it's going to be one year out here comma Now we're not going to use the payment because that represents an annuity What we want is the present value and we're just going to take that one number And and future value we want the so we're going to take the present value in order to calculate the future value So this is going to be the present value And so we're going to say okay brackets and okay, so then we would need 39 284 In year two to have the equivalent of the 38336 or this 38336 in year one Right and I could do that all the way across here. So if I so is that everything set up I can then put my cursor on the fill handle and drag that to the right So there we have it. So now we're going to say in year 10 I would need 49 73 you would think in order to buy the same amount of stuff if there was 2.5 percent inflation As the 38336 if we bought it and in the first year Okay, so then Once we have that we can think about a couple different ways that we might try to try to figure how much how much Insurance we would need one. We could say okay. What if I took the lump sum of the future cash flows So I might just say That if I was at period zero Then everything from period one on out the 10 years if I was to have it in one lump sum Is one way I can calculate it. So I could say I would need the sum of all of this stuff To the end and enter That year and then in this year. I would only need from year two on out for example Uh, but so I can copy that over I can I can double click on this in that ending number. That's f Uh a or b f 7 I don't want that to move and I want this beginning number to move So that when I go to the next cell it'll start here and copy over the start here and copy over So I'm going to say this the second one f for absolute reference You only need a mixed reference but an absolute works And then I'll copy that with a fill handle To the right And you can see that you get you get this kind of declining balance So you can say well as I get closer to that destination Then of course I wouldn't need As much of a lump sum money at the point that I died if I died four years in I should have left life insurance than you would think at the first the first year So that's one way you can do it Uh, the other way you might do it is you could say well, I'm gonna I'm going to calculate one lump sum lump sum based on year of death year of death death cash flow So I might say okay, I'm just gonna I'm just gonna take whenever I died Whatever the cash flow is at that point in time Hopefully I give enough of a lump sum upfront so they might be able to invest it And at least get an inflation return So whenever I die I'm going to try to start with that as the baseline and then just multiply it times How many years I have left So until I get to that 10 years out So I'm going to say this will be equal to at period zero this 38,000 I could say just times 10 because I would need 10 years of cash flow But I'm going to do it this way so I can copy it across. I'm going to take this 10 over here minus The the zero so that'll be 10, but it'll copy it across now I need I need to put brackets because of order of operations I wanted to do the subtraction before multiplication And there we have it So there's the 10 years you get a little bit less of a number here because you're not you're not trying to increase it for the increase in In the future value of the flows And if I double click on it that second number that 10 over here I don't want it to move when I copy it to the right So when I copy it to the right I want this number to move to the right but not the 10 So it'll it'll be 10 minus one for example So I'm going to double click on this the second this 10 which was in bf One I'm going to say f4 on the keyboard making it absolute and enter Put my cursor back on it fill handle drag fill handle to the right So there we have it. So that's another method we can use notice it slightly less Then the prior one here because we're just taking the point of death if I died four years away I'm going to take whatever I think the future value would be at that point And then and then try to calculate whatever remaining years are left at that point in time So that's another way that we can that we can figure this So then we could do it we could think about it this way too. I could say insurance Needed for cash flow. I might say okay Well, maybe that's too high for my insurance once I add everything else to it So I might try to say well if they're going to get this lump sum amount Then maybe that obviously they could invest that So I would like to say how much How much they would need up front to get a stream of payments and annuity payments for as many years are left 10 years at least to start out of that amount And so we could then say okay, so we could say in order to do that. I'm going to calculate like the real rate real real rate Which is going to be the 5% that we think that they can get if they were to invest that that's kind of Be kind of a fairly reasonable count, you know number of an of return Minus the inflation of the 2.5 Which is going to eat into the returns because they're going to have the 2.5 inflation So this will be equal to 5% minus the 2.5. Let's make that a percent of fine it and add some decimals Make it just need one decimal and we'll make so that's going to be 2.5 again I probably should have used a different inflation number so we don't have but whatever we're going to 2.5 Don't get confused about the two 2.5s. So then we're going to say here then that I'm going to try to say, okay at this point in time what then Would they need for a lump sum in order to get a stream of annuities of 38 336 for 10 years Uh With a rate the rate of return basically at the 2.5, which is the the return The rate of return minus the inflation that's going to kind of eat into that So we're going to have to do a future a a Present value calculation, which is a present value Of cash flows we're imagining into the future to put it into current terms So i'm going to say negative a present value Shift nine the rate i'm going to be picking up at that 2 Percent I want that to be able to copy to the right So i'm going to select f4 on the keyboard making it absolute It's because I don't want this 2 per 2.5 percent to move when I copy it to the right comma The number of periods it gets a little tricky again. It's going to be 10 But what I want to do is take this 10 over here Minus we're going to take then the zero which comes out to 10 But I want to be able to copy it across when I copy it across I don't want the 10 to move. I do want this zero to move So this one in bf1 I need to make absolute f4 on the keyboard so that I can copy that across and next time it'll be 10 minus 1 Which is 9 10 minus 2 which is 8 and so on and so forth comma The payment this is an annuity because we're looking at a stream of payments Starting at whatever the the current value is at the point of death Where is what our calculation will be? So i'm going to be picking up that 38 336 and enter so that gives us then the 3355 1 7 So you can see of course it's going to be a little bit less than these items up top because We're assuming if they get that lump sum they're going to be able to get kind of a return on it So either one of these are are numbers that you could basically use for your life insurance kind of calculation But it kind of depends on on how much life insurance you need for example And how much you can afford for example and and whatnot So they are all kind of reasonable Approaches you could use i'm going to take this last one. I'm going to put my cursor on the fill handle drag it to the right So so there we have it now you got to be careful on the timing of it Because we're kind of thinking at the end of the year at the end of year 10 Then it would be down to zero at that point in time because that would be the point of time that the student reached College age or is out of the house or something like that And then we can tack on to that any other any other kind of things that we would need to include on top of it So that's going to be our starting point again We could use this number or this number, but we're going to be basing it on this number So so that's going to be our first one for the cash flow needs per year Then we're going to be thinking about those kind of one-time things. So let's think about that emergency fund So we have the Emergency fund and we said We might use a heuristic like six months Expenses Expenses for example So we might say okay. Well if I've got my yearly Costs up top. I might just take that and divide it by two So i'm going to say this is going to be equal to the 38 336 divided by two so that now we're going to say if there was Obviously an emergency that wouldn't be something that happened on a year by year basis But we might have that one time amount That's why it's in a separate section over here that we're going to basically account for and then whenever I die I'm going to try to base it on On uh, actually, let's base it on the future value. I'm going to say this is equal to This number the future value divided by two so that when I whenever we die will base it on whatever we think the current Kind of expenses would be in six months of it So i'm going to copy that across copying that across Next we have the college cost. So let's do a little calculation over here for the college college And we're going to say let's put some Borders around this let's mix this black and white. So this is a future goal oriented cost So i'm going to say i'm going to say current. Let's say the current Cost is going to be equal to i'm going to go all the way to the left and say okay What did we say in our data? The current cost was We said it was 35 35 000 and 10 10 years the one kid that we're going to allow going to college Is going to be at college age. So it's going to be years uh years and 10 years Years to college to start We're going to say it's 10 years. I'll just put 10 i'm going to hard code it So i'll put font group underline So then we're going to say okay. Well, so what's the future value? So the i'll just call it future value or what will college cost and future value dollars when they actually start college We can calculate that with a future value calculation negative future value Brackets and the rate we're going to say inflation is that 2.5 percent So we're going to assume it goes up at 2.5 percent. Actually, this is the 2.5 i want And then comma number of periods for 10 years Comma it's not a payment because we're not talking about an annuity So there's two commas and then we want the present value, which is that 35 000 and enter So we're going to say at the point in time they start college. It's going to be the 44 803 So therefore if I think about this on a year by year basis note that they could earn We're going to assume that if if I was to die Then they would earn 5 percent we're going to assume so they could put the college funds away Let's get 5 percent to reach So how much would they have to save to reach the 44 803 for example? So let's say we're going to say okay the college Fund a goal oriented item first. We got to set the goal in terms of what the future value dollars we need are And then once we have that we could say, okay How much would I have if I died? You know at year zero How much how much would I have to need in order for them to invest it at a 5 percent return and reach that 44 803? So for that we're going to do a a Present value calculation So we'll say okay. Let's go here and say negative present value shift nine The rate is now going to be the five percent because that's what we think they can earn if they were to invest on average I'm going to say f4 on the keyboard dollar sign before the as and the three comma Number of periods. I'm going to do the tricky thing again It's going to be 10 because they got 10 years to invest, but I'm going to take that 10 I don't want it to move when I copy it. So I'm going to say f4 on the keyboard on that one minus The zero 10 minus zero that 10 minus zero will be not will be 10 And then comma and then the payment that we're going to have there's no payment because it's not an annuity comma The future value that we want we want to reach This 44 803 and future value dollars. So we're going to say enter So there's the 27 505 27 505 that they should be able to invest And get a return on average of the five percent to reach the 44 803 If I copy that across I'm going to put my cursor on av14 copy it across Then you could see Hold on a second. Something went horribly horribly wrong And if I double click on this one you could see what happened. I could say what happened. That looks right That looks right this number moved to the right because it's outside of my data set So I'm going to make that absolute reference. So I'm going to delete what I did here Delete that I'm going to say okay this number right there and sell as 9 double click on it as 9 needs Absolutization or at least a mixed reference, but an absolute reference is easiest And so I'm just going to say let's try it again copy it across So there we have it so we can see as we get closer To the point where the money is needed Then you would expect that we would need more money at the point of death because there'd be less time for it To be able to grow to the point of the college time so that they can then use it for that purpose So the next thing we have we have a similar kind of thing if we had a retirement. So if we wanted to help our spouse retirement funds So if we wanted to help our spouse save for retirement, let's imagine we had in retirement I'm going to pull this from the data set. I'm going to pull this equals to the left I'm going to go down to the retirement I think I put retirement that we wanted to help out with retirement and have a goal Of we're just said 500,000 so we're going to help out with the 500,000 Of retirement that we would like To do so i'm going to say this equals i'm going to go to the left And scroll down it and again you could you might have different ideas on what you know what you want to do with regards to it retirement for a spouse for example, but It's another goal oriented type of thing is the bottom line So if we're kind if we're trying to say at the point of retirement, which we're just going to assume is after basically the 10 years here Then we want to have Basically 500,000 we're going to say is built up and again This would be another thing kind of like the college where you'd say, okay How much would I need 10 years into the future? What would be the future value that I would need for retirement? You can get into that calculation, which I won't get into now But similarly once you figure that you might say, okay Then how much would I need to be put in place right now if I got a 5% return? To reach the 500,000 at the point of retirement at the end in this case of 10 years we're saying So we'll do a similar calculation. This is going to be negative present value shift nine the rate is going to be this 5% And then I'm going to say I'm going to say f4 on the keyboard because I want to copy that across Comma number of periods. I'm going to say once again It's going to be the 10 over here First one's 10 I'm going to say f4 because I don't want that to move to the right when I go to the right And then I've got minus zero. I do want that one to move So I'm not going to put f4 comma and then we've got the payment which is not Relevant here so two commas because this is not an annuity the future value then being The 500,000 and enter so we'd say okay if I died now I can have 306 957 if they were able to earn 5% on it to get to the goal of 500,000 So that's how you might do some of those goal oriented items I'm going to copy this to the right and see if I got it correct this time So there there we go But no I made the same mistake. So if I double click on this I I didn't Put an absolute reference on this one again So let's do it again. I did that on purpose. I'm doing that on purpose So I'm going to double click on this one that one in as 11 the last piece I'm going to make f4 dollar sign before the each component so that we can then Copy it across putting our cursor on it and then copying it across so we can see as we get closer to That retirement at our year of death We would need more in there at that point because it wouldn't have as much time to grow Let's make these blue and bordered. I'm going to go up top font Brackets make it blue and bordered. Okay, so then we've got the funeral expenses. Let's say funeral expenses Funeral expenses. That's a one-time cost. So I'm just going to say whatever it is I'm going to say this equals Scroll into the to the left and period zero. I said it was 8500 That might of course go up in accordance with inflation So I might try to taper that up. I might say, okay This needs to be in year two then it's going to be it's going to be negative future value shift nine The rate is going to be 2.5 f4 on the keyboard because I don't want that to move when I copy it over a comma Number of periods. I'm just going to say is one year out this time One year out and then comma. It's not a payment because it's it's not an annuity and then comma And I'm just going to pick that that's 8500 now the 8500 I'm going to say f4 on the keyboard because I want to start from that 8500 each time And I'm going to say f4 and enter And then I'll copy that across so I'm going to copy that across and you can see it goes up As we go over now notice I made that one maybe A little bit more difficult than we needed to have because we could also do it this way You could have started with the 8500 and then said I want to say 8500 times The let's say let's say times brackets one plus the the 2.5 percent and say f4 on the keyboard Close up the brackets and you get to that same eight eight thousand seven thirteen And then I could copy it across and it would take the prior balance plus Another increase of of the two percent. So that's another way it might possibly a more easy Way to see it than using that future value kind of calculation But those are two methods you can do for it Okay, so then the life. So let's say then we have The subtotal is going to be the life insurance before assets Is going to be equal to I'm going to sum this up Each year I'm going to start here Because all this stuff up top. We said we're going to we're going to use this calculation So I'm going to sum these up And so that's going to be our cash flow needs on a year by year basis I'm going to copy that to the right And then I'm going to take into consideration my liquid assets. I'm going to call these the liquid Liquid assets. So that's how much is needed. We already have if I look at my balance sheet liquid assets I'm going to go to the left and say the liquid assets are going to include the the Current assets that we can use to pay off the debt and I'm going to include the IRA Although again the IRA got to be careful with the retirement accounts that are below the umbrella Are there tax consequences like a death tax a state tax or Are they you're the person who died's retirement plan or the spouses for example? And in in terms of those calculations, although even if it was restricted for retirement, you know, you would have that for the retirement Portion of our calculations here, but in any case there it is and so I'm going to keep that as the same Let's make that a negative. I'm going to put negative and then brackets around it And I'll just say for the second one equals the same and I'm just going to keep that as it is across Here so there we have it And then I'm going to say that that's going to give us our life insurance life insurance needed And so this will equal the sum of those two subtracting them this minus that And so that gives us our 634 147 copying that across There we have it. Let's put an underline here I'm going to put an underline here font group underline and we'll put an underline here font group underline, let's put a double underline here We'll say font group double underline Okay, so now we've got kind of a decline. That's one method and and it got obviously you can piece apart any of these Tools that you would want to use but now you might be able to say, okay Now I got a life insurance maybe That I can say has a declining balance Over time which might make the life insurance possibly more affordable if you can come to a reasonable Assumption based on that and then choose like a term life insurance Which might be the best or easiest kind of thing to do for just buying life insurance calculation So let's make this blue and border And and and start there. So those are just some tools font group That if you were really to kind of dig into it in depth You can apply a whole lot of of course tools because we're trying to project into the future What would be needed and so on so that's one fit one method we can use next time We'll alter this just a little bit and we'll try to think well What if I started with the starting point of wanting to pay off the mortgage? At first right to take care of the liabilities and we could try we could try doing that kind of calculation Let's do a spell check real quick spell check future emergency Insurance okay, and so there we have it