 Personal finance practice problem using Excel. Estimated home price from monthly income 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 because we'll basically build this from a blank sheet. If you do have access, we got 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. We're going to use that to populate our tables on the right hand side. We're going to start off thinking from the standpoint of a financial institution such as a bank, taking our gross income, using the bank's heuristics, their percentages, their standard kind of techniques to think about how much loan the financial institution might be willing to give us. And we'll use that then to determine how much house we might be able to purchase, for example. We'll also take a look at our own financial data and think about putting together an income statement from it. So notice when we're thinking from the standpoint of a financial institution, they might have their own kind of heuristics, their own percentages that they might use. We would also want to do, of course, our income statement. If we were doing our own budgeting and look at the same kind of things, can we afford this type of thing from both of those standpoints? We will then put together our amortization tables and break that out on a year by year basis, both with formulas and with the use of a pivot table. So on the second tab, we've got our practice tab. We've got the pre-formatted tables on the right hand side. So if you use this tab, you can go through the practice problem with a little less formatting. If we go to the blank tab, that's where we're going to be. We're going to basically build this from scratch. Now there's a pretty big scratch on the left hand side. This is the scratch that we're going to start. That's a little scratch that we're going to build off of. So you've got to add this if you don't have that sheet. And I would start off by selecting the whole sheet. If you don't have any sheet, if you're just starting from blank, right-clicking on it, format the sales. I would make it currency, brackets, and then no dollar sign, no decimals. That's my starting point typically. And then start to enter this data. We'll go through the data right now. You could populate the data along with us if you would like to, if you're starting from a blank sheet. So we got the down payment. We're going to assume when we get the loan, it's going to be 10% down instead of like the standard 20% down. So that could change from period to period depending on the current economic conditions and financial conditions and rules and whatnot, government regulations and so on. So we got the loan. We're going to say 30 year loan. When we get to the loan item, the rate is going to be 6%. We're going to say property taxes and insurance per month. We're going to say is 600. We're going to put our balance sheet together. So we've got the checking, the savings, the emergency fund, and I just adjusted the spelling there, emergency fund. We got the IRA balance, the car that we're putting on the books. We're not really breaking out right here between current and long term. Just getting an idea. This is going to be our total assets, our liabilities. We got a car loan. So the net assets then are going to be the assets minus the liabilities. So that's our, you know, financial statement on the balance sheet side, which is where we stand on the income statement side. We've got our income one. And this is going to be, we're going to say that we have two W2 incomes, possibly a married couple or something like or two jobs that we have. We can measure the income from gross income. If you look at like your W2, for example, or your paycheck stub, you'll have the gross earnings. And then you've got the money that was taken out for withholdings, including things like income taxes. And that could be the net income that actually goes into your checking account. So really, when you think about the income you're getting from a W2 job, your actual income is the top line income because all the stuff they're taking out of it, including the taxes is really an expense to you, even though you're kind of forced to do at least the taxes, they're mandatory withholdings. So oftentimes from our, you know, financial statements, when we put our income statement together, we might just start with the net amount after taxes because that's the amount that hit our checking account from a cash basis standpoint. But in actuality, we should really have the gross amount and then take out the taxes for the net amount. So we got to kind of deal with that when we're doing our personal financial statements and putting our income statement together. And also when we're thinking about like giving our information to a financial institution, noting what our actual income is when we talk to them, like what do you mean? Do you mean gross or net? Oftentimes they're talking like the gross income. And we'll do that for the second income. We'll assume it's a W2 job as well. And then we've got our expenses, which we're going to list out and we're going to construct an income statement, utilities, food, gas, credit card, car loan, entertainment and so on. Okay, so let's start out and we're going to take a look at our affordable monthly mortgage payment. And we're going to try to do this from the perspective of the financial institution using certain heuristics, certain percentages that the financial institution is going to do. In other words, the financial institution might not actually base their full decision on kind of our financial statements, our balance sheet and our income statement. They might use some percentages based on our monthly income to determine how much they think we can afford based on that. So that's what we're going to start out with. So I'm going to go back up top and I'm going to say, okay, well, let's put a header up top. I'm going to make this sell a little larger. I'm going to make the header affordable monthly mortgage payment. And so I'm going to make this a little bit larger, maybe right there. I'm going to select three cells because I think I'm going to put some numbers on these two. So I'm going to go from here to here, make that a header format, go into the home tab up top back home to the home tab. And then we're going to go to the font group. We're going to make this black and white, black and white for the header. And then I'm going to start off with the gross monthly income, gross monthly income, not because it's like the grossest of the two numbers, like in terms of grossness, but because that's the top line, that's what they're usually asking for from the financial institution. So when you're looking at that paycheck stub, make sure you're picking the gross income. And also when you're looking to look at the W2 income, even if you're looking like you got line one and line three and I think line five are all income numbers, ones for federal income taxes, social security, and then Medicare, it's often the Medicare income. That's the highest income number because again, so you want to make sure when you, if you get benefits from your work, a 401k plan and that kind of stuff, make sure that you're picking the most appropriate number, which you're usually hoping is the highest number, your gross income before they took everything, anything out, including benefits and taxes, because that'll give you the most benefit from a loan standpoint because you're trying to look good. In this case, you're not paying taxes here. You're not trying to look bad. You're trying to look good so that the financial institution, you have the ability hopefully to get as much loan as you want to get. And notice that this calculation, you could think about it a little bit differently if you're thinking about it from the standpoint of the financial institution than the standpoint of your personal budgeting because the financial institution is going to build their heuristics, whatever thing, based on the law, based on their bank regulations and whatnot, and it might not make complete sense. And then we want to do our personal budgeting based on our actual numbers, hopefully our numbers being accurate numbers in terms of our actual income statement oftentimes. Okay, so I'm going to sum this up. Let's put an underline here. We're going to go to the font group, put an underline here. We're going to say this is going to be our gross yearly income. We're grossing up. We got two gross incomes. This is going to be a totally gross number because we've got two grosses that are summing up, summing up both of them up. Super gross, $119,000, and then that's going to be that's going to be on a monthly basis. So we're going to say, I'm sorry, that's on a yearly basis. So we're going to take it down to a monthly basis. So I'm going to say months and year is 12. And let's put an underline there. We'll put an underline under it. And so we're going to say this is going to be gross monthly income. I can't take that yearly number. It's too gross. It's too much. It's too much grossness. So we're going to divide it by 12. So this is going to be equal to the $119,000 yearly number divided by 12 to get to the $9,917. Let's do some indentation. I'm going to select these items. We're going to go to the Home tab, Alignment and Indent. And then we'll go on down here and go to the Alignment and Indent again. So now we're going to use the kind of heuristic from the financial institution. We're going to call it, we're going to say it's the rate used. I'll kind of define it out here as a guide line for principal. And then it's going to be interest, taxes, and insurance, often called the PITI. So this is kind of like a guideline, just type of percent from the financial institution. So you might hear this kind of as a common heuristic type of number that the financial institutions might use to be based off of your gross income to get an idea of how much you might be able to afford. Now again, that could be completely different from your particular financial situation given your income statement, which we'll take a look at as well. But you want to get an idea of what the financial institution is doing so that you can use that to think about, you know, put yourself in their shoes and think about how much loan you might, they might be willing to give based on that. So we're going to say, I'm going to say and other debt payments. So you want to talk to the financial institution and at the current time. And this is usually going to be somewhat uniform from financial institution to financial institution. If you're talking about a standardized loan, like a 30 year fixed, usually like 20% down, that's usually, you know, the standard starting point loan. If you get to more unusual types of loans, then of course these kind of heuristics are going to be, they're going to be changing more rapidly because you don't have as much standardization with them. So whatever your current situation is in time, the current economic situation and so on, you should be able to get a fairly good idea talking to financial institutions about, you know, how they come up to their general calculation based on the gross income to get to how much loan that might be willing to give you. So I'm going to say this is 0.38 is the percent we're going to use here. Again, talk to your financial institutions at any particular given time to think about what their current heuristics are. And you can kind of use the term PITI and go from there and see if you can get a feel for that. So we're going to say the number, we're going to make that 8%, 38%. And so I'm going to say then multiply this out. We're going to say, let's multiply this out. And this is going to be then, you know, I did some spell checking there, but I'm going to be the afford, this is going to be the affordable amount that could go towards a home. So this is towards a home for the amount, affordable amount that could go towards a home. So I said towards two words, two words a home. Okay, so let's do it. Now that we've got the spelling at least somewhat down, it kind of makes sense that it's two words, two words. I always just call it towards, towards, two words, two words. In any case, whatever, whatever. So I'm going to then go down here and say, we're going to say less. Now notice that this number right here might include other things. You notice that I said the PITI and so that's the principal, interest, taxes and insurance. So that's all the stuff that the bank's trying to use this kind of heuristic term to put all the stuff that might be related kind of to the home purchase into that one number to see how much could be afforded in it. So you want to know with this percentage what they are including in there. And you've got, again, the principal, interest, taxes and insurance just kind of in the name. They also might adjust this number to include other debt payments like credit card payments. So they increase the number if you have other debt payments, for example. And then they're going to say, okay, so that's going to be the number that we're going to use that we think you might be able to afford on a monthly basis to cover these types of things here. So now we're going to say, okay, but I want to get it down to the amount that's just applied to the loan balance, which means I want to remove from this number on a monthly basis the stuff for insurance and the other debts and the taxes so that I can get just to the amount that you're applying to the loan and then I'm going to use that amount to see how much it might be willing to loan us, right? So then we're going to say, okay, we're going to take out of this number. If that includes these other things, I'm going to take out the other debt. So I'm going to say other debt. We're going to take out of that number. So I'm going to say other debt, and I put that down here. And again, you might kind of think that this would be a general guide, which we had down here with the car loan payments. So I'm going to say car loan payments. I'm going to take that out and this is going to be equal to the 290, the 295. I'm going to remove that. Let's actually put that on the outside here and let's make it negative. Let's make it negative. We'll negative out the 295 and then we've got also the monthly property taxes. So what did I put the property taxes? So the estimated property taxes and insurance per month is going to be I'll say negative of the 600 there. And so that's going to give us our affordable monthly mortgage payment. And I'm going to sum this up equals the sum because it'll subtract out this and I'm going to sum these up but those are negative numbers so it'll subtract them out. And so you can get the general idea here, right? And so we want to talk to the financial institution and try to get to their calculation, their heuristic so that we can get to the amount that is going to be applied basically to the affordable monthly mortgage payment because then we can use that to try to determine how much loan we're going to get. And again, you could talk to the financial institution at any given point in time and from institution to institution with a standard loan kind of set up when you're talking about large institutions they'll typically have similar kind of heuristics that they will be using due to the fact that there's certain regulations that will be involved in calculating that as well as best practices within the industry and so on and just the standardization of the loans and so on. So let's go ahead and underline this. I'm going to put an underline here and I'm going to put some brackets around this and then we'll make this blue, blue and bordered. Okay, so now that we have that let's figure out what the affordable mortgage amount would be. So I'm going to go back over here and I'm going to say, all right, if I know that I could say, what's the affordable mortgage amount? Meaning how much loan can we get? How much loan can we get? That's how you should say it. Affordable mortgage amount. How much loan can I get? Okay, so in any case, we're going to calculate that based on this number. So usually we're calculating like the payment calculation, right? But this is the payment calculation. So normally you would say the payment calculation because I would have the loan amount, I would have the rate, and then I would just be calculating the payment calculation. So what I could do is look at the payment calculation and say, well, what am I looking for? What's the unknown here? It's actually the present value, the loan amount. That's what we usually put for the loan amount. So I'm just going to look for a function for the present value function to get to the loan amount. That's what we're going to do. So I'm going to go up top. I usually start with a negative present value instead of an equal and then brackets because that'll flip the sign to a positive, probably not the most proper way to do it because you should have put the negative inside, but the easiest, I think. And then the rate is going to be the 6% over there. That's going to be the percent for a year. So we're going to divide that by 12 because we want the monthly rate, comma, the number of periods is going to be 30, but that's in years and we want months. So times 12 and then comma the payment, then we got down here and it might be rounded, but it's the 2008, 73 and enter. So that means our loan then is going to be that 479, 248. Now you could double check that with your normal rate function and we'll also double check it with an actual amortization table later. But you could say, okay, does that make sense? Let me do the rate and do this one and see if I get back. I'm sorry, let me do my payment calculation, my normal calculation that I know and see if I get back there. So if I take the rate, this one, comma, or divided by 12, comma, the number of periods is going to be 30 times 12, comma, and then I take that present value, does that get me back here? And it does, right? So it looks like that's correct. So we'll double check it again with an amortization table. We'll do that next time. I'm going to make this black and white okay. So if we know that, what's going to be the purchase price because that's the loan. Let's make this blue, by the way, before you don't get ahead of yourself. Myself is so slow though. Just wait for yourself a second. Don't get ahead of yourself. So this is going to be the affordable home purchase price. The affordable home purchase price. So now we're going to calculate the afford. Now that we know that, because that's the loan amount, the amount that we could possibly get financing on, and that would be a large amount of financing. So that would be like a jumbo if that was the case typically. So it might be a little bit more restrictions if your financing is that high. But in the case, we're going to use that number. I'm going to then select these two. We're going to go into the home tab over here, font group. Let's make this black and white. And so this is going to be assuming I can get that much of a loan. Then let's say this is the affordable mortgage amount. Let's put that on the end. And I'm going to take a look at the rate of the cost that is not the down payment. So we're going to say the percent financed. It's a better way to say it. I'm going to say subcategory one minus the down payment rate or percent. So in other words, one minus the down payment rate, we're saying it's not going to be 20, but 10 this time, 10%. Just to switch things up, which might be a little less than standard. But again, it just depends on the whatever the current economic conditions you're in. Weird things happen in the housing market. People start thinking weird stuff after 10 years of weirdness. Weirdness becomes the norm until the weirdness reveals itself to be weird and then everything falls apart. And we start over again. So rate of cost that is so this is going to be the percent financed, percent financed. Okay. And then this is going to be one minus the 10. And let's make that is going to be 90% of course, because if we're going to put a down payment of the 10%, that means that we're going to finance the 90%. Let's put an underline here. Let's check the spelling check the spelling. Affordable affordable financed. Okay. Let's do some indentation home tab alignment and indent. And then we'll indent this again alignment and indent. And then this is going to be what we're calling the affordable home purchase price. And we're going to say this is going to be equal to that divided by the loan amount divided by the amount we're going to finance. And that comes out to the 532 498. Let's double check that number. Let's put a check figure. Let's put an underline. Let's put some blue borders around this thing, blue boards, hers. And then let's do a check, check, check, check. On this double check my numbers. And we're going to say this will be black and white. And this is going to be we're going to take that affordable home purchase price because this is how we normally do it. Right. We take that to start with, which is that number. And then we apply the down payment rate, the down payment rate. It's a down payment. I don't make payments unless they're down payments. And this is going to be a home number percent to find it. We're going to put an underline under that. And then this is going to be the down payment. Down payment, which is going to be equal to the amount times the 10%. And that'll give us the amount financed, which is going to be the home price minus the amount we financed. And there so we could double check. We double check that number. Check, check, check, check. Okay. So that looks good. So that we based all that kind of from the mindset of the financial institution. Now, of course, in practice, you would also want to compare that to your actual books. You might say, well, that doesn't make any sense compared to my actual financial statements, information. And it may not because, again, what drives the financial institutions is a bunch of stuff, including government bureaucracy and laws and whatnot that may not make a lot of sense, right? So you're going to, so then you also want to do your own budgeting to see if you can afford, you know, what is going on here. And then see if you could take any alternative kind of measures, if you can't get the financing, you think that you can afford in certain circumstances by doing other loans or negotiating in possibly less conventional, you know, loan structures or something. And in any case, let's do an income statement. Then I'm going to, I'm going to make a skinny by taking this skinny and I'm going to make another skinny over here, another skinny. And we're going to just call this a monthly income statement. So monthly income statement. Now this is what you would think that the financial institution, this is going to be kind of more on a cash flow basis, cash flow. This you would, you would think this would be kind of what the, what the financial institution would be basing it on, but they can't really trust individuals income statements as, you know, all the time and so on. That's why that's one reason they might use a kind of heuristic like this, just using a percent. So, but in any case, we're going to do it for ourselves because if this was done correctly, this would probably be the better way to go to see if we can afford stuff. Let's hide some cells. I'm going to be hiding from C to J CJ. I knew we see a CJ before someone named CJ. I'm not sure if those letters actually stood for something. I'm assuming they did. But we called their name CJ. Anyways, we're going to say this is going to be home tab font group. This is going to be black and white. And let's say we've got the income. Now I'm going to do this a couple different ways. Like if you're doing your income statement, you might like, if you were putting this into your like a QuickBooks or something, you probably just be using your, you might be using a cash basis system based on your bank account, which means you would, you would see the net check flowing in after withholdings of things like benefits and things like your taxes, which isn't as, you know, accurate because now you're kind of combining those things into, into one number. You're netting it out. And so that's, but we, that's, let's do it both ways. So I'm going to say, let's take this is going to be our income number here. And then I'm just going to copy that down. So we've got our two income numbers. So we've got the gross income and the net income, the gross and the net. Let's put the, let's put the gross column over here. And then we'll net it out to the, to the left. This is the gross side. The left side is the gross side. And the right side is the net side. So that's the gross. And then we'll do it as in a net construction over here. And we'll get to the same bottom line. And I'll show you the difference. So we're going to say the gross on the left of the second W two and the net on the left of the second W two. So this over here represents our yearly, our yearly income on a gross basis before we take the money out, which again, you would find that on, if you're paycheck stub, your gross income before the withholding some things like taxes and possibly benefits. If it's your W two income, you got like three income boxes, box one for federal income taxes, three for social security, and I think five for Medicare. It's actually the Medicare one, which I think is going to be the highest one closest to your gross income. But when stuff flows through to your actual software or if you're getting your information from the bank and you're just saying, my income is what got deposited into my bank account, then you're going to be using this number over here because it's going to be after they already took the money out. Now these are on yearly basis. So what I'm going to do is take each of these and divide it by 12. So I'm just going to divide them by 12. That's not 12. That's one. That doesn't do anything. Got to divide it by 12 to get it down to the monthly. So I'm going to take that and divide it by 12. Take that and divide it by 12. So there we have it and that's going to be our total income. So we'll say this is total income and summing this up. Sum it up. Now, again, it's a little deceiving because the this side over here on the net side of things is already taken out kind of our expenses which include income taxes. Okay, so let's do some indentation. I'm going to go to the home tab. Alignment indent. Let's indent again, double indent. And then I'm just going to pick up my expenses, expenses which I think I broke out on a monthly basis. And these will be the same for both columns. So I'm just going to pick up the expenditures and I'll just copy that down auto fill. And then I don't need these ones. That's too far. You've gone too far. It's okay. I could just delete them. I could just delete them. And then I'm going to indent. And then I'm going to pull the expenses in here for utilities. And then 120. And then I'll copy that down. And then we'll do that here too. I'll just say the same ones. Maybe there's the 120. The expenses are going to be the same on each column. And so there we have that. And I'm going to call this, I'm going to call this the net income or cash flow before taxes, which is a little deceiving because the taxes are already in there in our net calculation up top. So in other words, I'm going to say, I'm going to say, well, hold on a sec. I got ahead of myself. Don't get ahead of yourself. Let's call this total expenses. And then I'll indent that two times. Alignment indent two times. And sum up, sum it up there. And there we go. Okay. So then let's put an underline here, underline and put an underline there. And then this is going to be net income or cash flow before taxes, which is going to be that the total income minus the expenses. So there we have that income. And this should be the total income minus the expenses. So those are two different items here. And then I'm assuming that the difference between the gross and the net is taxes. It might be benefits to like a 401k plan and whatnot. But I'm assuming the difference is just taxes here. So then I'm going to put my taxes down below taxes down below, which is going to be the difference. And it's only going to be over here. When I when I took the net, the net, I'm going to say, well, what was the net consisting of the difference between this number, the gross and the net? I'm assuming is taxes. Right. So I'm going to put that on the on the outside here. And then so this is going to be my net income or cash flow after taxes, which is going to be equal to hold on. This should be on this side because this one didn't have the taxes and I'm going to subtract out the taxes. So minus the taxes. This one, the taxes were already included or already taken out. That's why I had the lower number up top. So this minus this. Now we're at the same number down below. So I'm going to put an underline and an underline here. Maybe put a zero there. OK, I know I did that kind of quickly, but the main thing I just want to point out is that you still want to do your own income statement, even if the bank isn't really using your income statement as the kind of heuristic that they're going to base their loan on. They might, you know, they could still ask for your income statement and also note that when you put your income statement together, if you're going to cash basis, which means you're going to be pulling in the net stuff that hit your bank account. But that's not really your gross income because they took out the withholdings. So you just want to be careful of that and make sure you're accounting for that. And you want to be able to account for that on your side because as long as you take it into consideration on the bottom line, then you could say, OK, this is how much income I have over and you can afford for your home and so on and so forth. And then when you talk to the financial institution, you want to know what the difference is between gross and net because you want to make sure that you give them the best number. Your point when you're talking to the bank oftentimes is not that the bank is helping you to actually budget. They're not there trying to help you out and say, hey, this is what you can actually afford. The ability to get as much financing as you need when you talk to the bank, right? So you're trying to give them the biggest number that can allow you the flexibility of the most financing. You're not trying to get budgetary advice from them and then you're trying to get a loan and then on your side, you're going to determine how much financing you think that you could afford on your side with basically your income statement would be the blue and and brackets. I'm going to unhide some cells over here. I'm going to unhide some cells. Wait a second. Let's unhide. Everything's unhidden. Did I delete the cells? I think I might have deleted them. And we did so using basically bank heuristics, bank percentages, bank estimates, we would of course also in practice want to do our own financial statements of balance showing us where we stand as of a point in time and an income statement showing performance, possibly an income statement broken out by month, which would be income monthly minus the monthly expenses because that would give us a good idea of how much more that we could afford. Note that the number that we come to with our monthly income statement might differ a lot from what the financial institution is coming to based on their heuristics. The financial institution might take more details in a more detailed kind of income statement or may not. It really depends on the circumstances at that point in time and the financial institution. But just note that you kind of have two different objectives with the financial institution. You're not really going to the financial institution to see if they could give you budgeting advice. You're not trying to say, hey, the financial institution is going to help me out with my month by month budgeting. No, you're going to the financial institution to get a loan. You're going to do the money in terms of loans that you can and then do your own budgeting to determine how much you can actually afford how much loan you would like to be taking out. So on the financial institution, of course, from their perspective, they're trying to do calculations that are going to determine how much that you could be paying back and they're going to do that in part based on their heuristics that they've used in the past. And it's also going to be influenced, of course, by the laws, the current law. You can have situations, we've seen situations where the calculations might be out of touch to what you think is actually a proper calculation with regards to what you can afford just given the economic environment. In other words, what they come to that they think they can afford might be quite different than what you come to that you can afford. Your goal with the financial institution is to try to look as good as possible, typically, to try to get as access to as much loan as you might and then do your own calculations in terms of how much you could afford with an actual personal income statement. So we'll dive more into the income statement that we might kind of put together on a monthly basis in the following presentation.