 Personal finance practice problem using Excel. Buy or rent home decision problem 2 part number 1. Get ready to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you don't have access to the Excel worksheet, that's okay because we'll basically build this from scratch from a blank sheet. If you do have access, there's three tabs on down below. There's an example tab, 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'll use that to build our tables on the right hand side. We're going to be comparing and contrasting the yearly costs for renting versus purchasing. Some of the assumptions that we make over here will be based on in part the information that we might construct from an amortization table. So then we will dive into the construction of the amortization table and the creation of a table, breaking out on a year by year basis to look at this in a bit more depth as well. The second tab is going to be the practice tab, which will have some pre-formatted sales. If you would like to construct or work this problem with less formatting involved. The third tab, the blank tab is where we will be working here, where we will be adding the formatting as we go. So if you just have a blank tab and you don't have this stuff over here because we're going to build it from scratch. And this is the scratch on the left hand side that we're starting with. That's the one scratch. So if you don't have that, then you can just type this in there. If you're working on a blank sale, I would suggest doing this. You select the whole sheet and I'm going to right click on the sheet. I would format it first. I'm going to format the sales to something like currency, no brackets. I get rid of the dollar signs and then remove the decimals. That's my starting point. And then you might add this detail, which I know is a little bit tedious, but you can add this information on the left hand side. So that you can be mirroring what we're doing, adjusting the formatting as needed, such as percents here and possibly decimals there, for example. And then we'll be at the same point and we can move forward from here to the next step of populating our information. And this is also, of course, the kind of step that you would want to do if you were, you know, building your own worksheet to make these kind of decisions. You want your data on the left hand side or in some other area that you can then pull from so that you can make adjustments to your source data and then run different projections with it. So let's see what we have here. We got the home cost. So we're going to assume the home cost of the 300,000. We've got it's going to be a homeowners association. So we've got the homeowners association fees that we'll have to deal with at this point for that type of property that would be purchased. We've got the marginal tax rate is going to be the 25%. That's going to be our highest rate. We'll talk more about that when it is applicable. We've got the rent payment for a house. So if we were to rent as opposed to purchasing, we're going to say the payments are going to be the 1,500. We've got the renters security deposit deposit of 125 seems fairly low, but in any case, we're going to say the renters insurance is going to be $80. We got the savings account interest. We're going to say is 2%. The mortgage payment, then if we were to purchase, we're going to say is the 1,458.44. Now notice that we kind of just assumed that at this point in time, we could get into more detail on calculating that you might take this home value. For example, think about the down payment that you would put on it and then calculate basically the mortgage payment. For example, we'll talk more about doing that after we after we get through this kind of first part, but that's not where our focus is now. And then we've got the national average property tax rate. So we're going to use that to figure or think about what our property tax would be homeowners insurance payment. We've got the maintenance and repairs. So on the home purchase, we're going to try to take a percentage of the value of the home to try to calculate that. And then which is what a common way that you might try to estimate those because they can be significant. Of course, interest lost on down payment 950. Then we've got the mortgage interest paid in the first year and we're giving this number again. But this is also something that we would want to estimate possibly from the amortization table. So we'll talk more about that when we build the amortization table, estimated annual appreciation and the equity growth from paying down the loan. Meaning when we get into the equity calculations, which is the difference between the value of the home and the loan on the home. Those are going to be some estimates and we can also think about, you know, the decrease in the loan itself, which is going to be partially pulled from the amortization tables. The payments that we're making were applying the amount that's paying down the loan when we're thinking about the increase in the equity. So we'll talk more about that as we go. Let's first think about the rental costs. So if I was to be renting, what would be the rental costs? So notice that you can also kind of vary this type of calculation to try to think about if you were buying say rental property to think about how much, you know, you get for renting versus versus the cost of the maintenance of the of the home, for example, and so on. So you can apply it in that standpoint as well. So we're going to say the rental costs. So rental costs. Let's make this a header selecting these three cells up top going to the home tab. I usually make the headers on the font group. I'm going to make it black and white black and white. And then we're going to say the rent payment for the house. So the rent payments for the home. We're saying pretty straightforward calculation. If we were renting, if the rent was 1500, I'm going to say on a month by month basis. Let's do it this way. Actually, let's say rent payments on the house. I'm going to say rent payments for a house colon and then do it this way. I'm going to say these are going to be monthly payments monthly payments on the month by month. We got the 1500 and then on the yearly then the number of months months month in year is going to be 12. And I'll underline that with an underline. And then I'm going to say this is going to be I'll just copy this. Then that's going to be my sub calculation and I'll get rid of the colon. And then we're going to do some indentation alignment indent. I'll double indent there double indent. And then we'll multiply this out. This is going to be the 1500 times the 12. So there's going to be the amount that we're paying for rent on a yearly basis. So then we've got the renters insurance the renters insurance they said was $80 a year. So I'm going to say, okay, I got to pay the insurance of $80. That's not too bad. And then we've got the interest lost on the security deposit interest lost on security deposit. So again, the security deposit is like a one time thing and we should get the money back. What we're looking at now is the loss in the amount that we could use that money to be investing in. And so that's the loss cash flow related to the security deposit. So I'm going to say, okay, what was the security deposit is 125. That's not bad. And so we're going to say there is that and then we're going to say that we could have earned 2% on it. So if we if we were to put it in a savings account, that's a fairly low amount because you might assume you put it somewhere else. For example, and get a higher rate for like stocks and bonds or something like that. But I'm going to assume if I had that in my savings account, which is probably where that 125 would be. Then I get a 2% return and I'm losing that big deal number percent underline it thought group and underline it. And that's going to be the interest lost interest lost. Do I even need to put that in? It seems insignificant. Well, it could be significant depending on your circumstances. So I'm going to select these. We're going to go to the home tab alignment and dent and then indent this again. Calculate this on the outer column. This is the 125 times the 2%. So there's the whopping $3 $3. And so then I'm going to say that's going to be the rental costs. So rental costs. Let's sum it up in the outer column equals the SUM function and sum it up. So there's the rental costs. We're going to say it's the 1883. Let's do a blue border thing here. Do our blue borders home tab font group making this blue. If you don't have that blue, by the way, it's right here in the more colors standard area. That's blue. Why do you use that blue? Because that's what the Excel is fun guy used home tab font group border eyes it to. So let's put an underline over here font group and underline the Excel is fun guys always right. And so he's got it's that color must be the color you have to use. So then we're going to I'm going to make a skinny column over here. I'm going to make a skinny by putting my cursor on C home tab clipboard format paint and make a skinny G. And then I'm going to hide the rental stuff so we can focus in on the home purchase stuff. So I'm going to select those and hide them hide them so I don't have to see them mess me up high. Get out of here so you don't bother. Distracting me. So this is going to be the buying costs. Let's say buying or home. Let's say home purchase purchase yearly costs or something like that because we're looking at the yearly costs here and comparing them to the yearly costs on the on the rental. That's what we're doing. That's the exercise. So I'm going to select these three. Let's make this a header home tab font group and underline underline make it black. I mean, and then make it white. That's what I'm trying to do. So then we're going to say the annual mortgage payments. Let's calculate the annual mortgage payments both the interest and principal, which we would get. Of course, we could take this number and figure out the mortgage payment using a payment calculation, which we'll do later. But we're giving them this time. So I'm going to start with the monthly mortgage payments, which is usually what we know first. That's going to be this amount down here. It's yellow because we're going to we're going to actually dive into the amortization tables later to get more detail on that number. And so this is on how you get that number. This is going to be the one four eight five in practice. You might actually first calculate the amortization table as a way to get this number and then start building the rest of your worksheet on it. But we'll focus in on that more in the second component. So months in the year 12. So there's going to be months in a year. Let me say January, February, March, April, and then that's over my 10 finger 12 12 months in a year. We're going to say home tab font group underline. And then this is going to be the annual going to copy this on down. Delete the colon annual payments. Then let's do some indentation home tab alignment indent and let's indent this one more time. Uno vase moss and then in J five equals this times that there's the yearly payments that we're talking about property taxes property taxes. So property taxes. We're going to say then that we're going to estimate the property taxes is is 1.5% for is that what I said no, no, no, the property taxes are the 1.2% there that's what I'm talking about. Okay, so that's going to be so if the home value then if I know the home value is 300,000 I could say okay what are the property taxes in my area I'm got the 300,000 home cost. I'm going to estimate the property taxes at the 1.2 you can get more detailed on that. I'm going to make this wider on that you might have you know because it might be different for the land versus the what versus the building and so on and so forth but that's going to be dependent upon your area that you are in you can get some estimates on that area to get the normal percent and then that will allow you to make adjustments to the home value and take the relative amount of property taxes hopefully giving you an accurate estimate that you can flux with different home prices for example in your worksheet. So I'm going to go to the number I'm going to make that a percent of five I'm going to add some decimals and just need one let's not get carried away. Don't carry it away and so then we're going to say this is the property taxes but I'm going to get rid of the colon and then we'll do some indentation. Home tab alignment indent double indent here pulling this into the outer column doing some multiplying which is the 300,000 times the 1.2 percentages. There's the 3,600 so now we've got the homeowners insurance. So let's calculate that one home owner owners insurance colon and we said that was 75 on a monthly basis 75 a month. So okay and I'm going to make this one larger to this will be dependent on the circumstances whether you have the homeowners insurance requirement say 75 and we're going to say months in a year again equals the months in a year. I'm just going to say equals that one up top which is 12 pretty sure if I'm not mistaken that one stays pretty constant that doesn't change much and then I'll copy the homeowners insurance put it down here. Multiply that out into the outer so 75 times 12 let's do some and then take let's get rid of the colon here. That doesn't make then we'll do some indentation home tab alignment indent and then double indent double indent. So then we got the interest lost so I calculate the interest loss which I just gave us the number here. So that would be mean the down payment that we made we're not we're trying to look at the average like the yearly costs after the home purchase the down payment then we're trying to think about well if I put the money on the down payment into the home. I'm losing in the future the earnings that I would be getting on a yearly basis from the amount we put in now again you could calculate this a bit more in depth you might first think about the home price and then think you got 20% down for example. And then calculate your after tax earnings and then that would be the amount that you're losing due to putting on a yearly basis due to putting the the down payment down because if you had the money you'd be earning money on it. So we're just going to give it this time at just that that 950 you could get into a bit more detail we might well we'll leave that as it is for now it's a general idea. Okay so then so that is that and let's do the maintenance actually before I do this one I'm going to delete this one. Let's before I do that one let's add maintenance and repairs that's important. Let's do that one first because that one you've got the home and we're calculating that and saying that we're going to estimate that 1.5% of the home cost is the maintenance repairs. Once again by my making this a percent of the home purchase price that allows us to change the purchase price in our worksheet and hopefully come up to a relative maintenance and repairs that is still reasonable based on the purchase price. So let's say we got the home cost and say that was that was the 300,000 the 300 and then we've got the percentage which is going to be the 1.5 that will that will be you know something that you have to estimate so you're going to get your best estimate on that. A number group we're going to percentify that let's add a decimal and let's go to the font and underline it and pull this on the outer column just going to copy and paste get rid of the colon do some indentation alignment indent and then double indent pull this out to the outer column and this is going to be the 300,000 times the 1.5% then let's add finally the interest lost on the down payment which we talked about already so I won't discuss it again because we talked about it already we talked about that font group underline. And then let's say this is going to be the total which is going to be the home purchase yearly costs we're going to say costs and then I'll sum this up outer column the equals s you to the M sum and that's 27451 now we're also going to have benefits from the purchase of the home that I'm going to put in a separate area. So I'm going to I'm going to select these items and let's make that border blue blue borders make it blue and then put some borders around it to protect it. Because once it's so nice people are going to want to take it. So I'm going to then select this one we're going to go then to the home group we're going to format painter it and put that over here to the skinny K make a skinny K. And then we'll put our cursor on G I'm going to go on over to J G to J and right click and hide those items so we can build our next table without getting distracted by all these other stuff that we're trying to distract us. So this is going to be the benefits of home ownership yearly we're going to say is the yearly benefit kind of stuff calculation. Okay so I'll make this a little bit wider. Let's go from here to and let's make that our headers home tab font group black and white on the header. And then we got the equity. So first we got the growth and the equity that we're thinking about. So we got the equity down here. And so this is going to be equal to the growth from from paying down the loan. So remember when you think about the equity that represents the difference in the home value to the loan value. And if we think about our net value or our net assets assets minus liabilities then you know the bigger that difference is the better we have we got bigger assets and lesser liabilities so that's going to be good for us but remember that one we don't we can actually we haven't actually realized the equity the growth and the equity because we haven't sold the home and to it's it's that equity is not something that is liquid we can't we can't spend it so keep those two things in mind because and there's two kind of components to the equity one is we could think about well if I assume we purchased a home for 300,000 and at least it doesn't go down in value at least stay steady and hopefully goes up but if it stays steady then I'm going to increase my equity by the portion of the loan payments that are going to that are going to the principal portion paying down the loan because as I pay down the loan then my equity should go up if the home value doesn't go down in value so that's kind of the more conservative side of equity calculation that would hopefully be increasing but again it could be possible that the house goes down in value and in the other side of equity we can think about hopefully equity increases because the value of the home goes up from 300,000 since the point in time that we purchased it so those two things are kind of components of the equity you can kind of break them out or think about them separately but just realize that the home value could go down from the 300,000 it's possible hopefully in the long run if you're holding it over 30 years and it would clearly you would think go up over the 30 year time frame but you know just got to keep that in mind and it's not a cash flow thing okay so and we'll also talk about how to get that equity amount from the amortization tables because the yearly decrease in the principal will change from year to year so it's not really a set number okay we'll talk more about that later and then we've got the tax savings from the mortgage interest so and we also let's also do the estimated annual appreciation up top, estimated annual appreciation let's put that up top this is the other side of the equity so there's two things happening with the equity the difference between the home and the home value and the loan one you pay down the loan and two hopefully the value of the home goes up but again got to be careful on those it's not liquid and it's not guaranteed that the value of the home goes up okay it could go down even okay so tax savings from mortgage interest interest so now we're getting into the to the tax benefits so the mortgage interest we're going to say the mortgage interest was what do we say the mortgage interest what did I put that the mortgage interest was I'm going to say equal to this one down here I said the 12,000 now that number notice we specifically kind of put here that that's for the first year and so that's that's kind of a deceiving calculation because I'd like to know what this is on a year by year basis right I'd like to know what what I'm going to do not just for one year since this is a long term investment and so so this number is a little bit tricky because it will it will it will change from year to year also note that again the mortgage interest deduction is something that's more complicated than simply taking your tax rate and multiplying it for most people because this home purchase is the thing that purchase people puts people over from standard deduction to itemized deductions and so that gap that difference between standard to itemize is is not really a benefit to you it's it's everything over and above the standard deduction so you really have to do kind of tax software to see what the actual benefit is run an actual projection and then you want to think about what's going to happen with that projection in multiple years in the future when your interest portion of your loan payment goes down the tax benefit is going to go down so it's so you so this is an estimate of your tax benefit but you really want to be more careful with with this calculation and you can do that with tax software basically but we'll make the estimate here we're going to see the marginal tax rate that means it's your highest tax rate because if this was going to be something that's deductible or have an impact on your income you're not you have a progressive tax system which means that you're going to be taxed at multiple rates but your next decision is going to be taxed at your highest rate. So if you were to adjust your income from this point you're talking about a tax change at your highest rate which is what's called the marginal rate. Okay 25% do a tax projection bottom line don't just you know you want to get more detailed than this but we're going to practice with this one. Okay so home tap underline this is going to be the savings here. We're going to get rid of the this let's do an indentation alignment indent double indent over here double indent and then multiply this out so we got the 12,000 times the 25% tax savings then of the 3,000 is what we're saying then we've got tax savings from property taxes. The other big item that is going to be most likely deductible if you have a home purchase another item that puts most people over from standard to itemize deduction another item related of course to the home purchase. So also note that the laws could change from year to year there was a fairly significant change increase in the standard deduction a couple years ago. So although this is a fairly would be if you be hard pressed to think of removing completely these deductions they put a severe limitation, at least if you have an upper income level item on the home taxes for the state taxes. So they could change right with the political winds blowing either way here so so you never know what's going to happen in any case so they got the property taxes, the property taxes. Where are those property taxes I have them here we said the property taxes. I see we said we said they were 1.5% last time so let's just recalculate it. I'm going to take the 300,000 times the the we said the property tax was 2.1 and that's going to give us the 3,600. So and I'm going to say this is going to be the property taxes property taxes and then we've got the marginal tax rate again, which was the 25% we're going to say that would be dependent on our income levels and so on. Let's go to the underline up top let's make that a percentifying that so and then let's copy this on down and get rid of the colon. Do some indentation alignment indent and double indent pulling this to the outside that's going to be the 3,600 times the 25% for the 900. And then we'll sum that up that's going to be then the benefits of home ownership yearly. I misspelled it probably but that's okay I'll fix it with a spell check. Then we'll sum it up on the outside summing it up and put in an underline there. Okay, so now let's make that blue and borders let's make it blue font group and border blue border blue. Let's do some unhiding from B to K putting my cursor on column B selecting on over to column K right click so we unhide those cells because they're they're not going to distract us anymore we need them now now they're now we need those cells. So now we're going to say this is going to be this is going to be the home purchase costs less less benefits and that's going to be equal to let's say the 27451-116 which now we can kind of compare that to the rental items over here's it kind of compare those to the rental costs on the decision making process I'm going to then select these items I'm going to put brackets around it and we're going to borderize it. Now also just realize that mainly these are going to like be cash flow kind of related items and these items could be cash flow related items these two items are dependent in part on the market on the value of the home at least not going down and possibly going up so and these two components are also not liquid even if our assets go up we can't so you might want to like point that out you might want to say this is the equities equity items is the sum of these items. And say this is this is the net you know cash flow or short term cash flow items something like that this is going to be the 15851 plus the 7700 and then I'll put some brackets around that borderizing that will put an underline here. So in other words you want to do your comparison in terms of your net asset value assets minus liabilities but you also want to make sure you're taking into consideration your cash flow to make sure that you have sufficient cash flow to deal with anything that needs to be happening and only basically dipping into the equity in essence that you're hoping will be going up when necessary so you don't want to be forced to dip into the equity unless there's an emergency or something like that or. Or something you know like that you want to be able to plan for the equity so you want to make sure that you got the cash flow in place OK so we've got these items here next time we'll go into this and we'll get into a bit more detail and actually calculate the amortization tables. This is something that you might often do first to try to get to one the home value number so you can kind of change this home value number to adjust the other factors. And then calculate your mortgage payments so that you can change your mortgage payments in alignment with different home value prices which will will then change everything else in the worksheet considering we're using percentages and so on. And the mortgage interest for the first year would typically be calculated from the amortization tables and the estimated annual depreciation or the difference or the pain down of a loan which could help you with your calculation on your equity calculation would also be gotten from in some way the amortization table. So you might actually do that component kind of first and draw these some of these yellow numbers from the amortization table will talk about how that might work in future presentation.