Personal Finance-Housing: Purchase vs Renting

1. Introduction: Personal finance, housing purchase versus renting is a project-based course focusing in on decisions related to housing, including the decision to purchase or rent. We're gonna be working through multiple practice problems. Down below, we have downloadable Excel worksheets, which can be opened up either in Excel or possibly using Google Sheets. Each of these will typically have at least two tabs, one with a completed problems. So you can see where the end result will be and possibly use it to deconstruct and work backwards. The second tab is where we will be working in the practice problem along with, in a step-by-step fashion, the instructional videos. The end result or the end of project, will be the Excel worksheets as the final project. 2. Estimated Monthly Cost Purchase Condominium: Personal finance, excel practice problem, estimated monthly cost purchase of condominium. Prepare to get financially fit by practicing personal finance, who we are in our Excel worksheet. If you don't have access to the Excel worksheet, that's okay because we're basically building this from a blank sheet here. If you do have access to the worksheet, there's three tabs down below, example tab, practice tab, and a blink tab. The example tab in essence being an answer key, let's take a look at it. Now. We've got the information related to the purchase of a condominium. On the left-hand side, we're gonna be populating the estimated monthly costs and then take a look and build the amortization table, and then build a summary table with formulas and with the use of a pivot table. On the second tab, the practice tab, we have some of the cells that are preformatted. So if you don't want to spend as much time formatting the cells, you can use this worksheet where the cells are already formatted. And then if we go to the tab to the right, this is where we will be working in the blank tab. Now, if you're just working on a blank sheets, you might want to repopulate this information. What I would suggest to do is select the whole sheet first format for sheet, and then specialized formatting for these cells as needed, e.g. the percent and so on. The format that I use, if I was going to select the whole sheet as I right-click Format Cells, I usually go to the currency is what I'm usually on. And then the bracketed numbers, I remove the dollar sign. And that's basically going to be our starting point for the most part. And then I'll make any adjustments we need to be making by adding percentages when necessary or adding decibels when necessary. So our information on the left-hand side, purchase a condominium, the mortgage is going to be 250,000 years, 25, The rate is 8%. Property tax per year is gonna be the 2,200 property insurance per year, 600. And the association fee for the condominium is going to be $300. We're focused in on what we think their monthly expenses related to the purchase will be will have more comprehensive problems later on in this section. So I'm going to start off with a header which I'm gonna call monthly housing payments. Monthly housing payments. If I misspell anything, I apologize. I'll go to the spellcheck at some point in time. I'm going to make this cell a little bit wider. Note that I might not need to go all the way past here, meaning I might have some overlap and be okay with that. I'm going to imagine that I'm gonna be using these two cells in my calculation down below. So I'll widen that cell if I need to. But just as a general rule, I'm going to say, okay, that looks pretty good to start out with. I'm going to highlight from here, from D to F. I'm going to make this a header, which I usually do by going to the Home tab font. I go to my bucket, I'm going to make it black and then the text white so that the headers will stand out with the black and white header. Then inside I'm going to say first we've got the monthly mortgage payments. That's gonna be the first thing that comes to mind when we got this loan. That's gonna be a monthly costs we're going to have to spend. So we're going to say, Alright, month, monthly mortgage payments. And now here I'm going to put this in the outer cell so I could make this a little wider. But I'm gonna put this in the outer cell because I'm going to have some sub calculations later on inside. So I'm not going to widen it yet. I'm going to use a payment calculation to do this. Now, I usually do that with a negative instead of an equal. So I'm going to say negative instead of equal. Technically, you should probably put the negative actually inside the formula, but this is the easiest thing to do in my opinion. So it will come out to a positive number. I'm going to stay negative. And then the PMT shipped nine. And now we've got our little box here to calculate our payment calculation. So I'm going to pick up the rate which is over here. Remember you always want the information somewhere else in your dataset so that you can change your dataset and you'll hold worksheet will change for you instead of hard-coding the 8% inside the formula, in which case it's a lot more difficult to change the formula. Now this is for a year. So every time we talk about a rate, we're talking about yearly rates because we don't talk about monthly rates because they would often be quite small and we want to talk about reasonable rates. So the convention when we talk about rates is a yearly rate, but the payments are monthly. Therefore, I have to make that a monthly rate. So I'm going to take that and divide it by 12. Notice, even though that might be a small rate here, if fine, in Excel to calculate it, excel can calculate it exactly by using that ratio. And then I'm going to say comma, that takes us to the number of periods. We're going to say this is a 25 year loan that we want to make them into monthly payments. So I'm going to pick up the 25 and then multiply it times 12 for 12 months, a year, there's 25 years. So then I'm going to say comma, we've got the present value. That's gonna be the actual loan amount. So that's gonna be the 250,000. And there it is. I'm going to say Enter, That's the 1009 30. Now I typically like to verify that double-check it when we calculate the amortization table, which we will do shortly. Now, the other things that we have involved here when we purchased the home that we want to make sure that we take into consideration are things like property taxes, if there's any association fees and any insurance e.g. so that we're making sure we kind of budget out the full amount that's going to be in place. You also might have maintenance that you want to average out and stuff. But then it guys, we're going to say this is gonna be the property taxes per month. Property taxes per month. Now, if we've estimated the property taxes per year as we've done here, I'll just divide it by 12. So I'm gonna, I'm gonna do the full calculation here so we could see it. I'm going to pick up that amount or pick up that text by using an equals. I'm going to pull this to the inside. That's why I put a colon up top by the way, because that means I'm gonna do a sub calculation and I'm going to pull the sub calculation inside these little technical things on how to build a table is kind of nice to know because you can, it'll help you to build and read tables if you can use these kind of sub Colin's are some subtitles and whatnot properly. So I'm going to say this is going to equal the 20,000, 2,200 for a year. And then I'm going to say the months in a year, months in a year or 12, which I can just hard code because that number is what it is. I'm going to put an underline here by going to the Home tab font Chrome and underlying put a line underneath it. No one has an underline, what we call it. And then I'm going to just copy this one down here. I'm gonna get rid of the colon because I'm going to pull this to the outside. This is the sub calculation. I'm just going to divide these two out. This is going to be equal to 12,000, 1,200 or 2200/12. I'm going to stop saying just keep saying just I'm just gonna do that. That's just what I'm gonna do. That's just what we do. And then I'm going to select these and we're going to go up top here, Home tab Alignment, and let's indent it. So I'm going to indent it. So now it's a sub calculation with a colon. It's inside and then I indented it and you might double indent this last one to show that now we've pulled that back out to the outside. There's a sub calculation formatting. Then we've got the same thing for the property insurance. So I'm gonna say property insurance per month calculation, colon sub calculation down below, because we got it for a year again. So I'm going to equal this year that description even over in my formula. Notice it's too long now. Now I need more space. So now I'm going to pull this column D out a little bit, put my cursor between D and E, pull it out right there. That looks good. And the amount then per year is going to be if that's $600. And then I'm going to say this equals because I already have it up top the months in a year 12 and this equals the 12th. So I could use some formulas since I already did that, save me some typing time. Home tab, font group, and underline this, I'll just copy this down and delete the colon, going to Control C. Paste it down here. Here's the, that's what I said I was gonna do. Here's me calculating it, and here's me doing it. Here's the end result, which I'm going to pull out into the outer column. This is going to be equal to the 600/12. I said I was going to do it and then and then I did it. That's how I do things. I said, this is what I'm gonna do and then I do it. And then there it is. There's the Home tab Alignment, indent. Let's indent this one again. Home tab Alignment and indent again. So then we have the association fees that's on a per month basis, so I don't need to do any special calculations. It was $300 per month. I'll just put that right into the outer column. No sub calculation necessary this time. And so that'll be the $300. Let's put an underline here. And so we're going to say that the cost that we have monthly housing payments or costs, let's say payment sounds like it's just a mortgage payments. But let's keep it there because these are all related to the housing payment. So I'm going to sum this up equals the trustee, sum equals SUM the trustees some function control dime. I'm going to hit the up arrow and then I'm just going to hold shift. And we're just going to we're just going to do that. Stop saying just stops. We're just doing that. That's just what we do. And then I'm going to dealt with double underline this Home tab, font group, and double underline it. And then I'm gonna make this blue. I'm going to select the whole thing for some formatting purposes to format it nicely. Let's right-click on it. And then we can go to this bucket here, or you can go to the bucket up top. If you don't have this blue, you go to More Colors down below standard and there's the blue right there. Why did you use why do you use that blue? Because that's what the accounting is fun or the Excel is fun guy used. And so that's what I use. There's no real reason I'm gonna go to the Home tab font group. We're going to hit the drop-down. We're gonna go down to the all borders. Let's put some borders around it. There it is. Looks wonderful. It looks just spectacular. Now let's start. Let's build our amortization. One table so that we can just practice building it. So what I wanna do is I want to make the same space between here and the next thing I'm gonna do, I'm not going to build anything right next to this one and the next cell, I want to make this a skinny cell then that needs to be a skinny. So I'm going to put my cursor on this skinny. So I have the same width, this guineas. And then go to the Home tab, clipboard, paintbrush it. And then I'll just paint brushy this one. So now we've got the skin is, I'm going to hide from C to F because I don't need this information here actually, I might need that. 19. Let's hide it for now. I'm gonna put my cursor on C. I'm going to drag on over to F and then right-click and hide as I build my amortization table. So here we go. Amortization table. So I'm gonna, I'm gonna do my headers first. Now note, I think I'm going to need 22 rows to have some headers. I'm not going to wrap the text because if I wrap the text and it makes the cell one like wide and I don't like that. So I wanted, so I'm just gonna, I'm just going to basically use the two rows here. Unless I was building an actual table that I was going to insert a table, we will insert a pivot table and I'll show you the problem with that. But if you were inserting the table, then you want the header all in one row. Even if you have a really long header and you'd probably have to wrap the text. So I'm going to stay that first. We're going to say this is going to be the year. You're gonna be my headers and then months. And then payment. Payment, that's fine. And then in tourist and then here's where I need the two I'm going to call it loan decrease. I used to call it principal amount, but I always misspell principle in principle. So now I call it loan decrease because I can I spell that more accurate, more time. Some people don't tell me you just did the wrong principle. Whatever, you know what I was talking about, That's what I tell them. But then they still they still pick on me, so I don't do that anymore. Any case, I'm going to highlight this now and we're going to then go to the Home tab. We're gonna go to the Font group. Let's make this our header stuff, making it black and white, black and white. And then we're going to go to the alignment and center it. There we have it. Now. We're going to need our number of payments. So how many payments do we need? We've got 25. We got 25 times 12. We're going to need 300 payments monthly. I'm going to start at zero here and then one. And then I'm just going to select these two. That's just, we're just gonna do that. That's just what we're gonna do. Okay, and then I'm going to select these two. I'm gonna put my cursor on the fill handle, That's the fill handle right there. Grab it. Grab it. Get a good grip. Get a good grip. Put your finger, chalk up your left finger because we're pulling that thing down to 300. So don't let go. Don't let go. We're going down to 300 down here. You can see the little number thing. And we're going right there, right there. Boom. Let's center it while we're here. Home tab, Alignment and center. There we have it. Now we're gonna do a fancy little calculation for the years because these first 12, I'd like to know what the year-by-year. Otherwise, I got to divide it out down below. This is a fancy little fancy maneuver that she might not, might not be aware of that we're gonna do right here, right now. So pay attention. So we're gonna say this is going to be equals two and round up, round up, and then shifts nine. And so I'm going to round up, I'm going to take this number. I'm going to divide it by 12/12, but I'm going to round it up. So that should be that. So it's going to round up to one here because it's one-twelfth, right? And then comma, now, you got it. You got to round it to the 0.1 digit. That'll tell you to round it to one. That's what it means to round to one with the Roundup. Let's do it. Boom. And I'd like to add a couple of decimals to see if it does it correctly. And then if I copy it down, see how this is all now one, because it's rounding up. It's rounding up for twelfths, rounded up to one CV for fanciness. I'm going to take this all the way down and see if it does it correctly all the way down. I think it is, I think it is super impressive. It's not just impressive, it's super impressive. I feel like now let's get rid of the decimals, number of group. Removing the decimals. That's the wrong way. I increase them and then I'm going to center it and the alignment. Okay, so there's that. Then we need the payment calc, we need, Let's pick up the loan balance on period zero. Let's do that first, that equals 250,000, and then I need my payment calculation, which is which I hid. So let's unhide it to get my payment calculation. I'm going to put my cursor on B and go on over to H B to H Buck, and then right-click and unhide. And that's that there's the amount not the fault, not this amount down here, but this amount because we're just talking about the amortization table. I'm going to say this equals that amount. I want to be able to copy it down. So I'm gonna make it absolute by selecting F4 on my keyboard or dollar sign before the F and the two, you only need a mixed reference, but an absolute reference will do and it's easier. So that's what we'll do here. Calculation for the interest is going to be equal to the prior balance. And then I'm going to multiply that times the rate at 8%, that would be for a year. So if I enter that, that'd be if it was for a year, but this is only a month. So I've got to take that whole thing and divide it by 12. I don't need brackets because it's the order of operations. So I could put brackets around this, but I don't need to because it's going to multiply before it divides. That's how it works. I learned that I'm, I'm so then this is going to be a subtraction of 1930 minus 21667. There's rounding involved here because we took the pennies off, be aware of. And so that's gonna be the loan decrease or the principal decrease. And then this is the loan balance or the principal. But not like a not like a principal at a school. You know what I mean? It's like the loan principal. That's why I don't use it though. So this is the 250 minus two minus b263. So there's our new loan balance. Now, we're gonna do this over and over again. If I copy this down and I should be able to copy it down, but it's going to have a problem. I think if I copy this down, we made absolute, but this is a problem. What did it do? It moved this cell down. So anything that's outside of my table I'm going to undo, I need to make absolute. So this is referencing something outside of my table, meaning that 8% that out that B5 needs to be absolute. So I'm gonna say F4 and the keyboard. This one is fine because both items are inside of the area I'm working with. It's not coming from my dataset. No absolute necessary. So now I can select these, I can drag it down or I could just double-click on the fill handle at this point. And then it populates for us. We're gonna go all the way down to the bottom. And if we get down to zero down below, then that tells us, that tells us, that's a good indication that we did it correctly. Now, we also know that you might also say, Hey, you can build this kind of thing with an online tool. And you can like he could check online tools to build this. So here's a loan calculator. I'm not advertising this website or anything, but this is, you can find many websites, 25,000 and it'll calculate this. I like to use this is kinda like a double-check for, for me because, because Excel is way more flexible if you learn how to build it in Excel. So notice that this does have some of these bricks here to help you out. But once you get this in, Excel, could do a lot more with it. So I would use these online calculators kinda like as a double-check. But if you really want to dig down on something, I would put it in Excel because you've got more, you've got more flexibility. And let me try to show you what I mean by that. So if I go back on over it just to check, this just came out with this came out with a percent first couple payments, the payments of one-sixth six, the payments, the interest is the one-sixth six for 91, for the second one. Let's just check that. 166491. So yeah. So what rounded there. Okay, So that looks good. I'm gonna make this, I'm going to make this all blue and put some borders around it. Let's put a border around it and make it blue. So this kind of confirms to us that this calculation was correct. That's one reason I like to do it. If you just get this number, which is often the only number you will get from. If you're talking to a broker or something like that, then that's not really given you a whole lot of information because it's nice to have that. But you'd also like to know what the, if you're doing a full calculation, you're going to want to know what the interest is per year and the difference in the breakout between the interest and the principal, as we'll see in future presentations, also be broken out differently as the years, as the years pass. So you might want to see that e.g. on a year-by-year basis. So now let's try to summarize this table. And this is again, one of the things you can do in Excel. You can't do as easily with some of these other tools that you see on line. Let's try to summarize on a year-by-year basis, the payments, the interest, the loan decrease, and so on. So what I'm gonna do is I'm going to, I'm going to copy some of these headers. I don't need the month header. I'll copy this whole thing. Well first let's copy the skinny. I'm going to take this skinny right there. And I'm going to go to the Home tab clipboard and take that skinny and make the same size skinny on n. And then I'm going to take these headers, we're going to copy it. Copy the header is and I'll put that on. Oh, but I don't need the months. I just want the years. So I'm gonna take this whole column and delete it. Delete that column. So there we have it. And now we want the years to be 123 down to the 20. So I'm going to select those three. Put the cursor on the fill handle, bring it on down to 20. Did I say 20 or 25 was a 25 years? Let's say it was 25. Is that what it was? I can't remember. Yeah, 25 years. I know what I'm talking about. Let's center this thing, Home tab Alignment, center. Okay, so now we want the payments for the whole, for the whole year. Now you could copy these or you could just take one payment divided by times 12 because the payments will be the same, but the interest will change from year to year. So I'd like to get a calculation that can pull this from this table. So we'll do it this way. You can also do this with a pivot table, which we'll see shortly. But I could do it this way. I could say, let's use an If. It's called a sum if calculation. So what I wanna do is say, if you see that number over here on the years, on the year is not the month. I want you to sum everything in this column related to this one that has a one next to it. So that's gonna be our matching calculation. I'm going to use this one to tie out to this column and sum everything in this column where this one is tied out to this column. Okay, let's see if that makes sense. So I'm gonna say this is gonna be equal to the sum if calculation. So there it is some IF. And then we've got the first one is the range. So we want to select the range. And so this isn't the sum range. This is the range of conditions that basically that I, that I need, which I want this one's in. So that's gonna be this range. Now you probably should not, should just select the table here, but I'm going to select the whole column because there's nothing underneath it. So I'm just going to pick this up, be careful of doing that because if you put something underneath it, which I'm not going to do, then that can mess up your formulas, right? But I'm going to say There it is, There it is. That's a nice, easy calculation. I'm going to say F4 on the keyboard so I can copy that one down because that range isn't going to change when I copy it down and across. So I'm going to say comma. And then the criteria is this one right there. I want you to search that range for this number one. And then I want you to go and I'm going to say comma. Once you find everything without one in it, I want you to some not that range of ones. I want you to sell them everything in the payments column. So I'm going to select the whole column, which again, be careful in selecting the whole column if you have something underneath it. So there is that. There it is, I'm going to say, okay, let's enter it and see if it does it. So it should have, it should assumed these. So it looks like it did it correctly. Let's try it again with the interests here because the interests will be different from year-to-year and it looks like I shouldn't be able to copy this down. And there it is, It's doing the same thing. And it's super impressive. If you're not impressed, like you shouldn't be, it should be, you should be impressed. So this is going, Let's do it again. This is going to be equals sum, sum. If so, then I'm going to select a range, which is this. The range is Hs, Hs, F4. F4, not just for F4 to make it absolute dollar signs and then comma, the criteria, It's gonna be this number one again, that number one comma. And then the sum range is going to be this time the interests column, column K. And then enter. I think that's gonna do it. Notice this one today. Yeah, that looks good. Then that should have summed up these. So that looks good. If I copy it down. Boom, it should still, now it should sum up, these are the 2s. So you get this nice, nice table which can tell you what the interest is. That'll help you to calculate the tax implications and whatnot on a year-by-year because it's going to change from year to year. You gotta be careful of that. And then we'll do the same. Let's do it again for the loan decrease in the principal. So let's gonna be equal to the sum if summit, IF, and then we're going to say the range is going to be this one like we did before F4, so we can copy it down comma and then the criteria is going to be this and then comma. And then we're going to say the sum range this time is the loan decrease. So I'm going to say, There it is. Now I could select F4 to copy this down. The fact is it's going to be the same column because I choose the whole column. So the ephors, the absolute is not really doing anything, so I'll keep that as it is. And then I can copy that down and alone decrease then should be this. If I sum visa, There it is, That looks good. Let's check the next year ones. So there's the 3544. Looks good. Okay. So this one is going to be a little bit different than last one because I want the lowest balance. Now let's do this again. This time I'm going to delete this and I'm gonna say, is there a way that I can fix this one? To copy it to the right. Copy it to the right. See, I can't do it because it did something funny there because it because it moved over this so to the right. And I wanted to keep it at one. So I just need to fix that by making a mixed reference. So this one right here, it's moving it to the right. That's that's a problem. So I want it I want it to move down, but not to the right. So that means that the three needs to move, but the 0 needs to stay the same. So I'm gonna put $1 sign before the row, but not the three, which is called a mixed reference. And then I can copy this one to the right, I think. And then I also should be able to copy it down. I can just double-click, boom. And so you can, now you've got a lot more information and a nice condensed table. This one we want the Indian balances as of the end of each year. So I want like this one, that's the ending balance as of the end of the year and in balance, so that means I need a Min function. I needed, I needed to give me the Min. So I'm gonna, I'm gonna do a Min if calculation equals min, mins if Men, if there it is, That's the one we want mins. If, take the smallest one if, and then we've got a little bit different on the range kind of things. The last one, so the Min range that we want is going to be this one. So I'm going to select the loan balance range and then comma for the criteria. The criteria is going to be this item. I'm sorry, this is the criteria range. The criteria range is gonna be h and then comma, and then the criteria, which is the one. So there we go and then Enter and it picks out the lowest one right there, the 24067. I'm going to copy that down. And you get this nice table summarizing this on a year-by-year basis, get into your Indian loan balance at the end of each year, and then your yearly interest calculations and payments which you can get the payments fairly easily, but the decrease in the loan, which you can also think of as possibly an increase in your equity as the loan balance basically goes down. In other words, you can think of basically the equity being the difference between the the value of the home and the amount that is financed if the value of the home stayed the same than the amount that you pay down the loan. The decrease from the principal is going to be an increase in the equity or value of the home. Although you can't really realize that until you actually sell the home or refinance the home. And then of course, if the home went up in value or the condominium than that Could you can think about that. It's like an increase in equity, in essence, as the value goes up, those are the two factors that are going to widen the gap between the value of the home and the amount that you owe on it paying down the principal portion of the loan number one. And hopefully if a value going up. Now, I can also do the same table, even easier in some cases by making a pivot table. So let's just do that one real quick. I'll just make a pivot table. Now, on a pivot table, I'm just going to select all the data, but I can't select this first row. I've told you this was a problem of me doing this two column thing. So I can't pick up that first column, but that's okay. I'm just going to pick up these as the headers and then go all the way down. I'm just going to insert a pivot table. So we'll select that whole thing. Then. We'll gonna, I'm gonna go all the way back up. I'm going to put the table inside the same area. So I'm going to say insert and let's say a pivot table. A pivot table. And maybe I'll put it underneath. Let's put it down here. I'm gonna put it in the existing worksheet. I'll put it, let's put it right there, right there in that cell and 0, 29. So I'm just gonna put it there, boom. And then it gets a little bit tricky to deal with. But once you do this a few times, it's pretty easy. I'm what I want the year. So the year it puts over into sums because the years that number. But I want to pull that over into the rows. That's the only tricky part. And then everything else will populate as you would think once you do that component. Meaning, I want I don't want them months because I just want the years are now want the payments, the interest, the decrease, and the balance which they populate as we would expect. On the right-hand side, summing up the same data, It's kinda ugly and the format, but it gives us that same data Nice and easy. Now the pivot table is a little bit tricky because you got to refresh it. And when you change the data on the right-hand side, it can be a little bit more tricky than something like this. Where were the formulas will update automatically? In other words, if I change the data on the right-hand side, the pivot table, but you've got to update it. It can be a little bit more finicky to kind of update than formulas. So that's one thing to just kinda keep in mind, but she can make a new pivot table easy if it gets, if something happens to, if there's a problem with it, by just doing this again, right? That's PivotTables pretty easy. Probably easier than the, than the formulas once you get this kind of down. And then we just want to format these cells. I got to do a little bit more to finish this up. One way to do that is each of these areas, I want to format the cells. So I'm gonna hit the drop-down value field settings. And I'm just going to change the format by going to the number formatting. And then go, I like go into currency, the brackets and then get rid of the dollar sign. And I'm gonna get rid of the decimals. So now I'm going to say, Okay, and Okay, so now it's formatted closer to what I like to see this one. I'm going to do the second one, so I'm gonna hit the drop-down. I'm going to go to the Value Field Settings. And then we're gonna go to the number format. I'm going to say this should be a currency brackets, dollar sign removed, decimals, gone as well. And okay, and, okay, so that one looks better. Let's do the third 1 s from the bottom Value Field Settings Number Format. And I'm going to say that this is going to be currency. Brackets, get rid of $1, sign, get rid of the decimals. And okay, okay, now the last one I got to change a little bit because I don't want to do the sum. I want to do a Min balance. I'm going to do the field. And I want the men here, not the sum but the men. And then the number format, and then currency brackets. No dollar sign decimals gone. Okay? And then, okay, so there we have it. So now this table should mirror the table. We have up top. You can see, and that's just another way that you can build it. I'm gonna, I'm gonna make these cells a little bit smaller so that they're not so wide. I don't like just a waste of space. Stop wasting space like that. Then you can, of course you can, you can, you can wrap the text here, but be careful of wrapping the text because it causes issues. Because this whole cell we'll get we'll get fat and everything else will be will look funny. So I don't like that's why I don't like doing unless I have to, but you have to deal with the table. Sometimes. That's gonna be, that's generally it now again, so this summarized data on the right-hand side, often useful when you do more advanced calculations and you're trying to think about what's gonna be the tax implication from year to year, not just in year one. What's gonna be the equity calculations on a year-to-year type of basis. It's also kind of nice if you're comparing different loans to try to see a summary of the loan, year-by-year breakout of the loans, which again is difficult to do if you use the online tools. The online tools are great. But I would use them as an estimating tool if you get more into more complex kind of thing. So that's the, that's my general idea. So we'll keep on practicing putting together those amortization tables in future presentations. 3. Estimate Affordable Home Purchase Price Part 1: Personal finance, excel, practice problem, estimate, affordable home purchase price, part number one, prepare to get financially fit by practicing a personal finance. Here we are in our Excel worksheet. If you don't have access to the Excel worksheet, that's okay because we basically build this from scratch here. If I do have access, there's three tabs down below, an 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. We have the information on the left-hand side. We're going to use that to build our tables. On the right-hand side, we're going to start at our gross income and think from the perspective of a financial institution using a rate to try to determine how much loan we might be able to get based in part on principle part on our monthly income. Now this is just an estimate. You would want to be looking at your financial institutions to see what conventions they might use to do a similar calculation. If you're working out estimates for how much loan you might be able to get. Once you've determined how much loan you might be able to get, you can take into consideration that down payment you might have to make to think about what the price of the home will be. So you can kind of start from there and go up to the price of the home. Notice this is also a little bit different than you might think about what you can afford in terms of monthly payments, which you might calculate by actually doing a monthly income statement. Because they make, the bank might use some different conventions than you might use when you're basically doing your monthly budgeting and when you're thinking about how much loan you might be able to get, you want to use the conventions of the financial institutions. Make sure to talk to the financial institutions to understand the conventions that they will use to require down payments and so on. So that you can start to estimate this stuff. But the idea would be we start with the income and then we try to get to the amount of the home price that we might be able to afford. The second tab is gonna be the practice tab. And that's going to have some free format itself on the right-hand side, which will be a little bit less work for actually format in the cells. And so you can use that tab if you would like. But we're gonna go to the blink tab, which has just this information on the left-hand side and we'll build everything else from scratch. So this is just a scratch that we have. And we're going to build everything from it. If you have a blank worksheet, you might want to format the whole worksheet. I would start by formatting the whole worksheet and then adding this information so that you can have your data up top and then you can build it from there. My starting format, just so you can see it here. I usually format my cells, usually they're at currency brackets. And then I remove the dollar sign. And then if I need to add decimals, then I'll add decimals. If I need to make some a percent, then I'll make it a percent. That's my starting point, oftentimes. Alright, so here we go, Let's build our table. We're gonna be thinking about the affordable monthly mortgage payments. So that's gonna be our first header. Let's put that up on the header here. I'm going to say affordable monthly mortgage payment. And then I'm going to make this a little bit wider. I don't have to make this cell as why does the whole header, because I'm going to have at least two columns probably where I calculate. So I've gotta make it at least like this wide so that these two columns will cover the end. That's kinda my thought process on it. Then I'll make this into a header format by selecting those three cells. And I make my header format go into the home tab font group, hit the bucket drop-down, I'll make it black and then the text white, so that there'll be distinct from the header from the rest of the stuff. So now we're going to look at the monthly gross income we can spend a month. So we're gonna say monthly gross income we can spend, I'm going to hit brackets. That means that I'm going to have a sub calculation. I'm going to pull this to the inside for this calculation, I'm going to look first at our monthly gross income, the 3,500. I'm going to pick up the 3,500. And then we're going to say that we're going to multiply that times. But sometimes the banks, you might hear this term, the PIT, either principal, interest tax and insurance rate. These, these are rates you want to, these are kinda calculations that you'd want to, again talk to your financial institution about to see how they're gonna be giving you the general calculation. So you can run some estimates on this with the current the current regulations out there. I'm going to pull that over now. That's a really long thing. So I could pull this over to the right. I'm not going to I'm going to type over it a little bit. Otherwise that's just way too long. So I'm gonna put my cursor here and I'm gonna say this equals 38. You can also abbreviate it. You might just put the PIT, which is probably the better thing to do. I might just put the PIT rate, maybe rate, and then I don't have to make this so long. I can make that a little shorter. And so I picked up that rate. So now I got to make it a percent so I can see it Home tab number of group making it a percent. And then I'm going to underline it font group and underline. So then I'm gonna put this to the outside, so I'm going to copy this. So I said what I'm gonna do with a colon. I pulled it to the inside and I'm going to remove this at the end, the colon, and I'm going to put this on the outside and then this is what we did. So now we're doing what we said we were gonna do. We're gonna say this times this. And that'll give us the monthly gross income. So now you can kinda see what there. Let's do some indentations before I discuss it here, a little gonna go. I'm going to highlight these home tab Alignment indent. I'm going to put my cursor here. Let's spell it right. Spell it. At least more Writer. I'm spelling it righter than it was. It's more righter than it was before. I feel like. So let's let's then indent this one again. So there we have it. So you can see kind of the idea. So the bank is going to say, okay, this is your gross income and this is the rate that we're gonna be able to apply to it, the 38%. And again, you want to talk to your financial institution on that to think about what we think you can afford. And that's going to include the principal interest, taxes and insurance. Now, they might alter the rate for basically other debt that might be included in that rate and have kind of like a standard rate that would include basically other, other debt in it, e.g. as well. So you want to get an idea or feel for the kind of standard calculations that your financial institution might use. And then you might need to adjust it then for things like the monthly property taxes and the insurance. Why would you do that? Because this number up top is including if we're including the property taxes, insurance and other debt and VAT number, than what I wanna do is pull out this stuff that's being applied to it and just look at the amount that's applied to the loan so that I can then use the loan amount to back into how much how much loan I can pick up. So let's see what that looks like. I'm going to say Okay. Included in that number is the is the monthly property taxes and insurance. That's what they included in this rate. So I'm going to subtract that out so I can get to just the mortgage payment amount that they're applying to the mortgage payment, monthly property taxes and insurance. And then I'm also going to take out other debts, meaning this number also this percent might include other debt that they kinda packed into that percent number, meaning this number is including other debt as well that you might have. So again, I want to pull that out if it is included, pull out the other debt so that I can get them to the actual amount that is related to just the mortgage payments. So this would be the affordable monthly, affordable monthly mortgage payment. And so now we're just looking at the mortgage payment amount. So this is just an estimate on an approximation. You want to look at your financial institution to get it, to get a feel for what their calculation looks like, what is included in this number so that you can basically get down to trim it down to we're getting down to the amount that's actually for the mortgage payment. Once we have the mortgage payment, then we can back into how much alone we might be able to get. Once we know how much low and we might be able to get, then we can consider the down payment to back into the actual home purchase price. So we're working up from the income level to the purchase price. So I'm going to sum this up, which will subtract it, because these two are negative numbers the way I formatted it. So summing this will subtract it out. So there's the 86860, I'm going to say that's the amount that we're assuming is applied to the actual mortgage that I think the bank would be willing to basically give us alone. For. Now, I'm going to format this, I'm going to select this. Let's make it, let's make it font. Let's make it this blue. If you don't have that blue, you go to More Colors. Standard and there's the blue, that's the Excel is fun guy blue. And then Home tab font group. Let's make some borders around it. So there we have that. Okay. So now I can I can I can back into the loan amount. Now there's a couple of ways you might do this. So e.g. you might say, I don't know how to really back into the loan amount, but I knew what the payment calculation. So you might say that you might use that calculation. So I'll show you how you can use a tool to do that. If that, if that other comes out the other way you want to look at it, you say, well, this calculation, this is the actual payment. And if I know that calculation is equals PMT, the payment calculation. If I was to calculate this number, that's the end number I want to get from the payment calculation. And the thing that I'm trying to get to is this present value. And that's how you can determine, well, I can say, well, is there a function for the PV function, the present value? Yeah, that's the function that you could use. But let's use this payment function first to just kinda see. And I want to also use this goal seek out to see, to get an idea of how this would work. So I'm going to say, let's estimate a loan amount first. I'm going to say, well, what if the loan was 100,000? And I'm going to try to use like a, like a similar to an algebra technique to figure to kinda back into what the what the loan amount should be. I'm going to I'm going to adjust this loan amount. I'm going to start out with a loan. I'm just going to put here I'm just going to pretend it was for 100,000. Then I'm going to try to recalculate this number, the 650, the payment, and then I'm going to change this loan using a tool called Goalseek to be the proper to be the proper tool to get to that payment. So in other words, I'm going to use the payment calculation, payment cow accumulation, using this number and the information on the left, I'll get something other than 860. And then I'll change this number using a tool in Excel to kinda back into enforce this number to be the proper number. So we'll change this number to the proper number using kind of like algebra. There's one unknown we don't know, and I'll just use the trial and error method of the algebra, right? I'm just going to keep on plugging in numbers until we get to the right number. But I'll use Excel to do that. And so it's a useful tool to be able to see that's called Goal Seek. So I'm going to go over here. I'm going to first use a payment calculation to try to get to this payment, but it'll be different because I'm just guessing the loan amount here just made up a loan amount. I'm going to say negative PMT shift nine. The rate is going to be then this rate over here at the nine per cent. And then that's a yearly rate. Whenever we have a rate, it's gonna be a yearly rate. So I've got to say I'm want to make it monthly dividing it by 12, and then comma, the number of periods is gonna be 25 for the years, but that's years now what months? So I'm gonna take that 25 B6 times 12, and then comma the present value. Now, that's the one we're looking for that we just guessed that we're going to then change, that's the 100,000. I'm going to say Enter. There we have something that's not quite right. So now I want to make this number be 860 by changing this number, which I could say, okay, well what was 101000 and so on and so forth. Or I can use a tool called Goal Seek to do that. I don't need to be on any particular cell to use it. So it's useful to know whether this tool is. So I'm gonna go up to the data up top, and then I'm gonna go to the forecast and the what-if analysis. What if Goal Seek. Let me tell you a what-if scenario. What if I set this cell? I want to set this cell right there to be. I have to hard-code this cell. I want to set that cell to be 860. I can't click on the 860 here. I got to type it in. I want to make that 860. I want to do that by changing by changing this cell. So change this cell to whatever it needs to be to make that sell 860. That's what we're saying. Okay. Okay. And then it just does trial and error and then it kinda facts into our loan amount. So that's one way that you can do it. And I just want to show that Goal Seek way. It's not the most optimal way because we'd like to use another formula to do it. But I just want to show that Goal Seek because that could be useful, a useful tool. So let's do it another way. Another way we can do it is the oldest, the loan or mortgage, mortgage amount is to say, okay, well, if I use this formula, what I'm looking for is that PV, the present value, that's the loan amount. So I could just say, well, I'll just reverse this and use the PV function. And that should get me there. So I'm going to say, Okay, I'm going to say negative. So that instead of equals, so I'll flip the sign. That's the way, the easiest way to do it's probably not the most proper way to do it because you put chip with a negative inside the formula somewhere. But I typically put it in front because it's pretty easy to do. And so I'm going to say negative present value brackets. I'm going to pick up the rate, which is the 9%, That's the yearly rate again. So I got to divide it by 12 and then comma, the number of periods is 25, That's years and I need months. So I'm gonna multiply that times 12 comma. And then the payment amount we have up top was the 860. So I'm going to say there's the 860 that should get me to the present value, which is the loan amount. So I'm going to say Enter. There it is, there's the loan amount. Again. Let's make this, let's make this fancy. Put some blue, some borders. So okay, so now we've got the loan amount. So now if I assume that the bank is going to require me to put 20% down, note that this could change as well. So you got to talk to the institution. This would be like a classical kind of standard, but we've gone through cycles where the bank had no money down and then they have too much money down and then it goes. So it just depends on the cycle you would think that it would even out but kinda learn our lessons over the, over the years, but the cycle still happen, right? So you're going to have the rates are gonna go up and down. And of course, the amount of the down payment that a bank will accept due to all the conditions on the market as well as regulatory conditions will go up and down. So whatever the downpayment is and whatever number they are using for this calculation up top, that's what you'll have to talk to the financial institution, but 20% fairly standard. What if it was 20%? So then I can say, okay, if I got the loan amount. Now I gotta, I gotta increase it to the affordable purchase price. So this is gonna be the affordable home, affordable home purchase. So I'm gonna, I'm gonna make this a header by selecting a couple cells because I'm going to use two cells for calculations. I think Home tab, font group, Let's make that black and white. And then so we first calculated the loan or mortgage amount up top. I'm going to put that in the outer column. So that's where we start. And then I'm going to take the percent financed. So the per, per cent finance. In other words, I'm trying to think about how much of the home purchase am I going to finance? Well, I'm putting 20% down. I'm not financing that. I'm paying cash for that. The rest 80% is the amount I'm going to finance. I'll do the calculations just so we can see it here. So the 80%, but I'm gonna say 11, I'll put that in the inner column. And then the down payment, down payment. Let's do it this way. Let's make this equal to the down payment per cent, which is 20%. This will equal the 20%. So one -20% or 100% -20%, if you want to think about it that way, would be the 80 per cent. Let's make this a percent. I won't make this a percent. You could you could make that a percent, 100% -20%, but we'll keep that at one. And then I'll make this an underlying thought group and underline. And then this is going to be the percent finance, which is gonna be one minus the 20, making that 8% by going to the number group percent, define it. That's not a word people don't like. Sometimes they get angry when I don't use non-words. But percent of five, I feel like it's a cool word. I'm pretty I think I'm going to get it into the new word, Oxford, new word, dictionary, the OED. Because it should go there syndicates. Let's go ahead and indent this one. By going to the Home tab Alignment indent. They got curiouser and curiouser. I heard in there, which wasn't a word from from some book or something. Any case, I got affordable home, affordable bull, home, her chess. So now we're going to divide this out. I'm going to take this amount divided by the 80 per cent. So that's how much home that we can purchase. So let's double-check that. Just give me a double-check figure because like yeah, sure. On that one, Let's give it a check figure. Check that out. Let's check that out one more time. So I'm gonna make this the header. This is my check figure, check, check. This is going to be equal to the home price, which is gonna be this amount. And then I got to put a down payment. This is how I would normally calculated of 20%, 20%, they're making me put down make that a percent, underline it. And so that means that I'm going to have the down payment of equal to 12899 times 20% 205620. So that means the amount, amount financed or the loan, you might call it the loan. A mortgage is gonna be equal to the 1208929 -20, 5620. So there it is. So we kinda double-check this VAT number and this to this number so we can kinda see it that way. So let's format this. I'm going to select this whole thing, make it blue. Font group. Let's make it, let's make it blue and bordered. Let's make this blue and bordered. Blue and bordered. You can see the idea here being that we're starting off with the income. We're trying to think about how much the bank might loan us based on that information by using the method that whatever financial institution is going to use which were, which were approximating here, you want to talk to the financial institution, we're trying to get the amount that would be applicable just to the mortgage payments so that I can then use that amount to back into the amount of loan they might be willing to give us. Then once we had to have the amount of loan, they might be willing to give us, then we can use that and think about the down-payment which they're going to require us to give to back into the Affordable purchase price, how much home we can actually purchase. So now that we have that down, next time, we'll just basically build an amortization table and the pivot tables related to this just to keep on practicing that. Because anytime you do these kind of calculations, it's useful oftentimes to get into more depth to have the amortization tables and then that information possibly broken out by year because then you're going to want to do your own budget and not just look at this 38%, but maybe think about your own personal income statement. And what's gonna be the tax implications and equity going into the future and so on. I think it's useful to be able to build the amortization tables and summary tables per year to do that. 4. Estimate Affordable Home Purchase Price Part 2: Personal finance practice problem using Excel estimate affordable home purchase price, part number two, prepare to get financially fit by practicing in personal finance. Here we are in our Excel worksheet and prior presentations we started our calculations. We're gonna be continuing on creating an amortization table. If you don't have access to this Excel worksheet, that's okay. You can go back to the prior presentation where we basically start from scratch from a blank sheet. If you do have access, there's three tabs on down below an example tab of practice tab blank tab, the example tab, in essence Dean and answer key. Let's take a look at it now. We've got the information on the left-hand side. We put together the tables on the right-hand side. Last time we're going to continue on and build these amortization tables at this point, just to reconstruct or rethink about what we did last time, we had a scenario where we had the income level and we wanted to go from the income level to the amount of whom we might be able to purchase. To do that, we thought about the PIT, which were thinking would be a way of financial institution, might try to determine how much we can afford to spend. And then we figured out how much of that portion would be the affordable monthly mortgage payment, basically, the loan payment amounts. Note, as we do that, this number could change from institution to institution from time to time, as time passes, as regulations passes, as the economy changes and so on. So you do want to check the financial institutions to get an idea of what that number is. It will, of course, this kind of calculations that financial statements or financial institutions will do will be more standardized for more standard types of loans, less standardized for less standard types of loans, like a 30-year fixed is a standard type alone and so on. So once we have the payment amount, we can then say, okay, how much loan could I get given that payment amount, if I know what the interest rate is and the length, the loan period, which we're going to say it's 25 years, then we calculate it. How much loan the bank or financial institute might be able to give us. Once we know that, we can determine how much, if we have to put 20% down, how much home we can purchase. Now, notice that 20 per cent could change again from time to time, from period to period and from institution to institution. And with different types of loans that might be more or less risky, right? But the 30-year fixed again, would be the starting point that you'd probably think about it the most standardized type of loan. And then we thought about then how much home we can purchase them. We kinda double-checked it to look at the home. The 20% down, this would be the downpayment. We would need cash of that for our calculation to basically do that as well. And that the amount that would be the financed amount. Now we're going to continue on and take this loan or this payment amount and this information, calculate our amortization table. And note that this calculation is something that we're kind of looking from the perspective of the financial institution to try to meet what requirements we can do to get the to get the loan amount and the home amount that we can purchase, then we might want to take that information and do a more in-depth calculations for more financial perspective using our actual income statement, then our tax calculations in which case we might want amortization tables. So we'll build an amortization table and then we'll break it out by year with formulas and with pivot tables. I think this is a useful tool to do, although you also have this information online tools you can catch, I'm not advertising this particular website, but you can use an online calculator to do a similar thing gives you an amortization table. But you can't do quite as much with it. It's harder to summarize the data in this format. It's a lot easier if you get used to it in Excel, although it can be intimidating at first. So that's what we'll practice building these tables are a couple of times. If you go to the tab to the right, this has blink information so that you can do this without having so much, so much formatting in Excel. And then this blink tab, we're going to build stuff from scratch. Now we started, we already did this component. We're going to start to build the amortization tables now on the right. So what I first need is a skinny cell right here. I need a skinny cell because I don't want anything right next to this one. So I'm going to copy this skinny cell on the right. And I'm gonna go to the Home tab, clipboard and format pane it. And I'm gonna put that on the skinny. Here's the skinny. I, the skinny I can still see the eye because it's skinny. We can make that one really skinny and still see the, unlike the C here which is kinda getting cut off, you can see because the seeds a little fatter, little thicker, not that it's fat, but any case, we're going to put our headers up top. Now, normally with these amortization tables, I like to have, I think I'm going to need two rows. And I could use the wrap tool. But then that makes this. So why the one and I don't like that because it messes everything else up, so I don't do that unless I'm going to put it into a table, which we will create a table from it, a pivot table. But I'll show you what the problems are. The pros and cons of wrapping is. What I'm gonna do instead is just unimagined. We're going to have two cells up top for the longer title names. I'm going to start with my headers. It's going to have a years. I'm going to say month, moon. And then I'm gonna say, we're gonna have a payment, payment. And then I got the loan decrease, which you might call the principal decrease. But again, I misspelled principle sometimes. And I make it like the wrong kind of principle. And then people make fun of me. So I don't do that anymore. I don't do that. I call it loan decrease and then loan balance. Loan balance. That's how it goes. What you might call principal balance, but not like a principal at a school. So then I'm going to highlight this and we're going to make this a header which I typically select these and go to the Home tab up top font group, drop-down. I like to make it black and white for the headers so we can see it a little bit more clearly alignment and in, and make that a center aligned. So then I'll start with a months. This is how long was this loan period? By the way, it was 25, 25 years. So this equals 25. 25 times 12 is 300 months. So I'm going to start at zero, then one, then two, and now Excel can recognize the pattern as most of us probably can't do. The next one is going to be three. So I'm going to auto-fill it. I'm going to select those, put my cursor on the fill handle, and then drag it down to auto-fill mixture. You gotta good hold, a good grip because we're driving this thing way down, autos driving it down. And then Dr. Phil does the calculation. I forgot how far I was going. 300, I started driving. Keep your eyes on the road when you're driving the autofill. So then I'm going to say there it is, 300. Let's center that while we're here, alignment and center. Now we're gonna do are tricky year calculation because I'd like to years here, so I know which year we're in, so I don't have to like kinda divide by 12 and figure out where we're in. So fancy formula. This is, you might not see this other places. This is, this is, you might not know. We're going to round it up. So we're going to say this is going to be a roundup, round up. So we're not rounding up like cattle. That's not like a cattle term. We're rounding the number up. So we're going to take roundup equals roundup brackets. And then we're going to pick this number to the right. And then I want to round that up. Now I'm going to take it and divide it by 12, so one-twelfth and then round it up to the first digit, some say comma. And I want to round it up and I got to put the digit which is I think as 0.1 to round it up to a whole digit. That's what you got to put to round it up to a whole digit. So there it is. And so then I'll typically add some decimals to make sure I did it right. And let's copy it down using the autofill. There it is. So it's still roundup. There's the formula. Round them up, round them up. And then I'm gonna take this and grab it and drag it down. Round up the doggies, which are actually cows. Round up the doggies. So I'm going to then say, let's do that, then we'll center it. Let's get rid of, let's, let's make the decimals go away. And I'll do that here too. I'm going to center and make the decimals go away. There we have it. Okay, so now we're going to calculate, we're going to take the payment or let's take the loan balance. I'm going to take a loan balance at period zero is going to be equal to the loan balance we calculated over here, the 102, because remember that's the loan balance. This is the purchase price because we're putting 20% down. So I'm gonna pick up that loan balance. And then we'll take our payments, which we calculated here. That's our payment that we then back into the loan balance on. So now I'm going to pick up the payment. The payment is going to be that one, that 860. And let's make that F4, F4, that one to make it absolute dollar sign before the F and the eight, you only need a mixed reference, but an absolute works and it's easy. So that's why we do it. So then we've got the decrease. Hold on a second. I'm missing interest. I'm missing interests. I need another column between these two. I'm gonna do that here. It's just, I did that on purpose so I can show you how to insert a column by putting our cursor on the M. I'm going to right-click there and insert like, where's the interests going to happen? This loan has no interest. What kind of magical bank or you work in with the cricket or something is not right? So this is gonna be equal to the 102497 times the rate on the left-hand side, which is gonna be the nine per cent. Now that would be the rate for a year. If I enter that, that would be a year. And this is per month. So I get to double-click on that. I'm going to divide this by 12. I don't need to put brackets around this or anything because order of operations multiply before dividing. There's the interests for the month loan balance decrease, which you might say principal decrease, is gonna be that minus that. There it is. And so the new loan balance is going to be equal to the 102479 minus the 91. So there we have it. Then I noticed you might wanna I wanna kinda check this to life. The online, the online tool I used, the online tool is kind of a check figure. And then I use excel to be more flexible. So I might use the online tool here and say, Okay, does this make sense? I got the load 10 247-910-2479, 25-year loan. The rate was 9%, I believe. Let's calculate it. Calculate it. And so now we've got the 860 payment looks right. I can pick up my amortization table. I've got interests 76087, 69.90, 1,769.91. So it looks like I'm getting the loan calculator kind of confirms me. It's another way to kind of confirm it. Now I could copy this down, but it's not going to work. So I'm going to copy it down and see what the problem is. This one did what we wanted because it's absolute. This one did not because we moved the interests down. So anything that's not inside the table or anything, in other words, it comes from the dataset. We want to make it absolute. So let's, let's delete that. And I need to make this interest calculation everything, it's not inside the table, which is that b, because that's coming from over here. I'm going to make that absolute F4. And now you only need a mixed reference, but an absolute is the easiest thing to not have to think about. Which one? Which dollar sign did you need? Because I don't need to copy it across. So whatever absolutes, fine. This one, I don't need to do anything because there's nothing from the data table. It's all inside where I'm working. This one doesn't have anything from the data table. That's all insights and nothing you need to do here. I can copy the rest down. So let's select those four. Double-click on Phil's handle. You don't have to drag it this time. You just double-click it and that should work. So I'm going to double-click on it and boom, so I don't have to grab it and drag it all the way down and drive it, drive all the way down here. But I still going to scroll down because I want to see that there's a zero at the bottom, which is our check figure, so that we can check this thing out and it looks like everything's good. So now we've checked it out here. We've also checked it to this amortization table. Now at this point you might say, well, this amortization table is easier to calculate, but we can't really group this amortization table into, say, year-by-year data as easily with a pivot table or using kept formulas. So the next step is something that is useful data that we can't do as well with the other, with the other table. And plus this one ties in, of course, to my dataset. So here, like if I changed, if I changed any of this data, the loan balance, the payment. If I change these calculations, this table will change automatically, whereas it won't if I have it outside. So everything's nice and tight inside of our calculation here, which is the way it should be. So I'm going to hit the drop-down. We're going to put make this blue and brackets, put some blue and brackets around it. Okay, So now I'd like to summarize this data on a year-by-year basis. I can use, I could do this with a pivot table or I can do it with formulas, will do both methods because there's pros and cons of both. Let's make this cell a little smaller. These cells don't need to be that wide. It's not fair. It's just a little wide. Okay. So now this one these prior can go. Okay. So now we'll make a year-by-year breakout because I'd like to see my payments for the entire year, e.g. poor and so like that. And then I'd like to see my interests for the year that will help me with my tax calculations. And I'd like to see my decrease in the loan balance and that'll help me determine how much equity I might, I might be gaining from year to year. This is quite useful when you're doing logn comparisons one loan versus another loan, which is going to be the next step. Of course, when you start to think about how much load can I get, how much house could I purchase? And then you're thinking about, okay, what am I gonna do? Tax planning in the future and so on. I need to know my interests and what's going to happen with my equity and whatnot and so on. So let's do that. So what? I need another skinny column over here. I need another skinny. Skinny. I am going to put my cursor on the skinny eyes so it's the same width. And go to Home tab, clipboard, paintbrush it, and put that on the skinny, skinny P. Then I'm gonna take my headers. Let's just take the same headers except I don't want the month, just the years. I'm going to take these headers and copy them. And I'll paste them right here in Q1, control V. And then I just remove the month. I don't want the month. So I'm going to put my cursor on our column and right-click and delete that. Delete. Month. Don't need you. And then we'll make this one a little bit smaller. And then I need how many years were at 25, I think 12. And I'm going to bring this down to 25. And I'm just going to summarize the data on a yearly basis as opposed to a monthly basis. Which is totally useful way, useful, way useful. So we'll put that down and then I'm gonna go to the Home tab, Alignment and center. Okay, so now we'll do our calculations. So I want to sum this up. This is what we're gonna do. We're gonna use a sum if calculation. And so I want to say if there's a one, I'm going to use this one. Say if that criteria matches in this column with all these ones, then I want you to sum up the related cells. This column, which would be like all these cells. Now it's pretty clear with the payment calculation because they're all the same. So it's gonna be the same from year to year. So that's kinda easy to calculate. I can just take the 860 times 12th, but when I go to the interest in the loan, then it changes from year to year. So then it becomes more in poor tante. So moss and port tante. So let's do it then. Let's do it here with the payment calculation. I'm going to say this equals the sum if summit, if under these conditions and criteria. So the range is going to be, I'm going to pick this range. That's where the, that's where the criteria range is. I'm going to select the whole thing. So I'm just going to pick up the whole thing now if there was anything underneath it, you want to be careful of that because but there's nothing underneath this one for me. So I'm just going to say the whole column down to forever. It goes right down, right down to the bottom of the Earth. The Excel sheet goes to right to the center of the planet. So then I'm going to say comma. And then we're going to, the criteria is going to be this one, the one and then comma, and then the sub range, as I want you to sum everything here. So everything that matches this number in this column, I want you to sum up the related number and the sum column is what I'm talking about. So Enter, so that should do it. So I'm going to sum this up. That's what I'm talking about. Yeah, alright, let's do it again. Let's do it here. Let's try it again. Equals the sum. Sum, not the sum. If equals the sum. And then the range is going to be the j. So that's gonna be the range. And then comma, the criteria is this one if you find that one in that range, so that's Q3, then comma I want you to some the related items and the interests range. In the interests range. And then Enter, Boom, amazing. Simply, amazing. 9177. Now you can copy that down if you so choose, and I am choosing to do it. Thank you very much. You don't have to choose to do it if you don't want to. It's my choice. I'm doing nine. So that's a 9069. So there we go. Okay. So then we could do it. Let's do it one more time with the loan balance equals the sum. This is a super cool formula. We're going to say the range is going to be here, home on the range comma. The criteria is going to be one comma, and then the sum range is this one. So if that one, when you find out one over here, then sum up the related stuff and the loan decrease, which you could call it principal decrease. There it is. And so let's sum that up and notice they're added up this way and double-check it. Checked twice. 12 checks by this 12 checks. Let's copy it down and then check it out, see how it differs from year-to-year. That's why that's why this is important stuff. You can't just, you can't just say you're one is the way it is because that's not the way it is. It is what the payment, but not with the other step. Now, we could I'm going to delete this and do it another way. I'm going to delete this and say, well what if I copy this one to the right? That would be the way super easy thing to do. The waste super easy. But if I copy that to the right, then there's a problem in Houston. Houston has a problem. Houston in their problems happening. This one this one moved over. I don't want this range to move over right here. First of all, I need to stop that. I need to stop that from happening. And then this one moved over to the right to that. Forgot to stop that from happening. In Houston. Houston has a problem. So we're going to say then let's sit that one. We're going to say this is going to be, we're going to make this absolute F4. So this range, does it move to the right? And then on this one you gotta get fancy. We gotta do tricky stuff because when I move it down, I want it to move down. So I want this three to go down. But the queue, I don't want it. I don't want that to move this one. I want to stay in this column so I need a mixed reference. So I'm gonna put $1 sign before the queue. And then this one is good. That one I want to move to the right. So that one I'll leave it leave it alone. Just leave it alone, man. I'll tell you when he's had enough. Okay. There it is. I don't know what I want to know what I'm talking about anymore. Let's copy it down. Let's copy it down, which I think we could select these and just double-click on it. And that should do it. That should do it, I think. Okay, now, the last one is a little bit different because I want the smallest number. So this number one, I want the smallest number over here, which would be down here. That's the Min function. So we're going to use a Min if men if equals Min, if, if you need an F on that one Min, if we want to say this one's a little bit different in the ordering. So we want the Min range, the men range is 0. So I'm going to just select 0 column and then comma. And then the criteria range is going to be the ones. So I want you to, I want you to look into this criteria range and then sum the related item over here if you find that criteria, which is what the criteria is, that one. So here's the criteria. The one. If you find that in the criteria range, then I want you to sum the sum range and the related some range. Let's do it. There it is. So that picked up the last one right here, 101336. Let's copy it down. Double-clicking. Boom. And now we've got this fancy table that gives us the year-by-year breakout. So I can try to figure out, okay, what's gonna be my interests per year with the principal decrease per year, what then it's gonna be the impact on my equity that interests is good. Let's make this blue before I start babbling too much, Let's make this blue and border. So then the interests can help you with your tax calculations, e.g. the loan balance can help us to determine in part what our equity difference is going to be. Equity, you can think of as basically the difference between the value of the home and the loan value, how much you owe in terms of the loan to things that are going to increase, that, hopefully increase it. One will be then as you pay down the loan, then that means the principal payment is the portion of your loan payments that's going to actually increase the equity because that's what's going to actually pay down the loan. And then the other, hopefully the value of the home goes up, which of course is dependent on the market. Either way, you don't really realize the equity unless you financed, again, refinanced AND or OR sold the home. Okay. Let's do it one more way. This was the Pivot Table way, which is maybe even easier to do but might be a little less flexible than this one, which might change more easily as you enter and change your data over here. So if I change my data over here, e.g. you might not want to do this, but I'm just going to like if I made this like 900, like if I made this 10,000 or something, my data on the right is going to change automatically. So that's what you want to, you want to be aware of. You want to be able to construct things in that way so you could change your dataset and have everything move over. In this calculation, I would probably want to change the rate here, or possibly my down payment or something like that, which will then change everything. In theory, if I did everything right, which I totally did. Of course. Any case. Let's go back on over. Now I'm going to select the whole thing. Now this is where, this is where you've got that issue where we put this in two columns, the header side, I can't pick up the full header here because I didn't do the Wrap Text thing. So that's where the issue is when you try to make something into a table. But that's okay. I'm just going to take this second header and then drag all the way down selectiveness whole thing. And I'm gonna make this into a pivot table, a table that pivots. So I'm gonna go into the Insert and make that into a pivot table. And we'll put that down here. I'm going to put it in the existing worksheet. Put it right here, so everything's nice and tight in the same area, I'm going to say, okay, so then I got my pivot table box, which is really intimidating looking, but not too bad. I'm first going to start with a year. That's what I want. On the right-hand side, I got to visualize this thing. It put it into some column, but I don't want it there. I wanted to end the rows. There's my years. Now. Everything else is pretty straightforward. I don't need the month, so I'm just going to pick the payment, the interests, the decrease, and the balance. And then everything populates for us. Nice and easy. Nice and easy. But the formatting is ugly. If I do say so, we're gonna, we're gonna, we're gonna have to fix that. I can't can't deal with that. So we're gonna go over here. We're going to hit the drop-down. And let's learn to value each of these fields and just adjust the number formatting. I like to make them currency bracketed. Get rid of dollar sign and decimals. Okay? Okay, there It's better. That's much better. That's my feeling better. I was a little stressed out there because it's messy. It's messy with all that decimals. Let's do it again on the second one, Value Field Settings. And then I'm going to say number formatting, currency, brackets, get rid of dollar sign and decimal. Okay, got it, That's so much better. Let's do it again. Let's do it again. Dropdown, value, field Settings, format, Currency, brackets, dollar sign gone decimal, decimal down, down. Okay? Okay. Alright, now the last one is a little bit trickier because I want the men balanced, not the sum. So I hit the drop-down value, it, I want the Min value, not the sum Min value. Then the rest is the same Number format Currency, brackets, dollar sign gone, decimal, decimal down, down. And okay, okay. So there we have it. And there we have it. It's got a blank cell here. When does that blank I'm not going to worry about that right now. Why is there a white okay. It's okay. It's okay. It's bothering me. That is Bob. That's because that's the first period zero. Okay? That's okay. But for now I'm going to, I'm going to squish these back together again. Make them a little squishier like that. So they're not taken up so much room. Now, up over here, notice I could wrap the headings down here with the wrapping of the headings, but that meant that makes this wholesale wide again. That's why that's why that wrapping heading. I don't really like to do it unless I have to. So there we have it. So there's a couple of ways that we could see that I think it's good practice to have this information. Notice that this one up top will change a little bit more easily as you format the data over here. If I was to change the data on the right and I change the pivot table, then you've got to make sure that you refresh the pivot table. And sometimes it can be a little glitchy to refresh. But you can always recreate the pivot table pretty quickly too. Once you get, once you get it down, down pretty, and then you get that information pretty quickly. Also, this one's probably a better one to use your formulas from, because the pivot table, if you, if you use a formula that goes to the pivot table, then again, it gets a little messy when you update the pivot tables. So pros and cons of those two methods. 5. Home Loan Comparison: Personal finance practice problem using Excel, home alone, comparison. Prepare to get financially fit by practicing personal finance. We are in our Excel worksheet. If you don't have access to the Excel worksheet, that's okay. We're basically going to build this from a blank sheet here, but if you do have access, we got the three tabs down below. We've got the example tap the practice tab and the blink tab. The 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. We're going to populate that and build our tables. On the right-hand side, we have three different scenarios that we're going to be constructing, amortization tables and then summarizing that data. As we think through it, we want to think about the practical use of this clearly, if you were thinking about different kinds of loans structures, you might be running at different scenarios and you might be changing factors, e.g. with the amount, the number of years, and the rates, e.g. they can't do that with online software. So if you were to look up loan calculators such as this one, you can then construct amortization tables, e.g. in this format, but you have a little bit less structure. And if you wanna do a side-by-side comparison, you can change the data over here to run different scenarios, but you can't see them side-by-side quite as easily. However, you can create different tabs and toggle back and forth between different tabs and try to do a side-by-side in that format. But you could do it in Excel. You have much more capacity to make those changes a little bit easier. So we could e.g. have this information on the left-hand side and then just what run one loan scenario on the right that ties into this data on the left. And then we can change this data on the left to change the loan calculations on the right-hand side. Or we might want to have the side-by-side. I might want to run two different loan scenario side-by-side so I can see them side-by-side. So we might have two different loans scenarios so that I could put the amortization tables right next to each other. And we're going to hide some cells to show how to do that. We can also summarize this data on a year-by-year basis as opposed to on a payment month by month basis. Which is another way that would be very nice to do some comparisons. And we'll show how to do those kinds of comparisons as well, building our table. So on the second tab here, we've got some preformatted worksheets so you can work on this one. And it'll be a little bit easier to work with because you don't have to format, do much of the Excel formatting. And then on the lake tab, we're actually going to build this from scratch. So this is the scratch that we have on the left-hand side that we're going to build from. So if you don't have the Excel worksheet, you can just build this scratch on the left-hand side. What I would first do is select the entire worksheet format. The worksheet under underlying formatting, which I would usually make Currency. Don't do this if you have this worksheet but currency. And then I would usually have the brackets and I usually start with no decimals. And then I would build an add any more information I need, including making a percent type of cell here, a percent type of cell here, and adding decimals when needed. So that's what we have this time. We've got a blank sheet. Everything is formatted thusly, except for this tab on the right, on the left-hand side, this is gonna be our source data that will then use to pull over. Remember that you always want your source data somewhere separate than your calculations. You don't want to hard-code the calculations so that we can change the source data on the left-hand side. Now I'm gonna do a quick like a recap of this source data so we can see it again. And then we'll calculate the payment. So I'm going to say, let's say we have the amount which is going to be equal to 4,412.3. I'm going to say three lone scenarios. I'll just call it the loan scenario at the 168. Then we've got the amount over here at the two to three. And then we're going to have the amount at the 199, and then we're going to have the years. So let's put the years. And I'm gonna say this is gonna be equal to the 15 years. This is going to be equal to the 30 years, and this is going to be equal to 20 years. Now, note, if, if you have this information on the left, you might just put like another, you might just constructed on the left here. If you are building the table from scratch, I'm just reworking it so that we can calculate our payment calculation on the right-hand side. So then we're going to say that the rate is going to be, the rate for our three scenarios is going to be equal to the 6.5. I'm going to make that into a percent by going to the Home tab number group and percent define it, which isn't a word, but I like it. I think it should be a word. And i'm I'm working on it. I'm working on making it a word. I'm going to make it happen, I make stuff happen. So then I'm gonna go to three. This is going to be equal to the 7%. And then we need to identify that number group per cent, define it per cent of five, and then we've got a desk normalize it for sanctified and destiny mobilized. And it didn't really need a decimal, but we'll put one there anyways. So it has the same number of decimals as the other one. Consistency. That's what we shoot for here. Number of group per cent to find this one. And we'll add a decimal 6.5. Now we've got the new thing which is the payment. Now this is the payment calculation that you can make and this will just give you the payment. We want more information than that. Typically we want the actual amortization table. Because we want the breakout between the interests and the principal and to see that fluctuation at least on a year-by-year basis. So the payment calculation, I do it this way instead of hitting equals, I hit negative. So it'll flip the sign and make a positive number. It's probably more proper to put the negative sign in the formula, but this to me is the fastest way to type it. So negative p and t shift nine. And then I've got the rate argument, which is simply gonna be up one that's gonna be that 6.5%, but that's a yearly rate because we talk about rates in years. We don't talk about like monthly rates are daily rates because they'd be really small. And so we usually talk in years. So I got to divide that by 12 to get the monthly rate because we're going to have the payments and months, of course like normal, then comma, the number of periods I'm going to say up, up is gonna be that 15 and E2. Once again, that's in years though, I need to make months because I'm doing this on a monthly thing. So times 12, 15 times 12. And then comma, the present value is going to be up, up, up that 16800168000 is it and enter. So we got the payment here. I could add decimals, but I'm going to keep it rounded at the 14, 63. Let's do the same thing now you could copy this formula to the right, but let's calculate it again because we're practicing our payment calculation and then I'll copy it over just to kinda show that we could do that too. But let's practice it again. This is negative PMT shift nine rate up once, that's a yearly rate. So I'm gonna, I'm gonna divide it by 12 to get the monthly rate comma to the next argument that's gonna be the number of periods. Number of periods is 30 years, but I need months, so I'm gonna take that and multiply it times 12 comma, and then up, up, up, There's our present value, the loan amount and enter. So there's the 1484. And then next we have negative PMT shift nine rate. Once. That's a yearly rate divided by 12 for the month. The rate comma to the next argument number of periods up to that's the years 20, but I need months, so times 12 to get to the months comma up, up, up. And there's the present value, the loan amount to get us to the 1448. So notice these two we both got the 1448, even though we have kinda substantially different terms in terms of the years and the rate. Now I could copy this across this way. So if I delete this one, delete it. Now, it's okay. It's okay. I'm going to copy this across, put my cursor on the fill handle, and copy it across. Okay, It's back, it's back. And let's make this, let's make this blue. Will go to the font up top and make it blue and bordered, blue and bordered. So there's our information. Let's build our amortization tables. I need a skinny because I don't like to, you don't want to build anything right next to the table over here. So I'm going to copy the skinny by going to the Home tab skinny column that is. And then the Format Painter and paste this guinea for a skinny, skinny h. Okay, and now we'll build our amortization table, amortization table construction time. So what I'm gonna do now note that I'm going to have a couple of headers that are gonna be longer than the width of a column that I would like. We could wrap the text, but that makes these cells fat. And we don't wanna do that because it kinda makes everything else look funny. So I'm just going to predict that I need two rows, two rows. So I'm going to say the first one is gonna be the year, the year, the month, and obtainment. And then Tab, and then we've got the end tourist. And then you could say principal decrease, but I say loan decrease because it's easier to spell by even spell that wrong. I noticed that I spilled it loan like that a couple times. So I mean, just spellings. Good spelling. Not my forte, my forte, my forte. So this is going to be loan balance, balance, and then we're going to make this black and white. So we'll select this and make this a header thing. We're going to go to the Home tab font group and make it black and white. Black and white. And then let's center it Alignment and center. And then we'll make this a little skinnier. Okay. So we're going to add up the months now, so there's 15 years, so 15 times 12 is 180. So I need to go down 181, 80, a 180 degree like turnaround. One did a 18180. So here we go. It's a super cool move. 180 kick flip. So it's gonna be right there, 180. And then we'll alignment and center it. That looks good. And then we're gonna do our years with our fancy trick, fancy trick on the years, which is a round up, round up, round up little doggies, which are really cows for some reason they call them dogs. And the songs sometimes round up the little doggies. Roundup and then brackets, I'm going to go to the right, divide it by 12. And then, and then I got to say, what category do they want to round? So I gotta hit comma and the number of digits is going to be 0.1 rounding it, and that tells it to round it to the nearest whole number. A whole number don't give me any half or partial members. Then will I use to add some decimals to make sure it does it right? And then if I copy it down, see how this is all in the year one. That helps us when we do the pivot table, e.g. so I'm going to copy that all the way down, all the way down to 180, throwing up, throwing out the 1AD kick flip. And then I'll send we're not really kicking or flipping anything, but still sounds better when you throw on the 1AD kick flip. And then we'll do that. Just to one-eighth is pretty impressive in and of itself though for me, I feel like, but I'm going to format this. So I'm gonna put a zero up top because that'll help us out with a PivotTable. I shouldn't have a blank cell for the pivot table because that kinda messes things up. So I'll put a zero up top this time. Haven't done that in prior presentations. And I was not optimizing what I should be doing by not doing that. And now I'm going to do it. So now this is going to be the loan balance of, we're going to start with this one, this first one, the 168. And then you might put zeros up here because again, you shouldn't really have any blank cells if you're planning on making a pivot table out of it, which we will do. So I'll put some zeros up top just to, just to fill the space. And then I'm going to say this is going to be the payment which is equal to the payment we calculated here. So there is the payment and then let's say f4 on the keyboard, so we can copy that thing down, copy it on down. And then the interest is going to be equal to the prior loan balance times the interest rate, which is the 6.5, that is a yearly rate. So I got to divide it by 12. I need to divide it by 12. That's a key component. Don't forget that, don't forget that piece. And this is gonna be equal to the payment minus the interest, which is the loan decrease or the principal decrease. Like to call it vastly, vastly, thusly. And then well, this is going to be equal to the prior loan balance minus the 553. There it is. Now we should be able to copy this down. Copy it down. But there's gonna be a problem in Houston. Houston will have a problem. Houston we have actually the problem wasn't in Houston. They just tell him Houston that we have a problem. It was in space when they had a problem, but they were just telling houston of the problem. Houston wasn't the one with the problem, apparently, any case. The point is that this interest item is outside of our data in the data set. It's not in our table, so we gotta make that an absolute reference. When we copy it down, it'll, it'll, it won't go down. The cell won't go down. So I'm gonna put my cursor in E3 and select F4. So there's $1 sign before the E dollar sign before the three. You only need a mixed reference, but an absolute as easy and it works in this case, we don't need to do that here because everything's inside of our area that we're working on. It's not my dataset, nothing in the data set here. So those, so copy down and move down as desired. So I'm going to select these four. We're going to double-click. We don't have to drag the fill handle. We just got to double-click the fill handle button, call it a fill button. They don't call it a fill button. It's still a handle. But you're really using it like a button. When you do that, a zero at the bottom, which indicates that we have done everything was done properly, hopefully. And so there it is. Let's make it blue and bordered blue and bordered blue and bordered, not the letters. If you don't have this blue, you go right here. Standard and it's that blue, that's the Excel. It's fun guy blue. Excel It's fun guy blue. So there we have it. So now this one of course will flex if I change the, change anything here like the rate to like 8% or something, everything will change. And that's the point. Now, of course, if I change the number of years, it becomes an issue because it's only 180, so you'd have to extend it down a little bit, but everything will basically populate and adjust with that. Now we could also want the year-by-year table. So I'm going to build a year-by-year table to see like a side-by-side thing. So we also might want to see it broken out on a year-by-year basis. So first of all, I'm just going to build this another table right next to these two, with these two scenarios. So instead of, instead of adjusting my data back and forth between the scenarios, I got a side-by-side of the amortization tables. So let's do that. So I need another skinny over here, the zero. So I'm gonna put my cursor on the H. I'm going to format painted. I'm going to hit that over here with the skinny. And so I'm going to try to do this a bit faster. So I'm gonna, I'm gonna copy my headers. That'll make it faster. It's going to copy that and I'm gonna put that right here. And then I'm gonna hide this data from H to n, putting my cursor on age scrolling on over to n. And then right-click and hide. And then let's do this again. This is gonna be how many periods we got 30 periods on this one. So this will be 30 times 12, which is 360. This is the full 360. We did the 1AD last time, 180 kick flip. But now we're doing a full 360 rotating all the way around. We're going to select these three and we're going to go all the way down to get that full rotation. Given that full rotation, going right back to the same stance that we were in when we started the maneuver. Started the maneuver, we're going right back all the way round 360, set of 180. So there we go. And I'm gonna go to the Home tab Alignment center it. And then that looks good. We'll do our roundup. Roundup equals the roundup little doggies, which are actually cows. And then we're going to pick up that one to the right and then divide it by 12 because there's 12 months. And then comma, the number of digits is going to be 0.1, which says rounded up to the full, whole number. Por favor, please. And then I'm just going to copy that down. Double-click in this time on the Fill button, which is called the fill handle, but we're using it like a button. So it's a Fill button in this case, Fill button populates it all the way down to the 30 years, just like that. No problem. Then I'm gonna put a zero up top fill in the blank space, put some zeros here to fill the blank space. The loan balance is gonna be the three-three are the two to three. The payment which we calculated this needs to be centered that looks off, something's off. I can't stand it. So much better. Okay. The payment then is going to be equal to the 1484. And then we're going to say F4 on it, dollar sign before the F and the Ford, you only need a mixed reference, but an absolute one works. And then the interests is going to be equal to the two to three times the 7% this time That's the yearly rate. So we're gonna, we're gonna divide it by 12, divide it by 12, and this is outside my dataset. So that needs to be absolute ties, which isn't a word, but it's what we're making it one again, we make, we make words here. It could be a mixed reference, but we're using an absolute. It's easy to do. This one then is going to be equal to the payment minus the interest. That's the loan reduction or principal reduction. If you so choose that terminology, the two to 3,000 minus one-eighth three. We're going to select those for sales and use the Fill button. Double-click on the Fill button, the fill handle button. And then all the way down, it should go down to zero. At the end of the day, it's a long day. It's a 30-year day, but at the end of the day, there it is at zero, end of the day, the end of the day. So we're gonna go back up top and then let's make this Home tab. Font group blue, blue skies it and bracket, blue attached and Akamai's. Alright, let's do it again. Let's make these a little skinnier. They should be a little skinnier. I feel like those are way too thick for this stuff. That's too much blank space in there. Okay, I'm going to take the skinny 0, skinny. Oh, we're gonna go to the Home tab and format pane it and skinny up the V, skinny up the V. And then we'll take the headers again. We'll put that up top headers and then do a hide, hide from ODU. And then hide. Okay, Let's do the same thing. This one's gonna go to 20 years, 20 years, 12 times 20, one-twenty. I could've done I could do that in my head. I'm not so dependent on computers that I couldn't do that one in my head yet. But I'll get there. This is a one, this is a 240 to 40. So we'll select these three. We're going down to a 240. Then she lands sideways. If you do a 240, but then you finish the rotation. On the ground. To 40 is dangerous. They were going for a 180 but over rotated to the 240. I'm getting mixed up to 40. Concentrate constant there it is. Got it, got it. Alignment, center it. Then we're gonna say this is going to be equal to the roundup, round up, round it up. And then we're going to say this is gonna be this 1/12. And then comma to the digits of 0.1 sent to the whole number. That's what that means. And then we'll just copy that down. There's our years. We'll do with double-clicking on the Fill button, Fill button. And then we're going to say it's a fill handle, but it's a button center in it. They didn't know it was actually going to be a button when they called it the handle. But then it became a button. And now it's called fill handle. Even though it's also a button, doesn't really look like a handle at all. That's what happens in these crazy times telling you. So we got the one we got the 11199 payment is going to be equal to the 1484. And then we're going to say F4 on the keyboard so we can copy that down. You only need a mixed reference, but the absolute one works. Interests is gonna be the 199 times the rate of 6.5. We're going to F4 that one because it's outside our dataset, then divide it by 12 to get it down to the monthly amount. We got the loan decrease, which is gonna be the amount of the payment minus the interest loan balance, then it's going to be the prior balance minus the loan decrease or principal decrease. You could call it the principal balance at the end, if you want to call it principal stuff instead alone stuff, then I'm going to select those four. Double-click the Fill button, Fill button. And so now it's down to zero at the bottom. We're gonna go back up top and just do some format. Blue and bordered. Blue and bordered. There we have it now if I was to unhide from going from G to W, Now we've got these three unhide and we can kinda see him on a side-by-side basis, which is something a little bit more difficult to do if you were jumping back and forth from tab to tab, although you could do a tab by tab, jump back on here if you're gonna use it, like if you're going to calculate this in software. But what you also can do in Excel is to make these into a year-by-year breakout, which can help you with what you feel like your equity might be from year-to-year as well as your your interests. I'm gonna do that on a side-by-side basis. So I'm going to, first, I'm going to hide some cells to the right, To the right of this one and we'll break it out on on just the equity. So I'm going to put my cursor on 0. And I'm going to scroll over to this side, AB, right-click and hide. And so now we're here. So now what I'm gonna do is I'm going to just try to break this out on a year-by-year breakout. So I'm going to need another skinny and the AC. So I'm gonna put my skinny here, skinny H, Home tab clipboard painted. And skinny, skinny, you can't even see the AC because the ascii doesn't fit in the skinny because it's so skinny. But that's okay. And so then what we're gonna do it, Let's take the same headers. These headers right here. Let's take those. I'm going to take those. And then I'm going to delete I'm going to delete the months. We don't need the months. Now we'll do this a couple of different ways. I'll do it with a pivot table and we'll do it with formulas. So what kind of delete this? And we'll just make a few of these on a side-by-side basis. So I need years from year 12 and bring it on down to this ones. What was the first one? It was like 15 years. 15 years fitting. 15151515. Fitting. Okay. Pronounciation, pronoun C8, the loan balance. So then what I'm gonna do is I'm going to say sum if calculations, then this is our sum if calculations equals the sum, we would like you to some IF and then brackets the range criteria. I want you to look inside this range. I'm going to select the whole column. And so be careful if there was something underneath it, you would want to not select the whole column, but just the actual range. But I'm going to select the whole column because it's easier. And then comma. And then we've got the criteria. The criteria is this one. So I'm going to say, if you find this one in that column, then calmer some of the related range, which is the payment range. This range right there where my home is, my home's on that range. By homes on that range. Home on the range. And then we're going to copy that down. And so that looks good. That looks good. I could have done that by just multiplying the payment out times 12 though, but I'm gonna do it over here with the interests where it gets more interesting. So I'm gonna say Summit if some, if brackets, the range. We want you to take a look at that range, i range and then comma, and then the criteria is that one again, and then comma. So if you see that one in that area than sum up the related item in this range, the interests range where my home is, my home's on the interests range. Home on the range. And then I'm going to copy that down. And then, so then if I sum these down to check it, double-check. There's the tenths, 719, amazing. Amazing. Let's do it again. Equals the sum if. And then brackets the range, the range, and then comma. And then the criteria is that one and then comma and then the sum range is now the loan decrease or principal decrease, whatever you want to call it. There it is. And then if I if I add these up to check it double time, double-checking 6843 there it is, that you could do this a little bit easier. Let's do it one more time by copying this across. I'm going to do you're going to delete it after all that work. It's okay because I'm gonna do it faster this time. I'm going to copy this across this one is I. When I copy it to the right, I don't want this range to move. So I'm going to F4, F4, make an absolute dollar signs the interest range. Or this one is the payment range. I do want that one to the move to the right, so I'm not going to do any F4 stuff on actually, no, this one is the criteria, that criteria, I want it to move down and this is the tricky mixed reference, but I don't want it to move to the right. So that means that I'm going to put $1 sign here. But not there. It's not absolute but mixed. That's where the fanciness comes in, that you could copy it to the right and down. And then on this range, I want that to move to the right to L next time. So I'll leave that as is, so I can copy it down. It looks like it's doing the right thing. I could copy it to the right, not to the loan balance, but it looks like it's doing the right thing. It's doing the right thing. That's what we do here. We do the right thing. That's what we do. Most trying to do the right thing. That's what they that's what I was. That's how it's taught. There we go. So now we're going to say There it is. That looks good. Okay? So now let's do the minimum loan balance here. This is gonna be equal. This one's a little bit different. We want to take the minimum balance. So the minimum balance equals the min. Little bit different on the criteria ordering here, but we're just going to take the Min range, which is going to be the Min, the Min range, which is this one first and then comma. And then it wants the criteria range, which is going to be this one. And then comma, and then it wants to criteria, which is this one. So same kind of thing, but they a little bit different ordering on there that took the lowest one which is right there. I think I was right. I didn't really read it, but I'm pretty sure it was right. Then. Copy it down with the Fill button. Fill button. So there we go. Now you could also do that same thing with a pivot table, which is a little faster. There's pros and cons to it. Notice that I can't pick up these two things for the header because I got two rows. That's the problem with having these two rows and a header instead of wrapping it. So there's pros and cons of that. So I'm just going to use this as the header and that's okay. Copy that all the way down. And I'm just gonna do a pivot table this time. Pivot table. Same thing, different way, different way. I'm going to put that run underneath here. So I'm going to say insert a pivot table. Pivot table. And I'm gonna put it in the existing area. I'm going to put it right there. That's where I want it. Let's put it let's put it down one I thought she wanted to know I want it. I want it there. So I'm gonna say, okay, and then I'm going to add my criteria the year. I got to pull this to the left, pull that to the left. And then the other I don't want them month's payment interests decrease. There we go. But now it's formatted, ugly. That's so that's so messy looking. So what we do is we fixed it up, we go into each of these, I'm gonna hit the drop-down value. Let's number format and make that currency brackets. Remove the dollar sign and decimals. And, okay, and way better, way better. Do it again. Do it, do it again, do it for the next one. So that one, it's not ugly either. So we're going to make brackets, read and get rid of the decimals on that one. So much better. Okay, two more times. Last one is a little bit tricky, little bit more trickier. So we're going to say, just doing the same thing here. I'll throw a vase, otra vez lo mismo casa. And lastly, this one, this one is the tricky one. We want to make that a min, not a sum, a min not a sum. And then the number format, this part's the same, lo mismo. And we're going to say get those, get that out of here. And there we got it so good. I'm gonna skinny up the columns a bit, skinny him up. Now, notice that these down here are rounded so I could wrap the text. Now I might want a total column. The reason I did this is I might want like a total down here. We've got the totals, right? So that gives us the grand total of the payments and the, and the interest in the loan balance. So that we could do that here. We could do that total here if we wanted to summing this up. It's not like it's not like we couldn't do that here and then just copy it across. So that can be useful, a useful tool. And let's make this blue and bordered. So there we have it. That's something you can't really see on the other, the other. If you do it over here quite as easily, and we can update these now let's do it. Let's do the other ones for the other table. So I'm going to do it again. I'm going to unhide these cells. I'm going to put my cursor from N to 80 NAD. Right-click my I had a car that had a license plate at 06:58, NADH 658. Now, any case, unhide. No one cares about your car. I love that car. It was a Volkswagen bug. I just told you no one cares. Okay, so now we're going to hide from V on over. Ah, right-click and hide. And then we'll hide. Do I need to hide this? I don't need the height. That'll just keep it right here. We just need to add a skinny. I'm going to do this faster. We're running just going along. This is way long timeframe. Let's go to the 0 here. I'm going to copy the skinny 0 and put that on the AI. The AI now it's just an a because you can't see the eye. And then I'm going to take the headers. We're going to put that up top, copying and pasting. I'm gonna get rid of the month because I don't need the months on this one month's or not. What we're looking at here, get rid of it. Years are going from one. How many years was again? 30. This is the long one. This is the long one. So here we go, 12, that gives us r. And then I'll take the fill handle and get a good grip on that handle with my my pointer finger and I'll drag it down. Well, that's where to sit there. I'm not going through 60, I'm just going down to 30. Don't get carried away. Don't get carried away. Alright, so then we're gonna go to Alignment and center. And then we'll do the same thing here. So we're gonna do sum equals the sum if Summit, if I'll tell you, I'll tell you when you submit, this is how it works. You're going to take this range right there, the p.band range, and then comma, the criteria is one. If you see ones in there, then comma, you're going to sum up some range, which is the payment range. That's what I want you to do. Let's let's actually copy this across. So how can I copied across the pp? I don't want that to move. So I'm going to say F4 and the keyboard, this one is the tricky one. I need the mixed reference, that one, I don't want it to move to the right. I want it to move down though. So I got to put $1 sign before the lettering, but not before the number. That's the trickiness and in the RR is the resting and relaxation. You can just, you can just sit there, rest and relaxation RR and then Enter. So then we're going to say copy this to the right. Does it still work? Does it do what it's supposed to do? It does. It does do what it's supposed to do just the way we had planned that it would do what it was supposed to. How ironic. I'm going to copy that down with the Fill button. So I think that works. And then we've got the slightly different calculation equals the sum. Now this is the Min. If the Min, if that one's for the last one we're going to take we're going to take this time. The men range, Min range, there's men on the range, and then comma. And then we've got the criteria, criteria range, which is this one. And then comma, and then we got the criteria, which is that number one right there. Boom, bam. There it is. Let's copy that down. So that's the men 20 to 20, which should tie out to the 22735. Let's put a total down here this time. Total. Sum it up, sown it up. Literal door line. It's been okay. Stop. This one doesn't need to be some that doesn't make any sense. And then we'll make that blue and bordered Home tab font, group border blue border, blue, border blue. Let's make a pivot table out of it. Same thing. I can't copy the header up here, just from here, on down, from there down. And we'll just construct a pivot table just like we did last time, which is doing the same thing in a different format. So you can choose whichever ones best for use, choose what's best for use. And so I'm going to say insert pivot. And then we're going to put that in an existing worksheet right there, right there. That's where we want it. I'm not changing around this time. I know where I want it. I know I'm going to pull this to the left. I know where i'm, I know what I'm doing. So we're going to then adjust the formatting on these same kind of layout. Let's just adjust the formatting number format and we want currency, brackets. None decimals gone. Boom, bam, better, boom bam, better it is. And then do that here, format, Currency, brackets, dollar sign, gone decimal down, down, boom, bam, better. And again, again, do it again. We're going to say currency brackets, dollar sign gone, decimal down, down, boom, bam, better. Lastly, we got the bit of a tricky one down here. We need the Min, then the number, and then currency brackets, dollar sign, gone down, down decimal. Okay? Okay. And we have it completed. I'm going to make it skinnier. So there's another way we've basically did the same thing and in another format. So we can see a side-by-side. Now between these two, we could do a difference if we wanted to on a year-by-year breakout between the two scenarios for interests and the loan decrease and so on. And then let's do it one more time. Uno vase mosque poor or five or I'm going to unhide. I'm not sure where I need to unhide something, just going to select the whole thing and unhide. One more round. Why? Because I didn't hear no bell. I didn't hear. I only stop when belt when there's like a belt. But it happens. I'm going to make this a little skinnier. 6. Adjustable Rate Mortgage (ARM) Rate Increase : Personal finance practice problem using Excel, adjustable rate mortgage or arm rate increase. Prepare to get financially fit by practicing personal finance, who we are in Alberta Excel worksheet. If you don't have access to the Excel worksheet, that's okay because we basically build this from a blank sheet. But if you do have access, there's three tabs down below an example Tampa practice to happen a blank tab. The example tab in essence being an answer key, let's take a look at it now. Information on the left, we're going to use that to build our tables. On the right, our scenario being that we have an adjustable rate mortgage, one in which the rate can change over time. And we're going to start out thinking, as of this point in time, what would happen if the rate than increased from the 6.5 to the eight per cent will think about the increase in the payments, which would be the first thing that we would most likely be concerned about to see if the cashflow is there. And then we might want to build, we would build in our amortization tables and we can get more complex scenarios such as what will be the interest and the loan principal and the equity that would be involved. And then we'll think about it from a different perspective. Looking at the loan, the original loan being a 30-year loan, adjustable rate, which was then adjusted. Meaning if you were taking on an adjustable rate loan from the start, you might then have more complex scenarios in the future that you would have to be running to say what would happen if the rate was to increase five years from now or one year from now. And try to think about best-case and worst-case scenarios given the risk factors are going up, That's the pros and cons of going from a fixed rate to an adjustable rate. You're typically looking for better terms in terms of the rate upfront. But you've got your exposure towards risk, which you'd have to approximate in some way in your decision-making process. The second tab is going to have the blink information on the right in blue. So you don't have to work on reformatting the whole tables if you don't want to. And then the blink tab we're going to reformat the whole table is basically working from scratch. Now if you don't have this information, you could build it pretty quickly. Just put this data on the left-hand side. You could basically take the whole sheet. I would select the whole sheet, format it first for your underlying formatting. You don't need to do this if you have the sheet, which I usually make a currency brackets and then remove the decimals and no dollar sign. And then, and then you'll adjust it as needed, e.g. formatting these as percentages and adding decimals as needed. Okay, So first we're going to save we've got the loan amount at this point in time, is that the 131466, the years are 20 and then we have the adjustable rate at the 6.5, which is going to, we're going to say, what if it increases to 8%, what would be the impact on the payments in that case? So that's the first thing we'll think about the original, the original payment or OG amount, as they might call it, in a more slinky Current. Cools term would be negative PMT or payment calculation. I started with a negative instead of an equal because I think that's the fastest way to type it. Although it's probably more proper to have a negative inside here. But I'm going to put negative PMT. We're going to start off with the rate was 6.5% Alpha rate. That's a yearly rate. So we're going to divide it by 12 to get the monthly rate. Remember, all the time whenever you hear the rate, it's always a yearly rate because that's how we talk about rates. Because if you talked about them in days or months, they would be small and not very easy to communicate. So comma, the number of periods we're going to say is 20. That's in years though, and we want it in months. So I'm going to take the 20 times 12, 20 times 12, and then comma, and the present value is at the 131466. Now we're considering that's the loan balance. We're considering like at this point in time, when the rate, basically what changes are general idea here. So we're going to say, and we'll expand on that later on in the problem. So that's gonna be the 980. Then the rate goes up to eight per cent. So now the new payment, new payment amount is gonna be, let's do the same thing with the 8% negative PMT. We might be able to copy this first one down if we wanted to adjust the absolute ties and mixed references, but we want to recalculate it to negative p empty brackets. The rate now is that 8%, That's a yearly rate. So we're going to divide it by 12 to get the monthly rate comma, number of periods is still at the 20. And so we're gonna say that's years. So I got to divide that by 12 to get to the monks. And then comma present value is still at this 131466 where we're at the same point in time with these different rates and essence. And something went horribly, horribly wrong because that doesn't, that doesn't make sense. Let's do it again. Negative p, empty brackets rate is this 1/12 comma, number of periods is 20 times 12 comma. And then the present value, the one-thirty 1466 and enter. That looks more reasonable. Okay, So then the difference, the increase, I'm going to take the bottom one minus the top one because I'm going to say it's an increase in the payment. So I'm going to take the 11o minus the 9809119 increase. There could be pennies involved here because I don't have the decimals currently. I'm looking for the underlying font and underlined, if I wanted to add pennies to this, I could do so, but I'm not, I'm going to take them back out. And this is a rounded number that we're working with here. Okay, so now we could think about this from a more complex scenario because you might think that's the first thing that you would think about. Can I afford that difference or change? But you also might want to think about, well, what's the impact on the interest for my taxes, e.g. and what's gonna be the impact on my equity kind of calculations if I plan on selling the home at some point in time. So to do that, we might want the amortization table. You might want to break this out by year. Again, you can do this online with online tools, but it's not as tied into your worksheets. So you can't really run as much scenarios as easily and run different scenarios with more complex worksheets because everything won't be connected. In that case, I would use this to double-check the work oftentimes and then try to reconstruct it in our table here. So I'm going to start off with the OG loan. I'll call it the OG loan. So I'm gonna take the skinny C and I'm going to make another skinny f out of it that's the same size Home tab, clipboard, paintbrush, it's skinny F. And then let's make our headers. I'm going to call this the OG loan, and I'll make that red just to give it, That's my title of this of this thing, the OG loan. Let's make it red and white. And then this is gonna be the year month payment, as we've seen in prior presentations. I'm going a little bit faster here. Interests loan balance. Loan balance. Notice I'm not wrapping the text. I'm using two cells so that I don't make us of a wide one, a wide one. I don't want the one to wide loan balance. Then I'm going to select these and that one. And let's make that black and white. Let's make it black and white, and let's center it. There's our headers. So I'm gonna make these two are a little bit more skinny, little more skinnier, skinnier horizon. Kind of skin arise it. That's what, that's what I call my diet. As skin arise myself. I'm doing some skin arising. And then I'm going to say, This is gonna be months, 120 times 12, 20 times 12, number of months. We need to 41 to let's do a 012 and then I'll go my shoe real quick. Now I'm back and I'm going to copy it down. We'll select those three and a copy that down to 241 to buckle your shoe and then fill handle it down to 240, which makes sure your shoes buckle because when you auto drive this thing all the way down, you don't want to trip. So then we're gonna go to the Home tab. We're going to go into the alignment and center it. There we go. And then this is gonna be zero. And then we'll do our R, our roundup formula to get the years here equals roundup. This is useful for the pivot table and the data that will do soon. So round up brackets, round up little doggies on the range. And so we're going to say Roundup that number divided by 12. But then I'm going to round it up with a comma. And then the number of digits, it's going to be at 0.1. Which means what to the whole number? To the whole number. Don't give me don't give me these partial point numbers, fraction phones. That's not what I'm looking for. And then I double-click on the fill handle button and then scroll down and it looks like it's doing. It has been designed to do. And then we'll center it. Just amazing. Whole thing is just amazing. Home tab, Alignment and center. And then I'll put some zeros here up top. And then the loan balance is going to be equal to the 1301466 enter. The payment is going to be equal to the OG payment, the 0 to the G payment, which was right there, the 980, I'm going to F4 that one absolute ties in it. We only need a mixed reference, but absolute works dollar sign before the E and the ones when I copy it down, it doesn't move that cell down because it's outside of my table information here, it's in the dataset, therefore, it needs to be absolute ties. That means absolute referencing it. This one is going to be equal to the loan balance times the interest rate, which is the OG rate, the original low rate that I wish we can hold onto that needs to be absolute ties because it's outside of my table in my dataset. So I'm going to F4 it dollar sign before the B and six, you only need a mixed reference, but the absolute one is easy. Divide it by 12 because that would be the interest for a year and we need the month interest and enter. Now we're going to have to loan decrease, which you might call the principal decrease if you were so inclined. And who's to stop you to be inclined? Leslie? And then we're in the loan balance, which is going to be equal to the prior loan balance minus decrease. And those are both in our area here and not in the dataset. So we don't need to absolute ties, any of that. No absolute references needed or mixed references or any kind of strange references to obscure things. I'm going to double-click on that. And then it'll copy it down, double-clicking on the Fill button. And it should be zero at the bottom, zero at the bottom. There's there's what it would be in the owed to the GI loan payment from that point in time for 20 years. Let's make that blue and then blue and board arised in a painted it blue. And then I've put some borders around it because it's so nice-looking that people probably want to break into it. So then I need to put the borders up. So then I'm gonna, I'm gonna make this a skinny. And let's break this out on a year-by-year breakout. Now we'll do that a couple of different formats and then we'll do the same thing for the new loan term. So I'm gonna, I'm gonna copy this skinny column F. And I'm going to put my cursor on the Format Painter and skinny up M F to the m FM, just like the radio station. And then we're going to say, I'm going to copy the same headers over here from the OG to the loan balance, copy that and put that right here in the end. But then I'm gonna get rid of the month because I don't want months. We're talking years overhears, years overhears, right-click and delete the months, and then the years. How many years overhears do we want? We want one to buckle my shoe and once my shoes on tightly so it doesn't slip and roll an ankle. I'm going to grab that fill handle and I'm gonna drive the auto drives it all the way down to 2020 years. That's what I'm talking about. And then we're gonna go to the Home tab, Alignment and center. So now we're gonna do our payments. So we're going to sum up, I want to sum up these payments. Everything in the ones and the one area right there, it should add up to the 11 760-211-7602. We're going to use a sum if a fancy formula, sum if fancy formula range in it. So we want to pick up this range. I'm going to pick up the whole thing in column G. I'm just going to say take the whole G column, gigi, gigi, gigi before. And then criteria is gonna be this one right there. And then comma and the sum range. Some range is going to be here. So we're saying everything that has a one in this criteria range over here, I want you to sum up the related number and the payment range. Boom, does it, and then it just sums that up. It's just incredible. And crushed he blade. Let's do it for the interests equals the sum. Range is going to be this same range right there at GG again comma and then the criteria, the number one again comma and then the sum range. Now this is different interests, that's what we want. That's what we want. This one will change from year to year. It's going to have a differential from years one, years too low and decreased. Let's do this. One equals the sum if I want you to summit, but I got these conditions. This range right here, you want to look at that range, GG range. And then I want you to take this criteria and find it in that range. And then some comma, the related some range field, which is, which is this is the loan decrease. That's what I want you to do. So there it is. Now I'm gonna do it one more time so that I can copy it. I don't gotta do it again, but we're going to copy it across this time. I'm going to double-click on this one. This GG, I don't want the GG to move to the right. So I'm going to say, let's just f for that one. So it doesn't move. This one needs to be a mixed reference because I want it. I want this number here not to move to the right. So I need $1 sign before the end, but not the three because I want it to move down. Trickiness. The III is one that we want to move to either G to k. So we're going to say, okay, leave that as is type theatre cross with the fill, auto-filling it, we should get the same thing. And then I'm going to double-click on the fill handle button and it copies it down. Just, just miraculously, it's like magic. It's like magic happened. Magic just happened. Light in front of my face. And so then we're going to say this is going to be equal to this one's a little different. I want to take the minimum, so equals the min if brackets. And then the men range is going to be this range G, and then comma. And then actually, sorry, I got that big, I got that mixed up. The mid-range is the credit is this range, the loan balance range, and then comma, and then the criteria range, That's the gigi, gigi, gigi is over there. And then we've got this last one. I'm trying to pull this one over so you could see it. And then the criteria is the one. There's the one. And so that takes the lowest number with a one next to it. There's the 1208152. Let's fill handle button it. Just double-click on that Phil handled button. Let's total it up down below. Total. Totally totaled. Totally, totally. Do you want to total it? Totally? I totally do. We're going to copy that across now, totaling it that way. Total, total. Alright, then we're gonna go up top and make that blue and border. I'm gonna do it also the same thing with a pivot table, which we've seen in the past. So I'll do it a little bit more quickly here. I can't copy the headers because there's two lines. I'm just going to use this header column. I'm going to go all the way down and I'm just gonna do the pivot table thing, same thing to it. Pivot table, insert pivot table. And we're going to put that right there. That's where I want it. That's where I want it. Okay. Hold on a sec. That's the wrong table. Undo that. That's not a pivot table. Whereas where did my undo things go? What happened? Okay, I fixed it. Let's insert the right table. This time pivot table, That's what we're looking at. Pivottable existing, concentrate, focus, focus like a beam of laser. And then I'm gonna take the years over here. I'm gonna pull that to the left. So there's our years. So now it's pulling over this data and now I'm just going to take not the month, but the payment, the interests, the data, and the balance. There. It just makes a nice table for us, just automatic Lee. And then let's format this thing. I'm going to hit the drop-downs and say value formatting, number formatting, I want to make it currency brackets, dollar sign gone decimal down, down and okay, okay, let's do it again. I missed that you did that way too fast. Let's do it again. Value Field Settings. On the next one, we're going to say number formatting, currency, brackets, dollar sign gone decimal down, down. Okay? Okay, that's still way too fast. Do it again. Okay, let's do it again. On the third one, we're gonna go number formatting, currency brackets, dollar sign gone, decimal down, down. Okay? Okay. I'll do it one more time. Slightly different though this one's different because I don't want the sum. I want the men, the men, the men, men. The next part is the same currency brackets, dollar sign gone decimal down, down. Okay? Okay, yay, yay. Okay, So now let's, I'm gonna select these columns are going to make them skin or eyes him. Just like when I go on a diet and Skinner as myself, I'm just going to skin arrives myself a little bit right there. Trim that, trim off some Skinner, skinner rising. Okay, so then we're going to, we're gonna do the same thing for the new rate one. So we can do a side-by-side things on one side, the other things on the other side. I call that a side-by-side. So I'm going to say that we're going to hide from, from F. Well, let's first copy the header. I'm going to copy this header from the OG. Let's copy that header here. And I'm gonna put that over here. I'm going to copy that header right there. And this isn't the OG, this is gonna be the rate change, one change. Okay, so now I'm going to hide, now I'm going to hide over here. Let's actually take the skinny. Well, let's go from the skinny, from H on over to right here, h to our horror, horror. Right-click and hide that stuff. And then I'm gonna make a skinny out of the S. I'm going to put my cursor on the C column. Home tab clipboard format painted and skinny skin arise to S, automatic skin arise. And then we'll do our months again, this one, how long is this 120 years? 200 to 40. To 40. So we're gonna say one, let's say 012, buckle your shoe because you don't want to roll an ankle. We're driving along way here. We're going to take the fill handle. We're going to drive this arm down 240, driving it down with the with the autofill. We're in our auto and we're driving it down with our shoelaces tied, buckled. However, your shoes work to 240. So there it is a 240. Does anyone have buckles on the shoe that would be like a boot book. Buckle your boot. It sounds even better. Buckle your shoe. Buckle your boots. Boots have buckles. Don't they? More than shoes? I don't know. Anyway, zero, we're going to say years are gonna be Roundup equals round up, round up brackets. This thing divided by 12 comma digits, 0.1. Boom. And I'm going to double-click on the Fill button handle thing. And that'll just summit down somewhat owned down, somewhat on down. And then round this and then I'm going to center this one. So then I'm gonna put some zeros up top. We're going to say the loan balance at this time is once again the one-thirty one for 66, but the payment now under the new thing is rising up to 1100. I'm going to F4 that make it an absolute going to take my interests calculation then is the 131466 times the rate which is now eight per cent at the increased rate, I need to make that absolute because it's outside in my dataset, not in the table I'm working on. So I'm going to say F4, making an absolute, you only need a mixed reference but an absolute as easy. And then we're gonna take that and divide it by 12 because that would be per year and we need it per month. The loan decrease is going to be the payment minus the interest. Neither of them need to be absolute or mixed or anything because they're inside the table we're working on the loan balance is going to be equal to the prior balance minus the two to three. Neither of them need to be absolute or mixed because they're not coming from the dataset. But inside the table we're working on, we're going to select these four. We're going to double-click the Fill button, the fill handle button, all the way down, check that it goes down to zero. It does. Let's go ahead and format these blue, blue and blue and border then make them blue. And once they're nice and blue and look good, we got to put borders around it or people will come steal our stuff because it looks so good. So there we go, blue and bordered. So then we're gonna do the same thing on our year-by-year breakout. Let's do a year-by-year breakout. I'm going to copy these headers and put them over here. And then I'm gonna, I'm gonna, I'm gonna skin arise, go from S Home tab, clipboard, paintbrush it's skin or eyes Z making it skin or eyes. And then I'm gonna get rid of the month because I don't need the month. So I'm gonna do this a little bit more quickly this time, most rapidly though. And then I'm going to say this is one to buckle my shoe and then drive it down with the autofill to 20, not too far. 20 We're not going that far. I can go I can go there with unbundled my shoelaces. I can go that far without even my shoes tied. I didn't even need to buckle my shoe. Go that far. But then we're going to say this equals the sum if brackets and the range is going to be, the range is going to be this range. I'm going to make that absolute selecting F4 on the keyboard criteria, then comma is going to be that one, and then comma and then the sum range. Notice that this criteria, by the way, needs to be a mixed reference because I don't want the AAA to move. So I'm gonna say dollar sign before the letters but not the numbers. And then the sum range is the payments. The VV doesn't need any, any absolute ties in because I want it to move to the right as we go. So enter. So wait a sec, something went horribly wrong. Something went horribly wrong and just do it again. Just do it again. You messed it up. Sum, if brackets range is going to be not you, that's the problem. It should be the range of t. That's your problem. You know what your problem is. Okay. I got lots of them criteria. We're going to go to the criteria. That's going to be the zero. That's where we need $1 sign before that one. And then comma the sum range is gonna be the payments. Okay. Try it this time. Does it work? Check it, Double-check it makes sure that you didn't mess it up again. 13169. See, I know what I'm talking about. I'm going to copy that to the right and then double-click the fill handle down on it. And so then there it is. There it is. So then let's do a slightly different one on the loan balance equals the main if S brackets, the Min range, we want this one. Why? Why? Because that's where it happened to. That's where the ranges that's just it's just the way it is. That's why, why, why? Criteria is gonna be this range t u, t. The criteria is going to be the one. Boom. And then it picks up this one down here, check it, make sure you didn't mess it up. You messed up last time. I know. I know I messed it up. Okay. But that was a onetime phone because I'm always right. I'm going to sum up the total serum equals to some of these. We're going to sum these out. And then we'll copy this one to the right, not all the way to the right butt right there. And then let's make this blue and border, blue and bordered will do the same thing with a pivot table. Let's make a pivot table doing the same thing just to show you the different methods you could do this. I can't take the top. I'm just going to take these as the header, a header portion. Copy that down to 20, copy it down to the 20 years. 20 years have passed. And it's been an interesting how that happened. I wondered pivot table. Pivot table. And then we're going to say existing worksheet. We're going to put that right here. And okay, DO k Put the years down? I'm going to copy that to the left and then I don't need the month payments, interests decreased balance. Then I'm going to format these so they're not ugly or at least less ugly. Could you make them a little less ugly? I'm gonna say, okay, It's not my forte making things less ugly. But I'll try. Let's do that. And so that's better. That's better. And then I'm going to format this one. We're gonna make it a currency brackets. Get rid of the dollar sign decimal down, down. And then, okay, okay, too, okay. One more time. We're gonna go to Number format, Currency, brackets, dollar sign, decimal down, down. Okay? Okay, last one is a little bit different and poco distinct day or something. Wait, now you've got distracted because you don't know what you're talking. This is gonna be a Min, Min number formatting and then currency brackets, dollar sign gone decimal down, down. Okay? Okay. And then I'm gonna make this a little bit skinnier. I'm gonna skin arise it a bit. Do a little skin arousing. Like that. Looks good. Okay, so that's another way we can do it. This one, too skinny, too skinny, too skinny. That's not healthy. It's not healthy. Okay, so there we go. Okay, so now lastly, let's think, let's think about this a little bit more complex. Let's say that the beginning of the loan, Let's make another skinny column. I'm going to go from Z here. And I'm gonna put that over here and make that skinny. Let's say that the loan actually started at a 30-year loan, which was 155074 years. Ears were 30 years. And the rate, the rate was the same rate of 6.56, 0.5 are starting rate or 0.065. And I'm gonna make that a decimal, add some brackets to sum. So let's think about that because now we're gonna kinda back into this 20-year loan. So notice here we kinda, kinda cheated here. We kinda started as if we're at this point in time, that's the loan balance. What would be the difference in the payments? But clearly, if there's, if you're starting at this point in time, the 6.5, you might have like a 30-year loan or something like that. And then you gotta make projections into the future. What if the rate changed 20 years from now to 8% or something like that, what would be the total play out of that? And clearly, if you were planning on selling the home or something like that, then you might have different scenarios in terms of what would the equity be at that point in time and so on with different kinds of scenarios. So let's, I'll try to kind of back into that by, by using this scenario. So we're going to say we started off with a 30-year loan at the loan of 155, 74. And then after 20 years, we're going to assume that that's when the rate then is going to basically increase, right? So I'm going to say, okay, let's, let's imagine that scenario. So let's run it. We're gonna say, okay, then the payment, payment would be equal to, I'm going to calculate the payment, which is going to be the PMT. Let's make it negative PMT. Pmt payment under these conditions would be the rate, the 6.5 per cent that's yearly. So I'm going to divide it by 12, and then comma number of periods is gonna be 30, 30 years this time, that's in months. So I got to multiply it times 12 and then comma, and then the present value of the 15574, there's the 980, that's the same 980 that we talked about in the original the original scenario. That 20 years we're going to say we started a 30-year loan. And let's run that amortization table just to, just to kind of back into this, get a fuller picture. So I'm gonna make this a skinny. I'm going to take this skinny. I'm going to put that skinny over here. I'm going to make this blue. Let's make this blue and bordered. And then we're gonna go over here and say our headers. So let's go pick up our headers so I don't have to type them in again. And I'm gonna, I'm gonna copy my headers up top. Same kind of thing. Lo mismo, CMP way. Okay, So this is the OG 30-year. So this is the OG ODE, the real OG. And then the months are going to be 30 years as 36001 to buckle your shoes, you need some tight shoes for this one because we're driving this thing down. 360. We're going to take that fill handle, drive it all the way down 360. You want to make sure you got your shoes tight. On that one. We're going to go down 363, 60, full 360 rotation. We're going to center it. And then the years are gonna be equal to. We're gonna do a round up, round up the little doggies brackets. We're going to take up on K4 divided by 12 and then comma. And we want to take that to the full digit, which is 0.1. And so that rounds it to one. Copy that down, put your cursor on the Fill button is zero up top. And then I'm just going to center this whole thing. I'm just going to check that it goes down to 30 years. It does. Of course it does. What you think I messed it up or something. I know what I'm talking about. I'm going to make this go skinny. And then we're going to put some zeros here. We'll say the balance is at the 15574. Payment is going to be equal to that 980 F4, making it absolute dollar sign before the letters and numbers, you only need a mixed reference, but an absolute we'll do interests prior balance times the rate 6.5, going to make that absolute because I want to copy it down and that's in our data table. So I don't want it to move dollar sign before the number and the letter comma, not a comma, then divide it by 12 because that was the year we want months. And so we'll subtract this out the 980 minus the 840. Then I'll subtract out this. This is the loan balance minus the loan reduction of the 140. Selecting those four or five, did it right? I should just be able to double-click the Fill button handle thing. And that should then create zero at the bottom. If everything was constructed the way that I imagined it to be, my imagination has been, has come to life. That's exactly how I imagined it to be. And then I made it than I like. Then I constructed the thing in my head on the table. So I'm gonna make this, I'm going to go up top and make this blue and bordered blue and border it. So there we have it. And you can see then if I go down and say, Okay, after ten years, after ten years, this is the point that I was trying to get to. After ten years down, here's after ten years down here. We've got that's where we kind of made that change. The 1301466, that's the loan balance at our starting point here where we said the one-thirty 1466, I kinda constructed it to be that. So you can say, we've thought about it as this point in time, but you can think about it as if you're going back from a 30-year loan and then where would you be if the rate changed? If you're starting from the beginning, then you got to take into consideration the risk somehow and run like a worst-case scenario in a best-case scenario in that kinda stuff. To compensate for the added risk that would be taken into consideration, which gets complex, but that's kinda the idea. Let's make this into our table thing again, I'm going to make tick this skinny and make a year-by-year breakout. And I'm going to copy these headers, copy those headers, put that right here. Let's get rid of the month because I don't need those months. We're not talking months now. We're not talking months. We're gonna say 12. And then I'm not even going to buckle my shoe. Don't even book well, you shoe because I can do this with my I'll do this barefoot. I'm not even putting shoes on. So you can just my feet are tough enough to go down to 30 without the shoes buckled. So I'm gonna say this is going to be equal to the sum if some, if brackets, the range is going to be not the month range over there. Don't do that again. That's where I messed up last time. Comma, and let's make that absolute F4 on the keyboard. And then comma criteria is this one that needs to be mixed because I want it to move down but not to the right. So does hold on a second. I'm gonna put $1 sign before the $1 sign. And then comma criteria, some range is gonna be the payments. Do that. That's what I want you to do things. Check it, make sure it's not messed up. 117, that looks good. Let's copy it to the right. And then I'll check this one. Checked that one, make sure it's not messed up. Make sure it's not messed up. And that's going to be 1029. Okay, that's good. And then I'm going to copy these down. Double-clicking. Copy it down. Looks good. Muy, muy Bien. This one's a little different. We're going to take them in. Men if brackets, range, we want the range, the range, this one comma, and then the criteria range a j. A j comma criteria is the one. Copy it down, double-clicking. Let's put a total down below. Do you want a total column? I totally do. I totally want a total column. Sum that up. Why even asked that totally? And now we'll copy it across. I'm going to put my cursor right there, copy it across with the fill handle, pulling the fill handle to the right. Let's make this blue and bordered border, blue, border blue. I'm going to select this and say there's the border, there's the blue border, blue. Now after the ten years, That's where we're at, at the ten years that what sets when we said that we ran the scenario from, you'll recall so right there, the one-thirty one for 66, that's when we ran this scenario after that 20 year time period as of that point in time. But you can also think of it all the way back out on a 30-year loan. Just for example's sake. Let's round it out with a pivot table. This thing needs to be rounded out because there's an edge. Get out the sandpaper, round this thing out. Here we go. We'll stand this thing off. So I'm gonna take this thing, we will do the pivot table thing. I'm going to take this all the way down for the 30 years. It's kinda long one, because this is the big guy. This is the 360 time period all the way round, 36,300.60 degrees and periods and months. So we'll take that and I'm gonna go to the Insert and then put in the pivot table. Putting in the pivot, please. Put in the pivot, please put in that right. Hold on a second. That's not how you do it. Messed it up. Do it again. I'm going to insert pivot table. Then I got to say I want it in the existing worksheet, that's the key point. Then I'll put it there. And okay, now let's clean it up so we can send valve and round it out. We're going to say year, pulling that to the left and not the month, but the payments to interests to decrease the balance and then make it look better. We're going to hit the drop-down Value Field Settings, Number format, Currency, brackets, dollar sign gone decimal down, down. Okay? Okay, next one, do it again, Value Field Settings, Number format, Currency, brackets, dollar sign gone decimal down, down, okay? Okay, again, Value Field Settings, number formatting, currency, brackets, dollar sign, gone decimal down, down. One more time. Slightly different though, last one, value field, this time not the sum but the men. Then the same port, number formatting currency brackets, dollar sign gone decimal down, down and okay, okay. Okay. And then they'll skin or eyes these skin or eyes, these just wish I could get her eyes, my belly like that. 7. Saving for Down Payment on Home: Personal finance, excel practice problem, saving for a down payment on a home. Prepare to get financially fit. Practicing personal finance. We are in our Excel worksheet. If you don't have access to the Excel worksheet, that's okay because we basically worked this from a blank sheet. If you do have access, there's three tabs down below. There's an example tab of practice tab and a blink tab. The example tab in essence being an answer key, let's take a look at it now. We've got the information on the left-hand side. We're going to be saying that we're saving up for the down payment on a home. Our starting point, which we can vary and alter. Once we have our worksheets set up, We'll be that we're going to save 7,500 for six years at a rate of six per cent. We're then going to see how much downpayment we would have after the six-year time period. We'll calculate that a few different ways. And then once we have that, we can think about how much we might have to finance considering that we need to put 20% down and assuming that we can finance basically the rest, once we have that, will then construct our amortization tables and our yearly basically summary of that amortization table. After that's all set up, then we could alter some of our data on the left-hand side and run different scenarios with it being one of the big benefits of doing this information or work in these kind of things in Excel. So the second tab, the practice tab, is going to be a tab that has some preformatted worksheets. So if you would like to use it, then you don't have to do as much formatting. The third tab is gonna be the blank tab, which we're just basically going to add the formatting working in essence from a blank sheet, just from this scratch, working from that scratch, working from scratch on the left. You can add that scratch if you don't have this worksheet. If you don't have this worksheet, I would suggest selecting the whole thing, right-clicking format in the sales to currency brackets, no dollar sign and remove the decimals. That's my starting point. Whenever I need to vary from that, I'm going to vary from that. I also make it bold by the way, but you don't really need to. I think it's easier to see on the presentations that way. And then you can just add this information, making that a percent and we're good to go ready to start. Okay, so we're gonna say that we're going to put 7,500 down each year to save for a down payment on a home. We've got these six years and it's gonna be six per cent. So we can do the easiest starting point is just a future value calculation. And this is also the most flexible because it allows us to change the years more easily, but we'll double-check it a few different ways practicing our future value calculations. So we're gonna say future value. Let's just call it f v. This is an annuity formula. I'm going to do it by saying negative instead of equals, which is probably not the most proper way to do it, but the fastest, I believe, future value, we're going to pick up the rate. This is per year, that's we're going to keep it per year. We're not going down two months. So it's gonna be easy as something that's really easy to do, like counting to three. That's how easy it is. And then we're going to say that the next one's going to be the number of periods, which is six. And so that's in years two. So then comma and then it's an annuity. So we're gonna be picking up the payments and not the present value because we're going to put that 7,600 in each year for six years. So we're going to say Enter, and there it is. Let's make that blue and bordered. That is an estimate. Probably pennies in there. Well, hold on a second. I don't want to make it white right there. That's not what I want to do. There are pennies, but we're going to round it right there. Now. We're going to double-check that because I'd like to see, let's make it borders. That's what I wanted to do. That's the other thing, blue and borders. Let's double-check it with an actual annuity calculations so I can envision what is actually happening. I want to see my money grew. So I'm gonna go to the Home tab. Let's paint brushy this skinny and make this one a skinny. Let's see how does this grow in money thing happened. Well, let's build a table on it. Periods and investment and then income. These are my headers or the table by the way, balance. So I'm going to, I'm going to make this a little bit larger because I can't see the words, all the words that I typed. I want to see those words. So it tells me what what's in below them. That's what tells me what's under him. So I'm going to then go to the Home tab. We're gonna go to the fight group. Let's make this a black and white up top alignment and center. And then we're going to have six periods. So I'm just going to say 123456. Now you could use an autofill to do that, but I just kinda like typing them in if there's only six periods because it's fun. We divert Edo. We're going to select those Home tab. We're gonna go alignment and center it. We can make this one a little bit skinnier, rised it with skin arise it because it's, we don't need that much space for numbers. And then we're gonna put 7,500 in per period. So I'm gonna say this is equal in the 1500s we're going to put in each year, I'm going to say F4 on the keyboard putting $1 sign before the B and for making it absolute, in other words, you only need a mixed reference. But an absolute works because I want to copy it on down. I'm gonna do that by just double-clicking the Fill button handle button thing. And it just copies it down, boom. And then we're not going to have any income in year one because you gotta be careful on when you're putting the 7,500 and how the annuity table works. So you put it in like at the beginning or end of the year. So here we think about it as no income happening in year one. That's out. The annuity, normal annuity formula works. And then in year two, that's when we're going to have the income that's gonna be generated. So just be aware of the annuity calculation beginning periods and how that's working. That's one reason it's nice. Double-check your annuity future value calculation with an actual table. So we're going to say this is gonna be equal to the seventh thousand 500 times the 6%. And that's how much we're going to earn in the next year. We're going to say this is gonna be equal to the prior balance, 7,500 plus the sum of the 7,500 we're going to put in at the end of year two, we're assuming and the earnings that we had during your two of the 450, closed up the brackets and Enter. Then we'll do it again. We could copy it down, but I'm gonna do it a couple of times so we can see how this thing works. 15 for 50 times the 6% tab. Now this equals the prior balance of the 15 for 50 plus the sum of the 7,500 we're gonna put in at the end of year three and the 927 that we earned during the year Enter and then it closes up the brackets formulas. Do it a couple more times this number times the 6%. And now we have equals the prior balance of the 203877 plus the sum of the 7,500 we're going to put in at the end of the year. That's how the annuity formula is thinking. And then the fourth 1,000 for 33 that we earned. She might be thinking, Hey, I'm going to put the 7,005 in during the year. It's an approximation. This is that we have to kinda think about it that way. If you're using a normal annuity or you could try to break it out on a monthly basis. And if you want to get more precise and whatnot, but it is an estimate in any case. Let's, let's stop this from here and let's try to copy it down because this is getting tedious. Let's delete this. You're going to delete. I'm going to delete it. Are you sure? Yeah, I can do it again. If I tried to copy can I just copy this one down? If I copy that down, this is a problem. That's a problem. Let's delete that. I think I need an absolute reference or mixed reference. You need something if it's in the dataset outside of your table that you're working on. That's when you really need to generate absolute ties it That's d5, or make it a mixed reference. I'm going to say F on the keyboard making an absolute dollar sign before the B. And by five, you only need a mixed reference, but an absolute will work. I'm going to select these two cells and now I should just be able to double-click the Fill button. And there it goes, summing it, summing it on down. So we're ending up at 50 to 315. We've kinda double-check that number right there so we see how it's calculated in a bit more visual way. So let's go ahead and bracket tides this one. And just to, just to see it another way, you can also break this down to an annuity of one, a series of annuity calculations, which is another way to just kinda understand these present value calculations. So I'm gonna put my cursor on the F column. And I'm going to say the paintbrush and let's just do it another way, which is kind of an overkill. It's already dead and you keep on killing it again. You can't kill it again to overkill that this is overkill. But I want to make sure that we've got this down cold, needs to be down cold, so we've got an overkill it until it's cold. Okay. I don't know what I'm talking about. Sorry. But anyways, we're gonna do this another way, we're going to select these items. We're going to go to the Home tab, Alignment and center. And then we're gonna go to the bucket, make it black and white. Let's make the investment column a little bit larger. And let's do the periods again, which are 123456. And this will be the total down below. And this time I'll just put the 7,005. Let's do it the same way. I'll just say equals this number. In that way, I don't need to absolute times, it'll just copy that one down because it will copy the same relative number like that. And then we'll just, we'll just do the, the future value for whatever time-frame that we are in. So in other words, this 7,500, if we put it in at the beginning, will be in there for the next five years, two years two through six. This one will be in there for the next three through six. So let's do, let's do that kind of calculation using the present value of one to see how much we'll have at the end of each of each time frame. So we will do that thusly. Hopefully that I like to say, thus late makes you sound, makes you sound smart when you say thus, thusly, any case. Equals the future value. Let's make it a negative future value brackets. And then we're going to pick up the rate, which is all the way on the left. Now, it's going to be that 6%. I'm gonna say F4 on the keyboard. To make it absolute, you only need a mixed reference, but an absolute one works comma, then the number of periods this time, I'm gonna do it one by one. And so what I'd like to do is this one is going to have five periods that we're going to put this singular investment in, that it's going to have time to grow over. I'm gonna calculate that this way because I would like to be able to, instead of just typing five, in other words, I want it to be able to copy it down. So I want to pick up this number six and then minus this number one. And so that'll come up with five, which are five periods. And when I copy it down, I would like it to keep that number six and then subtract two, which would be 46 minus 33 and so on so forth. That means this last one that needs to be an absolute reference that's in cell L7. So I'm going to say F4. It could be a mixed reference, but an absolute one works. And then comma, we're not going to use the payment because this will not be an annuity this time, but we're just going to use a series of payments of one, so another comma, and there is the present value which is going to be the 7,500. And Enter. Now I can copy that down. And now I've got these series of payments. This one's going to grow after until we get to year six by 10,037, this one will grow 7500-9469. This one will grow to 8933. And as we get closer to the endpoint, that investment that we put in is not going to grow as much because it doesn't have as much time if it grows at that steady rate of 6%. If we sum up at the bottom here. That's another way we can think about getting to that 50 to 315. I know we did that already, but this is another way to see it. And so you want to be able to see it multiple ways. Don't you? Gotta look at it from different angles. Okay. That has been thoroughly, it's cold. It's been overkill to the point that it's really cold at this time. So now let's take that and let's assume that we're gonna have a 20% down and think about how much loan we could get them. So I'm going to select column K. I'm gonna go to the Home tab and paint brushy and then put that right here on oath for a skinny, 0 skinny. And then we're going to say this is gonna be the home purchase amount. So I've got 50 to 315 to put down. That's what I'm going to throw down on it. So I'm going to then let's make this black and white Home tab thought group. Make this black and white. And I'm going to say that I'm throwing down payment, thrown down 50 to 315. And that's what I'm thrown down on the table. And then I'm going to say that the down payment rate is 20%. So we've got 20% down payment. Let's put, let's make that a percent and underline it. And so that means that the home price price could be equal to the 50 to 315 divided by the 20 per cent. So I should be able to purchase that to 65 to 74 if I can finance the rest of the home, If I was able to put 502315 down, Let's check that. Let's double-check that. Check figure, check it out. Let's do it the normal way because I kind of backed and that's not the way you normally do it. Normally you go home. Price is gonna be home price. Usually you start with the home price, which is that number. Then do the down payment, percent, down payment per cent of the 20 per cent. Let's make that a desk percent number per cent ties font underlying, That's gonna be the down payment, not rachis downpayment, multiplying this times that, that's how much we'd put down, that's matching up, that looks like what we would expect. And that means that the, that the finance amount financed is going to be equal to this number minus the down-payment. So we're going to have to finance to 09 to 60. So assuming we can get that financing, then we can throw down the 5023153. Then how much home could we purchase? Let's assume the rate at this point. We could use the same rate to kinda tie this all out. Let's, let's assume that the rate is 6% on the home too. It might be different, but we'll, we'll choose the same 6% because it'll tie everything together. That will really tie the room together. Like a good rug. Does that rub really tied the room together? Then we're going to say that the years are gonna be 30 years and then the payment. So now we can calculate the payment. So now if we're going to do that, Let's see how much our payment would be if we could finance now the 2096 to 60, which we got because we're trying to get as much house as we could. After we put the 20% down. So we're going to say negative payment, PMT, negative instead of equal or payment calculation is gonna be the rate. Now this rate is a yearly rate. Now we're talking months. So I want to take that rate divided by 12 this time to make it a monthly rate comma number of periods is gonna be 30. That's in years we need months, so times 12, and then come up the present value, which is gonna be the loan amount, not the home price, but the amount of the loan because we put we threw down 52,003, 15 of a down payment. That's a down down payment. Down payment is down. So there's the 1,000 to 55. Now let's, let's make this a skinny. Let's do our amortization table the most, construct this whole thing from that little set of data that we had. Let's make this blue and bordered. Now we've seen these amortization tables in the past. So I'm gonna do this a little bit more quickly here. But just we're just going to tie everything together just like that. Just like that rug does with the room at ties to that red really tied the room together. So then we're going to say that we have the, let's put our headers up top. Gonna be your monthly payment and to arrest. And then loan D. Hold on a second. Decrease. Notice. I have two columns here because I don't want to use the Wrap Text and I still want a long header loan balance. Let's move these down. So they're down here. I'm going to take these are going to Control X or cut them and put them right there. And then let's make this a header thing by going to the Font group, making it black and white and centered. I'll make these two a little bit skin arise. I'm going to skin arise in to make them skinnier, which you call it. That's what I call it. They're gonna be skin arised. Ruskin horizon, the columns. And then let's, let's do an autofill, taking this down 36360 degrees really, but kinda like whenever you hear 360, I kinda feel like it's degrees because it's like doing a 360, unlike a snowboard or something. I'm going to center that. And then we're going to say, there it is. And then the years are going to be equal to zero and this is gonna be equal. We're gonna do our roundup or fancy round up to pick up the years, round up, round them up. What do you want to round up? I wanted to take that number. That's what I want to round up and divide it by 12. And then comma rounded up to the whole number, rounded up, round them up. Little doggies, Roundup the little doggies. And then I'm going to double-click on this one. And it's going to double-click on the Fill button. Phil handled button. And there we have it. The roundup stat is that's what we have when I was referring to. Okay. So then on the payments Well, let's make the loan balance is going to be the loan balances that to 09 to 60. And then the payments are going to be equal to that one to 55. F4 on the keyboard. Make it an absolute. You only need a mixed reference, but an absolute will work. The interest is equal to the 209 to 60 times the rate of that 6%. That's outside of our tables. So we need to make it absolute so we can copy it down. You only need a mixed reference but an absolute works dollar sign before the queue and the 12th. And then we'll divide that by 12 because that would be the yearly rate and we need the monthly rate. Then we'll subtract these two out payment minus the interest. That's the decrease in the balanced loan decrease or the principal decrease, you can call it. And then we've got the prior balance minus the loan decrease. And that's our new item. Let's copy these down. These four. Double-click the Fill button, Fill button, Double-click and it should be zero at the bottom. Notice we can always double-check this kind of thing using a trustee loan calculator online if you wanted to. I'm not promoting this particular calculator, but there's a lot of them out there. And you could, you could plug this stuff into the loan calculator and say, okay, what if I had my loan of 2601574? Actually, no, that's not the loan amount. I have my loan of 209 to 60. It's a 30 year. The rate is at 6% monthly calculated, throw it down. There's our one to 55 about because we rounded it. And you can create your amortization table. However, you can double-check your amortization table and that's what I would use it for. So e.g. after the third payment, we're at it. Let's check this to 1041 after the third payment. So we can check that out. We can say, okay, third payment is to ten about, so it looks like it's calculating, but this amortization table is actually tied into this dataset, which you can't do as much. So if I change this dataset, the whole thing is going to change, which you can't, you can't run those kinds of projections that are all integrated. As easily with the loan calculator thing. Let's go, but it's a great tool to double-check and look up some other stuff sometimes to narrow things down. I'm going to make this blue and border. This is another thing we can't do with a loan calculator as much as give that year-by-year breakout, which we'll do next. We'll do that next. And that will be done. Thusly. We're going to select the column R. And let's make that, I want to copy that skinny Home tab and paint brushy the y to make it skinny. Why? Because I want to save some room with column y by making skinny. And then I'm going to copy the headers on over and get rid of other months. We don't need the months. And then I'm just going to make the year-by-year breakout from year one to down to 30 years because it's at 3030 year loan, 3030 year. And then we're going to center that. And I'm gonna put some zeros here just because I'm going to use a pivot table later. And zeros, you shouldn't have like blank cells in there. That's just best practices. And I like to practice the best stuff. So then we're going to say, okay, so then I'm going to sum equals the sum. If brackets, the range I wanted to pick up is this is the kind of criteria range I would call it. They just call it the range right there. I'm going to say F4 on the keyboard because we're going to want to copy that to the right and then comma the criteria is that one. So I want you to say, Hey thing. If there's this thing which is a one in that range, then I want you to sum up the sum range related some range which is there gonna be this payment range right there. And I also want to be able to copy this to the right. And so this cell right there, that number one, I want it to move down but not to the right. So I need to put $1 sign before the z and not the three. It's gonna be a mixed reference is what that's called the EU. You, I do want to move over to the VV next time. So I'm gonna say, okay, and boom, Boom, bang, bang. Called Poland over, over. And there's the 12486 was checked that number because that should be the sum of these Bad doggies. Those bad dog is summed up 12486. There it is. Looks good. And then I'll do the Min equals the min brackets. And then we've got the men if bracket range, we want to take this one on the range. That's not where my whole myosin on that range, but it's another range. Home mom, the rain. This is gonna be the criteria range, which is this one. And then Is that the correct? Yeah. And then comma and then the criteria is the one. And we can copy that down, boom. And we can copy these down. Boom. We can total it up. Down here, copy it down, total it up. That's what we do in Excel. Most of the time. Copy it down, total it up, copy down total up. Here we go. We're going to sum it up with the total and copy that across not all the way to the end because this one is like a balance column. And then I'm going to make that blue and bordered blue and border border blue, border blue. There's the border, there's the blue. Let's now do the same thing with a pivot table as we have seen in the past. I could, so I can't select this header. That's what the downside is when you add a table, but don't need it. I don't need it. I'm just going to select all this stuff. Do the same thing and pivot table format just to show you different options on how you can do this stuff. Pivot table. They're super cool and impressive. If you can pivot a table, most people don't know how to pivot. We're going to insert and we're going to say Pivot Table. And I'm going to put it in the existing worksheet. I'm gonna put it right there, like a pivot table. Holy moly, I'm going to drop the price on the home. 30% right there because I'm impressed by the pivot away. You pivoted that table and then I'll add the payment, the interests to decrease the balance. And then we'll do the formatting on these as we've seen in the past. I know I'm doing this quickly, but this is kind of a recap just so we can put all this data together in one place here, we're gonna bracket. Get rid of the dollar sign, decimal down, down. Let's get into the rhythm on this one. This time we're going to hit this one value field settings. We're gonna go to the number group. We're going currency bracketed, dollar sign gone decimal down, down, okay? Okay. Next one, Value Field Settings, Number format, Currency, brackets, dollar sign gone decimal down, down, and okay. Okay, last one is a little bit tricky because it's not a sum one, but instead a min thing. Men, thanks. So then we're gonna go to the number format and this is the same currency, brackets, dollar sign gone decimal downtown. And okay. Okay. Okay. I said, Okay, how many times? Let's make this a little skinnier. I already said, Okay. Okay, so then there it is. So now we've got this set up. So we got all that kind of built up from that, from that first piece of information. And once you have something like this set up, you can change things. Of course I can say, well, what if I put down the 9,000 or something like that, then this is going to populate for us. And I believe it should it should all work through here. If we've got everything tight out, the whole schedule ties up the pivot table, will not might have to refresh the pivot table. Right-click and refresh the pivot table. That's kinda like the downside of the pivot table, but you can recreate it. If it gets, it gets finicky on the, on the pivot table as well. So that's the benefit of Excel. You can run different scenarios on that. You can also change the rate pretty easily, that the number of years is a little bit more difficult because we calculated it these years here. If you weren't dependent just on this as the calculation for the years, it would be easier to calculate and change the years as well. But just note how whatever your scenario is. If it's going to be complex, if you're talking about things that are going to happen multiple years into the future, like a home purchase that you're going to do in five years. And then the thing is, can you going to have a home for 30 years? Your calculations, your estimates can get quite complex quite quickly. And excel has the flexibility to look at it from different angles in a way that you can't really get so much if you're kinda piecing together other tools oftentimes. 8. Buy or Rent Home Decision Problem 1 Part 1: Personal finance practice problem using Excel, buy or rent a home. Decision problem. Number one, part number one, get ready to get financially fit by practice in personal finance. Here we are in our Excel worksheet. If you don't have access to the Excel worksheet, that's okay. We'll basically build this from a blank sheet. If you do have access, there's three tabs down below. There's an example tab of practice to happen. A blank tap. The 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. We're going to build our tables from it on the right-hand side, comparing the yearly costs if we have renting or if we were continued to be renting versus the yearly costs after we have the purchase of a home. You can also modify this calculation to be thinking about, say, you're investing in rental property to think about what the rental costs would be, which could be income e.g. versus the costs for the buying of the home. So you can alter your thought process in that way as well. So remember when you're thinking about the purchasing of the home, you've got the initial purchasing costs of the home, which is going to basically include the down payments and the costs to get the home purchase process done. And then you've got the year-by-year costs that will be continuing on once you purchased a home. That's gonna be things like the mortgage payment that you could be making, the property taxes and so on and so forth. And you can think about those year-by-year costs in comparison to the rental cost. So we'll calculate those items and then we'll also make our amortization tables on the right-hand side. So on the second tab over here we've got that practice tab. This has some preformatted cells, so you can work your problem here with a little less Formatting. And we're gonna be over here on the blank tab. And we'll basically build this from scratch. Although the scratch is a little bit larger than some of our other problems, that's gonna be this information on the left-hand side. If you don't have access to this worksheet, then you could start your blank sheet. You can select, I would start by selecting the whole sheet, right-clicking on it, formatting it, and then making it currency. I usually go with the brackets. Are you removed the dollar sign and I removed the decimals. I'm not gonna do it to this worksheet because I already have these formatted here, but that's usually where I would start and then just put your data, just mirroring your data over here on the left-hand side and adjust the formatting as needed. Meaning putting percentages here, e.g. formatting them as a percent. And then you basically, you are at the same point here and we can continue on going forward. So we have the information on the left-hand side. Let's just take a quick glance at it. We've got the rental costs, the annual rent. So this would be something that of course, we would know in practice, the insurance that we're gonna be paying, the security deposit we have of the $800. And then versus the buying costs. First, we're going to start off by just kinda giving us some of the information like the annual mortgage payments, if you were making a decision as to rent or to purchase, this might be something that you're considering on different depends on different purchase options. So we're gonna kinda give it right here and then we'll make an amortization table to get a little bit more detail on that. And then we've got the interest portion. So again, this one is another one that you got to think about how you would get that from the amortization table. And it's something that changes from a year-to-year basis because the amount that you're painting will vary from the principal to interests. The interests being important because you might have a tax benefit from it. So we're going to assume the number here and we'll dive into the amortization table to get into that in a little bit more detail once we touched on it, that's why it's yellow here. By the way, property taxes, we're going to say that the 2,100, if you hadn't actually made the purchase, then you can kind of estimate what the property taxes will be based on the homes in the area and the tax structure. And then you've got the down payment and closing costs are going to be the 307181, the growth in equity. So equity is the difference between the value of the home and the loan on the home. And there's two kinds of things that kind of increase the growth and equity. One is you're paying down the loan. But when you pay down the loan, It's not the whole amount, It's only the amount that's going to principle and to hopefully the home is going up in value. And that's just due to the market. No matter which of those who were talking about the difference between the loan amount and the value of the home is not something that we have easy access to you. However, at any given time, depending on the economic circumstances, unless we sold the home or we got a second like a refinance up on it, which sometimes it's easy to do and sometimes it's not. It kinda depends on what the economy is doing. So insurance and insurance and maintenance, and then we've got the estimated annual appreciation. So we're going to think this is gonna be the Equity going up because of the difference or the pain down to the loan. And we'll talk more about how we get to that number. When we get to the amortization table, we're gonna give it right here, and then the yellow by the way, and then the estimated annual appreciation. Again, that's us saying the value of the home is going up. Now, we can't really assume that the value of the home could go down. The assumption that the homes only go up is not correct. Right. Because we've seen the homes go down usually under normal times, they go up over the long run. But this assumption that Holmes never go down, you gotta, you gotta take on or consider the risks of home going down after-tax interest rates. So we'll take that into consideration and use that. And then the tax rate, we're going to assume it's our marginal rate, our highest tax bracket which will come into play. Okay? Knowing that, let's first think about our rental costs on a year-by-year basis. So rental cost and cost, and this is gonna be like yearly rental costs, we might want to call it. I'm gonna make this a header. I'm going to select three cells here, gonna make that up my standard header thing, which is Home tab font group. I changed my, my Excel to not being in dark mode for some reason. It was in dark mode before, which I kind of like because I think it's better on your eyes. But I changed it because some other thing was in dark mode and I didn't want that in dark mode for some reason. And now this so that's why in case you're wondering why it's, why it's not in dark mode, all of a sudden. So in any case, annual rent. The annual rent we said was simply this 9,600. That's gonna be an easy number to come by if you've got a rental agreement. Not too difficult. We'll put that in the outer column here. We've got the insurance. Once again, pretty straightforward most of the time for us to pick that up. If we have any renter's insurance with that, we have to pick up and then we've got the sick. He lets say this is the security deposit. So on this one I'm going to call it the security deposit. And, and tourists lost. And what I'm gonna be considering here are the, are the cash flows, meaning what is, what am I losing from basically that security deposit on an annual basis, I'm losing the interests that I could have earned on it because I'm looking at basically the yearly cost in theory, I would get the security deposits back at the end of my rental term. But obviously, one, they probably going to take it anyways, but two, I can't earn interest on it as long as it's being held by the rental company. And that's what I'm kinda losing. So that's the opportunity costs. So we're gonna say, okay, security deposit 800, what are we losing? We're losing the after-tax interest rate. Meaning when you're talking after-tax interest rate, you're looking at the tax rate or the interest that you think you can earn on it, what you can earn on the 800 if you put it into a bank account or possibly investment. And then we're trying to account for the fact that we're going to have to pay part of that earnings to the government or after-tax earnings, we're going to say are the 6%. That's where we're coming up with the idea of the after-tax earnings. Okay. Let's make that a percent Home tab number group percent, define it, underlining it, the font group underline it. And then that's gonna be our security deposit interests lost calculation. So we lost that interests because it's in the security deposit and I can't earn any money when it's in there. I'm going to indent here, Home tab Alignment indent, and then indent again, indent. And so I said it's a colon. We indented into sub category. We brought it into the inner category to show that it's a sub calculation that we double indented. And I'm going to pull that into the outer calculation to say that's the end of the subcategory, 800 times the 6%. So $48 there. Let's put an underline under that Home tab font group underlying line underneath, known as the underline. Underline total rental costs. Okay, so there we have it. So now we're going to say sum in this outer column up, I'm just going to sum up the outer column equals the SUM of the rental, insurance and the security deposit interests lost. So we got a cost of on a yearly basis, we're gonna say of the 9,009 48. So that's our yearly costs there. Let's compare that to our yearly costs on the homestead side of things by homestead. Know what it is, but it's not just a home, it's a homestead which I assume is like the property around it and stuff. I'm gonna say, we're gonna say C. Let's put a paint brushy here and make this a skinny. Okay, So now we're gonna go to the homestead calculation. And so this is gonna be the cost to buy. So cost to buy or not to buy or I should say yearly. Yearly home cost. Let's call it. I'm gonna make this one a little bit wider. I don't have to go as wide as my header here because I'm going to have some other stuff I probably will later. But just to note that and maybe I want to hide these cells because I don't need to see this in-between my data. So let's go from column C and go to Column F. And hide that stuff so I can see my data on the left-hand side. Hide that stuff, poor file for. And then we're going to say this is going to be the annual annual mortgage payments. Actually, let's do that with an equal sign. And let's do some headers for you. Getting ahead of yourself. I'm going to select this 1.2 other cells. Let's make that a header, Home tab, font group, and then bucket make that black and white my header kinda style the head style. Then this is going to be the costs. The costs were going to say will be, Let's put a colon there, colon. And we'll say this is gonna be the annual payments. Annual payments. I'm going to put that here in column J, and that's gonna be the 10,700. Now, we're going to say is our mortgage payments, which again, we'll do the amortization table so we can kinda see where that is and you might be pulling that from an amortization table. So we could, we could set up an estimate for what we think the amortization table would be, which we would then draw this 10,700 a yearly payments from. So that we could make it a little bit more comprehensive in that way. We'll take a look at that in a second. Okay. So we've got that. We've got the property taxes. So we've got the property taxes that we're going to have to pay. So you gotta remember you got to pay the property taxes. That can be quite significant depending, especially if you're in a high property tax area, we've got the insurance and maintenance, so we've got to take that into consideration when we make the purchase. It's not just the mortgage costs. You got the insurance and maintenance stuff that's going to be in play here. So we're going to make sure we've picked that up. So we have that. And obviously, again, maintenance can be quite significant when you're making a purchase versus renting. Because if you're renting, then you don't have to deal with the maintenance, right. Purchased for the most part, at least when you're when you're home purchased and of course, things get old and you've got that could be significant. So what you can get into more depth in terms of how to, how to estimate that amount. Then we're going to say this is gonna be the after-tax interests loss. Let's call this after tax and tourist lost on down payment. Hey, meant now notice, I'm not seeing the down payment as basically a yearly cost here because I'm trying to think about what the costs are on a year-by-year basis, basically after the purchase was made. But we do want to do on a year-by-year basis on this year-by-year kind of comparison is say, well, whatever I had to put down, if I had to put this money down, then I'm not earning on it right ahead and take my money and put it on the down payment. So what I'm losing on a year-by-year basis is the interests that I could earn on that down-payment. So I'm gonna, I'm gonna save that. Let's do that calculation. I'm going to say that we have this if we put this money down, the 307181, I'm losing whatever I can earn on it, which we said was our after-tax interest rate or our earnings rate, whatever we want to be dividends, it could be other types of earnings. That's gonna be the 6%. Let's make that a percent. Go into the number of group and percent define it, which isn't a word, but we're gonna make it one. We're gonna make it a word. We identified that. So then we got the after-tax interests lost on the down payment. So we've got then after, Let's just copy this one and then get rid of the colon at the end. That'll be pulling out to the outer column. So now we should make this, I'm gonna make this column a little bit wider so I could see it over here. So then I can pull this over. So we're going to say this equals then this times that. And so that's the interests were losing because we had to tie up our money in that downpayment on the purchase. So I'm going to select these three. We're gonna go to the Home tab. We're going to go to the alignment and indent. And then I'm gonna go to Alignment and indent again, let's make the cell a little bit wider again so that we see the full payment there. Okay? And so then that's gonna be our total costs and I don't really need cost to buy. Let's get rid of this column up top. That's not necessary. That's not necessary. And so I'm going to I'm going to shift the cells up and I'm going to call this, it cost to buy, yearly, home cost. Maybe I should cause to buy or otherwise known as the sheerly home costs, cost to buy, yearly home cost. That's gonna be the bottom line. So I'm going to just sum it up on the outside, summit, up on the outside, 16681. So let's put some blue and borders around that. If we could make it look nice. Font group, border blue, border blue. And then I'll put an underline here. Font group underlying might even double underline that one, even though we're not stopping right there. So these are the actual Kinda costs that these are the key cash flow type of costs that we would be thinking of. And this is kind of lost. Lost. What we couldn't, the opportunity cost of putting the down payment in place. Then we also have the benefits with regards to the benefits of owning a home. So if e.g. we've got the equity that could be involved in the home and we could have tax benefits involved, but the home as well. So I'm going to put those into a separate calculation. So I'm going to highlight this. I'm going to go to they go to the paintbrush, make another skinny, skinny over here. So this is gonna be the benefits of home purchase. Benefits of the home purchase. Let's get into that. Let's hide some cells. I don't need this stuff. Let's hide from G to G, G to J, GJ. Right-click and hide it. And then I'm going to make this a little bit longer or wider, wider. And then let's make this our header format t-sne, which is the font group, black and white. Boom, boom. Okay, So now there's gonna be a growth in equity. So we're going to say grow in equity. The growth and equity we said was this amount. Now I'm gonna, I'm gonna try to think about the growth and equity in two different ways to equity, you'll recall, is gonna be the difference between the value of the home and the loan cost, the amount we owe to the bank, which means our assets are going up when we have that, we have more assets as that goes up, but we have to be careful with that one because it's not a current asset or liquid asset. I can't pay off my debts so easily with the equity on the home. So I want to be mindful of that because there's two things I want to think about my overall standpoint, my asset standpoint, and how much I could sell the home for. It's great that it might equity goes up, but I also need to think about my cash flow just to make sure I got the cash flow to pay off the bills. And this doesn't really contribute to cashflow unless we were to refinance the home, which might be doable under most normal conditions pretty easily, but not really easily. And then under some conditions that won't be that easy to do it. All right? It just depends on the economic conditions where there's two components to that difference. In one, you can think about the loan. So we can assume we can kinda hope that the home doesn't go down, at least stays where it's at, and hopefully it goes up in value. So as we pay off the loan, then the amount that's going to the principal is going to decrease the liability, that'll increase the equity. And then the other side, hopefully the value of the home goes up based on the market value. That's a speculation though, because we don't really know how much the home is worth until we sell it. And given depends on the conditions that we sell under. If we're under stress conditions, we're not going to get the market price of the home because we need the money at that point in time, we're gonna be read. So you gotta be a little careful on the non-liquid stuff. I'm going to put this in the outer column here. So we'll talk more about where to get that money or that number from. It's a little bit tricky to from year-to-year if you're talking about the decrease in equity from the loan. So we'll talk more about that shortly. Then we've got the estimated annual appreciation. So this is the other kind of difference in the equity that we're going to talk about from just the value of the home going up. And it's just an estimate we can we cannot guarantee that the home value is going to go up. We're hoping it goes up. But there are times that it goes down and the fact that it goes up, does it mean that it couldn't just drop? All of a sudden? Alright? And so, because we haven't realized the game, and you realize the gain by actually selling the home at that point in time. So it indicates we've got the tax savings for interests. So we've got tax savings for interest. So brackets. This is gonna be because we get to deduct possibly the mortgage interests portion of the mortgage payments. Now, this is another one that's, you want to be very careful about because there's more complex than just saying, I'm going to multiply the interest times your tax rate. Because if you were not itemizing before, maybe you were taking the standard deduction, then the the this loan is usually what kicks people over from going from standard to itemized deductions. And that difference between the standard and itemized deductions isn't really giving you a tax benefit. It's only given you a tax benefit over and above what the standard deduction was. You really want to do an actual tax plan on this, talk to your tax preparer or get the software and look at what the actual dollar difference is on your tax savings. And you want to also think about it from year to year because the interests will go down as the loan goes down. So it's not like you're gonna get the same savings for the whole 30 years. Plus the law could change as it did fairly significantly a few years ago. And so you can't really depend on it completely as well to be a deductible thing. So calculate it, figure it in there, but you gotta be careful about your savings. Don't just Well, I have to buy a home because the government wants me to you because they give me a tax benefit for it. That's not the government is not your friend. There. Don't, don't don't, don't think that they're trying to trying to, you know, you want to buy a home just because of that. We're going to say Home tab. We're gonna go number and put the brackets. So there it is. Let's put an underline here, fought group and then underline that. And then this is gonna be the tax savings for tourists, interest, interest. Okay, let's put this in the, in the, in the outer column. Now this is gonna be equal to 8,000 times 28%. Also note, there's 28% is usually the marginal rate, meaning we have a progressive tax system, so we have multiple rates that you're taxed at. And usually the next dollar would be the thing you're gonna be taxed at meeting your highest tax bracket. That's what, that's what the change is going to be added that highest tax bracket. But again, do get the software, talk to the tax professional, get the actual dollar benefit with a projection. And then you want to think about how that's going to change over year-to-year. Okay. Stop preaching on stop, just do the problem. So we're going to say alignment in dent, okay. And this is gonna be then tax savings on property taxes. So we've got a similar kind of thing with the property taxes because they're often deductible as a state tax on the federal tax return. So we're going to stay property taxes are then that 2,100. This is the other thing that often pushes people over to itemizing. That's why people that own a home are typically more likely to be able to itemize. But again, there's severe limitations on the state taxes, including it'd been capped at 10,000. So it includes property taxes and your state income taxes. So you might run into that cap as well. And again, you've got that same kind of thing from the standard or itemized deductions. So run the projection, run the software. Run the software. Don't just listen to someone saying, Hey, you got to buy a home because you get a tax benefit. It's you know, it depends. It depends. So we're gonna go to the Home tab number group per cent, font group underline. And this is going to be tax savings. And we'll double-click on this and get rid of the colon. Let's indent and indent alignment indent and then alignment indent and do our multiple multiplication 2,100 times the tax savings of 28 a percent. So then we've got the tax savings of the 588. Okay. And then we're going to calculate the benefits. Benefits of home perch her chest. Let's put that right here. And then we'll sum that up equals the SUM, summing it up. And so we've got this 6528. And let's put an underline here. We're gonna go font group underline. And then I'm gonna put some brackets around this brackets. And then we're going to do that, making it blue. So of these, notice that these two you can't really rely on. There's, this isn't really cashflow, even though you have a tax benefit in terms of your overall asset standpoint, these two, if you got them correctly calculated, are kinda like cashflow savings that you would have on it. So just be careful on that too. You might want to kind of distinguish the items that are gonna be cash-flow benefits versus other assets like equity in the home benefits. Let's unhide the cells between b and k. So B, k, right-click unhide. And so then this is going to be this is gonna be the cost less benefits of home her chest. So let, let's subtract these two out. We're going to say this was the cost minus the benefits. So there's the 10,001 53 net. And then if you take into consideration the equity the equity items, I'll pull those out. Those are equals the sum of these two. These two, which aren't really cashflow items, then you could say, you know, like more of the cash flow items are going to be equal to the 10,001, 53 minus 37. So actually I were to say 10,000, I'm going to add those back in this plus this, right? So then we've got the 13853. And then again, these two were hoping the equity is going up, but I like this one in particular, we can't depend on even so it's not like it's going to, it's not gonna be a cashflow items, right? It's going to be another asset kind of item, which is why you might want to break it out. Thusly, just to indicate that Let's make that like that. And let's put an underline here, font group and underline it. Okay, let's do a spell check as we're going here, review check the spelling. Equity. Equity. That wasn't bad. That's all I misspelled. I'm doing better. Okay, so next time what we'll do is we'll create our amortization table. And that'll help us to think a little bit deeper on these items. And it'll also give you some ideas that you might be thinking of building your amortization table, which from which you will pull these items in your dataset and then go forward with these types of calculations on the year-by-year items. So we'll talk more about that next time. 9. Buy or Rent Home Decision Problem 1 Part 2: Personal finance practice problem using Excel, buy or rent home decision problem one, part number two, get ready to get financially fit by practicing and personal finance. Here we are in our Excel worksheet. If you don't have access to the Excel worksheet, that's okay because in prior presentations we basically put this together from scratch. You can take a look at that presentation and start from scratch there if you so choose if you do have access to this worksheet. There's three tabs down below, an example Tampa practice tapping a blank tab. The example tab in essence being an answer key. Well, let's take a look at it now. We have the information on the left-hand side and prior presentations we started the practice problem doing a comparison for the yearly costs if we were to have the rent versus the yearly cost if we have the purchase. And then as we did that, we pointed out a couple of items including the annual mortgage payments, the interest portion, and the growth and the equity in particular that we included in our source data here. And we might get this information, e.g. from amortization tables as we think about the purchasing process. So that's what we will continue on with here. We'll think about the loan amount and then the home costs. And we'll build our amortization table and get our table on a year-by-year basis. So we can look at a little bit more in-depth. The calculation of safety interests, which helped us to calculate the savings on the tax returns, which relates relation to possibly the deductibility of the interests, e.g. and the calculations for the growth in the equity. So that's what we'll do now in the second tab, we've got the practice tab, which will have the blink blue areas that will be preformatted. So you can work through the practice problem with doing a little bit less formatting, if you would like. We're gonna be over here and the blink tag, which we started last time constructing this information. So we built these from scratch and now we're going to continue on with the loan calculation numbers and help us to kind of expand, as we said, on some of the items that we put into these calculations in the prior presentation. Okay, so let's think about actually put together our loans schedule. And this might be one of the first things you do. In order to base this data on your Excel worksheet, you might first think, well, what if I had varying loans, e.g. and then base your loan payments on that and your and your interests portion on that and so on. So let's do that. We're gonna go over here and say, okay, let's, I'm gonna pick up this column K. Make a skinny column by going to the Home tab Format Painter and make a skinny row. And then this is going to be the loan amount. The loan amount is going to be the amount that we're going to assume or the amount that I'm gonna kinda back into. And so we had the mortgage payments because we have this in our dataset, the mortgage payments. So I'm going to make this a little bit larger and I'm going to look at the monthly payments here. Now notice I gave this in our problem. So we set the annual payments were the 10,700. So let's say that. Well, if that's the case, then if I say this equals to 10700/12, I would have monthly payments of about 92. We can add the pennies, so it's really 89167. I'm going to remove the pennies just so it's easier to see here. So again, in practice, you might first come up to the loan amount, e.g. and then figure the payment. But lets back into the loan amount given this information because we could just practice working with are formulas to do so. Then we've got the periods. So I'm going to say the periods, I'm just going to assume a 30-year loan. And then the rate, the rate is going to be, I'm going to use that same 6%, which might not always be at the same rate, of course, that we had for the after-tax interest rate, but I'm going to use that same rate here. I'm going to use that as the rate. And we're gonna say that that's gonna be number percent. Obviously the rate will be dependent on what we're doing. You might not do a fixed 30-year, although that's probably the most standardized structure to first think about, and so on. Now normally we've got the loan up top and we might calculate the payment here. We might first think about the loan, how much money we need to purchase the home and then calculate the payment. But right now I've got the payment and I want to back into the loan amount and how much loan could we get if my payment was here? So there's a couple of ways we could do this. One way you could do it. You could say, well, if I take my standard payment calculation and I look about the missing data, I have the rate, I got, the number of periods, I've got the payment. What I don't have is this PV present value. The present value is the loan amount. So if I just look for a function that's the present value function, hopefully that will give me the loan amount and that's, that's the idea. So I'm going to say, let's just do that. I'm going to say, let's say this is going to be equal to negative PV, the present value to get to the loan amount, because I already have the payment. That's gonna be the rate down here, which is the 6%. That's a yearly rate. We need a monthly rate because we're talking months now, so I'm going to divide it by 12 comma. We've got the number of periods, so the number of periods is gonna be 30, but that is endears, we need it in months, so we multiply that times 12 and then comma, we've got the payment, which is gonna be that 892 that we have here that we calculated. And that's rounded. Note and enter. So that means that we can get the loan of the 1408723 about that's rounded again, you can kinda double-check that if you wanted to buy by then doing a payment calculation, the negative PMT, and just read what you might be more familiar with and take that 6% divided by 12 comma, number of periods is a 30 times 12 comma. And then the present value, the thing that we solved for is the loan, which gets us back to that 892. We can also double-check it when we build the amortization table, which we're going to actually physically do because it's good practice to do and it gives us more information for us to kind of build off of them simply this data. So then we can say, okay, well, if that's the amount we're going to finance, how much is the home costs? So this is the loan stuff. How much home can I purchase them if I know the amount of my loan here? So I'm gonna go font group borders blue. Let's make this the home cost for the good old homestead. My homestead cost. I'm gonna make that blue or black and white for my header. And then this is gonna be the loan that we calculated up top. I'm going to say the loan is equal to the 1408723. If I got to put 20% down, I'm assuming 20% down. So that's the percent financed. So that means 80%, which would be equal to one -0.2 or 20% would be 80% if I make that into a percent number group and percent, uh, find it. That's the amount that we would have to finance if I was to put the 20% down, right? So then I can say, Okay, well that means that the home cost, home cost would be equal to the 1408723 divided by the 80%. That means I purchased a home for 1805903, I put 20% down, which could change. I mean, 20% is kinda standard, but depends on the circumstances and the economy and whatnot. But I'm gonna assume 20% down to get the loan or financed amount of that. That's kind of a funny format. Let's take a look at the way you normally see it. This is me, my check figure. My check figure. Let's make this black and white. And let's put this the normal way we see it. Well, if the home was to cost the 1805903 and then I'm gonna put a down payment. Down payment. This payment is down. This is a down payment of 20%, 0.2, 20% number per cent to find it. And font group and underlining it, that would mean that we'd have the down payment is going to be equal to 1805903 times the 20%, 307101. That by the way, is where we got this, this downpayment 37 one-on-one in our problem that we used. So that's that. And then that is that. And then that means that the, that the loan, the amount that we're going to finance is gonna be equal to the home cost minus the down payment. Down payment. That payment is down. We're gonna go home tab font, group and border blue it, Let's do some border blue border, blue, border blue. Do some border blue here. Okay. Is that okay? What did I do? I underlined the whole thing. What you're trying to do, focus, focus like a beam of laser, font group underlying. Let's put an underline here. Okay? Now let's build our amortization table from this, because that'll help us to think about these interests in this equity number, some of these numbers that we were saying, What's that funny? Where did you come up with that? You just kinda magically put those numbers there. Let's go with the, let's make a skinny. Oh, copy the format in here, format and put that on and make a skinny our R. And I'm going to put my headers up top for our table year, month. Now notice this is something you can do with online tools, but I think it's better to do in Excel. But just so you can see it, Here's our online tool or one of them, I'm not promoting this particular tool, whatever tool you want to use it as a bunch of them, 1408723, and you can say, well, this is a 30 years, six per cent. Let's calculate it. Boom. And let's see the amortization table. So there's the 891, and so that does it for us here, but I can't really tie it into my whole worksheet. So I'm not satisfied with that personally. I just check it. So then this is gonna be the payment. And teres, this is gonna be, I'm doing two cells here, loan. Decrease. So I'm not wrapping the text because that makes, makes a wide one, a wide one column. And I don't want that because that messes everything else. So why should that column mess everyone else up just because it needs to be wider. That's how I feel about it. I don't use the wrap text because it's not fair to the rest of the cells. So I'm gonna go to the Home tab and we're going to say, let's go to the font group and make this black and white and center it will make these two are a little bit skinnier, skin horizon. It skin arised like that. Okay. And then this is going to be down from 012. And then you got to select those three and buckle your shoe because we're driving down to 360 degrees. We're doing a 360 degree kick flip here and you can see it with a number, the number that's populated in right there, we're going down 360. Long way down. Make sure you're grabbing that fill handle steadily. You've got a good grip on it. There's 36360. The rotation has been complete on the three-sixteenths kick flip. And so this is gonna be alignment and center it. Let's center it. I'll do the year staying here. I'm just going to say equals to one. This equals this one. I'm going to say, well, they're gonna do the roundup. I'm gonna do a roundup formula. Round up, round up the little doggies. And then I'm going to take that one, divide it by 12. And then I want to say comma and rounded up to the nearest whole number, which is given by the 0.1 in our formula. And this will give us the years which will help us with a pivot table or to do our formatting on a year-by-year basis. And then I'll just copy that down. Double-click on the Fill button handle, the Fill button. And then it fills it everything up. See how it filled it all up. That's why they call it the fill handle button. Alignment, center it. And then I'll center this one too. And then we'll do our payments. And we've seen this in the past. I'm doing this fairly quickly. I'm just going to put some zeros there because that's good practice for our tables. When we put the tables in place, the balance on the loan balance is gonna be the 1408723 and the payment is going to be equal to, this is a rounded number, the 89 D2, and that's going to be copied down. So I want to make it absolute because it's outside of our table here, it's in our dataset. So I'm going to say F4 dollar sign before the queue. And two, we only need a mixed reference, but an absolute one works. Let's calculate the interest, which is gonna be the 1408723, times the rate, which we said was 6%. That rate is outside of our table here, it's in our dataset. Therefore, it needs to be absolute or mix absolutes. The easiest thing to do because we could just hit F4 on the keyboard dollar sign before the queue and four. And then we're gonna divide it by 12 because that would be the interest for a year and we want the interests for a month tab subtracting the payment 189 minus the 744. And that gives us the 148 decrease in the loan balance. So of the 92 that we're paying, we're only paying down the actual loan of the 148 for the first payments for the loan balance prior was the 1408723 minus 148 means the new loan balances, 1408574. I'm going to copy these four are not copied. Them, select them, and then double-click the fill handle button, copying that down. Because we made the proper amortize or absolute reference I. Then if I scroll down, then it should get down to zero. That's a good double-check that we've done things at least properly, hopefully. Properly hopefully. And let's go back up top. I do a lot of hoping that stuff has done properly, but I feel like it has. So I'm going to then say fought group. Let's say this is gonna be blue and bordered. Let's put some blue borders around that one. There we have it. So now you can see of course, that the interests and the decrease in the loan balance are changing over time. And so that those numbers are kind of what we're basing these calculations on when we took when we talked about the tax benefit of the interests, we picked up this number and put 8,000. Well, it's not always 1,000 every year because it changes every year. So that number is once you've gotta be careful of same with the equity if we're talking about the equity with regards to the loan, the loan balance or the gap due to us paying down the loan. Well, that changes each year too. So we can kinda estimated how much our equity is going up due to that. And then think about the other side of things, which is also an estimate for the equity, meaning the value of the home goes up, but it's not exact. So what we would like to do is see this on a year-by-year basis, I could say, okay, well, the interests in the first year, if I select everything from year one. The year is around 8,008, 74, but in year two, if I select it, then it's 8,007, 61, it goes down. Each year it goes down. So if you've got a more complex projections, you gotta think your whatever your tax benefit in year one is going to be less as, as time passes. And so you can't, you got to take that. You've got to just realize that how can we think about that? We can make a table that breaks this out on a year-by-year basis, either using pivot tables or we can use formulas. And this is a step beyond what this online table can do for us because I want to break it out on a year-by-year basis now, I'd like to tie everything out on my Excel worksheet. So I'm going to say I verified my numbers to some degree or I would with this. And now I'm going to I'm going to take a step beyond I'm going past what I can do. Home tab, Let's make a skinny arm or take that skinny or to make a skinny. Why? Why? Because we need a skinny column right there. So I'm going to say Format, Painter, skinny. Why? Skinny? Why? Why is the why skinny? Because it has to be it's going to be skewed. So we're going to copy the headers. I'm going to put that on z. I'm going to remove the AAA column because I don't need the months. And we'll do this a couple of different ways. So let's do it. Let's do this on a year-by-year breakout. I'm just going to say 12. I'm going to take that two. And we're going to buckle. We're going to tie my shoe lace one to tie my shoe because I don't have buckles. Want to tie my shoe. And then we're going to copy that down home tab Alignment and center it. And then I'm just going to do some IF formulas, so equals the sum if brackets. And I want to take this range. So I'm going to say if you find a one in that range, then I want you to some the related payment column. So then I'm going to say, I want that, well, let's do this comma. And then the criteria range. The criteria is that if you find that one in that range, then I want to sum up the related range that has a one in it, which in this case is gonna be the payment items. Boom, and I'll put absolute reference in its shortly. But let's just practice that a couple of times again, there's the 107. That makes sense because if I sum this up, we get to the ten, we get to the 107. Let's do it here for the interests, which is more interesting because the interests changes from year to year. So let's do the interests which is more interesting. So this is going to be then the sum. If we're going to say the range is going to be this one. I want to get to that range because I want my home on the range. And then the criteria is going to be this one. No, no, no, no, no, no, no. The criteria is gonna be this. And then comma, the sum range is now the interest one. There we go. So there's that and then the loan decrease. Let's do it one more time. This equals the sum if some, but only under these conditions, this range right there. We're going to want a comma that. And then the criteria lookup, the number one in that range and then comma, and then sum up the related range, which is that range, boom, bam, there it is, man. The 18, two-six. Now we could copy this one across. So let's do that so we can make it even faster. I'm going to delete these two. So we can do it fast as ****, like Flash does flashes fast. We'll do it flashed fast. So we're going to say this one is the range. I want to make that an absolute reference so I can move it to the right. And this one doesn't move absolute ties in it, dollar signs. And then this one, I need a mixed reference. A mixed reference because I want this one to move down, but not to the right. So I need $1 sign before the z, but not the three. And then this one, I want it to move, I want the payment thing to move to the right. So I'm going to say leave it, leave it. Just leave it man. Leave him. Leave it alone. And then copy that to the right. And so then if I copy that, and so that looks like it's doing what it should and we could just copy that down. Double-click on the Fill button, copies it down just amazingly. And then we can get a loan balance which we want the end number on each time right there, that number right there. So I'm gonna say this equals the min if knew that apps on that one, multiple ifs, men. And then we've got the mid range, which is gonna be this range x comma, the main criteria range, which is gonna be the years again, comma, then the main criteria, which is that one again, I don't need to do any absolutes are mixed because I'm just going to copy it down and not across. And I chose the whole column down to, down to like, I chose the whole column. So it goes right down to the center of the Earth, right down to Dante's Inferno, down there. So I'm going to double-click down there. The Excel sheet goes down forever until it hits right in the middle of the planet. So I'm going to then go here and let's make this, make this blue and bordered. So now if I look at my interest on a year-by-year basis, I said around 8,000, which is kind of like somewhere reasonable because because really Your one, it's the 8,008. And then you can see it goes down. The amount of interests we're going to pay is gonna go down. It's under 8,000 by the time I get to eight. And then it's gonna go it's gonna go down to very far down until you're 30. I'm only paying for 140 interests, although I'm still paying 10,700 for the home. So if you were to actually estimate this number for your calculations for your tax benefits, then you have to think it's going to change from year to year. You might choose a number somewhere in the middle. You might say, okay, well maybe I'll be fair. Maybe I'll choose the number on your 15 or something like that for the interests. Or maybe that would be a more conservative number, at least for the top few years. You may try to average it in some way or you might say, hey, I'm going to sell the home after six years or something like that. I think so maybe I'll look at my benefit over an average maybe of these, of these six years or something like that. Or maybe you're just trying to calculate the benefit and the one particular year or you might calculate the benefit on a year-by-year basis and do multiple calculations. So just note that when you're looking at the benefit of the interests calculation, the interest changes from year to year and it's gonna go down. So your tax benefit is gonna go down from year-to-year, might not have a big effect from year one to year two. But when you go from year one to year 30, there is a big effect that will have an impact on the taxes. The loan decreases the same thing because of this loan decrease is the decrease in the loan balance. So if I if I assume that my balance in the home stays at the value that we said was 185. Then, then that's the home price. We're hoping the home price goes up. But even if it just stays the same, which we're hoping it could go down. But if it stays the same, then you'd have the difference between these two. That's going to be an essence. Our home, our home. The increase in the equity in the home, that means our assets are going up over basically our liabilities, which is good here. And we, we calculated this benefit. So this decrease in the loan is basically our increase in equity from a year-by-year basis or assets over our liabilities are looking better if we assume the home price basically stays the same as the purchase price. And then of course, we assumed that the home goes up in value, which is another assumption that's based on market assumptions. But notice that both of those assumptions are based on market assumption. This assumption that, that when you pay down the loan, your equity is going up, more conservative or less risky because it's less likely that your home actually goes down. Then the assumption that you're going to assume that your home goes up by some rate, that it goes up because again, it's possible the home goes down and it's quite possible that it doesn't go up at the rate that you assume that it's gonna go up. That means that this number over here that we thought about, we could think about the equity or the increase in equity. And to kinda components, we can think about the difference in the payments, which we said was 2,700. And then we can think about the growth and the value of the equity in the stock. Now, this number here though, again, it's going to change. It's actually going to go up or equity portion is gonna go up, meaning the interest portion is gonna go down from year-to-year, enter the amount that's gonna be applied to the equity goes up. So our equity isn't going up as much in year one. And then the same payments that we're making, 10,700 means it's going to be a more substantial amount applied to the equity going up in the later years. But also just remember that the fact that your equity goes up doesn't mean your cashflows going up. The only way to get that equity would be would be to sell the home or to refinance the home. Which under certain, you don't want to completely depend on that because if the economy goes bad, you're not gonna be able to sell. It just depends on the economic conditions. So you want to think about the cashflow as well as your overall asset situation. Now I can also make this table using, using a pivot table. And just note that you might do like this whole process first, think about the loan first, and then use that in some way to construct your data, meaning take that and then construct your annual mortgage payments and then your interests portion using some kind of estimate from your table, possibly taken the middle number or something like that that we've talked about. And then you could possibly take the middle number or somewhat on the growth to help to get to this number. And set your, set your, set your table up in that way. Okay, so let's just make this one more time. I'm gonna do the same thing with an amortization table. I can't use the two rows up top because I got these two rows as a header. So I'm just going to use this row and I'm just going to make a pivot table, which is might be an easier way to do this. It has some pros and cons, but just to check it out, We'll do that. So I'm just going to insert, just going to select that, insert a pivot table. And I want to put it in the existing worksheet. I'm just gonna put it right down below here. I'm actually going to make a total column. Let's put it right there and then insert pivot table, boom. And so now I'm just going to say years. And I want to pull that into the rows. That's the most confusing component on this particular pivot. I don't want months, I just want payments, interests decrease and balance. And it just builds that table just beautifully, except that it's got ugly numbers. So now let's format the numbers. I'm going to hit this drop-down just to format the numbers. And notice there's some balance isn't correct over here, so we'll fix that too. So I'm going to say number formatting, currency, brackets, dollar sign gone decimal down, down. Okay? Okay. You did that way too fast. That's okay, I'll do it again. Second one, some of interests, same thing, Value Field Settings. We're going to say number formatting. And we want to be currency brackets, dollar sign gone decimal down, down. Okay? Okay, that was still too fast, that was still too fat. Okay, we'll do it again. Third one here. We're gonna do the same thing. Value Field Settings. We're going to say number formatting, currency, brackets, dollar sign, gone decimal down, down, okay? Okay. Okay, I kinda got that one. But lastly, we're gonna do something a little bit differently, little bit differently. Now that you've got that, we're going to switch it up a bit. Value Field Settings. This one, we don't want the sum, we want the minimum, the minimum, the last balance, the ending balance, and then the number four. And this is the same currency, brackets, dollar sign gone decimal down, down. And, okay, and, okay, okay, okay, so there it is. We get another table. Pretty easy to set up. Doesn't change quite as automatically with changes in the data. I'm going to select these and Skinner eyes them, making them skinnier is the technical term for that, which I just made up. But I'm totally going to increase the size of the dictionary with these useful terms. The dictionary is not keeping up to date with the useful terms and I'm going to put a total column down here. This is gonna be the sum of these sum in this, sum it up. And then we're going to copy it across, but not to the end because that last one, sum the balances because that don't make no sense. That's why. Home tab, font, group, border, and blue. So there we have it. Let's put some underlines here too. Let's put some underlines there. We have it there and there we have it. So you can use this data in practice, you might do this, again, this alone stuff first. And then build your, build your DataTable on that, including this number and then estimating this number and this number and then go into your yearly kind of calculation. Stuff that we looked at as a component or a process with your decision-making. 10. Buy or Rent Home Decision Problem 2 Part 1: Personal finance practice problem using Excel, buy or rent home decision problem to part number one, 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 because we basically build this from scratch from a blank sheet. If you do have access, there's three tabs on down below. There's an example tab of practice tab and a blank tab. The 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. We'll use that to build our tables. On the right-hand side, we're gonna be comparing and contrasting yearly costs for renting versus purchasing. Some of the assumptions that we make overhear will be based on in part the information that we might construct from an amortization table. So then we will dive into the construction of the amortization table and the creation of a table breaking out on a year-by-year basis to look at this in a bit more depth as well. The second tab is going to be the practice tab, which will have some pre format of sales if you would like to construct or work this problem with less formatting involved. The third tab, that blank tab is where we will be working here, where we will be adding the formatting as we go. You just have a blank tab and you don't have this stuff over here because we're going to build it from scratch. And this is the scratch on the left-hand side that we're starting with. That's the one scratch. So if you don't have that, then you could just type this in there. If you're working on a blank cell, I would suggest doing this. You select a whole sheet and I'm going to right-click on the sheet. I would format it first. I'm going to format the cells to something like currency, no brackets. I get rid of the dollar signs and then remove the decimals. That's my starting point. And then you might add this detail which I know it's a little bit tedious, but you can add this information on the left-hand side so that you could be mirroring what we're doing, adjusting the formatting as needed, such as percents here and possibly decimals there, e.g. then will be at the same point. And we can move forward from here to the next step of populating our information. This is also, of course, the kind of step that you would want to do if you were building your own worksheet to make these kind of decisions, you want your data on the left-hand side or in some other area that you can then pull from so that you can make adjustments to your source data and then run different projections with it. Let's see what we have here. We got the home cost. So we're going to assume the home cost of the 300,000. We've got it's gonna be a homeowners association, so we've got the homeowners association fees that will have to deal with at this point for that type of property that would be purchased. We've got the marginal tax rate is going to be the 25%, that's gonna be our highest rate. We'll talk more about that when it is applicable. We've got the rent payment for a house. So if we were to rent as opposed to purchasing, we're going to say the payments are gonna be the 1500s. We've got the renters security deposit, deposit of 125 seems fairly low. But in any case, we're going to say the renter's insurance is going to be $80. We've got the savings account, interest, we're going to say is two per cent. The mortgage payment. Then if we were to purchase, we're going to say is do 1458, 44. Now, notice that we just assumed that at this point in time, we could get into more detail on calculating that you might take this home value, e.g. think about the down payment that she would put on it and then calculate basically the mortgage payment, e.g. we'll talk more about doing that after we after we get through this first part, but that's not where our focus is now. And then we've got the national average property tax rate. So we're going to use that to figure or think about what our property tax would be homeowner's insurance payment. We've got the maintenance and repairs that's on the home purchase. We're going to try to take a percentage of the value of the home to try to calculate that. And then which is what a common way that you might try to estimate those because they can be significant. Of course, answers lost on downpayment 950. Then we've got the mortgage interest paid in the first year. And we're given this number again. But this is also something that we would want to estimate possibly from the amortization table. So we'll talk more about that when we build the amortization table, estimated annual appreciation and the equity growth from paying down the loan. Meaning when we get into the equity calculations, which is the difference between the value of the home and the loan on the home. Those are gonna be some estimates. And we can also think about the decrease in the loan itself, which is going to be partially pulled from the amortization tables, the payments that we're making, we're applying the amount that's paying down the loan when we're thinking about the increase in the equity. So we'll talk more about that as we go. Let's first think about the rental costs. So if I was to be renting, What would be the rental cost? And notice that you can also vary this type of calculation to try to think about if you were buying, say, rental property, to think about how much you get for renting versus versus the cost of the maintenance of the home, e.g. and so on. So you can apply it in that standpoint as well. So we're going to say the rental costs. So rental costs, Let's make this a header, selecting these three cells up top go into the home tab. I usually make the headers on the Font group. I'm going to make it black and white. Black and white. Then we're going to say the rent payment for the house. So the rent payments for the home. We're saying pretty straightforward calculation. If we were renting if the rent was 1,500, I'm going to say on a month-by-month basis. Let's do it this way. Actually, let's say rent payments on the house. I'm going to say rent payments for a house, colon, and then do it this way. I'm going to say these are gonna be monthly payments, monthly payments on the month by month. We've got the 1500s and then on the yearly than the number of months, months, year is gonna be 12. And I'll underline that with an underline. And then I'm gonna say this is gonna be, I'll just copy this then. That's gonna be my sub calculation. And I'll get rid of the colon. And then we're going to do some indentation alignment. And then I'll double indent there, double and debt. And then we'll multiply this out. This is gonna be the 1500s times the 12. So there's going to be the amount that we're paying for rent on a yearly basis. So then we've got the renter's insurance. The renter's insurance, they said was $80 a year. So I'm gonna say, okay, you gotta pay the insurance of $80. Not too bad. And then we've got the interests lost on the security deposit. Interests lost on security deposit. So again, the security deposit, it's like a onetime thing and we should get the money back. What we're looking at now is the loss in the amount that we could use that money to be investing in. So that's the loss cashflow related to the security deposit. So I'm going to say, okay, what was the security deposit? 125, that's not bad. And so we're gonna say there is that. And then we're going to say that we could have earned 2% on it. So if we if we were to put it in a savings account, that's fairly low amount because you might assume you put it somewhere else, e.g. and get a higher rate for like stocks and bonds or something like that. But I'm going to assume if I had that in my savings account, which is probably where that one-to-five would be, then I get a 2% return and I'm losing that big deal. Number per cent underlying it font group and underline it. And that's gonna be the interests lost. Interests lost. Do I even need to put that in? It seems insignificant. Well, it could be significant depending on your circumstances. So I'm going to select these. We're going to go to the Home tab Alignment, indent, and then indent this again. Calculate this on the outer column. This is the one-to-five times to 2%. So there's the whopping $3, $3. And so then I'm going to say that's gonna be the rental cost. So rental costs, let's sum it up in the outer column equals the SUM function and sum it up. So there's the rental costs, we're going to say it's the 1880s three, Let's do a blue border thing here. Do our blue borders Home tab font group making this blue. If you don't have that blue, by the way, it's right here in the more colors standard area, that's the blue. Why do you use that blue? Because that's what the Excel is. Fun guy used. Home tab, font group, border eyes it too. So let's put an underline over here. Font group and underline. The Excel is fun guys always, right? And so he's got its dark color must be the color you have to use. So then we're gonna, I'm gonna make a skinny column over here. Make a skinny by putting my cursor on C, Home tab, clipboard, format paint, and make a skinny G. And then I'm going to hide the rental stuff so we can focus in on the home purchase stuff. So I'm going to select those and hide them or hide them. So I don't have to see them. Don't mess me up too high. Get out of here so you don't bother. Your thing is distracting me. So this is gonna be the buying costs, let's say buying or home, let's say home purchase, purchase, yearly costs or something like that. Because we're looking at the yearly costs here and comparing them to the yearly cost on the rental. That's what we're doing. That's the exercise. So I'm going to select these three. Let's make this a header, Home tab, font group and underline. Underline, make it black, and then make it white. That's what I'm trying to do. So then we're going to say the annual mortgage payments. So let's calculate the end. You will more guides humans both the interest and principle which we would get. Of course, we could take this number and figure out the mortgage payment using a payment calculation, which we'll do later, but we're giving them this time. So I'm going to start with the monthly mortgage payments, which is usually what we know first, that's gonna be this amount down here. It's yellow because we're going to actually dive into the amortization tables later to get more detail on that number. And so this is on how you'd get that number. This is going to be the 1485. In practice, you might actually first calculate the amortization table as a way to get this number and then start building the rest of your worksheet on it. But we'll focus in on that more in the second component. So months in the year 12. So there's gonna be months in a year. Let me see. January, February, March, April, and then that's over my ten fingers, 12, 12 months in a year. We're going to say Home tab font group underline. And then this is gonna be the annual and a copy of this on down. Delete the colon annual payments. Then let's do some indentation. Home tab Alignment in debt. And let's indent this one more time. Uno vase moss. And then in J5 equals this times that there's the yearly payments that we're talking about. Property taxes, property taxes, property taxes. We're going to say then that we're going to estimate the property taxes is 1.5% for is that what I said? No. No, no. The property taxes are the 1.2%. That's what I'm talking about. Okay, So that's gonna be, so if the home value, then if I know the home value is 300,000, I can say, okay, what are the property taxes in my area? I've got the 300,000 home cost. I'm going to estimate the property taxes at the 1.2. You can get more detailed on that. I'm going to make this wider on that. You might have because it might be different for the land versus the ones versus the building and so on and so forth. But that's gonna be dependent upon your area that you are in. You can get some estimates around that area to get the normal percent, and then that will allow you to make adjustments to the home value and take the relative amount of property taxes, hopefully giving you an accurate estimate that you can flux with different home prices, e.g. in your worksheet. So I'm gonna go to the number, I'm going to make that a percentage. I'm going to add some decimals. Just need one. Let's not get carried away. Don't carry it away. And so then we're going to say this is the property taxes. But I'm gonna get rid of the colon. And then we'll do some indentation and Home tab Alignment and dent double and debt here. Pulling this into the outer column, doing some multiplying, which is the 300,000 times the 1.2 percentages. There's the 3600. So now we've got the homeowner's insurance. So let's calculate that one. Home owner, owners and shorthands, colon. And we said that was 75 on a monthly basis, 75 a month. So I'm gonna make this one larger two. This will be dependent on the circumstances, whether you have the homeowner's insurance requirements, say 75 and we're going to say months in a year again equals the months in a year. I'm just going to say equals that one up top, which is 12. Pretty sure, if I'm not mistaken, that one stays pretty constant, doesn't change much. And then I'll copy the homeowner's insurance, put it down here, multiply that out into the outer, so 75 times 12. Let's do some N. Dan Tate, let's get rid of the colon here. That doesn't make. Then we'll do some indentation Home tab, Alignment and dent, and then double and double indent. So then we got the interests lost. So I calculate the interests loss, which I just gave us the number here. So that would be mean the down payment that we made. We're not we're trying to look at the average like the yearly costs after the home purchase, the downpayment, Then we're trying to think about well, if I put the money, the down payment into the home, I'm losing in the future the earnings that I would be getting on a yearly basis from the amount we put in. Now again, you could calculate this a bit more in depth. You might first think about the home price and then think he got 20% down e.g. and then calculate your after-tax earnings. And then that would be the amount that you're losing due to put in on a yearly basis, due to put in the, the down payment down because if you had the money, you'd be earning money on it. So we're just going to give it this time at just that nine-fifths, you could get into a bit more detail. We might leave that as it is for now, it's the general idea. Okay, so then that is that. And let's do the maintenance. Actually, before I do this one, I'm going to delete this one. Let's before I do that one, Let's add maintenance and repairs. That's important. Let's do that one first. Because that one you've got the home and we're calculating that and saying that we're going to estimate that 1.5 per cent of the home cost is the maintenance and repairs. Once again, by making this a percent of the purchase price, that allows us to change the purchase price in our worksheet and hopefully come up to a relative maintenance and repairs that is still reasonable based on the purchase price. Let's say we got the home cost and say that was that was the 300,300. And then we've got the percentage which is gonna be the 1.5, that will be something that you have to estimate. So you're going to get your best estimate on that, on number of group, we're going to identify that. Let's add a decimal and let's go to the font and underline it and pull this on the outer color. I'm just going to copy and paste. Get rid of the colon, do some indentation alignment and dance, and then double indent, pull this out to the outer column. And this is gonna be the 300,000 times the 1.5 per cent. Then let's add finally, the interests lost on the down payment, which we've talked about already. So I won't discuss it again because we talked about it already. We talked about that font drip underline. And then let's say this is gonna be the total, which is going to be the home purchase yearly cost. We're going to say it costs. And then I'll sum this up outer column, the equals s Due to the m sum, and that's 27 for 51. Now we're also going to have benefits from the purchase of the home that I'm going to put it in a separate area. I'm going to I'm going to select these items and let's make that border blue, blue borders. Make it blue and then put some borders around it to protect it. Because once it's so nice, people are going to want to take it. So I'm going to then select this one. We're gonna go then to the home group, are going to Format Painter and put that over here to the skinny K. Make a skinny K. And then we'll put our cursor on G. I'm gonna go on over to J GJ, and right-click and hide those items so we can build our next table without getting distracted by all these other stuff that we're trying to distract us. So this is gonna be the benefits of home ownership yearly. We're going to say there's a yearly benefit kind of stuff calculation. Okay, So I'll make this a little bit wider. Let's go from here to end. Let's make that our headers, Home tab, font group, black and white on the header. And then we got the equity. So first we got the growth and the equity that we're thinking about. So we've got the equity down here. And so this is going to be equal to the growth from, from paying down the loan. So remember, when you think about the equity that represents the difference in the home value to the loan value. And if we think about our net value or net assets, assets minus liabilities, then the bigger that difference is to better We have, we got bigger assets and lesser liabilities. So that's gonna be good for us. But remember that one. We don't, we can't actually, we haven't actually realized the equity, the growth and the equity because we haven't sold the home. And two, it's it's that equity is not something that is liquid. We can't, we can't spend it. So keep those two things in mind. Because and there's two kinds of components to the equity. One is we could think about well, if I assume we purchased the home for 300,000 and at least it doesn't go down in value if at least stay steady and hopefully it goes up. But if it stays steady, then I'm going to increase my equity by the portion of the loan payments that are going to that are going to the principal portion paying down the loan because as I pay down the loan, then my equity should go up if the home value doesn't go down in value. So that's kind of the more conservative side of equity calculation that would hopefully be increasing. But again, it could be possible that the house goes down in value. The other side of equity we can think about, hopefully equity increases because the value of the home goes up from 300,000 since the point in time that we purchased it. So those two things are kinda components of the equity. You can kind of break them out or think about them separately, but just realize that the home value could go down from the 300,000 if possible. Hopefully in the long run, if you're holding it over 30 years than it would clearly, you would think go up over the 30 year timeframe. But you just got to keep that in mind and it's not a cashflow thing, okay? And we'll also talk about how to get that equity amount from the amortization tables because the yearly decrease in the principal will change from year to year. So it's not really a set number. We will talk more about that later. And then we've got the tax savings from the mortgage interest. So let's, let's also do the estimated annual appreciation of top, estimated annual appreciation. Let's put that up top. This is the other the other side of the equity. So there's two things happening with the equity. The difference between the home and the home value and alone, one, you pay down the loan and to hopefully the value of the home goes up. But again, gotta be careful on those. It's not liquid. And it's not guaranteed that the value of the home goes up, okay? It could go down even, okay, So tax savings from more damage in tourists. So now we're getting into the, the tax benefits. So the mortgage interests, we're going to say the mortgage interests was what did we say the mortgage interests? Where did I put that? The mortgage interest was I'm going to say equal to this one down here. I said the 12,000. Now that number notice specifically kinda put here that that's for the first year. And so that's kind of a deceiving calculation because I'd like to know what this is on a year-by-year basis. I'd like to know what I'm gonna do, not just for one year since this is a long-term investment. And so this number is a little bit tricky because it will change from year to year. Also note that again, the mortgage interest deduction is something that's more complicated than simply taking your tax rate and multiplying it for most people because this home purchase is the thing that purchase people puts people over from standard deduction or itemized deductions. And so that gap, that difference between standard to itemize. Really a benefit to you. It's, it's everything over and above the standard deduction. So you really have to do kind of tax salts were to see what the actual benefit is, run an actual projection. And then you want to think about what's going to happen with that projection in multiple years in the future when your interests portion of your loan payment goes down, the tax benefit is gonna go down. So this is an estimate of your tax benefit, but you really want to be more careful with this calculation. And you can do that with tax software basically, but we'll make the estimate here. We're going to say the marginal tax rate. That means it's your highest tax rate because if this was gonna be something that's deductible or have an impact on your income. You're not, you have a progressive tax system which means that you're gonna be taxed at multiple rates, but your next decision is gonna be taxed at your highest rate. So if you were to adjust your income from this point, you are talking about tax changed at your highest, which is what's called the marginal rate, okay, 25%. Do a tax projection. Bottom line, don't just want to get more detailed than this, but we're going to practice with this one. Okay, So Home tab underlying, this is gonna be the savings here. We're going to get rid of this. Let's do an indentation alignment and dent double and debt over here, double in den, then multiply this out. So we got the 12,000 times to 25% tax savings then of the 3,000 is what we're saying. Then we've got tax savings from property taxes. The other big item that is going to be most likely deductible if you have a home purchase. Another item that puts most people over from standard to itemized deduction. Another item related, of course, to the home purchase. So also note that the laws could change from year to year. There was a fairly significant change increase in the standard deduction a couple of years ago. So although this is a fairly would be you'd be hard pressed to think of removing completely these deductions. They put a severe limitation, at least if you have an upper income level item on the home taxes for the state taxes. So they could change with the political winds blowing either way. So you never know what's going to happen in any case. So they've got the property taxes. The property taxes. Where are those property taxes? I have them here. We said the property taxes. Oh, I see. We said we said they were 1.5 per cent last time. So let's just recalculate it. I'm going to take the 300,000 times. The we said the property tax rate was 2.1 and that's gonna give us the 3,600. So I'm going to say this is going to be the property taxes Rob, rob or tea taxes. And then we've got the marginal tax rate again, which was the 25%. We're going to say that would be dependent on our income levels and so on. Let's go to the underlying up top. Let's make that a percentage, that cell. And then let's copy this arm down and get rid of the colon. Do some indentation alignment, indent and double n dent pulling this to the outside, that's gonna be the 3,600 tons to 25% for the 900. And then we'll sum that up. That's gonna be then the benefits of home owner home ownership yearly. I misspelled it probably, but that's okay. I'll fix it with a spell check. Then we'll sum it up on the outside, summing it up and put it in an underlying there. Okay, so now let's make that blue and borders. Let's make it blue, font group and border blue, border blue. Let's do some unhide from BTK. Putting my cursor on column B selected on over to column K, right-click. So we unhide those cells because they're. They're not going to distract us anymore. We need them now, now. Now we need those cells. So now we're going to say this is going to be, this is going to be the home purchase costs. Less, less benefits. And that's gonna be equal to, let's say the 27 for 51 minus the 11th six, which now we can kinda compare that to the rental items over here. So you can kind of compare those to the rental costs on the decision-making process. I'm going to select these items. I'm going to put brackets around it and we're going to board arise. It. Also just realized that mainly these are going to be cashflow kind of related items. And these items could be cashflow related items. These two items are dependent in port on the market, on the value of the home, at least not going down in possibly going up. So these two components are also not liquid. Even if our assets go up, we can't. So you might want to point that out. You might want to say this is the equities, equity items is the sum of these items. And say this is, this is the net cash flow or short-term cash-flow items, something like that. This is gonna be the 15851 plus the 7700. And then I'll put some brackets around that border rising that will put an underline here. So in other words, you want to do your comparison in terms of your net asset value, assets minus liabilities. But you also want to make sure you're taking into consideration your cashflow to make sure that you have sufficient cash flow to deal with anything that needs to be happening and only basically dipping into the equity in essence that you're hoping will be going up when necessary. So you don't want to be forced to dip into the equity unless there's an emergency or something like that or something like that, you want to be able to plan for the equity so you wanna make sure that you've got the cashflow in place, okay? So we've got these items here. Next time we'll go into this and we'll get into a bit more detail and actually calculate the amortization tables. This is something that you might often do first to try to get to one, the whole value numbers. So you can kinda change this home value number to adjust the other factors and then calculate your mortgage payments so that you can change your mortgage payments in alignment with different home value prices, which will then change everything else in the worksheet considering we're using percentages and so on. And the mortgage interests for the first year with typically be calculated from the amortization tables and the estimated annual depreciation or the difference or the pain down to the loan, which can help you with your calculation on your equity calculation would also be gotten from, in some way, the amortization table. So you might actually do that component first and draw these, some of these yellow numbers from the amortization table. We will talk about how that might work in future presentation. 11. Buy or Rent Home Decision Problem 2 Part 2: Personal finance practice problem, but using Excel, buy or rent home decision problem number two, part number two, get ready to get financially fit by practicing personal finance. There we are in our Excel worksheet if you don't have access to the Excel worksheet that so K and a prior presentation when we started this practice problem, we basically started from scratch. You could go there and start from scratch if you so choose, or you can follow along from here. If you do have access, there's three tabs to this worksheet down below. And example tab, a practice tab and I blinked AB. The example tab in essence being an answer key. Let's take a look at it now. We've got the information out the left-hand side and the prior presentation, we looked at the comparison of the yearly costs for renting versus those four are buying or purchasing. And now we're going to use that information to expand a bit more on the actual loan amortization itself, as well as a year-by-year breakout of the amortization table that we will construct in a few different ways that this possibly being what she might want to do first, e.g. to think about your home purchase price and kinda tie everything into the purchase price of the home, e.g. here, and then, and then use that to calculate your mortgage payment possibly. And then your down payment and all that kind of stuff. And then you can use your amortization table to make some estimates, particularly the table that we have condensed down to a year-by-year format to think about your interests, which we used for the taxation. But note that the interests does have a problem and that the yearly interest will change from year two years. That's something that you really want to keep in mind, as well as the equity calculation when we thought about the decrease in the loan balance, which would be an increase in the equity, the difference between the value of the home and the amount of the loan on the home. That to kinda changes if you think about the equity component just with regards to the pain down to the loan. We'll talk more about that as we go through the practice problem, we're basically focusing in on these items. And a yellow which you might then get. That'd be the mortgage payments and the mortgage interest and the estimated annual appreciation that we might get from the calculation of the amortization table. Okay, so we're gonna go to the practice tab. This basically has blew out preformatted sales that she could use instead of constructing the table from scratch. Then we got the blank tab where we started constructing the table basically from scratch. We did the two components thus far already. Now we're gonna be building that amortization table, and we're gonna use that to think a bit more deeply about the mortgage payment calculation and how you might set up your table to kind of construct this one based on possibly just the home value. So you might set up your table so you could change the home value and everything else populates from it, which would include the 1458 here. And then the mortgage interests. We have to figure out how we're going to construct that, but we can get that to populate automatically. And then the estimated equity from the down payment, you can get that to calculate, although it's a little bit tricky from the table. And then this increase in the value, you might say is a percent of the home value. You might say it's going to increase by so much percent. And you can actually make that a percentage if you wanted to try to get everything tied in to say the home value, e.g. alright, so let's do some hiding or let's, let's go back on over here. And we're gonna make this a skinny oh, column. We need a skinny. Oh, I'm gonna put my cursor on column K. We're going to go to the Home tab and format paid it and make a video. And then I'll first think about the loan, the loan that we're going to be needing to take out. So we said that we said that we have the home value. So now let's think about the value of the loan that we might have. So if I, if I start everything with the home value here, if I say the home cost and I look at my home cause I'm gonna say I'm gonna assume the cost is this 300,000, which I'm going to tie into that data. So if I change this 300,000, then everything else can change with it. If I go back on over 300,000, I'm going to say the percent down. So the down payment per cent or rate that I can use, I'll make this a little bit wider. I'm going to assume 20%, 0.2, 20% down, which is kind of standard. It might change from period to period, but I'm going to use the standard 20 down font underlying. That would mean that we've got the down payment, down payment amount of this will equal to 300,000 times to 20% or the 60,000 that we would have to put down then. And then we would say that we would have the loan amount loan amount or the amount financed would be the 300,000 minus the 60,000. So now we have that loan, I'm going to then make that blue or bordered and blue. So then we would also need to consider the mortgage payments. So let's say we have the mortgage payments and here we gave the mortgage payments. So I'm going to try to back into this number. In practice, we might calculate the mortgage payment based on the 240,000 and then the years if it was a 30-year loan and the rate e.g. but since I gave the mortgage payment in the problem, it's back into the unknown, which in this case is the rate just to practice our calculation. So I'm going to say, if I say the mortgage payment, I want them set the mortgage payment to be that 14858. So I'm going to set the mortgage payment to be that and that's rounded by the way. So that means that that is that, so I'm going to say then the periods, periods, or let's say years are gonna be 30 years. So I'm gonna say 30 years. And so then I'm going to back into the rate. So the rate then is gonna be like the unknown that I'm going to back into keeping everything else basically fixed here. So now I know the loan, I know the mortgage, I know the years now normally again, you'd calculate this payment. The payment calculation knowing the years, the rate, and the loan. Now I'm trying to back into the rate which you might do from time to time. One way you could think about that is to say, well, if I look at my payment calculation, that thing I normally calculate, I usually solve for the payment and I need the rate as part of my calculation. That rate is the unknown this time. So I can use is there a function that's a rate function? There is tobacco until the rate function. So that's what we'll use here. I'm going to say this equals the rate and then brackets. And now we've got the number of periods, which is going to be 30, that's in years. We want months. So I'm gonna multiply it times 12 comma. That payment then is what we have up top. So we were given the payment this time, so I'm going to take the payment and then comma and the present value is the loan amount, which I'm going to say is the 240,000 and enters hold on a second. I need a negative in there. Whenever you get something like that, you need a negative. I'm going to put the negative right there. And then if I make that a percent and add some decimals, that's gonna be a monthly rate because I got the monthly information now I'm just going to take that and multiply it times 12. So I'm going to take this whole thing to get a yearly rate, which is the customary way we see things in multiplied times 12, and we've got the 6.13 on the rate. So again, you're less common that you'd kinda back into the rate. You can imagine circumstances where that might be the case. You might say, I need my payment to be this and I'm going to hunt down or wait for the rate to be what it needs to be in back into the right. If you knew the rate, then you might be backing into the payment amount and then you might adjust simply the home costs and let everything else populate as you make different projections and your worksheets. Let's make this bracketed and I'll put some blue around it. I'll keep this one yellow, since that's the one that we calculated. Okay, let's make a skinny column. I'm going to take the skinny 0. And let's make the skinny are over here. And then we'll build our amortization table. So you could build the amortization table with an online tool. Of course, you could go to an online tool like this, but I would use this to help you kind of double-check the tool. You could just search in your Google search, find something like this. There's many different ones out there and say this is 240,000 and the periods are going to be a 30-year and the rate is going to be 6.13 about because we rounded it a bit and then calculate it and we get to the payment amount of that 1459. It's a little bit different because I rounded the rate and then calculate your amortization table. And notice that this rate right here is actually 6.1 266 because we're using a formula, so we're rounding it to 6.13. But I think it's useful to actually build your table and then builds your year-by-year tables. So we'll do that up here. I'll do it fairly quickly because we've seen it in the past. I'm going to say year, month, and then payment, and then enter asked and then decrease and then loan balance. Let's do a selection of those items. Go to the Home tab and make it black and white for the headers and center rise it. Amina skin arise these tube, that means making it skinnier. So new term that we're working on, we're working on finding acceptance for. And then 1012 Buckley, tie your shoe because most people don't have buckles. And then we're going to put our cursor on the fill handle and drag it down because we're going way down. Make sure you've got a good grip on that fill handle because we're dragging that fill handle down 363, 60, whole turnaround, a whole 360 degree turn or turnabout, Home tab Alignment centering. And then we're going to do our roundups over here. I'm going to say zero for the first one and then round up from my years now. So the first 12 months are in the same year. So I'm going to use a roundup formula. This will help us when we add our tables are year-by-year table roundup. Round up those, those little doggies. So roundup, we're going to say the number is there and then comma rounding it up to a whole number, which is represented by putting a 0.1 for some reason. And then bracket ties it and it rounds it up to one. I can put my cursor on that double-click the fill handle button. Instead of grabbing and dragging the fill handle, we could just call it, make it like it's a button and just double-click on it. And so now it's a Fill button. So it went down, something went horribly, horribly wrong. I didn't divide it by 12. Do it again, do it right. Round up brackets. This number divided by 12 comma, rounded up to 0.1. Okay, let's try it again. Double-click the Fill button. There it is. So all you got 12 ones and twos and threes on up to 12 30s. Hopefully, if it was done properly this time. So try not to. Hopefully, I didn't mess anyone up with that with that. Okay. I'm going to put some zeros up top. And we're going to say that this is gonna be equal, the loan balance, the loan balance, the 240, not the home cost alone balanced the payments are going to be equal to this 14, 58, which is rounded. I need to copy that down. So I'm gonna make it absolute F4 and the keyboard dollar sign before the queue and the six, you only need a mixed reference, but an absolute one works. The interests is gonna be the 240 times. We're going to pick up the rate which is about that 6.13, that's outside our table. We're gonna make it absolute because I don't want it to move when I copy it down. So I'm going to select F4 and the keyboard. You only need a mixed reference, but an absolute one works. That would be the yearly rate. We're going to divide it by 12 to get the monthly rate and then tab the loan decrease or principal decrease if you so choose to call it, that would be the payment minus the interest portion that rent on it, the loan balance or the principal, you might call it that will be left after the first payment would be the 240,000, the original loan amount minus the loan decrease or principal decrease to 33 about giving us about the 23967. Let's select those four items. Double-click on the fill handle button. And the fill button just does the whole work for us. It's amazing. They're putting me out of a job over here. It just does all the work and it goes down to zero down below. Let's go ahead and do some blue borders here. Blue borders. Why is Excel so good? Put me out of work. Man. Border blue. I didn't wanna do that calculation anyways. We're gonna go up top and then we want to break this out on a year-by-year basis. Now that's going to help us to get our interests on a year-by-year basis. We can see that the interests and the payment or the interests of the loan balance are going to change each time. Those are the important key components that we want for our calculations. Let's put a skinny over here on the skinny y by selecting skinny, our Home tab, hidden, hidden the format and skinny. Why? Why skinny? Why? Because I want a skinny column between the x and the z. So you've got to have a skinny. Why? Because that's the column between those two. That's why. That's why. That's why right there. And so I'm going to copy that and I'm gonna put that in z one, paste it. The headers get rid of the Month column because I don't need those. Get out here. Month column. We don't need you. We're going to then say, let's make this one a little bit smaller and put this from 12. And then we're going to copy that down to three because there's 30 years. So we'll grab it and drive it down with the autofill in my automatic jump into my auto and fill those up, driving it down my auto. And then we're going to center that. And then we're going to use the sum if calculations. We'll practice it a couple of different times. Some if amazing formula, simply amazing. So we're going to say this range right there, calmer. And I'll put in the absolutes later, but I want you to match it up to that criteria, that number one, you want to match it up right there. And then comma, and then I want you to pick up the related number in this sum range. Then Summit. That's what I want you to do. And it does it, it's amazing. So there it is, 175. Let's do it again for the interest. One, which is more interesting because it changes some if brackets range. I want you to pick up that range and then comma, and then look for my criteria, that one, That's the criteria I want you to look up in that range and then comma, then I want you to sum up the related range this time being the interests range, which is more interesting because it changes. So there it is. There's the 14. If I sum this up, checking it. 14623 checked, doubled, doubled, checked. Let's do it one more time. Brackets. I want you to look at that some range right there, comma and compare it to my criteria, which is that number one. And if there's a one there and one there, that I want you to some the relative range, which is the loan decrease right there, the WW with no F's not wrestling, not WWF, just WW. There's the 2879. Okay, let's do that so we can copy it across, make it an even faster deleting these two. If I select this one, I tried to make it so I can copy it across and down the SS. I don't want it to move. Stays the same SS minnow. So I'm going to say this is gonna be F4 and the keyboard make it an absolute. This one that criteria. I don't want it to move to the right, but I do want it to move down. And so I need $1 sign before the z, but not before the three. That's called a mixed reference, the UU, I do want that to move to V, V and then WW sound, we're going to then say, okay, copy that to the right. Copy that ten for Roger out. Copy, copy, read, read, copy. Selecting these three, we're going to double-click the Fill button, fill handle button, and it just puts it down there. We gotta do something slightly different on the last one because we want them minimum. I want the last number, I want the balance at the end of the year. In other words, let's do that by saying equals the MIN. If you need an s, ifs, brackets, main range, the x range for the mid-range, XX. You don't need to do any absolute Steph because this doesn't mean that this is an x-ray that formula or if that's just happens to be the range x x. And then we're going to say this is going to be the criteria range, which is going to be S. And then comma. And the criteria itself is that numero uno, that's number one case you didn't know. Double-click on the button, bringing that arm down. Let's bring it down and sum it up, sum it up, copied down, summit up as total, total and then equals the sum formula. Put in some totals in place. You walk totals. We totally want totals. Totally. We're going to copy this over, but not to the n column because that is the total there. And then we'll make that blue and border the border blue border, blue border, blue, border blue. Okay. So now you can see this on a year-by-year breakout, which is not something you can typically get as easy from your online tool, e.g. which is nice, and you can now determine the interest per year, notice it goes down each year. So if you're trying to think about what your tax implications are for interest, then you might try to take some kind of average of the year, maybe pick the one in the middle, e.g. or you might try to do a year-by-year calculation. To think about it. You might be planning on selling the home after a few years and you want to think about what your equity is, you could take your average possibly of the three years, but you can pull your interests calculation in some way from the amortization table and you want to recognize that the interests will go down. So if I look at my data over here, we just said that the interest was mortgage interests of the 12,000 and that's not exactly 12,000. That's not right. If for the first year because we could see that the interest is that the 14 and then it goes down below 12,000 down below here. So it's not 12,000 for the first year, but you want to pick, you might want to take like an average interest rate of some kind. But you can do that in some way by pulling from that table now. And if we had it all connected, if I, if I tied this from say, the average or from the mid number or something like that, then I can have everything connected and just basically change this big number up top the home price, everything else changing kind of automatically. The same is for equity growth. This item here, there's two things. When you want to think about the equity in the home, That's the difference between the home purchase price and the value of the loan. As you pay down the loan, you're making payments yearly that add up in this case to the 700s, 501. But in the beginning, a huge amount of its interests, meaning the loan balance is only decreasing by that. 2879. Now there's 2879 is decreasing the loan balance. And you can see that the difference between the home value, if we assume the value of the home stays at the 300,000 and the loan balance, that's that difference, is going to be the increase in the equity. So if I say this, 300,000 stays the same, we're assuming the home doesn't go up or down in value, then if we pay off if we pay off the loan, that difference is going to be the equity. Let me do that again. This is gonna be the 300,000 minus the loan balance, minus the loan balance. So you can see the loan. If we assume the home stays at the 300,000 and of course, we would generally assume it goes up, but somewhat more conservatively saying it stays the same, it could actually go down on, of course, then the difference between the loan balance and our home value. What the equity is. If I look at the change and the equity, this minus this, then the change in the equity is of course, the amount that is going to be the lone decrease. So the lone decrease, if we assume the property at least stays the same and doesn't go up, would be an increase in our equity. And we could use that to start to think about what my equity situation will be over here as well, which we assumed with this number. But again, you might pull that. In some way from the table. Now notice the change from year to year isn't gonna be the same. It started pretty low. And then at the end it's going to be quite high. It from year-to-year because you're paying yearly the 700s, 501. And at the beginning, most of its interests, at the end, most of its equity. So we might then adjust our equity calculation using some kind of average number in here, possibly to pick the one in the middle, or possibly trying to do a year-by-year calculation and variant for the equity depending on how we want to set it up. But we can tie them to the equity in some way over here to pull this number from making it, once again tie into our dataset so that we can just change one number, e.g. if I just want to change the home value and try to have everything else kinda populate in our worksheet. We could tie that in here, which you can again do so much if you're trying to pull this in from multiple areas, such as an online tool like this. So there's gonna be the equity calculations, so those are the key components there. And then of course, we can make this same table from a pivot table if we want. Let's just add a pivot table. You can't, I can't add this first column because it's two columns for the header. So I'm just going to say the second column all the way down. This might be easier to implement, but it's a little bit more difficult to draw from if you were going to make formulas that are going to a pivot table. So just be aware of that. But if I go to the Insert tab and just make a pivot table, I want to put that in the existing worksheet and I want to put it right there. Boom, that's where I want it. And then okay. And then I'll just put the years down hears, but I want this pull to the left and the rows area, and then I want the months. I don't want the interests, I want the decreased and the balance and there it is. Boom. Now I'm just going to format. They look a little nicer. So I'm going to hit the drop-down and the months thing. The value field settings format, the cells, currency, brackets, dollar sign gone decimal down, down. Ok, Oh, ok. That was way too fast, but it looks, it does look better, I must admit, but that's way too fast. So let's do it again. I'm going to hit the drop-down again. We're gonna go to the value field. I'll do it again so you can see it. We're gonna go to the number format and then currency brackets, dollar sign gone decimal down, down. I still didn't see it. Let's do it again, but it does look better. It does look better, but do it again so I can see it. Okay. Value Field Settings on the third one, Number Format and then currency brackets, dollar sign gone decimal down, down. And okay, okay. Okay, I get it now. Alright, now that you get it, we're going to change it up a little bit on the last one because we want the minimum balance. What you're gonna, we're gonna make it a little different. So we're gonna go to the View, this one. We don't want the sum, we want the Min. That's all, that's all the difference I'm doing. That's all. It's not a big change. And in this part's the same brackets, dollar sign gone decimal down, down. Okay? Okay. And then I'm going to skin arise these cells, which means to make them skinnier. So we're going to go ahead and use that term. If you haven't heard of that term, It's because the English language has not yet picked up on it, but I'm sure they'll catch up to us at some point. And so they'll they'll get there. They'll get there. So now we've got the same thing. He could build this table down here a little bit quickly, more quickly. But again, you can't really reference to the pivot table as easily because it doesn't refresh quite as easily. So it gets a little bit, a little bit finicky. You got to right-click and refresh it. And if it doesn't work, you can always just add the pivot table back in because we did that pretty quickly. Once you do it a couple of times, It's pretty easy. But this table up top, we'll probably adjust a little bit more easily if you were to say to just tie everything into say this number and you want it to make adjustments. If I was to change this to 350,000 and just say boom, I want everything to then change over here and then change all this stuff. And then I want my summaries to change over here. And then I want this stuff to all populate. And notice these cells are not, are not changing automatically, which we could kind of set them to change automatically by doing what we discussed, kinda tying them into some kind of average and so on. So let's put that back to 300,000. There, there it is. 12. Estimated Home Price from Monthly Income Part 1: Personal finance practice problem using Excel, estimated home price from monthly income, part number one. Get ready to get financially fit by practicing personal finance. We are in our Excel worksheet. If you don't have access to the Excel worksheet, that's okay because we basically build this from a blank sheet. If you do have access, we got three tabs down below, an example tab of practice tapping a Blake tab. The 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. I'm going to use that to populate our tables. On the right-hand side, we're going to start off thinking from the standpoint of a financial institution such as a big taking our gross income using the banks heuristics, their percentages there standard kind of techniques to think about how much loan the financial institution might be willing to give us. And we'll use that then to determine how much house we might be able to purchase, e.g. we'll also take a look at our own financial data and think about putting together an income statement from it. So notice when we're thinking from the standpoint of a financial institution, they might have their own kind of heuristics, their own percentages that they might use. We would also want to do, of course, our income statement if we were doing our own budgeting and look at the same kind of things, can we afford this type of thing from both of those standpoints? We will then put together our amortization tables and break that out on a year-by-year basis, both with formulas and with the use of a pivot table. So on the second tab, we've got our practice tab. We've got the pre formatted tables on the right-hand side. So if you use this tab, you can go through the practice problem with a little less formatting. If we go to the blank tap, That's where we're going to be. We're going to basically build this from scratch. Now there's a pretty big scratch on the left-hand side. This is the Scratch that we're going to start. That's a little scratch that we're going to build off of. So you've got to add this if you don't have that sheet. And I would start off by selecting the whole sheet. If you don't have any sheet, if you're just starting from blank, right-clicking on it format the cells. I would make it currency brackets, then no dollar sign, no decimals, That's my starting point typically. And then start to enter this data will go through the data right now and you can populate the data along with us if you would like to. If you're starting from a blank sheet, we've got the down payment. We're going to assume when we get the loan, It's gonna be 10% down instead of like the standard 20% down. So that could change from period to period depending on the current economic conditions and financial conditions and rules and whatnot, government regulations and so on. So we've got the loan, we're going to say 30-year loan. When we get to the loan item, the rate is going to be 6%. We're going to say property taxes and insurance per month, we're going to say is 600. We're gonna put our balance sheet together. So we've got the check-in, the savings of the emergency fund and I just adjusted the spelling their emergency fund and we got the IRA balanced the car that we're putting out the books. We're not really breaking out right here between current long-term, just getting an idea, this is gonna be our total assets or liabilities. We've got a car loan. So the net assets then are going to be the assets minus the liabilities. So that's our financial statement on the balance sheet side, which is where we stand. On the income statements side, we've got our income. One. This is gonna be, we're going to say that we have to W2 income, possibly a married couple or something like or two jobs that we have. We can measure the income from gross income. If you look at your W2 e.g. or your paycheck stub, you'll have the gross earnings and then you've got the money that was taken out for withholdings, including things like income taxes. And that could be the net income that actually goes into your checking account. So really when you think about the income you're getting from a W2 job, your actual income is the top line income because all this stuff they're taking out of it, including the taxes, is really inexpensive to you, even though you're kind of forced to do at least the taxes, they're mandatory withholdings. Oftentimes from our financial statements, when we put our income statement together, we might just start with the net amount after taxes because that's the amount that hit our checking account from a cash-basis standpoint. But in actuality, we should really have the gross amount and then take out the taxes for the net amount. So we gotta kinda deal with that when we're doing our personal financial statements and putting our income statement together. And also when we're thinking about Given our information to a financial institution, noting what are our actual income is when we talk to them, like, What do you mean? Do you mean gross or net? Oftentimes they're talking like the gross income. And we'll do that for the second income. We'll assume it's a W2 job as well. And then we've got our expenses, which we're going to list out and we're going to construct an income statement, utilities, food, gas, credit card, car loan, entertainment, and so on. Okay, so let's start out and we're going to take a look at our affordable monthly mortgage payment. And we're going to try to do this from the perspective of the financial institution using certain heuristics, certain percentages that the financial institution is going to do. In other words, the financial institution might not. Actually base their full decision on our financial statements are balance sheet and our income statement. They might use some percentages based on our monthly income to determine how much we think we can afford based on that. So that's what we're going to start out with. So I'm gonna go back up top and I'm gonna say, okay, well, let's put a header up top. I'm going to make this cell a little larger. I'm going to make the header affordable, monthly, monthly mortgage payment. And so I'm going to make this a little bit larger, maybe right there. I'm going to select three cells because I think I'm going to put some numbers on these two. So I'm going to go from here. Here, make that a header format. Go into the home tab up top back, home to the Home tab. And then we're gonna go to the Font group. We're going to make this black and white, black and white for the header. And then I'm going to start off with the gross monthly income. Gross monthly income. Not because it's the closest of the two numbers, like in terms of closeness, but because that's the top line, that's what they're usually asking for. The financial institutions. So when you're looking at that paycheck stub, make sure you're picking the gross income. And also when you're taking a look at the W2 income, even if you're looking like you've got line one and line three and line five are all income numbers. Once for federal income taxes, social Security and Medicare, it's often the Medicare income. That's the highest income number. Because again, you want to make sure if you get benefits from your work or four when k plan and that kind of stuff, make sure that you're picking the most appropriate number which are usually hoping is the highest number your gross income before they took everything, anything out, including benefits and taxes. Because that'll give you the most benefit from a loan standpoint because you're trying to look good. In this case. You're not paying taxes here. You're not trying to look bad. You're trying to look good. So that the financial institution, you have the ability hopefully to get as much loan as you, as you want to get, right? And notice that this calculation, you can think about it a little bit differently. If you're thinking about it from the standpoint of the financial institution, then the standpoint of your personal budgeting, because the financial institution is going to build their, build their heuristics, whatever thing for based on the law, based on their bank regulations and whatnot. And it might not make complete sense. And then we want to do our personal budget and based on our actual numbers, hopefully our numbers being accurate numbers in terms of our actual income statement oftentimes. Okay, so I'm going to sum this up. Let's put an underline here. We're gonna go to the Font group, put an underline here. We're going to say this is going to be our gross yearly income. Gross it up. We got to gross income. This is gonna be a totally gross number because you've got to grosses that are summing up, summing up both of them up. Super gross 119000. And then that's gonna be that's gonna be on a monthly basis. So we're going to say, I'm sorry, that's on a yearly basis. So we're gonna take it down to a monthly basis. I'm going to say months in a year is 12. And let's put an underlying there. We'll put an underline under it. And so we're going to say this is going to be gross monthly income. I can't take that yearly numbers to gross. It's too much it's too much closeness. So we're going to divide it by 12. So this is going to be equal to the 119000 yearly number, divide it by 12 to get to the 9,009, 17. Let's do some indentation. I'm going to select these items. We're going to go to the Home tab, Alignment and indent. And then we'll go on down here and go to the alignment and indent again. Now we're going to use the kind of heuristic from the financial institution. We're going to call it a, we're going to say it's the rate used. What kind of define an out here as a guide line for or prints. And then it's gonna be in terrorist taxes and insurance, often called the PIT. So this is kinda like a guideline, just type of percent from the financial institution. This, so you might hear this as a common heuristic type of number that the financial institutions might use to be based off of your gross income to get an idea of how much you might be able to afford. Now again, that could be completely different from your particular financial situation given your income statement, which we'll take a look at as well. But you want to, you want to get an idea of what the financial institution is doing so that you can use that to think about, put yourself in their shoes. And think about how much loan you might, they might be willing to give based on that. So we're gonna say, I'm gonna say and other debt payments. So you want to talk to the financial institution and at the current time, and this is usually going to be somewhat uniform from financial institution to financial institution. If you're talking about a standardized loan, like a 30-year fixed, usually like 20% down, That's usually the standard starting point loan. If he gets more unusual types of loans, then of course these kind of heuristics are gonna be, they're gonna be changing more rapidly because you don't have as much standardization with them. So wherever your current situation is in time, the current economic situation and so on, you should be able to get a fairly good idea talking to financial institutions about how they come up to their general calculation based on the gross income to get to how much loan that might be willing to give you. So I'm going to say this is 0.38 is the percent we're going to use here. Again, talk to your financial institutions at any particular given time to think about what their current heuristics are. And you can kinda use the term PIT and go from there and see if you can get a, get a feel for that. So we're going to say the number, we're going to make that a percent 38 per cent. And so I'm going to say then multiplying this out, we're going to say, let's multiply this out. And this is going to be then I did some spell checking their butt. I'm gonna be there afford this is gonna be the affordable amount that can go towards a home. So this is towards a home for them out. Affordable amount that can go towards a home. So I said towards, towards, towards a home. Okay. So now that we've got the spelling, at least somewhat down, it kinda makes sense that it's towards towards, I always just call it towards, towards, towards, towards any case, whatever, whatever. So I'm going to then go down here and say we're going to say less. Now notice that this number right here might include other things. You notice that I said the PIT and so that's the principal, interest, taxes and insurance. So that's all the stuff that the banks trying to use this kind of juristic term to put all the stuff that might be related to the home purchase into that one number to see how much could be afforded in it. So you want to know what this percentage, what they are including in there. And you've got, again, the principal interest taxes and insurance just in the name. They also might adjust this number to include other debt payments like credit card payments. So they increase the number. If you have other debt payments, e.g. and then they're going to say, okay, so that's gonna be the number that we're going to use that we think you might be able to afford on a monthly basis to cover these types of things here. So now we're going to say, okay, but I want to get it down to the amount that is just applied to the loan balance, which means I want to remove from this number on a monthly basis the stuff for insurance, the other and the other debts and the taxes so that I can get just to the amount that you're applying to the loan. And then I'm going to use that amount to see how much it might be willing to loan us, right? Then we're going to say, okay, we're gonna take out of this number. If, if that includes these other things, I'm going to take out the other debt. So I'm going to say other debt, we're going to take out of that number. So I'm going to say other debt and I put that down here. And again, this, you might think that this would be a general guide, which we have down here with a car loan payments. So I'm going to say car loan payments. I'm going to take that out. And this is going to be equal to the 290 to 95. I'm going to remove that. Let's actually put that on the outside here and let's make it negative. Let's make it negative. Well, negative out the 295 and then we've got also the monthly property taxes. So what did I put the property taxes. So the estimated property taxes and insurance per month is gonna be, I'll say negative of the 600 there. And so that's gonna give us our affordable monthly mortgage payment. And I'm going to sum this up equals the sum because it'll subtract out this. And I'm going to sum these up, but those are negative numbers, so it'll subtract them out. And so you get the general idea here, right? And so we want to talk to the financial institution and try to get to their calculation there heuristic so that we can get to the amount that is going to be applied, basically do the affordable monthly mortgage payment. Because then we can use that to try to determine how much loan we're gonna get. And again, you could talk to the financial institution at any given point in time and from institution to institution with a standard loan kind of setup. When you're talking about large institutions, they'll typically have similar kind of heuristics they will be using due to the fact that there are certain regulations that will be involved in calculating that as well as best practices within the industry and so on. And just the standardization of the loans and so on. So let's go ahead and underline this. I'm going to put an underline here. And I'm gonna put some brackets around this. And then we'll make this blue, blue and bordered. Okay, so now that we have that, let's figure out what the Affordable Mortgage amount would be. So I'm gonna go back over here and I'm going to say, all right, if I know that, I could say what's the affordable Bull? More GAD mortgage. Amount, meaning how much loan can we get? How much loan Can we get? That's how you should say affordable mortgage amount. How much loan Can I get? Okay, So in any case, we're going to calculate that based on this number. So usually we're calculating the payment calculation, right. But this is the payment calculation. So normally you would say the payment calculation because I would have the loan amount, I would have the rate and then I would just be calculating the payment calculation. So what I could do is look at the payment calculation, say, well, what am I looking for? What's the unknown here? It's actually the present value, the loan amount. That's what we usually put them for the loan amount. So I'm just going to look for a function for the present value function to get to the loan amount. That's what we're gonna do, is I'm gonna go up top. I usually start with a negative present value instead of an equal. And then brackets because that'll flip the sign to a positive. Probably not the most proper way to do it because you put, should have put the negative inside. But the easiest, I think. And then the rate is going to be the to the six per cent over there. That's gonna be the percent for a year. So we're gonna divide that by 12 because we want the monthly rate comma, the number of periods is going to be 30, but that's in years and we want months, so times 12 and then comma, the payment, then we got down here and it might be rounded, but it's the 2000s, 73 and enter. So that means our loan then is gonna be that 479 to 48. Now you can double-check that with your normal rate function and we'll also double-check it with an actual amortization table later. But you could say, okay, does that make sense? Let me do the rate and do this one and see if I get back. I'm sorry, let me do my payment calculation, my normal calculation that I know and see if I get back there. So if I take the rate, this one comma or divided by, divided by 12 comma, the number of periods is gonna be 30 times 12 comma. And then I take that present value. Does that get me back here? And it does right, so that it looks like it looks like that's correct. So I'm going to double-check it again with an amortization table. Will do that next time. I'm going to make this black and white. Okay? So if we know that what's gonna be the purchase price, because that's the loan. And let's make this blue by the way, before don't get ahead of yourself. Myself is so slow though. Just wait for yourself a second. Don't get ahead of yourself. So this is kinda be the affordable home purchase price. The affordable home purchase price. So now we're going to calculate now that we know that because that's the loan amount, the amount that we could possibly get financing on, and that would be a large amount of financing. So that'll be like a jumbo if that was the case typically. So it might be a little bit more restrictions if you're financing, is that high? But any case, we're going to use that number. I'm going to select these two. And we're gonna go into the home tab over here, font group. Let's make this black and white. And so this is going to be assuming I can get that much of a loan. Then let's say this is the affordable mortgage amount. Let's put that on the end. And I'm going to take a look at the rate of the cost that is not the down payment. So we're going to say the percent percent financed. Financed, better way to say it. I'm gonna say subcategory one minus the down payment rate or percent. So in other words, one minus the down payment rate, we're saying it's not gonna be 20 but ten, this time 10%, just to switch things up, which might be a little less than standard. But again, it just depends on whatever the current economic conditions you're in. Weird things happen in the housing market. People start thinking weird stuff. After ten years of weirdness, you get weird. This becomes the norm until, until the weirdness reveals itself to be weird. And then everything falls apart and we start over again. So rate of cost, that is, so this is gonna be the, the percent finance, percent financed, okay? And then this is gonna be one minus the ten. And let's make that is going to be 90%, of course, because if we're going to put a down payment of the 10%, that means that we're going to finance the 90%. Let's put an underline here. Let's check the spelling. Check the spelling. Affordable, affordable finance to okay, let's do some indentation, Home tab Alignment and indent. And then we'll indent this again, alignment and indent. And then this is going to be what we're calling the affordable home hurt chess price. And we're going to say this is going to be equal to that divided by the loan amount, divided by the amount we're going to finance. And that comes out to the 532 for 98. Let's double-check that number. Let's put a check figure. Let's put an underline. Let's put some blue borders around this thing. Blew boards, IRS. And then let's do a check, check, check, check on this, double-check my numbers. And we're going to say, this will be black and white. And this is gonna be, we're gonna take that affordable home purchase price because this is how we normally do it, right? We take that to start with, which is that number, and then we apply the down payment rate. The down payment rate. It's a downpayment. I don't make payments unless they're down payments. This is going to be at home. Number per cent, define it. We're gonna put an underline under that. Then this is going to be the down payment. Down payment, which is going to be equal to the amount of times the ten per cent. And that'll give us the amount financed, which is gonna be the home price minus the amount we financed. And so we can double-check. We double-check that number, check, check, check, check. Okay. So that looks good. So that we based all that kinda from the mindset of the financial institution. Now of course, in practice you would also want to compare that to your actual books. You might say, well that doesn't make any sense compared to my actual financial statements information. And it may not because again, what drives the financial institutions as a bunch of stuff including government bureaucracy and laws and whatnot. That may not make a whole lot of sense, right? So, so, so then you also want it to your own budgeting to see if you can afford what is going on here, and then see if you can take any alternative measures. If you can't get the financing you think that you can afford in certain circumstances by doing other loans are negotiating and possibly less conventional loan structures or something. In any case, let's do an income statement then I'm gonna, I'm gonna make a skinny by taking this skinny. And I'm going to make another skinny over here, another skinny. And we're going to just call this a monthly income statements. So monthly income statement. Now this is what you would think that the financial institution, this is going to be more on a cash flow basis. Cash flow, this, you would think this would be kind of what the, what the financial institution would be basing it on. But they can't really trust individuals income statements, as, you know, all the time and so on. That's why, that's one reason they might use a heuristic like this, just using a percent. But in any case, we're gonna do it for ourselves because if this was done correctly, this would probably be the better way to go to see if we can afford stuff. Let's hide some cells. I'm going to be hiding from C to two j, C j. I knew see a CJ before someone named CJ. I'm not sure if those letters actually stood for something. I'm assuming they did, but they were named Cgb. Anyways, we're going to say this is gonna be home tab font group. This is gonna be black and white. And let's say we've got the income. Now I'm gonna do this a couple of different ways. Like if you're doing your income statement, you might if you were putting into this interior like a QuickBooks or something, you probably just be using your you might be using a cash-basis system based on your bank account, which means you would see the net check flowing in after withholdings of things like benefits and things like your taxes. Which isn't as accurate because now you're kinda combining those things into one number, you're netting it out. So that's week, but let's do it both ways. So I'm going to say, let's take this is gonna be our income number here. And then I'm just going to copy that down. So we've got our two income numbers. So we've got the gross income and the net income, the gross and the net. Let's put, let's put the gross column over here. And then we'll netted out to the, to the left. This is the growth side. The left side is that gross side, and the right side is the net side. So that's the gross. And then we'll do it in a net construction over here and we'll get to the same bottom line. They'll show you the difference. So we're gonna say that grows on the left of the second W2 and the net on the left of the second W2. So this over here represents our yearly, our yearly income on a gross basis before we take the money out, which again, you would find that on if your paycheck stub your gross income before the withholding some things like taxes and possibly benefits. If it's your W2 income, you've got like three income boxes. Box one for federal income tax is three for Social Security and I think five for Medicare. It's actually the medicare one, which I think is gonna be the highest one closest to your gross income. But when stuff flows through to your actual software, or if you're getting your information from the bank. And you're just saying my income is what got deposited into my bank account. Then you're going to be using this number over here because it's gonna be after they already took the money out. Now these are on yearly basis. So what I'm gonna do is take each of these and divide it by 12. So I'm just going to divide them by 12. That's not 12, That's one. That doesn't do anything. Got to divide it by 12 to get it down to the monthly. So I'm gonna take that and divide it by 12. Take that and divide it by 12. So there we have it, and that's gonna be our total income. So we'll say this is toe, toe income. And summing this up. Sum it up. Now again, it's a little deceiving because this side over here on the next side of things is already taken out. Kind of our expenses, which include income taxes. Okay. So let's do some indentation. I'm gonna go to the Home tab Alignment indent. Let's indent again, double indent. And then I'm just going to pick up my expenses expenses, which I think I broke out on a monthly basis. And these will be the same for both columns. So I'm just going to pick up the expenditures and I'll just copy that down, auto-fill and then I don't need these ones. That's too far. You've gone too far. You've got it's okay. I can just delete them. I could just delete them. Then I'm going to indent and then I'm going to pull the expenses in here for utilities and one-twenty and then I'll copy that down. And then we'll do that here too. I'll just say the same ones. Maybe there's the one-twenty. So the expenses are going to be the same on each column. And there we have that. And I'm going to call this, I'm going to call this the net income or cash flow before tax, which is a little deceiving because the taxes are already in there In our net calculation up top. So in other words, I'm going to say, I'm going to say, well, hold on a sec. I got ahead of myself. Don't get ahead of yourself. Let's call this total expenses. And then I'll end it that two times alignment indent two times. Sum up, sum it up there, and there we go. Okay, So then let's put an underline here. Underline. It put an underline there. And then this is going to be net income or cash flow before taxes, which is going to be that the total income minus the expenses. So there we have that income. And this should be the total income minus expenses. So those are two different items here. And then I'm assuming that the difference between the gross and the net is taxes. It might be benefits to a four when k plan and whatnot, but I'm assuming the difference is just taxes here. So then I'm going to put my taxes down below. Taxes down below, which is going to be the difference. And it's only going to be over here. When I took the net. The net, I'm going to say, well, what was the net consisting of the difference between this number that grows? 13. Estimated Home Price from Monthly Income Part 2: Personal finance practice problem using Excel estimated home price from a monthly income at port number to 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 because in a prior presentation we basically put this together from a blank sheet so you can go back there and start there from the blank sheet if you so choose, we're gonna be continuing on here with the practice problem. If you do have access to the sheet, there's three tabs down below. And example Tampa practice tab in a blank tab, the example tab in essence being an answer key, let's take a look at it now. Prior presentations, we've got the information on the left-hand side. We used it then to populate the data. On the right-hand side, we first thought from the perspective of a financial institution using certain heuristics of the financial students institution, such as a bank, to determine based on our gross income, how much they think we might be able to afford for the home purchase process. And then distilling that down to how much we can possibly afford just simply for the financing of the loan. We then use that to determine how much loan we might be able to purchase based on that information. And then we use that and the estimated down payment to get to the amount that we might be able to afford to purchase on the home. And then we double-checked our number. Now we want to think about them, the financial statements. In other words, you might look at this first number from the bank and say, well, the bank is using certain types of juristic kind of shortcuts you might think, instead of using what you would think that they would use, which would just be the financial statements. Notice again, from the banking side of things, there's a couple of different reasons. There's, there's multiple reasons they might use a different kind of shortcut type of system. One is that they might not be able to trust the financial statements from a bunch of different kinds of individuals. They'd have to kind of verify them and there's costs related to verify and so on. Whereas they can fairly easily verify the income number with W2 statements and something like paycheck stubs and so on and so forth so that they can use the paycheck stubs, then they have more verification. There's less kind of variables if they can use that as a heuristic from there. And then you also have the situation where there's gonna be regulations in terms of government regulations, that they're compliant, banking regulations and so on. So you could be in a situation where you're saying, Hey, look, the number that the bank is coming to may not be something that you think is appropriate based on your personal financial statements as you do your financial statements. Now, the personal financial statements may be something that the bank requires or asked for and may not depends on the circumstances, but we want to do our own income statement as well. In any case. I remember when we're dealing with the financial institution, we have kind of two separate goals. The institution on their end, they're trying to determine if we can pay back the loan, of course. And they want to be feeling quite secure that we can in order to give the loan on our perspective with the financial institution, We're not going there for budgetary advice. I'm not going into the financial institution to give me budgetary advice. I'm going there to see how to get the maximum amount of capacity for loan capacity that I think I might need in the event that I might need it. And then I'm gonna do my own budgetary calculations with my own income statement to think about how much I can afford. So those are two kinds of objectives. When you're talking to the bank. You typically want to look as good as possible, of course, to end so that you get the highest loan possible that you want to be honest and upfront, but you want to give them the numbers that would provide you with the best loan possibilities. Our budgeting side from the personal use, that's when we actually want to budget and see what we can actually afford and how much loan we would want to be taking. Those are two different things. We're not just going to take how much loan the bank will offer. We're going to take the loans up to the amount that we could afford. And we would like the bank to be willing to give us a more loan in the event that we needed it, right? So we want that number to be as high as possible typically. So that's gonna be the example of the practice tab. And we're going to have the information that already has some cells populated over here so you can do less Excel formatting in it. And then the blank tab is where we built it basically from scratch. And this is where we're gonna be continuing on here. So now we're just going to construct an essence of financial statement and compare and contrast that to the number we got down here so that we can practice our own budget team. So let's hide some cells. I'm going to go back on over here. I'm going to make a skinny k first. I'm gonna go to column G, And I'm gonna go to the Home tab. And we're going to go to the paintbrush, going to make a skinny K. And then I'm going to hide the cells from C to D, from C on over to Jay. I'm going to right-click and hide those cells, not not delete them. Just hide them from CJ. Cj one time, someone the price did for something, but I'm CJ. Any case. Here's the financial statements. So we got the balance sheet, which shows where we stand as of a point in time. But we're more focused oftentimes kinda like on the income statement. Possibly we want to break down our income statement on a year-by-year basis. Now, note when you're looking at your income statement, there's a couple of things you might, the ways that you might put this together, you might actually try to compile this information just from your, from your paycheck stubs and your W2 2s and then your vendor bills and so on to get a good idea of it, That's one way that's fine. Or you might be using software or something like that. And basically going from the bank statement in essence, oftentimes being more on a cash basis method. I just realized when you're looking at the income side of things, it gets a little tricky because the income often has these withholdings involved in it. So if you're on a cash basis system and you have like a QuickBooks or other kinds of software that's basically constructing your financial statements and pulling this stuff in from the bank feeds from the bank, from the financial institution. Or if you are building your financial statements from the bank statement and your W2 employee, the amount that hits your bank is actually the net check and not the gross check. Now remember when you talk to a financial institution, they often want the gross amount and that's often what you would like to give the financial institution because you want to give them the highest amount, because that will give them that the most likely result in the highest loan value on there. And on our end, when we get down to our net income, we want to take into consideration whether or not we're calculating the income statement with the gross or net and then just be aware of that, be conscious of it as we do the financial statements. So that's one thing we will take into consideration. So we've got our income, we're going to assume this to W2 incomes, possibly by husband and wife, married, or possibly just someone working two jobs kind of thing on the gross pay and then the net pay. We're going to assume the difference between the two, between the taxes that were withheld, but there also could be benefits that were withheld two, and then we've got the expenses and we just listed out a couple of expenses so we can practice putting together our income statement here. So let's just construct an income statement. We're gonna do it kinda more on a cash basis, cash flow income statement. So this is gonna be a monthly, monthly income statement. And this I'm going to make basically a cash flow. Cash flow. And then we'll make this black and white for the headers up top. Let's make this Home tab. We're gonna go to the Font group, will make it black and white. And then we're going to go with our income. Income is the top of the income statement. And we've got the two incomes. I'm gonna, I'm gonna use two columns here because I'm gonna kinda do the income on a gross basis. And then on a net basis, those are the two ways you might see the income statement. They're both okay. As long as you as long as you know that you're you're reporting the income. So I'm going to use two different columns, and I'm going to assume the first one. We've got the gross amount. It's not really gross. It's just that's the top line. After that. It's not like it. That's not like it's slimy one or anything. And then this one and let's put the amounts here. This is going to be equal to the gross income, which we're going to say the 47,000, That's for a year. I'm going to divide it by 12 to get the monthly amount. Now you might get the gross income from your paycheck stub, e.g. they might get it from your W2. But if you look at your W2, note that there's three income lines on it. There's box one, box three, and box five. Box1 really only has the income that is related or subject to federal income taxes. So that might even be reduced by something like a 41k plan or something like that box the medicare one box three is for Social Security. Box five is the Medicare. That's probably the actually the highest one, which is closest to your gross income. And then your net income is going to be over here. Which if you're getting the information from, I'm going to take this divided by 12. If you're getting the information from the bank, That's what actually hit the bank. So if you're constructing your financial statements from the bank, you're more likely to be using the net income over here. And you just want to be aware of that because it's already net of at least taxes. And those taxes are your taxes, right there actually expenses to you, their taxes that you paid that were taken from you by your employer. So we're going to stay down here and then we're going to save that. We have our other income. The growth side of things, the gross income. So gross income is so gross. Why is income? Income is good. Income is that's the gross income. So we're gonna go down here and then this one's going to be the net income. And then this is going to be equal to the net income on this one. Divided by, divided by 12. So there's our there's our gross and net income on our income statement. I'm gonna go ahead and sum. Those up. Let's sum it up and we're going to call this total income. So we'll do with the growth side first, total, total gross income, total gross. And then we're going to say this is the total, the sum of the net income. We'll put some underlines here. Why not? Some Home tab, some font groups, some underlying. Let's do some indentation selecting these items, we're going to go to the Home tab Alignment and indent it. And then we'll indent this one again, double indentation. And then we'll put our expenses in place, expenses brackets. And I'm just going to list them out here. These are things that she could of course get from their bills themselves. Or you might go to your bank statement and look at your expenses. If you have software like QuickBooks or other types of softwares that can compile this. They might be drawing this from basically what clears your bank or credit card funds, e.g. and so we can go down here. And software is getting better and better at doing, at being able to construct this kinda stuff from banking and finance stuff. So it might be worth doing. I'm just going to copy this down. So I went too far. You've gone too far, but that's okay. I did it on purpose because I can just delete those. Calmed down. It's okay. You've gone too far. Then we're going to set utilities is going to be over here. Is one-twenty. I'm just going to copy that down. So we're going to pull this utilities. And again, you could get this from your bank statement, from the credit, from the actual statements themselves compiling them. But you might want software to construct this stuff. It's, it's useful. And so we'll copy that down. I'm going to put some underlying down here. Notice I put the car loan payment as an expense. So that's a little bit tricky because really if you're on an accrual basis, there would be a decrease to the principal rather than an expense and so on. But we're going to assume it's kinda like a cashflow item that we're spending each time. And we're trying to think about this on a kind of cash-flow basis is think about how much Added cash flow we would have if we were to make a home purchase and so on. So let's go over here and say we've got the font group and underline. And then this is gonna be the total expenses. Total expenses equals the S to the u to the m, otherwise known as the sum. And then we're going to say this is gonna be, this will be the net in calm income or cash flow. I'm going to say before taxes, even though the taxes are included in one of them, but not the other and the net but not the other. But I'm going to say before taxes and this is going to be equal to the income minus the expenses. That's how these things work. This will be the same income minus the expenses. And then the key point I just want to point out down here is you've got your taxes, which I'm assuming is the difference between the gross and net income. You might have benefits as well, but I'm assuming it's just the taxes. And so I'm going to say the difference between these two A's, the taxes. So I'm going to come out to the same number except that the taxes are included up here, which would be if you're using the cash basis. This is what actually hit the bank because they took it out on the employer side of things, your taxes. Okay. Let's put an underline here. Underline here. We'll put an underlying there and an underline there, and I'll put a zero there. So it has something to underline and that's going to give it. Let's do some indentations. You get ahead of yourself. Stop getting ahead of yourself. But I'm so slow. I hate waiting for my I hate waiting for my you gotta wait. You gotta wait for yourself. This I'm going to indent this one. I'm going to indent Home tab, indent this one. And then this is going to be the net income or cash flow after tax is. And this is going to be equal to the total income minus this number here. Well, hold us, it's going to be after techno, hold on a second. It's going to be this number minus the taxes. That's what it's going to be. That's what it's going to be. And, and then this one is going to be equal to this minus that. So we get to the same bottom line number. Let's put the double underline to indicate the bottom line. That's the bottom line which has two underlines. That's how you know. Okay, so then let's put some borders around it, blue and border it. Blue and border. We're gonna go to the border blue. Border blue does do a spell checking. Did you mess up any spelling? Know because I just copied it over. I didn't even type any spelling. I can't mess it up because the data over here was messed up. So that is it. So then down. So notice again we get to the bottom line. The main thing I just want to point out, one is that when you put together the income statements, you might be putting together gross or net. And then you want to think about your own kind of net income. We want to think about how much we can afford as a separate kind of thing to what the bank is doing. We might use this to bank might ask for something like this, in which case we're gonna give them the information. But the bank might be doing something different. And again, we're not really talking to the bank in order to do our personal budgeting. We're not there not a financial planner for us. They're trying to determine whether or not they're going to give us alone. And we're trying to get as much access to as much as we can. Not that we're going to take as much loan is they'll give us because we want the ability to take as much loan as we need. So if I unhide my cells over here from B to L, bold and unhide, then you can see that their number here on the affordable mount might be substantially different. And I'm just obviously we just kinda made these numbers up. They might be whatever heuristic that they're using is based on whatever standards complete averages. It's just they're trying to use just a number that covers everyone. It might be completely different to what to what we actually come out with because our actual finances are actual circumstances might be quite different. We might have a completely different lifestyle than other people on their normal, that there are normal heuristic that are average numbers that they're using are using. So, so that's, so, that's fine. So we want to basically, again talk to the bank and use whatever format that they're going to use so that we can make the estimate of how much loan could be available to us. Again, not so that we could actually take out that much loan possibly, but so that we can try to get as much access to financing as we can have. And then we're gonna do our own financing, our own budgeting to determine how much loan we actually want to take out, how much loan we can, we could actually afford. And for that, we would like to have as accurate as possible actual financial statements, balance sheet and income statement. Next time, we'll take this information and we'll build our, our tables from it, our amortization tables, and our, our grouping of that table by year. And that of course can help us with the budget and from this point, so once we have the income statement before the loan kind of process takes into consideration, then of course, we want to think about, well, what would happen after, if there was a change? What's gonna be the difference from the standard now to the change? And those things will include things like what's gonna be the cost of the home and so on. It taken into the Interests tax implications in that kind of stuff. 14. Estimated Home Price from Monthly Income Part 3: 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. And prior presentations, we've basically built this from a blank sheets. You can go back there and start from there if you so choose. We're gonna be continuing on from here. If you do have access to the worksheet, there's three tabs down below, and example tap a practice tab in a blank tab. The example tab in essence being an answer key, let's take a look at it now. The information is 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 boil 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-check 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 alone and the payment of the loan, we're not looking to the bank for budgeting information. They are not or financial planner. They are people we're trying to get a loan from there. We have a specific objective there, and we have a different objective for our financial planning, our personal financial planning on which we wouldn't want to be using the bank juristic, 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 bake how much they'd be willing to loan us so that we have the capacity to take out the alone that we think is appropriate on our head. We're not just going to try to take out that loan that the bank thinks the maximum loan that they'll give us. We're trying to, we're trying to say, hey, Bank were 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 determined appropriate. Right. And so then we're gonna 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. 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, 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 the 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 cashflow differences and the non cashflow differences. In other words, our equity in the home, this big asset we have on the books at versus the loan that we're putting on the books, verse and the cashflow that we're going to need to pay our debts coming forward, are going forward. The second tab over here is going to have the preformatted cells on the right so that you can work through the practice problem with a little less formatting. Wanna do so much Excel formatting and then the third tab or continuing onto work this from scratch. So we've been building this together. So I'm gonna go to the right-hand side now and I'm just going to make another skinny column. The skinny, skinny, oh my darling. And so we're gonna go over here to k. We're gonna go to the Home tab. We're going to go to the paintbrush and make a skinny, skinny 0. 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 estimates, estimates, rotation none. Then we estimated that we can buy a 532 for 98 home. And then we're going to put down I'm just going to recalculate the down-payment. So I'm gonna put down, I'm gonna 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 the 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 put an underline, their 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 will 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 gonna be covered. We're going to say it's a standard 30-year 30-year loan. And that rate needs to be present ties. So I'm going to make that percent taxation on it. So let's go and percent ties 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 if everything is done properly, this amount because that's what we based this number on. We've 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 p empty brackets. We're going to pick up the rate, the six per cent, that's for a yearly rate. We want a monthly rate, so I'm going to divide it by 12 and then comma number of periods is gonna be 30, but that's in years we want months, so I'm gonna take that times 12. 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've said it was going to do. So let's make that blue. We're going to go to the Home tab font group blue and bordered, blue and bordered. Now note that she could go on line 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 show you other resources you've got available to you. For 79248. This is gonna be a 30-year loan, six per cent. And then we'll just say boom, calculate. And there's the 2873323. You can make the amortization table. There it is. That's amazing, but I almost think it's easier for us to do it because I'd like to break this down on a 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, I'll make my own, I'm making my own amortization table. It'll be way better. So I'm gonna go to the skinny over here. I'm gonna go to the Home tab Format Painter. And I'm going to make a skinny arm. And we'll do our headers. I'll do this fairly quickly. I know we've seen this in the past. You're gonna do this again because this is important and I like doing it. It's fun. Okay? We're doing that again. Interests we're almost done 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 then I'll make these a little bit skinny this to the stuff we're going to make that we're going to skin arise it, which is the technical term for making it more skinny. 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 autofill. I'm going to copy that down to 360. We're using the autofill driving it down, autos driving it down. Dr. Phil's gonna do the calculation. It's an auto-fill. Auto-fill autos the driver. And then Dr. Phil calculates because he's a Dr. even though he's not really like a Dr. that does that kind of calculate. So then we're going to say this is gonna 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, that 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 0.1. That tells you to round it to the next whole digit. That's what the 0.1 means. Then, okay, rounds it up and I can just put my cursor on it, double-click the Fill button, which is really a handle. That'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 Phil handled 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. 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 F4 on my keyboard dollar sign before the Q and a six-year-old and need a mixed reference, but an absolute one works and it's easy. It's so that's a virtue. And then we're going to say this is the 479 loan balance times the rate of 6%. We want that 6% not to move. So I'm going to F4 on it, making an absolute dollar sign before the queue. And the four, you only need a mixed reference, but absolute one works. That would be for a year's interests. We're going to divide it by 12 because we want them months interests. And then we'll do some subtract Sean payment minus the interest and enter. No absolutely necessary because nothing's coming from the dataset we're all in SAT or table that we're working on, that we want the prior balance minus the loan decrease to get the new balance or the new principle, whatever you wanna call it. No absolutes are mixed needed because we're inside our data table. We want both those relative sales to move down as we copied 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 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? Demand. It takes a long time to scroll that far. My scrolling fingers got a crown. 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 gonna go to the Thought group blue, It's highs it and border rise 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, 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 gonna be possibly important information for our budget's going forward. I'm going to put my cursor on the skin ER, and format pane it and we'll make a skinny y. Why? Because the y was two 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 month 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? Then? I'm going to make it skin or eyes. The Z, which is the technical term for making it skinnier. It's the verb format of making it skinnier or skin horizon it. And then this is gonna be 10012. And then we could just copy that down to 30 using the, now you gotta 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 gonna go over the top and we're going to make that centered. Then we're going to calculate the payments with the sum if formulas, because I want it 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, sum, if summit, if brackets range and you would think you would be the sum range here. But now we're talking criteria range which kinda 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 set of range, then I want you to do something to some other range, which is gonna be comma, the sum range, in this case the payment range, the UU. I want you to sum up everything that has a one over here, but ***** sum 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 gonna say this is gonna 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 in that range, Excel, you listening, Excel Comma. What I want you to do then is sum up this range, this time the interests range. So the ones like all of 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 28. So that worked out. The interests is often more interesting to you 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 and you can double-check it the two check check, check 585. 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 gonna put F4 on the keyboard, make it an absolute, this criteria, I want it to move down but not to the right. We need a mixed reference. So I'm going to put $1 sign before the z. But not the three. And then this one, the UU, Uu to move over to v, v into w, w. So I'm not gonna 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 that's what I thought it was going to do and it did that. 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 the loan balance, I want the end balance of each time period here. So this is a Min formula, similar kind of thing, but it's a men, men, men, if 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 Min range, which is this range. We're starting off with this one and comma. So 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 Min range. That's what. So then it takes that one. Excels paying attention. Thanks for paying attention, Excel. So then I'm gonna, we're gonna put a sum at the bottom of this. So it'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 sum this up. I can't afford that. Why am I even doing this? Okay, So there we have it. So now, of course, the interests can 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 to. 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 got to think about the benefits of the home in terms of both 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 are cashflow that we're gonna be having. So we can use these year-by-year numbers are often helpful to do that. And we can also just realized that we might have a tax benefit, but that's going to change from year to year because the interest is gonna 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 interests that you're gonna 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 gonna be happening going forward. With regards to the law these days, things 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 gonna 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. Then we're going to say just put a pivot table and 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 gonna go in the values. I want to pull that into the rows. And then I just got it. I don't want the month, I want the payments, the interests, 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 liked 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 format 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 looks way better, but you did it way too fast. Do, I'll do it again. Second one, value field settings. We're gonna 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 gonna go Value Field Settings, number formatting, currency, brackets, dollar sign gone decimal downtown. Okay? Okay. Okay, I got it this time. Alright, this is going to switch it up a bit on the last one, what? We're going to switch it up a little bit value field. This time we don't want the sum, we want the Min, we want the Min. 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 gonna skin arise these cells from z to AD. Skin or eyes 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's the technical term of the future. So there we have it, and 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 dataset. Over here.