 Personal finance practice problem using OneNote. Estimate affordable home purchase price. Get ready to get financially fit by practicing personal finance. You're not required to, but if you have access to OneNote and would like to follow along, we're in the icon left hand side in the practice problems tab in the 7050 estimated affordable home purchase price tab. Also take a look at the immersive reader tool or practice problems are in the text area too with the same name, same number, but with transcripts, transcripts that can be translated into multiple languages and either listened to or read in them. Information on the left hand side, we're gonna use that to build our blue tables on the right hand side. Our objective is in essence to take our monthly gross income and use that to then determine how much home we can purchase using our worksheets. How are we gonna go from the monthly gross income to how much home we can purchase? We're gonna first step, take the perspective of we know the monthly gross income looking from the perspective of the financial institution, the financer, typically the bank, in terms of how much they think that we can pay on a monthly basis for the financing based on our monthly gross income using typical metrics that a financial institution might use in general metrics, heuristics of the bank. Once we know that amounts, how much we can pay or how much the bank is willing to allow us to pay back on a monthly basis for finance, then we might be able to use that to determine how much loan they would be willing to give us. Once we know how much loan they would be willing to give us, we can use the down payment that is typically required and use that to determine how much home we might be able to purchase. So once again, we're going from our gross income and ultimately arriving at how much home we could purchase based on that metric, let's see how that could work or how we might go about that type of analysis. So we're going to start with the 3,500 for the monthly gross income. We've got the financial institution and again, we're looking at this from a financial institution perspective. You might be saying, hey, why don't I get my income statement and take a look at my financing from that perspective? You would want to do that from on your books to determine how much you think that you can afford for the monthly payments and so on. But from the financial institution's perspective, they might use some heuristics, they might use some standard rates that they will apply and these are often gonna be kind of rates that will be applicable for standard types of loans, such as say a 30 year fixed, you're probably gonna have more standard kind of rates that they might be using and these could be determined for multiple different things. They might be subject to the current environment that's in there, the current regulations and of course the financial institutions and possibly your current conditions. But to some degree, they could be somewhat standardized. So you want to take a look at the financial institutions at that point in time and think about what the rate that they are using. So you might hear a rate called a PITI, which is the principal interest taxes and insurance rate and that's them trying to determine, basically they might use that rate to help to determine how much monthly amount that you can spend towards. In this case, it says right here, of course the principal, the interest, that's gonna be the loan and then we have the taxes and insurance that is also could be kind of packed into that rate that they might be using. So then we've got the other debt that we have here, that's gonna be the $200. So we might have other debt that is in place that we're paying down as well, that we want to be considering in our calculation and that a financial institution might kind of pack in to this rate calculation up top. And then we've got the loan, it's gonna be a 25 year loan, we're gonna say at 9% and we've got the down payment percent is 20%. So when we purchase a home, we're gonna say that you gotta put 20% down, like with the rate here, like with this calculation up top, these things will be dependent on the current situation. So they might be somewhat standardized as of a point in time for standardized types of loans, like a 30 year fixed and for certain conditions that might be met for a certain individual. So you wanna determine where those items are at a point in time. And so oftentimes you gotta put 20% down as like a standard down payment. If that's to require down payment, then we can use that to determine the home purchase price. Monthly property taxes and insurance, we're gonna say is 270. Okay, so given that information, we're gonna take the monthly gross income, we can spend calculation. So this is going to be the monthly gross income, 3,500. We're gonna use our heuristic, our rate that we would be determining that we're assuming is kind of the rate that a financial institution would be using. That's the PITI, the principal interest, taxes and insurance. We're using 38%, that percent may change from depending on the economic circumstances. So look at your financial institutions and see what at this point in time, the applicable rate could be for you. And if we multiply that out, that's gonna be the 3,500 times the 0.38 and that's gonna be the 1,330. Now, note that that rate includes principal interest, principal and interest taxes and insurance. And sometimes they might adjust that rate to include things like the other debt that is included there. So that this number right here, they might be trying to pack in everything, not just the financing charges on the loan. And what we want to do is get down to the financing charges on the loan so that I can use that to determine how much loan we might be able to get. So what I'm gonna do then is I'm gonna take that amount and if the monthly property taxes and insurance are included in this monthly fee, because this number right here is used to try to think about whether or not we could afford the whole purchase process, not just the financing. But I wanna get down to just the financing. So if the 270 was the monthly property taxes and insurance, as we see here, I'm gonna pull that out of this number so I can get down into this number to just the financing number. We've got the other debt, so once again, this 1,330 may include or maybe increase to take into consideration other debt that you might have. It's just one nice quick calculation number that say a financial institution you could use is we don't wanna have other debt in this number because we wanna get down to the amount that is just related to the financing of the loan. So if I took this 1,330 minus the 270 minus the 200, we get down to the 860. So now that 860 we're assuming at this point is how much they're willing to give us basically just for the financing of the loan. Now I know that the rate, let's assume the rate is 9%, this is another thing that would be fairly standard as of a point in time if the credit was good and if you're talking about standard loans as opposed to loans that are gonna vary like adjustable rate loans and so on. If we know that, then we can get into, we could try to back into the amount that we're going to be financing. And we can do that a couple different ways if you're using Excel, one way you can do this is you've probably had practice with this payment calculation right here to calculate the payment. Because usually we would say, okay, I know what the loan balance is and I know what the rate is and I know the period so I'm gonna calculate the payment. So most people might have their first understanding of this payment. If you look at the little calculation down below and we will do this in Excel if you wanna practice it in Excel but just a quick recap, if you look at the payment calculation down below, really what we're trying to back into is the loan balance which is represented here by the present value. So you could say, okay, what if I do a calculation for the missing variable, which is the present value and that's one way you could get directly there. If you didn't know that, you can also use a tool which is called goal seek. So you might say, hey, look, I'm just gonna guess what the loan amount is, let's say 100,000 up top and then you might do your calculation here, your normal payment calculation that you know and then adjust your loan using trial and error or using a tool in Excel called goal seek to basically find the proper number. So that might look something like this, you'd have your normal payment calculation, the rate would be the rate which would be this 9%, that's a yearly rate, we want a monthly rate. So we divide it by 12 comma the number of periods, the number of periods would be 25, it's not a 30 year loan, we're saying it's a 25 year loan, that's years we want months, so multiply times 12 and then comma the present value, the thing we're looking for, we're gonna pull from this cell which we might start off just putting 100,000 in and then if we put that in there, we'll come up to a number that is not matching this 860 and then we can do trial and error, we can just adjust this number until we get to the 860 and there's a tool to do that automatically in Excel called goal seek, which is a good tool to recognize. Or of course we can just say, hey, look, that present value calculation, why don't I just solve for the present value? Is there a function in Excel that I could just do the present value which will basically get the loan amount? There is of course, and so we could do the present value calculation, which would be the present value of the rate, the 9% divided by 12 comma the number of periods, 25 years times 12 to get to the months and then comma and then we want the payment which is gonna be the 860 to determine the payment and that once again gets us to the 102 479. So you would assume that if they're saying, if we're thinking the financial institution is willing to apply 860 for us to apply to the loan payment, how much they think we could afford to pay on a monthly basis, you would think maybe they would be willing to finance us the 102 479 based on the 9% loan if we assume a 25 year loan, which it might be more standard to use a 30 year loan, but we'll keep the 25 year. So now we wanna think, okay, well, if I know that, if I know how much they're willing to finance us, how much home could we purchase? Because, and we could say, well, how much down payment do I have to put down if we assume the standard down payment classically is 20% and it could change because you, and you would think that it would be standard over time, but we've had time periods where they took the down payment basically to zero, which seems crazy because of government intervention and financial institutions doing weird things and different loan structures and whatnot. So you could see that kind of vary going up and down, but if you take the standard classic 20% down and you're gonna finance 102 479, that's how much loan you're gonna take out, you could take, all right, the percent financed would be one or 100% minus the 20%, that means we're gonna finance 80%. So we're gonna put down 20%, we're gonna finance 80%. So that would mean then that if I took this 102 479 divided by 0.8 or 80%, we would get the affordable home amount of the 128 99 about. Now that calculation might not look a little as standard, so you might wanna just check your number. I might say, okay, let me, does that work? If I bought the home, if I had a home of the 128 99, and then I said I wanted to put 20% down or I had to, I got the 128 099 times the 0.2, that's gonna give us our 25 620 down payment. So then I'm gonna take the amount of the 128 099, purchase price minus the 25 620, that would mean that we would be financing getting a loan for the 102 479 amount. So that in essence is, so of course you would have to be able to afford the down payment too, and then be able to get the loan, the financing of the 102 479 in order to possibly get the home of the 128. So that's the general idea. So sometimes it could be useful to try to think from a financial institution perspective and say, here's my income that I'll have to give them, most likely to verify, here's their heuristic, here's their rate that they use and what does that entail to determine how much they're willing or they think that we can afford. Again, we might agree or disagree with that. We could say, according to my income statement, I can afford more than that possibly, and we're gonna do that on our side, but we have to use the bank's calculations to try to think about how much they're willing to loan us. And so we might use whatever they're gonna use. I'm gonna refine it down to the amount that's applied just to the financing of the loan. And then based on that, we can then get into the amount they'd be willing to loan us given the standard loan structures of the time, which would include years, mostly 30 year loan oftentimes, if you use the most standard structure and the rates at the point in time. And then you can use that to try to think about, well, if there's a standard 20% down payment, then how much home could I purchase on it? Once you have that, then of course you can construct your amortization tables and you could build this all on an Excel worksheet that kind of ties together. You could get these amortization tables from the online resource, a table such as this. But again, I recommend doing this in Excel, if you could. And we do do a practice problem if you wanna check it out because that means that you can tie all this information to this data on the left, and then you can change this data and you can say, well, what if my income was changed or what if they changed this 38% or this nine, you could change any of the data, which will change much more easily. So the loan calculation, we got the 102 for 979, that's the amount that we're saying is the financed amount, not the home purchase price. And then if we were to try to calculate our amortization table, we would say, okay, now we've got the 102 for 79 times 9%, I think it was 0.09, that would be for a year divided by 12, so we're gonna be paying interest on the first payment of the 769. If I paid $800, 860 minus the 769, that means we're only getting the 91 decrease in the loan from that first payment, 102, 479 minus the 91, that's the 102, 388. If I go then to the second payment, you could see there's a change because of course, if I take the second payment, which is the 102, 388 times the 0.09 divided by 12, we got the 768 about. If I take the 860 minus the 768 about, now it's a 92 difference in the loan. And if I take the 102, 3102, 388 minus the 92, we get down to around the 102, 2, now you could see this difference is small here, but of course, as we go through the loan, you're gonna see this big difference and flip between the rate and the principal. And that's gonna be important if you gotta take into considerations more complex calculations with regards to taxes, for example, the end of the table will result in zero by the bottom of the table. And so if I go to the right then, we can summarize that on a year by year basis. And this is something that you can't often do as easily with the online tool. The online tool could get us here. I could say 102, 479, for example, 102, 479 in an online tool. It's a 25 year, nine. And I'm gonna say, give me the calculation. So there's the 860 amortization table and we can kind of check our amortization tables here or use this amortization table. And you could do that pretty quickly just as I threw it in there. But you can't really tie it out. Like I can't really tie it out to this whole other calculation as we did in Excel. And then if I wanna do this calculation, which I'll show you how to do in Excel, if you dattle, tie out the payments, the interest, the loan decrease, and then the loan balance, which is shown down here, 101, 336, for the end of year one, for example, on a year by year basis. That's quite helpful for us to try to determine what's gonna be the tax implications, what's gonna be the impact on the equity calculation, which is the difference between the loan amount and the value of the home, hoping that the home either stays constant at the minimum or goes up in value hopefully, but it may go down, we'll recognize that. And then the loan balance on a year by year basis down to the 25 years. You could do this with formulas. You can also do it with a pivot table. There's pros and cons. This is the same data in a pivot table format. So I highly recommend checking that out in Excel and we'll work these problems in Excel and work these two things out. And then you can use these tables to tie into more complex calculations, such as your taxes, and if you sold the home in so many years and so on, and you can also then go back to your source data if you were putting this in Excel, and if you changed your monthly income, or if you changed what the bank's number is here, if you changed your insurance or the down payment or the interest rate, if you think those things are variable, if you run different scenarios, then you can run those scenarios way easier if you set the whole thing up properly to tie into the source data.