 Personal finance practice problem using Excel. Life insurance using personal financial statements part number 8. Decreasing balance method number 2. 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 it from a blank sheet and prior presentations continuing on with it now, but if you do have access, three tabs down below. Example practice and blank, example tab in essence being an answer key. Let's take a look at it now. We have the information on the left hand side, calculations on the right hand side. We started out putting together the personal financial statements, balance sheet, two formats of the income statement, accrual and cash basis in essence. Then we started our life insurance calculations using some more kind of simplified types of methods. Then we did our mortgage or our amortization table for the home loan, breaking it out on a year by year basis to help us out with our more complex calculations for life insurance. We then tried a declining life insurance calculation, adding time value of money concepts and now we're going to do a second time value of money calculation this time taking into consideration the loan balance and trying to think about well what if we paid off say the mortgage upfront as kind of our starting baseline at the point of death given the life insurance needs. Let's go to the practice tab where we have some pre-formatted cells on the right hand side so you could work through the practice problem with less excel formatting on the blank tab we're going to do the excel formatting. So I'm going to go all the way to the right here so we're going to add some new data on the right so I'm going to we left off last time way over here on bf it's not the bff but just the bf so then I'm going to make a skinny column for bg so I'm going to take the last skinny column which is way over here at at I think it is even though I can't see it because it's too skinny. I'm going to go to the home tab and then go to the paint brush and select that paint brush and we'll make this one a skinny bg. Let's pull over our data so I can pull over the same data that's right next to here I'm going to say this is the inflation and I'll just copy this information down I got inflation let's copy this to the right and I'm going to copy it down so then I'm going to delete this last bit and I can't see anything here because I need to percentify if I want to recognize so I'm going to go to the home tab our number group and percentify let's add a decimal so there we have it so we've got our inflation 2.5 rate of return we're going to assume five percent 2.5 on the real rate making this a little bit larger so there we have that now I'm going to go through this a little bit faster because a lot of it will be similar to what we did before but we're going to have another or a different type of assumption with regards to that liability for the mortgage okay so I'm going to take another skinny column I'm going to take that skinny column and go to the home tab and paint brush that paint brush the bj column so we have a skinny there I'm going to call in bk one this is going to be called the life insurance needed it's going to I'm going to call it a decreasing balance for example method number two I'll just put a two I'm going to make this a little bit wider and then I'm going to put the years up top these are going to be the years that we're going to expect that we we're going to need the flow for possibly based on our retirement years possibly a generic seven to ten which would be a heuristic type of number possibly based on how long the youngest child will reach get out of the house age you know or something like that so I'm going to pull this over to ten so then we're going to center this home tab alignment center and then let's make some black and white on the headers so there we have that we'll make this one black and white two black and white on the header okay so we're going to do our same yearly expenses we got to break down our expenses by kind of behavior of the expenses so our yearly cash flow if we were going to die someone's dependent upon us how much would they need for the yearly cash flow needs and so we're going to say we're going to start with the expenses expenses expenses not include including the mortgage and the basic bottom line assumption here that's going to be a little bit different if I go way back on over to my balance sheet we're going to go back on over to the balance sheet and so we're going to say a couple of things we could we can base our calculations on we might say hey look I'm going to look at it from future cash flows keeping for example the mortgage on the books as we did last time and assume we're just going to continue to pay off the mortgage as per the arrangement with the mortgage and think about the future cash flows that would be necessary to do that or you might start off by saying hey look I'm going to try to get enough life insurance to pay off the mortgage and possibly any other liabilities but the mortgage would be the big one and therefore they could pay that off at the point of death which means that my cash flow needs after that point will be much lower assuming that we pay off the the big liability upfront so that's going to be the the thought process that we have here so if I look at my expenses then we had the two kind of methods this method over here is is not including these are the life insurance that's not the income statement income statements we have this one over here not including the cash flows for like liabilities this one included the car and the and the and the credit card but also the mortgage so i'm just going to pick this one over here say we're not going to include the liabilities i'm just looking at the expenses and i'm going to pay off the big liability at the point of death we're going to assume and therefore the expenses or the cash flows they would need at a year by year basis after death would only be the 21840 not including at least the mortgaging so i'm going to go back on over and say all right so that means that way over here we're going to say that the expenses are going to be equal to i'm going to go over to that income statement and pick up the needed expenses on a needs basis not including cash flow for the mortgage so there we have it now i'm going to copy that across i'm going to do it this way this time i'm going to say equals the one before it enter and then i'm going to put my cursor on the one i'm going to just copy that across to 10 and then we'll consider time value of money related to it shortly and then i'm going to also have the nanny costs that we're going to we're going to add to it we're going to say if i die let's tack on and say there's going to be some nanny costs that might be necessary to take care of the kids i given the fact that we're short a an adult in the room not that not that i was ever an adult in the room but we're going to then go ahead and say we're going to take that one and copy that one across and so there we've got that wait a sec that's the wrong number hold on a second equals that's that's the funeral cost we want the nanny stuff so there it is i'm going to say and so now it picks it up all the way across so that looks good okay then what we'll do is i'm just going to add those up as my yearly cash flow needs i'll just sum that up equals the sum of those two and i'm going to copy that across copy it across copy that roger out roger out and then i'm going to put an underline under the three thousand six underline home tab font group not the double but just the single underline and because the double we reserve for the bottom line not just the standard underline and then i'm going to i'm going to indent home tab alignment indent and double indent here alignment double indent so then we might have our our calculations for say the future value so now i'm going to say the future value in other words if i died like one year out you would expect that the needs in order to meet the same obligations would now be higher due to inflation due to the purchasing power of the dollar having gone down and therefore we're going to say inflation's at the 2.5 percent and note it might be more proper to kind of stagger this to say well at the end of of year zero then i would need the 25 000 increased a year but i'm going to assume this 25 000 a year later at the 2.5 increase so in other words in year zero we would have the same amount if i go to the year one let's start at year one that 25 440 would be larger in future value terms due to inflation for the same basket of goods and whatnot so we're going to say future this is going to be future value shift shift nine the rate is going to be this 2.5 for inflation f4 on the keyboard dollar sign before the bi and two so that it doesn't move when i go to the right because it's outside of my data set data table i'm working in comma number of periods i'm going to pick up up top so that can move to the right as i move it to the right and then comma it's not a payment because this is not an annuity therefore comma again we want the present value which is that 25 440 and enter so there we have it you can see it increases of course i'm going to copy it to the left and we'll just get the same number that's why i didn't do it over there because it's zero periods out and then i'll copy it to the right and we can have it going out to the right and then that's how much would be needed at the end so then i'm going to go back on over and say now we've got the we're going to call this lump sum for yearly cash flow so a couple ways we could think about this we could say okay well and now i'm considering like the end of of year zero how much would i need at that point in time i'm going to sum up the price the next time frame up to to 10 periods out so in other words how much lump sum what i need one way we can think about is to say equals the sum of years one out to year 10 and that's what our needs would be there and i could say enter and so if i had one lump sum to cover that cash flow based on that increase that's one way we can think about it although of course if you got it in a lump sum we might be able to invest some of it to get earnings on it so we'll take that into consideration shortly i'm going to double click on this and i'm going to put that second one meaning that item that's out here i don't want the endpoint to move when i copy it to the right but i do want the beginning point to move therefore that second number f4 on the keyboard dollar sign before the letter and the number and enter so if i copy it to the right putting my cursor on the fill handle to do so copying it on over to the right so there we have it notice this last one gets a little messed up because i locked this 10 cell so it's actually trying to go to that one and then kind of backwards so it kind of messes it up so that one should really be zero because we're saying at the end of year 10 so you could you could compensate for that better if you so choose okay so then we're gonna say all right another way we might look at it we might say let's take a look at the lump based on year of death so another way we could say it is well i'm just going to take this current amount here times the number of years that are remaining left so we might then say okay i'm going to start with whatever the future value is at the point of death and try to just simply multiply it times the years that are remaining so i could say that this would be equal to this amount times i'm going to get a little bit tricky on on the 10 years instead of putting just 10 i'm going to put brackets and i'm going to put the 10 over here because i want to copy it across f4 on the keyboard to make that absolute an absolute reference and then i'm going to subtract the zero up top so this one will move to the right but the 10 will not so the next one will be nine for example enter and then i can copy that to the right and so now i could say okay well whatever year i die at if i die six years in i want to take whatever that future value is at that point in time and multiply it times the number of years that are remaining that's one way we could take a look at it and then the other way we could look at is we could say okay well let's try to say insurance uh needed for cash flow let's think that instead of doing that we're going to try to say i'd like to have an equivalent amount of purchasing power based on based on you know this number into the future and have an annuity that can basically pay it out in other words if they get a lump sum payment then they should be able to invest it hopefully getting a return of the five percent so if i assume there's a return of five percent and inflation is going to eat into that return of the two point five the net or real rate is going to be the two point five so how much would they need in a lump sum in order to get you know an annuity of payments that would be equivalent to the this amount so that they can make so that they can make their payment so that's going to be a more uh a lower kind of number that we're going to get to do that a more affordable possibly number for the life insurance so we're going to say okay let's try that calculation that's going to be we're going to take the present value of an annuity and i'm going to base it on this future value calculation whenever the point of death is so i'm going to say okay negative present value brackets we're going to take the rate which is going to be the real rate which is going to be the rate of return on the investment they can have minus the inflation f4 on the keyboard to make it absolute because i want to be able to copy it to the right without that cell moving comma number of periods i'm going to say is going to be in this case 10 i'm going to take the 10 over here f4 on the keyboard minus doing that tricky minus thing again minus the zero so that when i copy it to the right it'll be 10 minus one and 10 minus two and so on and then comma and then the payments this is an annuity this time i'm going to say it's going to start at whatever that future value is at the point in time of death in this case the 25 440 and enter let's copy that to the right so i'm going to copy that to the right auto filling it to the right and so there we have it so now we're saying okay if i died like five years in five years later then the current time frame the future value would be the 28783 and i'm trying to get an annuity based on a real rate of return the 2.5 which is the rate of return they can invest that lump sum in minus the inflation that would kick into it so that they can have you know the equal amount in terms of purchasing power basically going forward from that point so that's going to be a little bit less than just me just trying to figure out the lump sum you know amount in this format so that's the number that we're going to use for that calculation and then we've got the next thing we're going to be picking up is the mortgage let's say the mortgage liability is going to be a one lump sum calculation i'm going to say i just want to pay off the mortgage up front so i'm going to say whatever the mortgage is and this is where our annuity table comes in notice we made our annuity table over here and we made it based on not months but years so that we can do this kind of calculation i can say well in year zero it's at the 150,000 but and i'm just going to pick up the liability saying i don't want to just pay off the payments i want to just wipe out the mortgage and this is some life insurance you might tie directly to the mortgage in this way so that it will decline it will go down over time it'll decrease as the mortgage gets paid off if you're still living right so that would mean it'd be a cheaper life insurance so i'm going to say in year two i'm going to say it's going to get a bit tedious and year two i'm going to scroll over here and say that we've got the 145,995 tab and year three i'm going to scroll over and say that we have in year three the 137,230 and year four scrolling over we're going to say that we had 132,437 and then in your wait a sec that was not year four did i get messed up i'm going to say and you started over in year two i said that it was going to be the 141,744 and then in year three it's going to be then the 137,230 and so on so i won't let make you watch this but i'll continue on that process okay so now i have it here so we've we've added those amounts down notice note from the future here we've got this a little bit staggered incorrectly and that we have a zero and then 150 here and so we're going to move that to the right so that we have period zero being the 150 and then starting at year one and so on we'll correct that at the end of the presentation so it'll look like this and we'll do that when we get down to here summing it up so you could put the proper number here if you so choose it won't have any impact on anything else until we sum everything up to line these numbers up you might use this trace tool which i like to put down here you could find it in the formulas tab and use your trace tool and tab across to see where that information is coming from useful tool to know that the year that i based this on the 10 years might be until i'm in retirement or the spouse is in retirement or the kids out of the house or something like that might not tie into the same amount of years left for the mortgage so you could kind of adjust this for the mortgage which we're saying currently has 20 years at this case that is left over you could go out further you know on 20 years for example to cover these differences and the needs that might be there with regards to the needs for the 10 years for the child or to the retirement or something like that and then the mortgage if you so choose but i'm just going to stop it there here and continue on that's going to be the major difference that we had from the prior calculation where we kind of just assumed in the prior calculation that we're just going to continue to make the payments okay so then we're going to say that we got the emergency fund emergency fund which we said is generally going to say six months six months expenses so i have my yearly needs up top so this is a one time need now so i'm going to take that yearly need that we futureize with the future value and divide it by two and that's going to give us the 12 720 i'm going to copy this across so we don't know when that's going to happen but we're going to say there might be this one time event that we need the emergency funds to kick in so that's why it's a little bit different of a calculation than this yearly cost up top or the mortgage then we've got those goal-based items which is the college for example the college and so with the college just like we did before let's just redo it real quick we're going to say college and we're going to just we're just going to calculate it for that one special kid that last youngest kid that's going to go to college so we're going to say i'm not sure if he's lucky or unlucky given the state of the colleges these days but we're going to say the current cost of college this is how you do these goal-oriented ones similar for the retirement and we're going to say if it costs today 35 000 then the then and it takes years we'll just say equals equals the years to start it's 10 years before that kid it's going to be exposed to the great experience of college we're going to say then that means that the future value is going to be what's going to be the future value that way they will need when they start college this is what we'd have to do we've got to get the future oriented goal and then think about how much we'd have to put away until and under savings to get to that goal negative future value brackets the rate is going to be we're going to say inflation is the 2.5 comma number of periods 10 years comma it's not a payment because we're not talking about an annuity so two commas present values the 35 000 and enter so we're going to say that 44 803 is what we need in future value terms but if we die they can take whatever lump sum we have hopefully invest it at 5 percent and so how much you know what would we need in order for it to grow to get to that 44 803 so we can expose to our child to the wonders of the current college uh situation so now we're going to say so how to do that so we're going to say let's say that we need then let's say present value i'm going to say negative present value shift 9 the rate on the growth now it's going to grow at 5 percent so it's going to grow at 5 percent and we're going to say and we're going to say f4 so i can copy that to the right comma number of periods this is our tricky number of periods currently it's going to be 10 but i'm going to say f4 in the keyboard to make it absolute and then i'm going to say minus the zero which is still going to be 10 and then we're going to say comma and the payment there's no payment because this is not an annuity comma future value needs to be this 44 803 that 44 803 is outside of our data therefore i need to make it absolute f4 in the keyboard dollar sign before the bi and nine and enter and then i could say okay let's copy that across so you can see this one increases as we get closer to the goal so if i die at at 10 years later i would need the full amount but if i die earlier than that you would think whatever money they get they could invest in order to reach that that goal would be the idea the same would be for retirement so if i had a retirement goal retire retirement and again you'd have to figure out you know what's the retirement goal do a similar calculation on how much you want at retirement and we're just going to say it's 500 000 just to just to give another example of another kind of goal oriented item it's going to say 500 000 as our retirement goal and so we would do the same thing then similar calculation you might do a similar calculation if you're thinking about an elderly parent that might need might need help in later years you could do a similar kind of calculation a goal oriented type of calculation so i'm going to do this again negative present value shift nine the rate is going to be at the two at the five percent that we could hopefully get a return on f4 on the keyboard making it absolute comma number of periods is going to be the 10 over here f4 in the keyboard because i don't want that endpoint to move minus the zero the zero that one i do want to move therefore no f4 comma and then we got no payment because this is not an annuity two commas to get to the future value and that then is going to be the 500 000 i don't want that to move to the right when i copy it to right therefore absolute reference f4 in the keyboard dollar sign before the bi and 11 so let's copy it to the right putting my cursor on it see if we did it correctly so i'm going to copy that over so now we're saying similar kind of idea if they get the lump sum we can put in the 306 957 if they can invest that then they can get a return on it hopefully to get to the targeted goal that would be the idea there and then we've got the funeral expenses funeral expenses this is a one-time cost just burn me up and throw me in the ocean it's not expensive have a bonfire it's not environmentally sound we can't burn you in a bonfire because there's you'll cause pollution for crying out loud 8500 it's just because i'm bereaved doesn't mean i'm a sucker anyways we're going to increase that for inflation as we go going forward so we can do this a couple different ways let's do it the easier way this time instead of using future value i'm just going to say okay if there's 2.5 inflation i can say this is going to be equal to the prior balance times brackets i'm going to say 1 plus 2.5 so it's going to be 100 102.5 right so that we can increase it by 2.5 is the point so then i'm going to put i need the brackets because of order of operations so i can add before multiplying and there we have it and if i copy that across then it should take the prior balance hold on a second it didn't do it it didn't do it right prior balance and then i need to make an absolute reference of this 2.5 because i don't want it to move because it's outside the data set f4 and the keyboard enter now let's do it i'm going to copy it to the right it picks up the prior number times that one plus 2.5 102.5 let's copy that across so there we have that so the one time funeral costs okay so that's going to give us our life insurance before before considering our assets life insurance before assets before assets is going to be equal to the sum so i'm going to be picking up this factor nothing above that and then i'm also going to have the uh hold on the mortgage i'm going to copy this down for some reason i started the mortgage one period off hold on a hold on a second hold on a second okay i think i fixed it now and one way you can kind of check these kind of things notice is you could use these little tracer tools which i put into my favorites over here they're in the formula tab and the tracing so i can see where the formula is coming from so right and that's one way that we could try to try to see if i'm pulling these from the right area so it looks like they're being pulled from here so i think that's appropriate and i can turn those things off so hopefully i've got that right remove you can remove it here those are really neat little tools okay so let's add it up again equals the sum and i'm going to be picking up then this insurance needed on down to to paint off the mortgage to the emergency to college retirement and the funeral and enter and so that's going to give us the 128 334 if i copy that to the right i'm going to copy it to the right and so there we have it and so uh that looks good and then we are going to consider how many assets are currently in place so less so i'm going to call it liquid liquid assets that are currently on the books on the balance sheet so i'm going to say equals scrolling over to the balance sheet and we could say okay in terms of the balance sheet we could we could have then the liquid assets which i'm going to say are anything in checking or the cash or savings for example that i can have access to plus the i rub which i'm going to say they might have access to at the point of death or at least it could be used for retirement kind of components of our calculation which we added so i'm going to say enter and so there is that i'm going to make it negative double clicking on it and put bracket negative brackets so that i make the whole thing negative and then put my cursor on it and drag to the right so we're going to subtract that amount out and hold on a second i can't do it like that because it's not absolute so instead i'm going to just say the next cell equals the prior cell enter put my cursor on it and drag it to the right we'll do it that way so there we have it and that's going to give us our insurance our life insurance needed and i'm going to sum this up equals the sum of these two which will subtract them out given the second one is a negative number and copy that across copy that across roger that copy out let's put some underlines up here i'll put an underlying here font group underline let's put an underlying here i'll say font group underline double underline here and so we'll say font group double underline and so those are just some tools i'll make this a little smaller that you can use and another method that you might basically consider and possibly ending up with more of a declining balance over time which might be useful for say term life insurance i'm going to select this whole thing that could be more affordable if you have that declining balance so those are some tools that could be home tab font group brackets and we'll hit the drop down blue i'm going to blue a fire if you don't have that blue more colors standard blue right there okay and we'll go to the to the left i'm going to make this blue and bordered blue and bordered and this blue and bordered blue and bordered this blue and bordered blue and bordered and review it spell check it looks good so there it is