 Personal finance practice problem using Excel. Estimated home price from monthly income part number three. Get ready to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you don't have access to the Excel worksheet, that's okay. In prior presentations, we basically built this from a blank sheet. So you can go back there and start from there. If you so choose, we're going to be continuing on from here. If you do have access to the worksheet, there's three tabs down below and example tab, a practice tab and a blank tab, the example tab in essence being an answer key. Let's take a look at it now. The information's on the left hand side. We're using that to populate our tables on the right hand side. We started out from the mindset of the financial institution looking at the financial institution heuristics, thinking about our gross income and how much a financial institution might determine that we can afford going towards a home purchase. Then we boiled that down to how much we could get for just the financing of the loan. And we used that to determine how much loan we might be able to receive. We used that then considering the down payment to determine the affordable home purchase price. We double checked the affordable home purchase price and the loan calculation. We then thought about our own income statement and we looked at the fact that we would want to be doing our own financing. In other words, when we're talking to the bank about how much we can afford with regards to a loan and the payment of the loan, we're not looking to the bank for budgeting information. They are not our financial planner. They are people we're trying to get a loan from. We have a specific objective there and we have a different objective for our personal financial planning on which we wouldn't want to be using the bank heuristics. We would want to be using our own financial plans and our income statements and so on to determine how much we think we could afford. So we're going to try to max out with the bank how much they'd be willing to loan us so that we have the capacity to take out the loan that we think is appropriate on our end. We're not just going to try to take out the loan that the bank thinks the maximum loan that they'll give us. We're trying to say, hey, bank, we're good and we'll do our own figure on how much loan we want. We want you to be there to provide whatever loan we determine appropriate. Then we're going to go over here and we're going to use the data that we put together for our purchase price and our loan amount to build our amortization tables and then break that out on a year by year basis, both with the use of formulas and with the use of a pivot table. Now note, obviously, once we have our personal income statement put together, then we would want to look at projections from that point in time with regards to the home purchase, look at the differences with regards to what our income statement would look like at the changes, the changes from wherever we're at now to the purchase price. And of course, we want to take into consideration the substantial differences that could be involved with the taxation as well as the cash flow differences and the non-cash flow differences. In other words, our equity in the home, this big asset we have on the books versus the loan that we're putting on the books versus and the cash flow that we're going to need to pay our debts coming forward or going forward. The second tab over here is going to have the pre-formatted sales on the right so that you can work through the practice problem with a little less formatting if you don't want to do so much Excel formatting. And then the third tab is continuing on to work this from scratch. So we've been building this together. So I'm going to go to the right-hand side now and I'm just going to make another skinny column, the skinny-o, skinny-o, my darling. And so we're going to go over here to K. We're going to go to the home tab. We're going to go to the paintbrush and make a skinny-o, skinny-o. And then I'm going to recap my data up top here. So we're going to just create our loan data. So we have the purchase price. So we're going to say the home, let's just call it home cost. So we said the cost, we calculated over here with our Estimation Nundance. We estimated that we can buy a $532,498 home. And then we're going to put down, I'm just going to recalculate the down payment. So I'm going to put down, I'm going to make a down payment. And I'm just going to multiply it out here or I'll just take the down payment we calculated over here. That payment is down. It's not just a payment, it's a down payment. And so then we're going to say that the loan amount is going to be equal to the home cost minus that down payment. And then we're going to, we could put an underline there, font group and put an underline. And so that means the amount that we're going to finance is this amount. So now let's pick up our normal kind of terms. We'll pick up the rate over here, which we said was 6%, 6% on the rate and we'll build up our loan. We got the years that are going to be covered. We're going to say it's a standard 30 year, 30 year loan. And that rate needs to be percentized. So I'm going to make that percentization on it. So let's go and percentize it. And then the payment amount, the payment amount, I'm going to recalculate the payment based on this information. And I should come to you if everything is done properly, this amount, because that's what we based this number on, right? We got this amount. And then we use that to get to this number. I'll just recalculate it to get back to that number just so we can kind of give another double check using our payment calculation. Let's check it out. Check it out and see if it works. So I'm going to say negative PMT brackets. We're going to pick up the rate, the 6%. That's for a yearly rate. We want a monthly rate. So I'm going to divide it by 12. And then comma, a number of periods is going to be 30, but that's in years. We want months. So I'm going to take that times 12. And then comma, the present value is not the home value, but the loan amount, the amount we're financing and enter. So there's the 2873C told you so that ties out to that. Just like we thought, just like we said, that's what we said it was going to do. So let's make that then blue. We're going to go to the home tab font group blue and bordered blue and bordered. Now note that you could go online once you have that information and get your loan calculations. But again, I would use something like this as a double check. This is an online tool just to show you that you could check these out. So you show you all the resources you got available to you. Well, 479248, this is going to be a 30 year loan, 6%. And then we just say, boom, calculate. And there's the 2873, 33. You can make the amortization table. There it is. It's amazing. But I almost think it's easier for us to do it because I'd like to break this down on the year by year basis and possibly draw other areas from it from my from my budget and stuff that I want to tie it all together. So I want to use that as a check figure. I'm going to make it myself. I'll do it myself. I'll make my own. I'll make my own amortization table. It'll be way better. So I'm going to go to the skinny O over here. I'm going to go to the home tab format painter and I'm going to make a skinny R and we'll do our headers. I'll do this fairly quickly. I know we've seen this in the past. You're going to do this again. Yeah, because this is important and I like doing it. It's fun. Okay. We're doing it again. Interests that we're almost done. This is like one of the last times I'll do it. Loan decrease and loan balance. I'm going to make this black and white up top, black and white, home tab, font group, black and white. And we'll center this and I'll make these a little bit skinny. The stuff, the stuff, we're going to make that. So we're going to skinnerize it, which is the technical term for making it more skinny, which no one really has that technical term yet, but they're going to pick up on it soon because I made up the technical term. I'm going to take these three and I'm going to use the auto fill. I'm going to copy that down to three, six, zero. We're using the auto fill, driving it down. Auto's driving it down. Dr. Phil's going to do the calculation. It's an auto fill. Just get it. Auto fill, auto is the driver. And then Dr. Phil calculates because he's a doctor, even though he's not really like a doctor that does that kind of calculate. So then we're going to say this is going to be equal. This is going to be a roundup calculation to get the years round up, round up, round up. So we want to take this number, the number one, we want to divide it by 12. And then I'm going to round that up to the whole digit with a comma to the number of digits, which needs to be point one. That tells you to round it to the next whole digit. That's what the point one means. And then, okay, rounds it up and I can just put my cursor on it, double click the fill button, which is really a handle. It's a handle, but you can use it like a button now. It looks like a button, not much like a handle, but it's a fill handle button. Home tab, alignment center, we're going to center this thing. And there we have it. Let's put some zeros up top. And then we'll put the loan balance, the loan balance. It's all alone over there. It's a loan balance, poor thing. So we're then going to say that the payment is going to be equal to this amount. I want to be able to copy that down. So I'm going to say at four on the keyboard, dollar sign before the queue and the six, you only need a mixed reference, but an absolute one works and it's easy. And so that's a virtue. And then we're going to say this is the 479 loan balance times the rate of the six percent. We want that six percent not to move. So I'm going to F four on it, making an absolute dollar sign before the queue and the four, you only need a mixed reference, but an absolute one works. That would be for a year's interest. We're going to divide it by 12 because we want the month's interest. And then we'll do some subtraction payment minus the interest and enter no absolutes necessary because nothing's coming from the data set. We're all inside our table that we're working on. Then we want the prior balance minus the loan decrease to get the new balance or the new principle, whatever you want to call it. No absolutes are mixed needed because we're inside our data table. We want both those relative cells to move down as we copy down and enter. And then we're going to take those four cells. I'm going to double click the fill button handle. And then I'll go all the way down and just double check that it goes down to zero at the bottom. Is it at zero at the bottom? Man, it takes a long time to scroll that far. My scrolling fingers got a cramp. I can't scroll anymore. I'm going to go up top and say that this is going to be blue and bordered. We're going to go to the font group, blue, it ties it and borderize it. And then you could check these numbers if you so choose to the online tool. But now that we've got it here in our worksheet, do they work? Let me check. Yeah, I think that's right. Now that we have them here in our worksheet, we can use them to say build a month by month schedule. So now we'll build our month by month or our year by year schedule, which could help us to determine the amount of interest on a yearly basis. And that's going to change from time to time, the equity difference on a yearly basis, which will change from year to year. And that's going to be possibly important information for our budgets going forward. So I'm going to put my cursor on the skinny R and format paint it and we'll make a skinny Y. Why? Because the Y was too non skinny and we needed skinny. That's why that's why right there. And that's why we made it skinny. So I'm going to copy this one. I'm going to put that in Z, copying the headers, putting it in Z. We're going to remove the months because we don't want months. Now we're talking years here. We're talking years here. I'm going to delete that one. What happened there? And then I'm going to make it, I'm going to skinnerize the Z, which is the technical term for making it skinnier. It's the verb format of making it skinnier or skinnerizing it. And then this is going to be one zero, no, not a zero, one, two. And then we could just copy that down to 30 using the now you got to use the handle. We're going to grab it. It's not a button this time. It's a fill handle that we're going to grab and we're going to drag it. We're going to drag it down, get down here, drag it down. Then we're going to go over the top and we're going to make that centered. And then we're going to calculate the payments with the sum if formulas, because I wanted to sum up everything in year one, everything in year one, which is all this stuff, and then all that stuff, and all that stuff, some if formula, super cool formula, some if summit if brackets range, and you would think it would be the sum range here, but no, we're talking criteria range, which I would kind of think it should say criteria, but that's the one we'll do absolute soon. So I'm going to say comma criteria. It's going to be that number one. So this is what we're saying right now. We're saying if you find that one, and that like set of range, then I want you to do something to some other range, which is going to be comma the sum range, in this case, the payment range, the you you I want you to sum up everything that has like a one over here, but like some this one up in the payment range. That's what we're talking about. And then enter it, let's do it again. That didn't make sense. You can't explain stuff. I'll do it again, we'll do it better. We're going to say this is going to be the sum if brackets, we're going to pick up the range. So we can say that range, that's like the criteria range, comma, and then the criteria is that one. So if you find that one, and like that range, Excel, you listening Excel, comma, what I want you to do then is sum up this range this time the interest range. So the ones like all these ones, and then you can double check it, we can check it out with a double check. And we can say that adds up to the 28 so that worked out. So the interest is often more interesting too, because it it changes from year to year. So then this one, let's do it again equals the sum if summit if once again, Excel this criteria, like look in that area, comma, and then if you find this thing that's like matches that one and that criteria, then comma, I want you to sum up the range, the relative range, which is w this time, the loan decrease and enter, you could double check it to check, check, check, five, eight, eight, five, there it is done it, we did it again. Now I'm going to delete these two, and I'm going to try to copy it across. I'm going to try to make this so I can copy it across the SS. I don't want it to move SS minnow. We don't want that one to move. So I'm going to put F four on the keyboard dollar making an absolute this criteria I wanted to move down but not to the right we need a mixed reference. So I'm going to put a dollar sign before the Z, but not the three. And then this one, the UU, we want you to move over to VV and then to WW. So I'm not going to do anything to that one and enter and I could copy that to the right. And it will then do do the thing that we thought it was going to do. So there that's what I thought it was going to do and it did that. So then I'm going to select these I'm going to double click the fill button, and it copies it down and it's just it's just incredible. I must say and the loan balance I want the end balance of each time period here. So this is a men formula similar kind of thing, but it's a men men ifs men ifs that's the one that's what we want. So Excel this one's a little different Excel pay attention pay attention. We want the men range which is this range we're starting off with this one and calm us you can look into that range and then you're going to compare that to the criteria range which is this one. So you can look for the criteria and then comma and the criteria you're looking for is that one. So if you find that one in the criteria range, then I want you to take the smallest one that's related to that criteria in the men range. That's what we would so then it takes that one. Excel's paying attention. Thanks for paying attention Excel. So then I'm going to some we're going to let's put a sum at the bottom of this let's total it up total it sum it up sum it up and then we'll copy that across wow that's a lot holy moly is that right okay I'm going to then sum this up I can't afford that who why am I even doing this okay so there we have it so now of course the interest could help us to do the calculations for the taxes and so on noting that the interest is going to change from year to year and the loan decrease is going to show us help us with the equity because remember the equity is the difference between the asset amount of the house and the loan amount so the amount that we pay down on the loan is going to increase the equity as well as we hope the loan the home value will go up over time as well which hopefully will increase the equity too so when we do our calculations going forward we did our budgeting over here we then want to take a look at what we project to be our income statement basically going forward and we want to think about our balance sheet as well our assets minus our liabilities and we've got to think about the the the benefits of the home in terms of both the the non liquid kind of benefits which means the increase in the home value hopefully the equity going up assets minus liabilities meaning the home value minus the mortgage payment which is a benefit to us but one which we can't tap into to pay the month by month bills unless we were to sell the home or refinance and also consider the cash flow benefits that or you know our cash flow that we're going to be having so we can use then these year by year numbers are often helpful to do that and we can also just realize that we might have a tax benefit but that's going to change from year to year because the interest is going to be substantially different from year to year so you can't really just say well here's the tax benefit that I calculated in year one which is of course the highest tax benefit that you're going to have because that's the highest amount of interest that you're going to be paying throughout the entire loan so you want to consider that you also want to consider changes in the law with regards to income taxes we have no idea what's going to be happening going forward with regards to to the law these days things are don't don't seem entirely stable so in any case so now I'm going to do the same thing with a pivot table so I could select this whole thing up top but I can't select these two headers I'm just going to select this as the header and then I'm going to go all the way down I could do this possibly more easily with a pivot table so let's just do that because we want to show all the possibility I'm going to insert and then we're going to say just put a pivot table in that's as easy as that and I want to put it in the existing worksheet I'm just going to put it right underneath there right there and there's our pivot table and then we'll just build this out years clicking the years that's going to go in the values I want to pull that into the rows and then I just got to I don't want the months I want the payments the interest the decrease in the balance and it just builds it super easy that's way easier why didn't you do that first because I kind of like the formula format actually because it helps us to put formulas to and whatnot but this is quite nice too and then I can format this I'm going to format them so they don't look as formatted ugly so I'm going to hit the drop down here field range we're going to number format this thing to currency brackets dollar sign gone decimal down down okay okay that what that looks way better but you did it way too fast do I'll do it again second one value field settings we're going to go to the number formatting currency brackets dollar sign gone decimal down down okay okay that's better but I still didn't see it I still didn't let's do it I'll do it again with the third one we're going to go value field settings number formatting currency brackets dollar sign gone decimal down down okay okay okay I got it this time all right this is going to switch it up a bit on the last one what I got you know we're going to switch it up a little bit value field this time we don't want the sum we want the men we want the men but then everything else is the same so we're going to hit the currency brackets dollar sign gone decimal down down okay okay and then I'll make this a little skinner I'm going to skinnerize these cells from z to ad skinnerize them which is the technical term for making them more skinny which isn't really a word but I'm making up the technical term it's going to be it it's the technical term of the future so there we have it and and so then we can continue on with that information to possibly then construct our budget going forward drawing possibly from this table and in that format we could tie everything that we're putting together basically from our gross income line items that we put in our data set over here