 Personal finance practice problem using Excel. Life insurance calculation tools part number four, mortgage. 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 as we have been doing in prior presentations. If you haven't seen the prior presentations, you might wanna go back and take a look at them. However, it's not required to move forward from this If you do have access to the worksheet, there's three tabs down below. Example, practice blank. The example tab in essence being an answer key information on the left hand side, calculations on the right. The practice tab, having some pre-formatted cells so you can work through the practice problem with less Excel formatting. The third tab, we're gonna be doing the Excel formatting as we do the calculations. Quick recap of what we've done thus far when we're thinking about life insurance needs. We might break them out first to thinking about people that are dependent on our income stream. If we were to die, how much income might they need for the day-to-day kind of calculations for how long might they need them in terms of years and how much lump sum at the point of death would be necessary to provide for that. You might do that by one, looking at your current wages, for example, or two, you might look at the expenses side of things and use that as basically your baseline. You may then multiply it times a set number of years using a heuristic or you possibly might use a years based on how many years until your child is 18, how many years you have left of life or working years, how many years your wife or your spouse has left for in terms of working years, for example, to determine the flow for that many years, noting that then we can think about added calculations on top of that for goal-oriented things such as college savings, such as spouses retirement help, for example, such as a elderly parent possibly medical needs at some point in time and tack that on after we think about the basically day-to-day kind of cash flow needs, that's one way that we might approach it. And then, of course, we can also take a look at our differences in terms of our liabilities and our assets versus our liabilities, which we'll touch on this time. So then we calculated the insurance needed, which you might use to 600,000, you might take a percentage of that using like 70% as a heuristic given the fact that you won't be around, so the expenses will be less a bit and because if they got the lump sum, they could invest it in order to get the cash flow that they would need for that time frame. You could do different calculations. You might say, I would like to have as much investment as needed in order to get that cash flow. So we looked at that kind of calculation, which is another way you can think about it. You might say, let me do an annuity type of calculation and think about how much they would need in order to be able to pull out that cash flow need per year, given the fact that they could have earnings on it. So we did an annuity calculation. We considered inflation, taken that into consideration and note that as we thought about these calculations, we also noted that when you're thinking about those cash flow needs up until that end point like retirement or something like that, we saw that we might have declining needs as our working years go by if we're using that kind of calculation. Therefore, you could think about a life insurance policy that would cover basically or decline over years which would need less and then tack on top of that those targeted goals which we talked about last time using college as an example where we would then say, how much would we need at some future point and then try to figure out how much life insurance we would need at that point and notice that that one would actually increase as we get closer to that target year if I was to die if we were to die at a later point in time. So we did that calculation in which we can add on to our earnings calculation. The other method that we might use and we could use this tool in multiple different ways is to think about our biggest liability for most people being the mortgage and try to figure out, okay, I could have the life insurance necessary to just cover the mortgage. It would be nice if I was to die and it would have enough money to pay off the mortgage which would be nice just in and of itself or we could think about how much money would be necessary to continue to pay down the mortgage on a liability perspective and use that as part of our total calculations or a couple of different ways we might work in the mortgage big liability component to our life insurance calculation. So let's first do our calculations on the annuity tables. We talked about calculating annuity tables in prior sections, but I think it's useful to be able to do an Excel and we'll use that to practice our annuity tables. Note that you could use online tools as well. For example, here's a loan calculator, but I like to use these to kind of double check so that I can do the calculations in my worksheet in Excel and then use that worksheet to tie into any other kind of more detailed calculations I have based on it would be up my suggestion. So let's build it out here. We'll practice building out an annuity calculation. I'm gonna select the skinny column. I'm gonna go to the format painter. I'm gonna make A-W skinny and then let's just pull in our data first. So I'm gonna say, let's pull in, I'm gonna say this equals and I'm gonna try to pull over the data from my data set on the left-hand side. I've got the mortgage. I've got the years, I've got the rate. So let's go enter. Let's say we've got the mortgage. I'm gonna copy that down and it'll pull in the rate and I'm gonna copy it down one more time. There's the payment. I'm gonna copy it to the right and it should pull in the numbers. I'm using the fill, the auto fill. So there we got the 150,000, the 15 years, the rate. I'm gonna make this a percent, home tab, number group, percentifying it. If you don't have the worksheet, you could just punch this data, put this data into your worksheet and this will be our starting point. Now the payment, I'm gonna recalculate the payment just to practice the payment calculation. Note that if you're trying to think about your life insurance, you probably saying, well, I got the loan a while ago so you might know what the current loan balance is. You could see how many years it is out into the future, for example. And then you might have the rate and the payment. You might have them kind of laid out for you or you might not know, you might have one unknown that you can kind of back into. But just to practice the payment calculation, let's go ahead and practice that one. And I'm gonna say if there was 15 year loan, 4% rate, the amount is at the 150,000. And to calculate the payment, I would say negative PMT brackets. And then the rate is gonna be that 4%. And we're gonna take that, that would be the yearly rate. So I'm gonna say divided by 12 to get the monthly rate because the mortgage is a month-by-month calculation, comma, number of periods is the next argument. So I'm gonna pick up the 15, that's 15 years, which I'm gonna say I'm imagining that that's the current point that I'm getting the life insurance. There's 15 years left maybe for the calculation. And then I'm gonna take times 12 because that's in years and we want the months. And then comma, the present value is gonna be the loan amount at this time, 150,000. This could also be a useful way to double check to make sure that your data makes sense because if you recalculate your payment calculation down here, you should get to the same payment calculation you have, which will mean that your amortization table should work. Meaning if I use this data up top to calculate my payment, then my amortization table should work. Let's build an amortization table and then once we build it, we'll then construct a year-by-year amortization table, probably the best tool to help you with your calculations for life insurance. So you could do this with an online calculator. I could say, okay, what if I had a loan of 150,015 years and then we said the rate was, what did I say the rate was? I forgot, I forgot, 4%, 4% on the rate. And then calculate it. So there's the amount, which is, this is mine's rounded, 1,109.53. You could make the amortization table. There you have it. But the amortization table is on a month-by-month basis and I can't use it really in conjunction with my other calculations that I might want to be adding to it. So I would rather recalculate this or reconstruct it basically in Excel. So I'm gonna say, all right, can I make that in Excel? Cause I'd like to see the year-by-year numbers. So to do that, I'm gonna go, okay, let's make another skinny column in AZ. I'm gonna put my cursor in this one, whatever that is, and go to the home tab, clipboard and format painter, make a skinny AZ. And then I'm gonna say, we need the headers of years, months, interest. I'm gonna call this loan and I'm gonna use two cells, decrease, decrease. So I'm not gonna wrap the text cause I don't wanna mess up any other cells. I'm gonna use two cells, which isn't great if I have a table, which we'll see. But if you don't have a table, I think this is the nicest way to do it. So it doesn't kind of distort any other cells in your worksheet. So this is gonna be the loan balance. We'll make that our headers. So I'm gonna bring these down. I wanna put them down at the bottom here. So I'm gonna say, let's control X. I'm gonna put them down here instead and then we'll select those cells, home tab, F, and we'll go to the font group. We'll make this black and white and I'll center it. Now I'm gonna do this fairly quickly cause we've seen these calculations in prior sections. But so you can go back there to see it a little bit more slowly. I'm gonna make these a little bit smaller but just to practice our calculations. So I want the months and I want the years here so that I can then use that to get a year by year breakout. So I'm gonna start then at the months. I'm gonna say it starts at zero, one, two. I'm gonna select those three. I need to go 15 years out. So 15 times 12, what's 15 times 12? That's 180. So I'm gonna select those three. I knew that already, 180. So half the 360, 180. What I do, 180 kick flip something. I don't know, what are you talking about? I don't know. So this is gonna go down to 180. That's what I know. That's what we're doing. And then I'll center that. We're gonna go to the alignment and center it. And then I could do my tricky years calculation. And I wanna have the years here because that'll help me to break it out. So I'm gonna start down here so I can see it a little bit better. This is gonna be equal. I'm gonna use a roundup, little fancy calculation. Roundup, roundup little doggies. Roundup double clicking that. I wanna take this cell to the right. I'm gonna take that and divide it by 12. And then I wanna round it up to the next whole number. So I have to say comma. And then I'm gonna round it up so that everything that's in the first year will round up to one, right? Everything next year will round up to two. To do that, I think you have to say negative point one. That's the placeholder to say, I want you to round up to a whole number and enter. So let's just double check it. I usually increase the decimals, that makes sense. And then if I copy it down, I got one all the way down and then it goes to two. That looks like it's doing what we expect. Let's take off the decimals. I'm gonna double click on it. The fill handle button, that should take it all the way down. So now we got the years for each of the 12 months going down to 15 years. Let's center that, that looks good. The first one starts at year, we'll just put a one up top too or let's put zero, zero there, center, center that. Okay, and then the balance. The loan balance is gonna be equal to the starting point of 150,000. We'll calculate the interest per month, which is gonna be equal to the 150 times the 4%. That 4% is yearly interest. Therefore, I have to divide it by 12 to get the monthly interest and enter 500. The loan decrease then, hold on a second, that's the interest, I need the payment. I'm missing a cold column. I'm gonna put my cursor on column AC, I need another column. Right click and insert, I need a payment. I wasn't, I don't wanna make payments though. I don't want there to be, you have to pay it. You have to make payments. So this is gonna be equal to the payments of the 1110 that's rounded. So that means if I pay 1110, the interest is 500, the loan decrease is gonna be equal to the 1110 minus the 500. That's the amount that the loan's gonna go down by. And so the new loan balance is gonna be equal to the prior balance minus the loan decrease. And that gives us the 149,390. I'd like to copy this down. So this cell right here, because it's coming from something outside the table, needs to be absolute. So when I copy it down, it will not move down. F4 on the keyboard, dollar sign before the AY and the four. Double click it on the interest. This four is outside of my table. It's in the data set. Therefore I need to make it absolute. It's an AY3, put it in my cursor in AY3, F4 on the keyboard, dollar sign before the AY and three and enter. Selecting those three items. Double click in the fill handle button. Copies it on down. So there's our loan balance. There we have it. If I get all the way to the bottom, I should have a zero at the end of the day. There's the end of the day. At the end of the day, after 15 years, day 180, it's at zero. That's an indication that we got it right. We can then check that to our balance over here on the amortization table worksheet calculator online. If we so choose. And then I'm gonna make this blue and bordered, blue and bordered, border blue, let's say border blue. And so there we have it. Let's make this border, let's make that blue too, border blue. Now what I'd like to see is this broken out on a year by year basis because that's often what I would like to do for my calculations for budgeting and for my insurance calculation. So we can do that a couple different ways. I'm gonna put my cursor on this column to make a skinny BG. I'm gonna go to the home tab and go to the paint brush and paint brush the BG to make a skinny BG. I'm gonna do this with formulas and then we'll do it with a pivot table. So I'm gonna select the same headers up top, copy that, put that in BH and enter. But I don't need the months because I'm just talking years this time. So column BI needs to go. I'm gonna select the whole thing, right click on it, delete it because I don't need it, delete it because I don't need it. That's what I'm talking about. I'm gonna make the years smaller and then I'm gonna say zero, one, two, bring it down to 15, selecting those three. I wanna bring it down to 15 with the fill handle this time, dragging it on down to 15 because there's 15 years. Center that alignment and center. Okay, so now I wanna take the payments for everything that lines up to year one, then the interest, everything for year one, the loan, everything for year one. So I'm gonna do that with the if function. So there's nothing on year zero here. We'll start on year one. This is gonna be then equals sum, but sum if. And I'm gonna use the ifs with an s. You can use either one, but this I think it's the more new one, ifs with an s and it's got a similar format as the other sum if functions. So here we've got the argument. We've got the range, the sum range. I want you to sum the stuff in the payments column. So I'm gonna select the whole column because there's nothing underneath it. So I'm just gonna select the whole thing. And then I'm gonna say comma criteria range, meaning when you see that zero in the criteria range or that one in the criteria range over here, which is the year, that's the criteria range. B, A, I'm gonna select the entire column because there's nothing underneath it, comma. What is the criteria, that one? When you see that one in this here, matching it up here, then I want you to sum up the related items in this payment item, that's what we want you to do. And enter, there it is. I could check it by selecting these down to here. There's the 13, 3, 14 looks good. I'll do the same thing here and then I'll practice how we could format it to copy it across. Here I wanna add up all the interest for year one. It should come out to 5,864. Let's do it again, equals the sum if, sum if, sum range is gonna be this range now. And then comma, the criteria range is the same. So that's gonna be this one. And then comma, the criteria is the one. And then enter, let's do it one more time. For the loan, that's gonna be the loan decrease. So this is gonna be equals the sum if, s, bracket, sum range is gonna be the loan decrease this time, comma, criteria range is gonna be the years, comma, and then the criteria itself is the one. And enter, so there we have it. That's the loan decrease should tie out to this number, seven, four, seven, four, five, zero. Okay, could I copy this one across? So I'm gonna say, I'm gonna delete these, double click on this one. I'd like to copy it down and across. So this one is the sum range. So that one is this one here, but I want that to move to the right when I copy it to the right. So I don't need to do anything to that. When I move it down, it should be good because I selected the whole column. So this one is the criteria range. That's this column over here. I don't want it to move when I copy it to the right. I want it to stay the same. So I'm gonna say F4 in the keyboard and put a dollar sign between that full range of B-A-B-A. This one is the criteria, which this is where it's tricky. I'd like it to move down, but I don't want it to move to the right so I can copy it both down and to the right. So when I move it to the right, and those are the letters. So I need a dollar sign before the letters. This is called a mixed reference because I want the four to move down, this four to move down, but not the B-H to move to the right. And then enter. And I did something horribly, horribly wrong. What did I do wrong? I put an and instead of a dollar sign. That's not a dollar sign. That's a, now you got a percent sign. Okay, don't you know what a dollar sign, then I'm going to put my cursor on the fill handle and drag it to the right. I know what a dollar sign is. I know what a dollar sign is. So then, and then I'm going to copy it down. I'm going to select those three and we'll just double click on the fill handle, boom, copying it down. So now we've got those copied down. It should, this one is now doing what it should. I think that looks good. Then the loan decrease. Now, obviously there's no payments in year zero. The loan balance starts out to 150. Now, this one's a little bit different. The men balance is, you can do this with a equals M-I-N balance brackets. The, the, I'm sorry, not the men. We want to say, we want to take then the men if equals the men if with an S. Okay. So then we've got the men range, which is the loan balance. And then comma, the, the criteria range, which once again is BA. I'm just selecting the whole column, comma. And then the criteria is this one. I don't need to do any absolute references or anything because I'm not copying it to the right. And I choose the whole column. So I'm going to say enter. And now it took the 41, 142550, which is the ending balance at the end of year one right there. I'm going to put my cursor on it, double click and copy that down. So now we've got this broken out on a year by year basis. If I put the total, summing up the total here, this is our total in terms of payments. Copying that across not to the end here, but there's our, there's our loan balance calculated again. I'm going to make this blue and border. So now border blue, I've got a tool that we can use to kind of, to kind of help us out with our life insurance calculation, which I'll talk about. But first let's do it one more time, this time with a pivot table. Now notice because the header has these two columns to it, I can't use two columns as the header. So I got to just use this second column, which is kind of a problem when I use those two columns. So you got to be aware of that. But I'm going to select all of this down to the 15 years and look how fast we can do this with a pivot table. So I'll say pivot table. I'm going to go in insert pivot table, boom. And I'm going to put it in the existing worksheet. I'm just going to put it right there. That's where I want it. That's where I want the pivot table. We'll say, okay, makes the pivot table. And then the only tricky part here is when I pull down the years, because there's numbers in it, it puts it to the sum column or the values. I want to pull it to the rows. So there's the years, rows. And then I don't need the months, but I want the payments. I want the interest. I want the decrease. I want the balance broken out by year. And then I'll just format this thing. And so I'm going to go to the dropdown on this one. Do the value field settings. And I like to go, I'm going to make the number formatting. I want to make this currency, negative numbers, and then no dollar sign, no decimals, and enter, okay. Let's do it to the second one. You said that was too fast. So I'll do it again on the second one. We're going to say, let's go to the number formatting, currency, brackets for the negatives, no dollar sign, decimal, down, down. That's way too fast still. Let's do it again. I'll do it again on the third column. And then we're going to go value field settings. We're going to go to the number formatting, currency, brackets, negative numbers, no dollar sign, decimal, down, down, okay, okay. All right, I got it this time. But that's still, this time it's going to be different. Why it's different value field formatting. This time we don't want the sum of it. We want it to be the min, the min, selecting the min. And then we do the number formatting, which is the same currency, bracketed numbers, no dollar sign, decimal, down, down, okay, okay. So there we have it. So that's another way you can kind of, you can pull those numbers. I'm going to make these columns a little bit smaller, selecting from BH to BL and make this a little bit smaller. Make this a little bit smaller. Okay, so the way that you might use this tool within your calculations is you might be saying, hey look, if I was to die, I'd like to basically have enough life insurance to kind of wipe out the big liabilities maybe. So you might say, I'm going to try to use these loan balances as at the end of each year as kind of my baseline where I'd like to have the life insurance. So again, you can see where, if you were to use that format, you could see that as time passes, if you do not die, if you die at a later point, then you expect these loan balances to be less and you can tailor your life insurance possibly to go down over time to possibly get a more reasonable life insurance policy that can cover the needs and safeguards you in a way that you need. That's one way that you can think about it. You can also think, okay, I'm not sure I can get enough life insurance. I'm not sure I want them to pay off the full balance. Maybe I get enough life insurance so that they can pay the payments, just continue paying the payments that we have been paying, right? And so you could say, for example, obviously the payments would be then straightforward in terms of a cash flow, it's locked in, it's not going up with interest if you locked in the interest rate, for example, and then you could determine what's your yearly amount of payments and you can add that into the calculation for your overall life insurance as we've been discussing before in the prior calculations. So those are a couple of ways we might use these numbers. So notice this is like a liability, a balance sheet number. This number over here is like a balance sheet number or you can think of it in terms of cash flow number on the payment calculation. So those are a few tools that we can use there. So we'll use these tools in another practice problem where we'll actually make basically a budget and then think about how we might use these tools to do an estimate for life insurance.