 Personal finance practice problem using Excel. Estimate affordable home purchase price, part number two. Prepare to get financially fit by practicing 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, a practice tab, blank tab, the example tab in Essence Dean and Ancer 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 gonna 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 home we might be able to purchase. To do that, we thought about the PITI, which we're 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. Now 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 wanna 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 of loan 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 of the loan period, which we're gonna say is 25 years, then we calculated 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% 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 so the 30-year fixed again would be the starting point that you'd probably think about at the most standardized type of loan and then we thought about then how much home we can purchase. Then we kind of double checked it to look at the home, the 20% down. This would be the down payment. We would need cash of that for our calculation to basically do that as well and then the amount that would be the financed amount. Now we're gonna continue on to 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 loan amount and the home amount that we can purchase. Then we might wanna take that information and do a more in-depth calculations from our financial perspective using our actual kind of income statement and 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 calculate, I'm not advertising this particular website but you can use an online calculator to do a similar thing, give 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 a couple of times. If you go to the tab to the right this has blank information so that you can do this without having so much formatting in Excel. And then this blank tab we're gonna build stuff from scratch. Now we started, we already did this component. We're gonna start to build the amortization tables now on the right. So what I first need is a skinny sale right here. I need a skinny sale because I don't want anything right next to this one. So I'm gonna copy this skinny sale 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 eye. The skinny eye can still see the eye because it's skinny so we can make that one really skinny and still see the eye unlike the C here which is kind of getting cut off. You can see because the C is a little fatter, little thicker, not that it's fat but any case, we're gonna put our headers up top. Now normally with these amortization tables I like to have, I think I'm gonna need two rows and I could use the wrap tool but then that makes this sale, you know why the one and I don't like that because it messes everything else up. So I don't do that unless I'm gonna put it into a table which we will create a table from it, a pivot table but I'll show you what the problems of the frozen cons of wrapping is. What I'm gonna do instead is just imagine we're gonna have two sales up top for the longer title names. I'm gonna start with my headers. It's gonna have a years. I'm gonna say month, month and then I'm gonna say that 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 principal sometimes and I make it like the wrong kind of principal 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 which you might call principal balance but not like a principal out of school. So then I'm gonna highlight this and we're gonna 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 header 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 the 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 gonna start at month zero and then one, then two and now Excel can recognize the pattern as most of us probably can too. The next one's gonna be three. So I'm gonna auto fill it. I'm gonna select those, put my cursor on the fill handle and then drag it down to auto fill. Make sure you got a good hold, a good grip because we're driving this thing way down. Auto's 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 auto fill. So then I'm gonna say, there it is, 300. Let's center that while we're here alignment and center. Okay, now we're gonna do our tricky year calculation because I'd like the years here so I know which year we're in. So I don't have to like kind of divide by 12 and figure out what year we're in. So fancy formula, this is, you might not see this other places. This is, you might not know, we're gonna round it up. So we're gonna say, this is gonna be a roundup. Roundup, so we're not rounding up like cattle. That's not like a cattle term. We're rounding the number up. So we're gonna take roundup equals roundup brackets and then we're gonna pick this number to the right and then I wanna round that up. I'm gonna take it and divide it by 12. So one 12th and then round it up to the first digit. So I'm gonna say comma and I wanna round it up and I gotta put the digit which is I think it's 0.1 to round it up to a whole digit. That's what you gotta 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 auto fill. And so there it is. So 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 gonna then say, let's do that and then we'll center it. Let's get rid of, let's make the decimals go away and I'll do that here too. I'm gonna center and make the decimals go away. There we have it. Okay, so now we're gonna take the payment or let's take the loan balance. I'm gonna take the 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 backed into the loan balance on. So now I'm gonna pick up the payment. So I'm gonna set a payment is gonna be that one, that 860 and let's make that F4, let's 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 interest. I need another column between these two. I'm gonna do that here. 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 gonna right click there and insert like where's the interest gonna happen? This loan has no interest. What kind of magical bank are you working with? That's not, they must be crooked or something. It's not right. So this is gonna be equal to the 102497 times the rate on the left hand side, which is gonna be the 9%. 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 gotta double click on that. I'm gonna divide this by 12. I don't need to put brackets around this or anything because order of operations multiplying before dividing there's the interest for the month. Loan balance decrease, which you might say principle decrease is gonna be that minus that. There it is. And so the new loan balance is gonna be equal to the 102479 minus the 91. So there we have it. Then, and notice you might wanna, I wanna kinda check this to like the online tool. I use the online tool as 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 loan 102479. So I'm gonna say 102479, 25 year loan. The rate was 9%, I believe. Let's calculate it. And so now we've got the 860 payment looks right. I can pick up my amortization table. I've got interest 768769 and 91. So 769 and 91. So it looks like, you know, I'm getting the loan calculator kinda confirms me, it's another way to kinda confirm it. Now I could copy this down, but it's not gonna work. So I'm gonna copy it down and see what the problem is. This one did what we wanted, cause it's absolute. This one did not because we moved the interest down. So anything that's not inside the table or anything in other words that comes from the data set, we wanna make it absolute. So let's delete that. And I need to make this interest calculation. Everything that's not inside the table, which is that B, cause that's coming from over here. I'm gonna make that absolute F4. 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? Cause I don't need to copy it across. So whatever, absolute's fine. This one, I don't need to do anything cause 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. It's all inside, so 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 gonna 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'm still gonna scroll down cause I wanna 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 formula. So the next step is something that is useful data that we can't do as well with the other table. And plus this one ties in of course to my data set. So here, like 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 gonna hit the dropdown. We're gonna 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. We'll 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 fat, it's just a little wide. Okay, so now this one, these probably 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, for example, poor hand. And so like that. And then I'd like to see my interest 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 be gaining from year to year. This is quite useful when you're doing loan comparisons, one loan versus another loan, which is gonna be the next step, of course, when you start to think about how much loan can I get? How much house could I purchase? And then you're thinking about, okay, what am I gonna do, you know, tax planning in the future and so on. I need to know my interest and what's gonna happen with my equity and whatnot and so on. So let's do that. So I need another skinny column over here. I need another skinny. So this skinny eye, I'm gonna put my cursor on the skinny eye so it's the same width and go to home tab, clipboard, paint brush it and put that on the skinny P, skinny P. And so 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 gonna take these headers and copy them and I'll paste them right here in Q1, control V and then I just remove the months. I don't want the months. So I'm gonna put my cursor on R 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 we're at 25, I think? One, two, and I'm gonna bring this down to 25 and I'm just gonna summarize the data on a yearly basis as opposed to a monthly basis, which is totally useful, way useful, it's 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. I wanna sum this up. So this is what we're gonna do. We're gonna use a sum if calculation. And so I wanna say if there's a one, I'm gonna 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 in 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 kind of easy to calculate. I can just take the eight, 60 times 12. But when I go to the interest and the loan, then it changes from year to year. So then it becomes more importante. So mass importante. So let's do it then. Let's do it here with the payment calculation. I'm gonna say this equals the sum if, sum it if under these conditions and criteria. So the range is gonna be, I'm gonna pick this range. That's where the criteria range is. I'm gonna select the whole thing. So I'm just gonna pick up the whole thing. Now if there was anything underneath it, you wanna be careful of that, but there's nothing underneath this one for me. So I'm just gonna say the whole column down to forever. It goes right down to the bottom of the earth. This Excel sheet goes to right to the center of the planet. So then I'm gonna say comma, and then we're gonna, the criteria is gonna be this one, the one, and then comma, and then the sum range is 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 gonna sum this up. That's what I'm talking about. Yeah, all right, let's do it again. Let's do it here. Let's try it again. Equals the sum, sum, not the sim. Sum if equals the sim. Okay, sum if, and then the range is gonna 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 sum the related items and the interest range and the interest range and then enter, boom, amazing. Simply amazing, nine, one, seven, seven. 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 nine, zero, six, nine. So there we go. Okay, so then we can do it. Let's do it one more time with the loan balance. Equals the sum, if this is a super cool formula. We're gonna say the range is gonna be here. Home on the range, comma, the criteria is gonna be one, comma, and then the sum range is this one. So if that one, when you find that one over here, then sum up the related stuff and the loan decrease, which you could call principal decrease. There it is. And so let's sum that up and notice there. So let's add it up this way and double-check it. Check twice. I want two checks by this one. Two checks. Let's copy it down and then check it out, see how it differs from year to year. That's why this is important stuff. You can't just say year one is the way it is because that's not the way it is. It is with the payment, but not with like the other stuff. Now, we could, I'm gonna delete this and do it another way. I'm gonna 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 way super easy. But if I copy that to the right, then there's a problem in Houston. Houston has a problem. It's always Houston and their problems happening. And so this one moved over. I don't want this range to move over right here. First of all, so I need to stop that. I need to stop that from happening. And then this one moved over to the right too. That doesn't, we gotta stop that from happening in Houston. Houston has a problem. So we're gonna say, then let's hit that one. We're gonna say this is gonna be, we're gonna make this absolute F4. So this range doesn't 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 Q, I don't want that to move. This one, I wanna stay in this column. So I need a mixed reference. So I'm gonna put a dollar sign before the Q. 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, so there it is. I don't 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's a little bit different because I want the smallest number. So this number one, I want the smallest number over here. So, which would be down here. That's the min function. So we're gonna use a min if. Min if equals min if. Ifs, you need an F on that one. Min ifs. So we want to say, this one's a little bit different in like the ordering. So we want the min range. The min range is O. So I'm gonna just select O column. And then comma. And then the criteria range is going to be the ones. So I want you to look into this criteria range and then sum the related item over here. If you find the 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 sum range. Let's do it. There it is. So that picked up the last one right here, 101-336. 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 interest per year? What's the principal decrease per year? What then it's gonna be the impact on my equity, let's make this blue before I start babbling too much. Let's make this blue and bordered. So then the interest can help you with your tax calculations, for example. The loan balance can help us to determine in part what our equity difference is gonna 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. Two things that are gonna 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 gonna 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 you 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 changed my data over here, for example, you might not wanna do this, but I'm just gonna, like if I made this like nine, like if I made this, you know, 10,000 or something, my data on the right is gonna change automatically. So you just, so that's what you wanna, you wanna be aware of, you wanna be able to construct things in that way so you could change your data set and have everything move over. In this calculation, I would probably wanna change like the rate here or possibly my down payment or something like that, which would 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 gonna select the whole thing. Now this is where you got that issue where we put this in two columns, the header. So 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 gonna take this second header and then drag all the way down, select in this 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 gonna put it in the existing worksheet and I'll put it right here. So everything's nice and tight in the same area. I'm gonna say, okay. So then I got my pivot table box, which is really intimidating looking, but not too bad. I'm first gonna start with the year. That's what I want on the right-hand side. I gotta visualize this thing. It put it in the sum column, but I don't want it there. I want it in the rows. There's my years. Now everything else is pretty straightforward. I don't need the months. So I'm just gonna pick the payment, the interest, 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. So we're gonna have to fix that. I can't deal with that. So we're gonna go over here. We're gonna hit the drop down and I'm gonna value each of these fields and just adjust the number formatting. I like to make them currency bracketed, get rid of the dollar sign and decimals. Okay, okay. There it's better. That's much better. That's getting my feeling better. I was a little stressed out there because that's messy. It's messy with all that decimals. Let's do it again on the second one. Value field setting. And then I'm gonna say number formatting, currency, brackets, get rid of the dollar sign and decimal. Okay, oh God, that's so much better. Let's do it again. Let's do it again. Drop down, value, field setting, format, currency, brackets, dollar sign, gone, decimal, decimal, down, down, okay, okay. All right, now the last one's a little bit trickier because I want the min balance not the sum. So hit the drop down, value it. And I want the min value, not the sum, min value. Then this 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. What is that blank? I'm not gonna worry about that right now. Why is there a blank cell? Okay, it's okay. It's okay. It's bothering me. That is bothering me. That's because that's the first, that's period zero. Okay, that's okay. But now I'm gonna squish these back together again. Make them a little squishier like that so they're not taking up so much room. Now over here, notice I could wrap the headings down here with the wrapping of the headings, but that makes this whole cell swide again. 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 that'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 changed the pivot table, then you gotta make sure that you refresh the pivot table. And sometimes it could be a little glitchy to refresh, so, but you can always recreate the pivot table pretty quickly too once you get it 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 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.