 Personal finance practice problem using Excel. Life insurance using personal financial statements, part number six, mortgage 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 basically built this from a blank sheet in prior presentations and we're continuing on with it now. But if you do have access, three tabs down below. Example, practice and blank. Example tab, an essence being an answer key. Let's take a look at it now. Information on the left-hand side, calculations on the right. We started out putting together the personal financial statements, balance sheet, income statement, two formats of the income statement on an accrual, an essence, or cash basis method. We looked at methods one and two for calculating the life insurance based on that information. Now we're gonna add another component and that's gonna be the loan amortization table so that we can then use all of those components to get to some more nuanced type of calculations, breaking out the life insurance needs that we might have on a year by year basis, possibly looking into life insurance that has kind of a declining death benefit, for example, as we get closer and closer later on in life in accordance with these calculations. So here we're gonna focus in on the annuity calculation which you may be able to get from your financial institution or you might be able to do an online calculator to do it and there's many out there, I'm not advertising any one of them, so but here's an example of one of them. However, I would use these as more of a tool to help us in our worksheet because I'd like to get the information into the worksheet for a couple different reasons. One is that I would like to break this information down into a year by year calculation instead of a month by month calculation and two, if I was to change anything for estimates, I could change this on the left-hand side with just changing the data and we can tie this information number three into our other kind of calculations that we're making for the life insurance more easily looking and using formulas. So that's what we'll do here. The second tab is the practice tab. It has a pre-formatted worksheet so you can work through the practice problem with less Excel formatting. The third tab, a blank tab which we will do the Excel formatting in. So I'm gonna go all the way to the right to add some more space. I wanna have a small double A and that's gonna be where we're gonna start our information. I'm gonna put my cursor on the skinny W so that we can copy it, format a home tab, format painter, take in the paint brush and make it a skinny double A, skinny double A and then we're gonna say that let's just reformat the information over here. So I'm gonna say when you gotta need the loan amount, the rate, the years and then the payments which I'm just gonna pull from our data set on the left using formulas. So we have it right next to our data that we are working in to build our tables. Now note, obviously you might have some years that have passed within the loan when you're trying to do in your life insurance calculation. So you might try to take your loan balance for example and start from that point forward to kind of construct say your amortization table from there using the online tools and possibly the table provided by your loan institution, financial institution to help you with your calculations or give you double check on the calculation. So I'm gonna imagine we have a loan currently on the books. I'm gonna say this equals, I'm gonna go all the way to the left to pull it from my data set. If you don't have the data set you can obviously just build the loan from scratch and we're gonna go down here and I said we had a loan on the books. It's actually from the balance sheet. Let's pull it from the balance sheet. We said it was on the books here, the mortgage of 150,000. We've got the rate it's gonna be equal to. I'm gonna go all the way to the left and scroll down to the bottom. I said the rate was down here somewhere. There's the inflation rate. Mortgage rate is 6% we're gonna say, enter. I'm gonna say that that is gonna be a percent. We need to percentize that. So home tab, number, percentify it. Now obviously if you have an adjustable mortgage or something like that, it could be a little bit more complex if a calculation were assuming a fixed rate mortgage. The number of years, I'm gonna say equals and I put that on the data somewhere on the left, scrolling down to the data. We said it was a 20 years left on the mortgage. I'm gonna say enter. If you don't have the data you can just compile your data over here. And then the payments. I'm gonna recalculate the payments. This is one way you can kind of double check yourself. So if you have this information proper up top then you can recalculate the payment. And if that ties into what your payment is that it actually is, you can kind of double check it if you already have like the mortgage set up and you're like 10 years into a 20 year mortgage or something like that. So I'm gonna do that by saying negative PMT. There's our payment calculation. Double click on the payment calculation or say shift nine. We want the rate, which is the 6%. So I'm gonna click on the 6% but that's the yearly rate. And we want the monthly rate. So I'm gonna divide it by 12. Comma, the number of periods we're gonna say is 20, 20 years remaining. But that is in years and we want it in months. So we're gonna times 12. Comma. And then the present value is the current loan amount which is the 150,000 and enter. That gives us the 1,075 about it's rounded. We can double check that with the online calculator if we wanted to. I could say online calculator, 150,000. We've got the 20 year. We're gonna say 6% at the rate. Run it online calculator. We got it at the 174.65. I can make the amortization table for example. And there we have it. But the amortization table is broken out by year. And I can't as easily work it into my worksheet. So I still think it would be better to use this as a double checking tool as opposed to not doing it in Excel, right? So I'm gonna do it in Excel. So now we're gonna create the amortization table in Excel and then we'll break it out on a year-by-year calculation using pivot tables and formulas so that we can use that for our more complex life insurance calculation, possibly a declining term life insurance. It's gonna be great. So I'm gonna select these four first or whatever the four. I don't know, there's more than four. But I'm gonna go to the home tab, font group, border and blue ties those drop down. If you don't have that blue, it's down here. More colors, standard, blue. There it is. And then I'm gonna make a skinny AD by going to the, this one, I'm gonna say home tab, format painter and then AD add. And then the next one. So we're gonna say this is gonna be our headers. I'm gonna start one cell down on the headers because I'm gonna have a couple headers that need two cells. So I'm gonna say year, month, payment. I'm doing this quickly because we have seen this in the past, but I think it's useful. It's a useful tool. These amortization tables, great practice. Interest. And then here's where I'm gonna have two lines, loan, decrease. Now notice I didn't wrap the text. I could put that on one cell and then go to the home tab alignment and wrap the text. But I don't like to do that unless I'm gonna make a table out of it, which we will do. I'll make a pivot table, but I could still make the pivot table without it. I don't like doing it because it makes everything else messed up or widens the row for everyone else. And I don't, why mess everyone else up just for that one column name. It's not fair to the whole column, everyone else that has to use that column. You see what I'm saying? So I don't like doing that. So I'm gonna do this one too and then I'm gonna select this whole thing and we'll make that our header row. Home tab font group, we'll say it's gonna be black and white and I'm gonna center it. So there we go. That looks good. And then I'm gonna select these two and make them a little bit more skinny, skinny them up to those two, put in my cursor in between them, skinny those up. There we go. And then we got 20 years. So if I say 20 times 12, that's 240 periods that we need. So I'm gonna say, we're gonna start at the period zero, one, two, I need 240 of them. I'm gonna select three of them, put my cursor on the fill handle and I'm just gonna grab that fill handle, make sure I got a good grip, a good grip on it, cause I'm going down to 240 periods, 240, just driving it down there with the fill handle, 240, 240 right there. That's where we need it. Center it, alignment and center. That looks good. And then I'm gonna do the year by year calculation with a little bit of a fancy, little fancy trick here. I'm gonna say it's gonna be equals round up, round up. And I'm gonna say I wanna take round up function and I'm gonna take the number of the month and I'm gonna divide it by 12, 12 months in a year. So as long as that's above zero, it'll round everything in the first 12 months up to one is the idea. If we can get it to round to the right digit, which I gotta do by saying comma, which digit is it? You gotta say it's negative 0.1. That tells Excel that you're rounding it to a whole number. So I'm gonna say brackets and enter. Let's double check it by saying there's no decimals on it. That looks good. And then I'm just gonna double click on the fill handle and that should take it all the way down. Let's see if it does what we want. So now I've got one for month one, one year one, year one, year one, and then year two on 13. That looks like it's doing what we want. Year two for 13. This is important for like pivot tables and other types of tables because you wanna have a populated cell on each one of them to have it properly formatted in case you didn't know that. So I'm gonna scroll up top. Now you know it. That's what I'm here for. That's what I do. I tell people stuff they need to know and stuff they don't need to know. Plus possibly most stuff they don't need to know but still some stuff that's useful. Alignment, I'm gonna center this whole thing and then we're gonna go to the loan balance and let's pull over the loan balance of the 150,000 on period zero. Payment calculation is always gonna be the same 1,075 all the way down. So I'm gonna say this will be equal to the 1,075. That's rounded by the way. I don't want it to move when I copy it down. So I need to make that an absolute reference or a mixed reference, but an absolute is easier. So F4 on the keyboard, dollar sign before the AC, a dollar sign before the four and enter. And then the interest calculation is simply gonna be equal to the 150,000 times the rate which is 6%, but that's a yearly rate and we're just talking about a month right here on the interest. Don't get crazy on a loan person financial institution. It's really that divided by 12. So that's gonna give us the 750. So if we're paying 1,075 equals 1,075 and 750 is going out the door. It's going to interest. We're not gonna see that again. It's not decreasing our principal. It's just the rent in essence, the interest. That gives us the 325. That means the loan is gonna go down equals the 150 minus the 325. There it is. Let's do one more just for the fun of it. One more time. The payment is gonna remain the same, 1,075. I want to be able to copy that down. So I'm gonna say F4 in the keyboard, dollar sign before the AC and the four tab. Interest is now calculated at the 149, 675. Now a new number, therefore we're gonna get new interest calculation times the 6%, but that's a yearly calculation. And also note that that is outside of our table here. And therefore I need to make it an absolute reference. So when I copy it down, it doesn't move down. I'm gonna select F4 on the keyboard, dollar sign before the AC and the two. And then I'm gonna divide it by 12, which I can hard code because 12, there's always 12 months in a year. It's not gonna change. I can hard code otherwise just type it in. Otherwise no one has just type it in. So then I'm gonna say this equals to 175 minus the 748. That gives us a decrease in the loan balance of the 326. And then the new loan balance is the prior loan balance to 149, 675 minus the 326, giving us the 149, 349. Let's select those four and see if we can just copy that down as easy as possible. And the easy way to do it is I can just double click the fill handle button. I'm not even calling it a handle now. It's just a button, even though they don't call it a button, but now it kinda is because you could just double click on it like a button. And then if it brings us down to zero at the end of the day or at the end of 240 days or at the end of 20 years, then that's an indication that we have done things properly. And so you can see that I have an indication that I've done things properly here. So then I'm gonna select this whole thing because it's zero. And we just clarified that that's an indication that things are done properly. I'm gonna take this whole thing and then I'm gonna make it blue and bordered. We're gonna go to the home tab, font group, border and blue ties it. If you don't have that blue, by the way, it's over here in the more colors, standard that blue right there. That's the one I use. You don't have to use that blue. You can use whatever makes you happy. But don't piss off your supervisor if it doesn't make them happy because you gotta be a team player here, people. Gotta be a team player. Anyways, this is my life insurance. I'll make it whatever is my mortgage loan calculation. I could do it the way I want to do it. So this is gonna be, so you can use this as kind of like a double check on this side. So we got the 149.675. 149.675 on the first payment. So then we're gonna break this down on a year by year payment because that will probably be more useful when we're trying to figure out it with relation to our life insurance. I might wanna say, hey, can I set up my life insurance so that it ties to the ending balance, the liability possibly as time passes. And so we might wanna use something like that. So to do that, I wanna break this out and say, well, where am I standing at the end of each year? Not the end of each month. Okay, so I'm gonna do that with a formula basis and then we'll use the pivot tables to do the same stuff. I'm gonna select the skinny column over here because I want a skinny AK, AK, the skinny AK, which stands for that column, AK, the column. So I'm gonna go to the home tab and then paint brush it and then go to AK, AK, the column. And then I'm gonna select these cells, copy them, put them in AL, OW, good ol' OW. It's good to see you, OW. And then we're gonna, I'm gonna delete the month, the AM, because I don't like waking up in the AM. So I'm gonna right click and then delete that. So there. But now I got another AM. AM doesn't go away still. I tried to get rid of it, but no, it's still there. So then I'm gonna select these two. Let's take the years and make that a little bit skinnier. And then we'll say our years are gonna be one to 20. So we could start at zero, zero, one, two, down to 20. Selecting those three, taking the fill handle, grabbing it, grabbing it, getting a grip on it and going down to 20, 20 years. And then we'll center that alignment and center, boom. And then the balance, the balance we need up top, I'll start at period zero. Loan balance is simply that 150. And then the payments will start at period number one. So what I'd like to do is use an IFS function and I want it to sum up everything that's in year one, which is gonna be these 12, eight, 90, which is fairly easy to calculate because that one, of course, the payments are the same, but the interest is different. So that's why you could see this is important. This is important. So I'll do it first. Let's do the payments first. Equals the IFS, and you can use the IF function, IF, wait a second, wait a second. We wanna say this equals the sum IF. Okay, that's what I'm looking for. And there's two of them. There's the regular one, but the one with the S, I think it's the newest one. You can use either one, but I think this is probably the newer and more improved one. So it looks more like the other one. So anyway, another one, just like the other one. Sum range, so the sum range is gonna be this one. I'm just gonna select the entire range because there's nothing underneath it. So I'll just pick up the whole thing, and then I'm gonna say comma, and then the criteria range is this one because everything with a one is the one that we wanna sum up the stuff and the sum range. So I'm just gonna select the whole AE column, and then comma, the criteria is this one. So in other words, we're saying, hey, look, if you see that one in the criteria range, then I want you to sum up not the criteria range, but the sum range. That's how, that's what we're saying to Excel, but we're saying it like in Excel ease. That's like the Excel language, so they can understand it. Let's do it a couple more times, and then we'll try to format it so we can copy it across and down. This equals the sum ifs, sum ifs. Let me tell you the, when you sum Excel, so you know sum range, the sum range is gonna be now the interest column. I'm just gonna select the whole column, and then criteria, you wanna sum that, but only Excel, only if the criteria range here is this one. So you gotta like look at the criteria and then match it to the criteria, which is the next comma, which is this one. So if you see that one in the criteria range, then you wanna sum up the other range known as the sum range, the related sum range. And enter, let's do it one more time. We're gonna say this equals the sum ifs with an s brackets. This time the sum range is now the loan decrease and the ai column comma, the criteria range remains the same. And then comma, the criteria is once again one. And enter, so there we have it. So now I'm gonna delete those three cause the last one's a little bit different, but I'm gonna delete those two and I'm gonna try to make this first one so I can copy it both down and across using fanciness. So I'm gonna double click on it and say, okay, for this first one, the sum range, that's this one, I want that to move to the right. When we go to the right, and it should be okay to copy it down because I made the whole column, which isn't like exactly the most proper thing to do, but I think it'll work here because there's nothing underneath it. Criteria range is gonna be this one. Now this one, I don't want it to move when I move right. So I'm just gonna say F4, make it an absolute reference on the whole thing, dollar sign before the first part and the second part. And so that's good. And then this one, the criteria, I want it to move down like it normally would, but I don't want it to move to the right when I copy it to the right. So that's the letters, not the numbers. I want the numbers to change, but not the letters. So AL, AL, you gotta stick to AL. AL's a good guy. You've gotta make sure that that one I'm gonna put a dollar sign so you don't leave AL and go to the AM because then you gotta wake up early if you go to column AM and we don't like doing that. So we're gonna keep it there. And so then that's a mixed reference, by the way, and then enter. And then I'll put my cursor on it and I'll go to the fill handle and drag it to the right and see if it does what we think it should do. And I think it did do what we think it should do, right? That looks right. And then I'll select these three and click the fill handle button, double click on it, I should say, and it brings it on down, bring it on down to 20 years. How come isn't it, shouldn't it? And then, yeah, that's right. So then, so obviously this interest now, like we could check it, like if I add up these interests, there it is. And that comes out to the 8891, that looks good. This number should be the loan decrease for the years one, everything with a one in it. And that comes to the 4005, that's good. Now the loan balance needs to be the minimum. So this is a little different, but it's similar. Equal sum ifs, I'm sorry, it's the men, men if. I want you to take the smallest number, ifs. And you only have the one with an S, not the other one this time. And so the men range is gonna be this one. I want you to take the smallest number in that range, Excel is what I'm telling you, comma, but that's the men range, but the criteria range, you're gonna do it only if, looking at the criteria range, there's the criteria, and you find in that criteria range, the criteria, comma, which is that number one. So if you find that number one, the criteria in the criteria range over here, then you sum, not that range, not summing, then you find the smallest number not in that range, but in the men range, which for year one should be this last one down here, the 145.995. So hopefully I said that right and didn't confuse anyone. So then I'm gonna double click on the fill handle button and bring that on down, bring it on down. And at the end of the day, or at the end of 20 years, it should be zeros, 240 days I think is what it was. So the total, then if I sum up the bottom line, we're at the bottom line, sum it up. Why are you giving me all this crap? Why don't you just give me the bottom line, okay? Because that's too general. You need more than the bottom line I'm telling you. People always just want the bottom line. I'm gonna take this one and drag it to cross. You can't make a decision just on the bottom line all the time, I gotta tell you. I hate to say it, people lie about the bottom line because you don't look at the stuff that was above the bottom line. And then you don't know if the bottom line was even right, right? And then you're making decisions on the wrong bottom line. Any case, I'm gonna select this whole thing, home tab, font group, and we'll put a border and blue around it. So there we got it. Now then we can do the same thing with a pivot table and that's even easier, but I can't use these two headers. So I'm gonna select the whole table with just the one header column and then I'm gonna go all the way down to the 20 years. Now just throwing a pivot table, no problem. I go to the insert tab and I just say pivot table right there. I just wanna throw at it. What happened? That's the recommended pivot table. I wanna go just to a normal pivot table. I'm gonna hit the button, not the dropdown. And then I'm gonna go to the existing worksheet. I'm just gonna put it into the existing worksheet. It says which cell do you wanna put it in? That's what Excel is asking, that's the location. I'm gonna say it's that cell, Excel, that cell, Excel. And I just say okay and boom, pivot table. So I'm gonna add the years. That's, it's gonna go into the values because it has a value in it. I need to drag that over to the rows because I got 10 years on the left now. I don't want the months, I want the payment, I want the interest, I want the decrease, I want the balance, all that stuff I want. And it defaults to summing it up just like we saw up top. So that's correct for everything except the last one where I wanna take the men thing and it's got this ugly formatting, the general formatting that we'll fix right here by just going to the dropdown. I'm gonna do this fast as we've seen it before, but I'll try not to go too fast. We're gonna go to the value and then it says sum, I'm gonna go to the number formatting, currency, bracketed numbers, no dollar sign, decimal, down, down, okay, okay. That was too fast, okay, I'll do it again. This time for the sum of the interest, we're gonna hit the second one, drop down, value field settings, it's on the sum, that's right, number, format, currency, bracketed numbers, dollar sign, gone, decimal, down, down, okay, okay. That was still too fast, okay, we'll do it again. This time, just so, because I went too fast last time on the third column, drop down, field value setting, number, format, currency, brackets, dollar sign, gone, decimal, down, down, okay, okay. All right, I think I got it that time. Well, this one's gonna change here. What? It's gonna change just a little, it's a little different for this last one because it's defaulting to the sum and we wanna make it the min this time as you recall from our calculations up top. So then the rest is the same, I guess that's okay then. Currency, brackets, dollar sign, gone, decimal, down, down, okay, okay. So there we have it and this should mirror basically what we did before up top. Another way you can do it, I'm gonna select these from AL, from AL to app, AL to app and make them a little skinnier. So now we've got this table which can help us with our calculations on the life insurance now because we can say, okay, maybe I wanna tie my life insurance to the loan balance and maybe I wanna think about how I can get life insurance that might have a decreasing death benefit as I live longer because I'm hoping that I pay off my loan and therefore if I was to tie that I could leave my whoever's dependent upon me with enough money to pay off the loan and possibly get a more affordable life insurance policy by having a premium that basically decreases over time and we can work that into the calculation. We might also kinda consider obviously what the cash flow is. So when you're thinking about the life insurance we might first think, hey look, I wanna just have enough cash flow to be paying off the life insurance for example, I mean paying off the loan in the same way it is structured over the life of the loan. That's one way we can kinda think of it. Or we might say, hey look, I would like to structure my life insurance in such a way where they can basically pay off, for example, the mortgage and try to think about what the ending balance would be as part of the life insurance calculation. So in future presentations we'll get into a bit more nuanced calculation for the life insurance and try to figure out what the cash flows are on a year by year basis and possibly think about more complex life insurance kind of structures where we can maybe have a declining balance and think about whether or not we can have life insurance that has a declining balance as we live longer given the fact that hopefully we'll have less liability over that time and less need for a cash flow for the people dependent upon us for our current cash flow.