 Personal finance practice problem using Excel life insurance calculation tools part number three college savings calculation 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 which we have been doing in prior presentation so you can go back and take a look at them if you so choose but you're not required to we could go from this point forward if you do have access to it there's three tabs down below example practice and blank example tab in essence being an answer key we've got the information on the left hand side calculations on the right focusing in on types of calculations that hopefully we can put together and understand to customize our own life insurance calculation needs. The second tab being the practice tab has some pre-formatted cells to help you work through the practice problem with less Excel formatting. The third tab a blank tab where we've been putting the calculations in place doing the Excel formatting. So recap of what we've seen in prior presentations usually when we're thinking about how much life insurance we need the starting point is to think who is dependent upon me if I was to die how much would they need per year given the fact that my earnings are no longer there for how many years would they need that cash flow and then try to figure out what lump sum at the point of death would be necessary to basically provide for those needs multiple ways you can do that you could start with your wages for example or you could start with your expenses try to add up what the expense needs are once you have kind of the cash flow needs you might use a generic say 10 years for example 7 years or something like that or you might try to determine what your life earnings expectancy would be or possibly the earning the life or the timeframe for which your youngest child would reach 18 for example or possibly your life your spouses a working career in terms of how many years they would need now notice you might first go into say something like retirement or something like that or until your child reaches 18 and then say but yeah but what about retirement after that what about college savings or something like that well once we get that cash flow per year calculated we can tack on which will do this time added things which are goal oriented target oriented items such as a retirement such as saving for a parent that might need you know medical needs if later on in life such as saving for college so then we came out to the insurance needed at the 10 years we might then discount that at like a 70% for example because if we're starting with wages we might say one I'm not there and two you might be able to save some of this money and therefore in order to get the same cash flow you could invest it and it might be something less that be a heuristic kind of way to calculate it you might try to calculate the cash flow to determine how much life insurance you could have to get the cash flow you would like 60,000 here just from the investment which will of course would be a lot higher life insurance or you could say let me do an annuity calculation to try to figure out how much cash flow would be necessary given the fact or assuming they can invest the lump sum they got at death at 5% and then we can get a more possibly reasonable calculation we also might take into consideration inflation within that calculation as well so we can make it a little bit more complex using that we can also use this as we saw in prior presentations to think about decreasing for example the balance of the life insurance and think about possibly having a maybe a term life insurance that goes down in value as we get closer to that end goal where the cash flow might not be as necessary at the end of our working career or at the point where the child reaches 18 or something like that and then tack on the other goal oriented items like retirement savings and college for example which is where we stand now so now let's go back on over here we're going to do this we're going to think about okay now that I've thought about that component what if I've got a kid that I would like to support through college so how can I basically tack on to my prior life insurance calculations something like a savings for college or you could do a similar thing for retirement for your spouse or for an elderly parent that you think might have a needs later on in life medical needs or something like that similar calculations for all those we don't need the yearly cash flows at the point of death you need the cash flows after that point in time to to goal oriented kind of targets which is a little bit different calculation which we can tack on to what we did in terms of the cash flow needs from a day to day perspective okay so let's think about college we're going to say okay let's make a skinny column I'm going to take this skinny column go to go to home tab and say we got the format painter I'm going to make AL skinny skinny AL and then we're going to say college cost of college let's say cost of college alright so the way to do this is similar kind of thing for saving for college we want to think about what is going to be the targeted future value cost of the college that we're going to need and I'm just going to plan it at the point in time that we start college so maybe they're going to be in it for four years at the beginning of that four years how much would they need in future value money in order to pay for college at that point in time you can get a little bit more detailed say well what if you had earnings over the four years but we'll just say target goal at the time they start college I want to have this money ready to roll so I'm going to widen the cell a little bit larger so and I'm going to make some header calculations so we'll select these three cells home tab font group let's make this black and white and then let's say okay well let's first think about what the current cost of college is current college cost so that's something we can kind of nail down hopefully and say okay it depends where you go but let's just say that the current cost we're going to just assume and this may not be completely accurate but we're going to you know you can do your own research on the current cost I'm going to say it's 40,000 so then I'm going to say the years the years until we need the money years until college years until college well let's say that we're going to start college start at age 18 I'm just going to say they're going to start college at 18 let's say or 19 or something 20 I don't know so you're going to the city college for two years you're going and that's the way and you know whatever your plan is and then we're going to say the kids age current kids age is going to be I think I said that equals to we're saying they're five right now we'll say they're five so we got a college fund for you kids you better you best get to study and I'm going to go to the home tab font group and underline it subtracting this out so this is yours until well let's just copy this one up here years until college we'll copy that here double click on it I'm going to go to the end of it remove the colon so that means they're going to have to how many years 18 minus five right 18 minus five so 13 years they got 13 years by by that point we need the money for them to get to get into college and what not assuming that you know they're able to or whatever but even so they can they're going right I don't care I don't care I don't care man anyway this is going to be I don't really care I'm just you so you don't have to go to college if you don't want future value costs of college so then I'm going to figure out the future value cost of college because I'm going to say that there's inflation involved so I said inflation's 3% so if inflation's involved and let's put that down let's put that down here let's say this equals so I could see everything in the same spot my inflation calculation this equals the inflation of the 3% we'll go to the home tab numbers percentify it I'm going to select these two will make that border and blue okay so well hold on a sec that's not blue let's make that this one needs to be blue not black okay so then let's put an underline here font group and underline so I'm going to do I'm going to do a calculation which is going to be the future value calculation how much money will we need in future value terms if there's 3% inflation over 13 years until the point we need the money so okay so I'm going to say this is going to be negative future value that's how I do it typically instead of equals brackets the rate is going to be that 3% comma number of periods is going to be that 13 years comma and then we're going to say the not a payment because this is not an annuity so comma the present value because it's just one that we're looking at future value of one is the 40,000 and brackets and enter so we need 58 741 at the end of 13 years in future value terms so okay so now we got to think okay well let's make this let's make this blue and bordered so let's assume that we earn we have earnings of 5% so I'm going to say if I have 5% earnings if I was to have earnings if I can put my tuition my college fund somewhere and earn a rate of return of 5% 5% and we're going to say okay let's make this let's make this a percent percent to find it and we'll put some blue borders here blue and borders so then how much would we would we have to put away in order to get in order to get to that future value of 57 58 741 let's select this item here let's make a skinny AP I'm going to hit the paint brushy skinny AP and then I'm going to say years and then insurance insurance years and then insurance and so I'm going to try to calculate this on a year by year basis because the closer we get to that 13 year of time frame then the more we're going to have to we're going to have to have in the life insurance right because now we're not going to have time for it to accumulate so it will depend upon when we die which again yeah we could get a little bit more precise possibly in terms of our life insurance over time so let's first calculate it on a year by year we'll talk about that in a second I'm going to go to the font group make this bucket black and white let's center this now let's make the years a little bit skinny or and then I'm going to say from 012 and select those three I'm going to take it down 13 years this time 13 years take it down break it down 13 years center it okay so that would mean so if what would I what would I need to be putting in place to reach to reach this 58 741 that would be a present value calculation meaning how much would I have to put in at year zero if I had 13 years to get up to that 58 741 so I'm going to say okay this is going to be negative present value brackets the rate is going to be I'm going to say I'm going to earn on it 5% earning on it 5% comma and then the number of periods is going to be I'm going to say a little bit tricky here 13 but I'm going to say minus 0 I want to say minus 0 because I want to be able to copy that down when I copy it down I don't want the 13 to move I do want the 0 to move so that 13 which is an aq 15 I'm going to say f4 on the keyboard dollar sign before the aq and the 15 so then comma the the payment is going to be the payment calculation is going to be no I'm I don't have a payment because it's not an annuity comma again we're looking at the future value which is going to be that 58 741 that's where we want to get to in the future I want that to stay the same when I copy it down so I'm going to select f4 on the keyboard this first one also is outside that's that 5% so I'm going to say f4 on the keyboard for that one too and then enter so there there we have it so if I took that 31 152 and I put it in place earnings on it the earnings on it after the 13 years would give me that future value of the 58 741 which is what I would need counting inflation to get to if I copy that down double clicking on the fill handle then that means obviously as I get closer to to the to the time when they actually need the college funds I'm going to need more because I don't have as much time for it to grow so therefore as I get closer here then I'm going to need more money in it and at your 13 I got the full 58 741 now again we can kind of compare this notice over here where we said when I looked at my needs in terms of my working years what the cash flow needs are we said it actually went down as I reached my target whatever that be be that my working years years until my spouse is retired years until the kid is 18 or whatever those are going down because obviously there's going to be less of those years as time passes this one the targeted goals are going up so if I wanted to combine these together to try to think about what the life insurance if I want to decreasing life insurance I can combine these together which will do in a future presentation to say so I need this life insurance plus this at year zero right this and this at year one for example to try to get a more nuanced calculation of what the actual needs might be and try to think about what a tapering might be if I wanted to life insurance policy to kind of taper off as time passes okay let's make this blue and bordered blue and bordered so I'm going to say font group make that border blue and just to understand it a little bit more let's just take this first one and say okay how does that how does that work if I had the 31 152 would that really grow to be 58 741 at the end of 13 years let's test it I'm going to put my cursor on this column whatever that is I can't really see it and then I'm going to go to the home tab format painter let's make the as as a skinny as skinny as so then I'm going to say this is going to be years we'll say earnings and then I'm going to say the balance years earnings and then the balance let's make this our headers so I'm going to select those three home tab font group let's make that black and white alignment let's center it let's make the at a little bit smaller at it's a little bit slowly at any two letters make it smaller and then 012 let's select those three copy that down to the 13 years 13 years let's center that centering that okay so we're going to start off and say does this really work if I started at period 0 at the 31 152 and I earned 5% per period that's an average of course we don't know what's actually going to happen in the future we might earn less we might earn more but 5% fairly decent number fairly conservative kind of number that we could use we could have losses right we don't really know but we're going to sit where that's what we're going to use as our average so we're going to say this is going to be the 31 152 times we're going to pick up that 5% I want that to stay the same when I copy it down in other words I don't want that cell to move down therefore f4 on the keyboard dollar sign before the and and the 10 and enter then the balance is going to be equal to the prior balance plus the earnings and enter so if I select those two and just click the fill handle fill handle button so you can see how it grows each year the earnings are going up because we have more accumulation of the earnings and we get to that 58 741 at the end just to kind of prove that we could do the same thing for each you know for each year for example and try to try to see that calculation but that's just to give us an idea to prove that concept so let's make this blue and bordered let's go ahead and make that blue and bordered bordered and blue so we can do a similar calculation like I say any kind of targeted goal so if you're if we're thinking first we thought about what what the cash flow needs in terms of expenses for our work in years or however many years they think that cash flows needed then after the work in years you might think about okay what are the retirement goals are you should you be saving putting something away possibly for retirement goals where you can use a similar kind of of of a goal based calculation for example and and you might be doing the same for a college so we got a goal based kind of calculation or you might say I want to save to have enough money put away for the needs of an elderly parent or something medical needs or something like that which would be a goal based calculation which we can add on to so now we've got this of the cash flow needs and then we can kind of add on our goal based calculations and you can see how we could do this in a lump some method and try to estimate an idea of what we need or we could think about we would still if we added these together most likely have a declining need for our our life insurance over time because we would have less working years if we were to be living so we could then subtract them out and say well what kind of insurance maybe that decreases over time for example the next way you might look at it is you might of course take into consideration your balance sheet and how much you have more liabilities a lot of liabilities or obviously later on you might have net assets you might have assets involved and think about the liquid assets versus the non liquid assets like the house for example and we also might be thinking from the liability side of things the big liability being the mortgage so we might try to think about it how could I do my life insurance possibly tied into the mortgage balance to some degree so that we can we can we can take that take that method that we might use for life insurance that we'll talk about next time.