 Personal finance practice problem using Excel. Buy or rent home decision problem number two part number two. Get ready to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you don't have access to the Excel worksheet, that's okay. In a prior presentation, when we started this practice problem, we basically started from scratch. You can 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 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've got the information on the left hand side in the prior presentation. We looked at the comparison of the yearly costs for renting versus those for 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. This possibly being what you might want to do first, for example, to think about your home purchase price and kind of tie everything into the purchase price of the home, for example, here. 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 interest, which we use for the taxation, but note that the interest does have a problem and that the yearly interest will change from year to years. That's something 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, too, kind of changes if you think about the equity component just with regards to the paying 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 in yellow, which you might then get, that would 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 going to go to the practice tab. This basically has blue pre-formatted cells that you could use instead of constructing the table from scratch. And 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 going to be building that amortization table, and we're going to 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 then set up your table so you could change the home value and everything else populates from it, which would include the $1,458 here and then the mortgage interest. We'd have to figure out how we're going to construct that, but we could 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, for example. All right, so let's do some hiding, or let's go back on over here and we're going to make this a skinny O column. We need a skinny O. I'm going to put my cursor on column K. We're going to go to the home tab and format, paint it, make a skinny O. And then I'll first think about the loan that we're going to be needing to take out. So 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 start everything with the home value here, if I say the home cost, and I look at my home cost, I'm going to say I'm going to 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 and say I've got the 300,000, I'm going to say the percent down. So the down payment percent or rate that I can use, I'll make this a little bit wider. I'm going to assume 20% point to 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 underline. That would mean that we've got the down payment. Down payment amount of this will equal the 300,000 times the 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 finance would be the 300,000 minus the 60,000. So there we now we have the loan. I'm going to then make that blue or bordered and blue. So then we would also need to consider the mortgage payments. Let's say we have the mortgage payments and here we gave the mortgage payment. So I'm going to try to back into this number and 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 for example. But since I gave the mortgage payment and 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 or the say years are going to be 30 years. So I'm going to say 30 years. And so then I'm going to back into the rate. So the rate then is going to 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 calculate this payment off 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, the 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 great function. There is to back into 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 going to multiply it times 12, comma. The 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 a non 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 going to 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 multiple times 12 and we've got the 6.13 on the rate. So again, you're less common that you would kind of back into the rate. But 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 then hunt down, you know, or wait for the rate to be what it needs to be and back into the rate. 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 in 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 O and let's make the skinny R over here and then we'll build our amortization table. So you could build the amortization table with an online tool, of course, so 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 the amortization table and notice that this rate right here is actually 6.1266 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 build your year by year table. 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 interest and then loan 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. I'm going to skin arise these two. That means making it skinnier. It's a new term that we're working on. We're working on finding acceptance for and then 1012 buckle 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 got a good grip on that fill handle because we're dragging that fill handle down 360 360 a whole turn around a whole 360 degree turn or turn about 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 roundup for 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 or year by year table roundup. Roundup 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 point one for some reason and then bracketize it and it rounds it up to one. I could put my cursor on that double click the fill handle button instead of grabbing and dragging the fill handle. We could just call it and 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 roundup brackets. This number divided by 12 comma rounded up to point one. Okay, let's try it again. Double click the fill button. There it is. So all you got 12 ones and 12 twos and 12 threes on up to 12 thirties. Hopefully if it was done properly this time. So try not to hopefully I didn't mess anyone up with that with that there. Okay, I'm going to put some zeros up top. And we're going to say that this is going to be equal the loan balance the loan balance the 240 not the home cause the loan balance the payments are going to be equal to this 14 58 which is rounded. I need to copy that down. So I'm going to make it absolute F4 and the keyboard dollars now before the Q and the six you only need a mixed reference but an absolute one works. The interest is going to 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 going to 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 the 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 233 about giving us about the 239 767. 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 want to do that calculation anyways. We're going to 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 interest on a year by year basis. We can see that the interest in the payment or the interest 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 in the skinny Y by selecting skinny R and home tab hitting the hitting the format and skinny Y. Why skinny Y? Because I want a skinny column between the X and the Z. So you got to have a skinny Y 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 going to put that in Z one pasted 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 one to and then we're going to copy that down to 30 because there's 30 years. So we'll grab it and drive it down with the auto fill in my auto. I'm going to 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 some if calculations will practice it a couple different times. Some if amazing formula simply amazing. So we're going to say this range right there comma 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 some range and then and then some it. That's what I want you to do and it does it. It's amazing. So there it is 17 five. Let's do it again for the interest one which is more interesting some if 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 interest range which is more interesting because it changes. So there it is. There's the 14 if I sum this up checking it 14 623 checked doubled doubled checked. Let's do it one more time some if 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 a one there that I want you to sum the relative range which is the loan decrease right there the W.W. With no it's not wrestling not W.W.F. Just W.W. There's this two eight seven nine. 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 try to make it so I can copy it across and down the S.S. I don't want it to move S.S. Stays the same S.S. Mino. So I'm going to say this is going to 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 so I need a dollar sign before the Z but not before the three that's called a mixed reference the U.U. I do want that to move to V.V. And then W.W. So I'm going to then say okay copy that to the right copy that ten four Roger out copy copy read read copy selecting these three going to double click the fill button fill handle button. And it just puts it down there we got to do something slightly different on the last one because we want the 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 M.I.N. if and you need an S if brackets main range the X range for the main range XX you don't need to do any absolute stuff because and this doesn't mean that this is an X rated formula and if that's just happens to be the The range XX 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 so then we're going to double click on the button bringing that on down let's bring it down and Sum it up sum it up copy down sum it up as put it total total and then equals the sum formula putting some totals in place. Do you want totals we totally want totals totally we're going to copy this over but not to the end column because that 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 for example 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 for example, 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 so you could take your average possibly of the three years, but you could pull your interest calculation in some way from the amortization table, and you want to recognize that the interest will go down so if I look at my data over here, we just said that the interest was mortgage interest of the 12,000 and that's not exactly 12,000. That's not right for the first year because we could see that the interest is at the 14 and then it goes down below 12,000 down below here. It's not 12,000 for the first year but that would you want to pick you might want to pick 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 this 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 17501 but in the beginning, a huge amount of its interest, meaning the loan balance is only decreasing by that 2879. Now this 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, you know, 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 the if we pay off the loan, that difference is going to be the equity. Let me do that again. This is going to be the 300,000 minus the loan balance right that 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 of course, then the difference between the loan balance and our home value isn't is what the equity is. And if I look at the change in the equity this minus this, then the change in the equity is of course, the amount that is going to be the loan decrease. So the loan 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 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 going to be the same it started pretty low. And then at the end it's going to be quite high from year to year because you're paying yearly the 17501 and at the beginning most of its interest 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 in the one in the middle or possibly trying to do a year by year calculation and varying for the equity depending on how we want to set it up. But we can tie then to the equity in some way over here to pull this number from making it once again tie into to our data set so that we can just change one number. For example, if I just want to change the home value and try to have everything else kind of populate in our worksheet, we could tie that in here, which you can't again do so much if you're trying to pull this in for multiple areas such as an online tool like this. So there's going to be the equity calculation. So those are the key components there. And then of course we can make this same table from a pivot table if we want. So 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 and 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 OK. And then I'll just put the years down here. But I want this pulled to the left in the rose area and then I want the months. I don't want the interest. I want the decrease and the balance and there it is. Boom. Now I'm just going to format so they look a little nicer. So I'm going to hit the drop down in the months thing. The value field setting format the cells currency brackets dollar sign gone decimal down down OK 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 going to go to the value field. I'll do it again so you can see it. We're going to 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 could see it. OK. Value field settings on the third one. Number format and then currency brackets dollar sign gone decimal down down and OK OK OK. I get it now. All right. 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 going to. Yeah we're going to make it a little different. So we're going to go to the view. This one we don't want the sum we want the men. That's all that's all the difference I'm doing. That's all. It's not a big change. And then this parts the same brackets dollar sign gone decimal down down OK OK. 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 you 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 times. It's pretty easy. But this table up top will probably adjust a little bit more easily if you were to say to just tie everything into say this number and you wanted 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 on these cells are not are not changing automatically which we could kind of set them to change automatically by doing what we discussed kind of tying them into some kind of average and so on. So let's put that back to 300,000 and there there it is.