 Personal Finance Practice Problem using Excel. Buy or rent home decision problem number one, 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. 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, 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 build our tables from it on the right-hand side, comparing the yearly costs if we have renting or if we continue to be renting versus the yearly costs after we have the purchase of a home. You can also kind of modify this calculation to be thinking about, say, your investing in rental property to think about what the rental costs would be, which could be income, for example, versus the costs for the buying of the home. So you can kind of 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 we'll be continuing on once you purchase the home. That's going to be things like the mortgage payments that you're going to 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 costs. 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 the practice tab. This has some pre-formatted sales. So you can work your problem here with a little less formatting. And we're going to 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 going to 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 could 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. I remove the dollar sign and I remove the decimals. I'm not going to 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, for example, formatting them as a percent. And then you basically were 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 going to be paying. The security deposit we have of the $800 and then versus the buying costs. Now, first, we're going to start off by just kind of giving us some of the information like the annual mortgage payments. If you are making a decision as to rent or to purchase, this might be something that you're kind of considering on different, depends on different purchase options. So we're going to kind of give it right here and then we'll make an amortization table to get a little bit more detail on that. So and then we've got the interest portion. So again, this one is another one that you kind of 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 paying will vary from the principal to interest. 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 touch on it, that's why it's yellow here, by the way, property taxes. We're going to say the 2100. 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 got the down payment and closing costs are going to be the 37181. 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 kind of things that kind of increase the growth in equity. One is your 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 principal and to hopefully the home is going up in value. And that's just due to the market. No matter which of those we're talking about, the difference between the loan amount and the value of the home is not something we have easy access to. However, at any given time, depending on the economic circumstances, unless we sold the home or we got a second like a refinance on it, which sometimes is easy to do. And sometimes it's not. It kind of depends on what the economy is doing. So insurance and then insurance and maintenance. And then we got the estimated annual appreciation. So we're going to think this is going to 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 going to give it right here. And then the S that's why it's yellow, by the way. And then the estimated annual appreciation. Again, that's that's us saying the value of the homes 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 homes never go down, you got it, you got to, you know, 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, this we're going to assume is our marginal rate, our highest tax break 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 costs and costs. And this is going to be like yearly rental costs. We might want to call it. I'm going to make this a header. I'm going to select three cells here going to 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 is so if that's, 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 9600. That's going to 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 any renters insurance with that we have to pick up. And then we've got the security, let's say this is the security deposit. So on this one, I'm going to call it the security deposit and interest lost. And what I'm going to 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 interest that I could have earned on it because I'm looking at basically the yearly costs. In theory, I would get the security deposit back at the end of my rental term. But obviously, you know, one, they probably going to take it anyways. But two, you know, I can't earn interest on it as long as it's being held by the rental company. And that's what I'm kind of losing. So that's the opportunity cost. So we're going to say, OK, security deposit 800. And 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 in an investment. And then we're trying to account for the fact that we're going to have to pay part of that earnings to, you know, the government. So after tax earnings, we're going to say are the 6% that's what we're coming up with. That's the idea of the after tax earnings. OK, let's make that a percent home tab number group percent to find it underlining it and font group underline it. And then that's going to be our security deposit interest lost calculation. So we lost that interest 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 in the subcategory. 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 underline line underneath known as the underline underline. Total rental costs. OK, so there we have it. So now we're going to say the sum in this outer column up. I'm just going to sum up the other column equals the SUM of the rental, the insurance and the security deposit interest lost. So we got a cost of on a yearly basis, we're going to say of the 9,948. So that's our yearly costs there. Let's compare that to our yearly kind of costs on the on the homestead side of things. My homestead. I don't know what a stead is, but it's not just a home. It's a homestead, which I assume is like the property around it and stuff. So I'm going to say, we're going to say, see, let's put a paint brushy here and make this a skinny. OK, so now we're going to go to the homestead calculation. And so this is going to 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 going to make this one a little bit wider. I don't have to go as wide as my head or 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. Por favor. 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 first. You're getting ahead of yourself. I'm going to select this one and two other cells. Let's make that a header. Home tab font group and then bucket make that black and white my header kind of style the head style. Then this is going to be the costs. The costs we're going to say will be let's put a colon there colon. And we'll say this is going to be the annual payments annual payments. I'm going to put that here in column J. And that's going to be the 10,700. Now that's we're going to say is our mortgage payments, which again, we'll do the amortization table so we can kind of see where that is and you might be pulling that from an amortization table. So we could set up an estimate for what we think the amortization table would be, which we would then draw this 10,700 to yearly payments from. And 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 got that. We got the property taxes. So we've got the property taxes that we're going to have to pay. So you got to 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 got the insurance and maintenance. So we got to take that into consideration when we make the purchase. It's not just the mortgage costs. We got the insurance and maintenance stuff that's going to be in play here. So we're going to make sure we pick 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? Which is kind of for the most part, at least. And when you're when you're home purchase, then of course, you know, things get old and you got to that could be, you know, significant. So what so and you can get into more depth in terms of how to how to estimate that amount. So then we're going to say this is going to be the after tax interest loss. Let's call this after tax interest lost on down payment payment. Now notice I'm not saying the down payment as basically a yearly cost here because I'm trying to think about what what the costs are on a year by year basis basically after the purchase was made. But what 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? I had to take my money and put it on the down payment. So what I'm losing on a year by year basis is the bit is, you know, the interest that I could earn on that down payment. So I'm going to I'm going to say that let's do that calculation. I'm going to say that we have this if we put this money down the 37181. I'm losing whatever I can earn on it, which we said was our after tax interest rate or our earnings rate, whatever we we want to could be dividends, it could be other types of earnings. That's going to be the 6%. Let's make that a percent. Go into the number group and percentifying it, which isn't a word, but we're going to make it one. We're going to make it a word. We percentified that. So so and then we got the after tax interest 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 and that'll be pulling out to the outer column. So now we should make this I'm going to 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 interest we're losing because we had to tie up our money in that down payment on the purchase. So I'm going to select these three. We're going to go to the home tab. We're going to go to the alignment and indent. And then I'm going to go to alignment and indent again. Let's make the cell a little bit wider again so that we see the full payment there. OK. And so then that's going to be our total cost. 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 the cost to buy yearly home cost. Maybe I should cost to buy or otherwise no one is like this yearly home cost cost to buy yearly home cost. That's going to be the bottom line. So I'm going to just sum it up on the outside. Sum it up on the outside 16681. So let's put some blue and borders around that if we could making it look nice font group border blue border blue. And then I'll put an underline here font group underline. We might even double underline that one even though that's we're not stopping right there. So these are the actual kind of costs that like these are the you know the key cash flow type of costs that that we would be thinking of. And this is kind of lost lost what you know what we what we couldn't the opportunity costs of putting the down payment in place. So then we can we also have the benefits with regards to the benefits of owning a home. So if for example we've got the equity that could be involved in the home and we could have tax benefits as involved with 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 the go to the paintbrush make another skinny another skinny over here. And so this are going to 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 J G to J G J 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 formatting which is the font group black and white boom boom. OK. So now there's going to be a growth in equity. So we're going to say growth in equity. Now the growth in equity we said was this amount. Now I'm going to I'm going to try to think about the growth in equity in two two different ways. The equity you'll recall is going to be the difference between the 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 we 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 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 my equity goes up. But I also need to think about my cash flow just to think make sure I got the cash flow to pay off the bills. And this doesn't really contribute to cash flow 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 at all. It just depends on the economic conditions and where there's two components to that difference and one you could think about the loan. So we can kind of assume we can kind of hope that the home doesn't go down at least stays where it's at and hopefully 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 will 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 a stressed 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 going to be ready. So you got to 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 the equity from the loan. So we'll talk more about that shortly. And so then we 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't we cannot guarantee that the home value is going to go up. We're hoping it goes up. But you know, there are times that it goes down and the fact that it goes up doesn't mean that it couldn't just drop, you know, all of a sudden. Right. And then so because we haven't realized the game and you realize the game by actually selling the home, you know, at that point in time. So in the case so we got the tax savings for interest. So we got tax savings for interest. So brackets this this is going to be because we get deduct possibly the mortgage interest portion of the mortgage payments. Now this is another one that you want to be very careful about because you know there's it's more complex than just saying I'm going to multiply the interest times your tax rate. Because if you were not itemizing before, meaning you were taking a standard deduction, then the this the 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. So 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 interest will go down as the loan goes down. So it's not like you're going to 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 so you can't really depend on it completely as well to be a deductible thing. So you know calculate it figure it in there. But you got to be careful about you know your savings. Don't just think that well I have to buy a home because the government wants me to because they give me a tax benefit for it. That's not the government's not your friend. Don't they 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. So we're going to say a home tab and we're going to go number and put the brackets. So there it is. Let's put an underlying here font group and then underline that. And then this is going to be the tax savings for interest interest interest. Okay let's put this in the in the in the outer column now. This is going to be equal to the 8000 times 28%. Also note this 28% is usually like 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 going to be taxed at meaning your highest tax bracket. That's what the that's what the change is going to be at at that highest tax bracket. But again do the 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 the stop just do the problem. So we're going to say alignment indent. Okay and this is going to be then tax savings on property taxes. So we 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 say property taxes are then that 2100. 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 on the state taxes, including it being capped at 10,000. So that includes property taxes and your state income taxes. So you might run into that cap as well. And again you got that same kind of thing from the standard to itemize deduction. 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 going to go to the home tab number group percent 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. 2100 times the tax savings of the of the 28%. So then we've got the tax savings of the 588. Okay, and then we're going to calculate the benefits. Benefits of home purchase purchase. Let's put that right here and then we'll sum that up equals the SUM summing it up. And so we've got the 6528 and let's put an underline here we're going to go font group underline. And then I'm going to put some brackets around this brackets and then we're going to do that making it blue. So so of these notice that these two you can't really rely on there's this isn't really cash flow even though you have a tax benefit in terms of your overall asset standpoint. These two if you got them correctly calculated are kind of like cash flow savings that you would have on it. So just be careful on that to you might want to kind of distinguish the items that are going to be cash flow benefits versus other assets like equity in the home benefits. Let's unhide the cells between B and K so BK right click on hide. And so then this is going to be this is going to be the cost less benefits of home purchase. So let's subtract these two out we're going to say this was the cost minus the benefits. So there's the 10153 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 cash flow items. Then you could say you know like more of the cash flow items are going to be equal to the 10153 minus the three seven. So so actually I would say 10,000 I'm going to add those back in this plus this right so then we've got the 138 53 and then again these two we're hoping the equity is going up but like this one in particular we can't depend on and even so it's not like it's going to it's not going to be a cash flow item 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 like 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 you know your amortization table which from which you will pull these items in your data set and then go forward with with like these types of calculations on the on the year by year items so we'll talk more about that next time.