 Personal finance practice problem using Excel. Buy or rent home decision problem one 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 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 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 in prior presentations. We started the practice problem doing a comparison for the yearly costs if we were to have the rent versus the yearly costs if we have the purchase. And then as we did that, we pointed out a couple 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, for example, 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 say the interest, which helped us to calculate the savings on the tax returns, which relate to possibly the deductibility of the interest, for example, and the calculations for the growth in the equity. So that's what we'll do now. On the second tab, we've got the practice tab, which will have the blank blue areas that will be pre-formatted. So you can work through the practice problem with doing a little bit less formatting if you would like. We're going to be over here in the blank tab, 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 kind of our loan schedule. And this might be one of the first things you do in order to base this data on in your Excel worksheet. You might first think, well, what if I had varying loans, for example, and then base your loan payments on that and your interest portion on that and so on. So let's do that. We're going to go over here and say, okay, I'm going to pick up this column K, make a skinny column by going to the home tab, format painter and make a skinny O. 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 going to kind of back into. And so we have the mortgage payments because we have this in our data set, 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 said the annual payments were the 10,700. So let's say that, well, if that's the case, then if I say this equals the 10,700 divided by 12, I would have monthly payments of about 892. We can add the pennies. So it's really 891.67. I'm going to remove the pennies just so it's easier to see here. And so, again, in practice, you might first come up to the loan amount, for example, and then figure the payment. But let's back into the loan amount given this information because we could just practice working with our formulas to do so. And then we've got the period. 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 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 going to say that that's going to 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 kind of 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. How much loan could we get if my payment was here? So there's a couple 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. So 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. So that's going to 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 going to be 30, but that is in years we need it in months. So we multiply that times 12 and then comma, we've got the payment, which is going to 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 148, 723 about that's rounded again. You can kind of double check that if you wanted to by 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 the 30 times 12 comma. And then the present value, the thing that we solve 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 then simply this, you know, this data. So then we could say, okay, well, if that's the amount we're going to finance, how much is the home cost? So this is the loan stuff. How much home can I purchase? Then if I know the amount of my loan here, so I'm going to go font group orders blue. Let's make this the home cost for the good old homestead. My homestead cost. I'm going to make that blue or black and white for my header. And then this is going to be the loan that we calculated up top. I'm going to say the loan is equal to the 148723. 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 minus 0.2 or 20% would be 80%. If I make that into a percent number group and percent to find it, that's the amount that we would have to finance. If I was to put the 20% down, right? So then I could say, okay, well, that means that the home cost would be equal to the 148723 divided by the 80%. That means I purchased a home for 185903. I put 20% down, which could change. I mean, 20% is kind of standard, but it depends on the circumstances and the economy and whatnot. But I'm going to 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 185903 and then I got, I'm going to put a down payment down payment. This payment is down. This is a down payment of 20% .2, 20% number percentifying it and font group and underlining it. That would mean that we'd have the down payment is going to be equal to 185903 times the 20% 37101. That, by the way, is where we got this down payment 37101 in our problem that we used. So that's that. And then, so that is that. And then that means that the, that the loan, the amount that we're going to finance is going to be equal to the home cost minus the down payment, the down payment. That payment is down. We're going to go home tab font group and border blew 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. It's not what you're trying to do. Focus, focus like a beam of laser font group underline. Let's put an underline here. Okay, now let's build our amortization table from this because that'll help us to think about the interest in this equity number. Some of these numbers that we were saying, what's that funny? Where'd you come up with that? You just kind of magically put those numbers there. Let's go with the, let's make a, the skinny O copy the formatting here format and put that on and make a skinny R 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, with online tools, but I think it's better to do in XO. 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. There's a bunch of them, 148.723. And you can say, well, this is a 30 years, 6%. 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 going to be the payment interest. This is going to be I'm doing two cells here loan decrease. So I'm not wrapping the text because that makes that makes a wide one, a wide one column. And I don't want that because that messes everything else up. Why should that column mess everyone else up just because it needs to be wider? That's how I feel about it. So that's I don't use the rap text because it's not fair to the rest of the cells. So I'm going to 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. We'll make these two a little bit skinnier, skinnerizing it, skinnerized 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 could see it with a number, the number that's populated right there. We're going down 360. It's a long way down. Make sure you're grabbing that fill handle steadily. You got a good grip on it. There's 360 360. The rotation has been complete on the 360 kick flip. And so this is going to be alignment and center it. I'll do the years thing here. I'm just going to say equals to one. This equals this one. I'm going to say, well, they're going to do the roundup. I'm going to 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 point one 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 in 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 these in the past. I'm doing this fairly quickly. I'm 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 going to be the 148723. And the payment is going to be equal to, this is a rounded number, the 892. 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 data sets. I'm going to say F4, dollar sign before the Q and two. We only need a mixed reference, but an absolute one works. We're going to calculate the interest, which is going to be the 148723 times the rate, which we said was 6%. That rate is outside of our table here. It's in our data set. Therefore it needs to be absolute or mixed. Absolute is the easiest thing to do because we could just hit F4 on the keyboard, dollar sign before the Q and four. And then we're going to divide it by 12 because that would be the interest for a year. And we want the interest for a month. Subtracting the payment 189-744. And that gives us the 148 decrease in the loan balance. So of the 892 that we're paying, we're only paying down the actual loan of the 148 for the first payment. So the loan balance prior was the 148723 minus the 148 means the new loan balance is 148574. I'm going to copy these four, not copy them, select them. And then double click the fill handle button, copying that down. And because we made the proper amortite or absolute reference side, 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 is done properly, but I feel like it has. So I'm going to then say fought group, let's say this is going to be blue and bordered. Let's put some blue borders around that one. So there we have it. So now you can see, of course, that the interest and the decrease in the loan balance are changing over time. And so those numbers are kind of what we're basing these calculations on. When we talked about the tax benefit of the interest, we picked up this number and put 8,000. Well, it's not always 8,000 every year because it changes every year. So that that number is one you got to be kind of 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 kind of estimate it 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 interest in the first year, if I select everything from year one on the year is around 8,874. But in year two, if I select it, then it's 8,761. It goes down. So each year it goes down. So if you've got a more complex projection, you've got to think whatever your tax benefit in year one is going to be less as time passes. And so you got to take that. You got to just realize that. So how can we think about that? We can make a table that breaks us 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. I'm going to say, I've verified my numbers to some degree or I would with this. And now I'm going to take a step beyond. I'm going past what that I can do. Home tab, let's make a skinny R or take that skinny R to make a skinny Y. Why? Because we need a skinny column right there. So I'm going to say, format painter, skinny Y. Skinny Y. Why is the Y skinny? Because it has to be. So we're going to copy the headers. I'm going to put that on Z. I'm going to remove the AA column because I don't need the months. And we'll do this a couple different ways. So let's do this on a year-by-year breakout. I'm just going to say one, two. I'm going to take that two and we're going to buckle. We're going to tie my shoelace. One, two, tie my shoe because I don't have buckles. One, two, 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 the sum 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 sum the related payment column. So then I'm going to say, and I want that. Well, let's do this comma. And then the criteria range, the criteria is that. So if you find that one in that range, then I want you to sum up the related range that has one in it, which in this case is going to be the payment items. Boom. And I'll put absolute reference in it shortly, but let's just practice that a couple times again. There's the 10-7. That makes sense because if I sum this up, we get to the 10-7. Let's do it here for the interest, which is more interesting because the interest changes from year to year. So this is do the interest, which is more interesting. So this is going to be then the sum if. And 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, no, the criteria is going to be this and then comma the sum range is now the interest one. There we go. So there's that. And then the loan decreased. Let's do it one more time. This equals the sum if. Sum, but only under these conditions. This range right there, we're going to want to comma that. And then the criteria, look up the number one in that range and then comma and then sum up the related range, which is that range. Boom. Bam. And there it is, man. And so there the 1-8, 2-6. 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 fastest. Like a, like, like flash does. Flash is fast. We'll do it flash 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. So a mixed reference. So because I want this one to move down but not to the right. So I need a dollar 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 them, 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. We could just copy that down. Double click on the fill button. Copies it down just amazingly. And then we can do the loan balance, which we want the end number on each time. So right there, that number right there. So I'm going to say this equals the men if you need an F some that one multiple ifs. So men ifs. And then we've got the men range, which is going to be this range X comma the men criteria range, which is going to be the years again, comma, and then the men criteria, which is that one. Again, I don't need to do any absolutes or 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 till it hits right in the middle of the planet. So I'm going to then go here and let's make this make make this blue and bordered. So now if I look at my interest on a year by year basis, I said around 8000, which is kind of like, you know, somewhere reasonable because because really in year one, it's the 8008. And then you can see it goes down the amount of interest we're going to pay is going to go down it's under 8000 by the time I get to year eight. And then it's going to go it's going to go down to very far down until year 30. I'm only paying 340 in interest, although I'm still paying 10700 for the home. So 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 like a number somewhat in the middle. You might say, OK, well, maybe I'll, you know, to be fair, maybe I'll choose the number on your 15 or something like that for the interest. 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 in 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 interest calculation, the interest changes from year to year and it's going to go down. So your tax benefit is going to go down from year to year. It 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 if this loan decrease is the decrease in the loan balance. So if I assume that my balance in like the home stays at the value that we said was 185, 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 in essence, you know, that our home, our home, you know, 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 calculated this benefit. So this decrease in the loan is basically our increase in the equity from a year by year basis. Our 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 assumptions. This assumption that when you pay down the loan, your equity is going up. It's more conservative or less risky because it's less likely that your home actually goes down than 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 going to go up. So that means that this number over here that we thought about, we could think about the equity or the increase in equity in two kind of components. We could think about the difference in the payments, which we said was $2,700. And then we could think about the growth in 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. Our equity portion is going to go up, meaning the interest portion is going to go down from year to year and then the amount that's going to 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 cash flow is going up. The only way to get that equity 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 going to be able to sell the home, right? It just depends on the economic condition. So you want to think about the cash flow as well as your overall asset situation. Now, I can also make this table using a pivot table and just note that you might do 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 interest portion using some kind of estimate from your table, possibly taking the middle number or something like that that we 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 table up in that way. Okay, so let's just make this one more time. I'm going to 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 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 going to put it right down below here. I'm actually going to make a total column. So let's put it right there and then insert the 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, interest, 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 the sum 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, decibel down, down. Okay. Okay. You did that way too fast. That's okay. I'll do it again. Second one, sum of interest. 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 fast. Okay. We'll do it again. Third one here. We're going to 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. So there it is. We get to another table. Pretty easy to set up. Doesn't change quite as much. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Shouldn't change quite as automatically with changes. I'm going to select these, and skinnerize them, making them skinnier is the technical term for that, which I just made up at that. I'm totally going to increase the size of the dictionary with these useful terms that dictionary is not keeping up to date with the useful terms, not going to put a total column down here. This is going to be the sum. summon this up, sum it up and then we're gonna copy it across but not to the end because that last one you don't sum the balances because that 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 so there we have it so there and there we have it so so you can use this data in practice you might do this again this loan stuff first and then build your you know build your data table on 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