 Personal finance Excel practice problem estimate affordable home purchase price part number one Prepare 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 here. If you do have access There's three tabs down below an example tab a practice tab and a blank tab the example tab in essence being an answer key Let's take a look at it now We have the information on the left-hand side. We're going to use that to build our tables on the right-hand side We're going to start at our gross income and think from the perspective of a financial Institution using a rate to try to determine how much loan we might be able to get based in part or in principle part on Our monthly income now This is just kind of an estimate You would want to be looking at your financial institutions to see what conventions They might use to do a similar calculation if you're working out estimates for how much loan you might be able to get Once you've determined how much loan you might be able to get you can take into consideration the down payment You might have to make to think about what the price of the home will be so you can kind of start from there And go up to the price of the home Notice this is also a little bit different than you might think about what you can afford in terms of monthly payments Which you might calculate by actually doing a monthly income statement Because to make the bank might use some different Conventions than you might use when you're basically doing your monthly budgeting And when you're thinking about how much loan you might be able to get you want to use the conventions of the financial Institutions make sure to talk to the financial institutions to understand the conventions that they will use to require down payments and so on So that you can you could start to estimate this stuff But the bot the idea would be we start with the income and then we try to get to the amount of the home Price that we might be able to afford So the second tab is going to be the practice tab and that's going to have some pre-formatted cells on the right hand side Which will be a little bit less work for actually formatting the cells And so you can use that tab if you would like but we're going to go to the blank tab Which has just this information on the left hand side and will build everything else from scratch So this is just a scratch that we have And we're going to build everything from it if you have a blank worksheet You might want to format the whole worksheet I would start by formatting the whole worksheet and then adding this information so that you can have your data up top And then you could build it from there My starting format just so you can see it here I usually format my cells usually that are at currency brackets And then I remove the dollar sign And then if I need to add decimals, then I'll add decimals if I need to make some a percent Then I'll make it a percent. That's my starting point oftentimes All right, so then here we go. Let's build our table. We're going to be thinking about the affordable monthly mortgage payment So that's going to be our first header. Let's put that up on the header here. I'm going to say affordable monthly mortgage payment And so then I'm going to make this a little bit wider I don't have to make this cell As wide as the whole header because I'm going to have at least two columns probably where I calculate So I've got to make it at least like this wide So that these two columns will cover the end That's kind of my thought process on it And then I'll make this into a header format by selecting those three cells And I make my header format going to the home tab font group hit the bucket drop down I'll make it black and then the text white So that it'll be distinct from from the header from the rest of the stuff So now we're going to look at the monthly gross income we can spend so month So we're going to say monthly gross income we can spend I'm going to hit brackets that that means that I'm going to have a sub calculation I'm going to pull this to the inside for this calculation. I'm going to look first at our Our monthly gross income the 3,500 so I'm going to pick up the 3,500 And then we're going to say that we're going to multiply that times But sometimes the banks you might hear this term the PITI the principal interest tax and insurance Rate these these are rates you want to these are kind of calculations that you'd want to again talk to your financial institution About to see how they're going to be giving you the general calculation so you can run some estimates on this With the current the current regulations out there So I'm going to pull that over now. That's a really long thing So I could pull this over to the to the right. I'm not going to I'm going to type over it a little bit Otherwise, that's just way too long So I'm going to put my cursor here and I'm going to say this equals The 38 you can also abbreviate it. You might just put the PITI which is probably the better thing to do I might just put the PITI rate maybe Rate and then I don't have to make this so long. I can make that a little shorter And so I picked up that rate. So now I got to make it a percent so I could see it home tab Number group making it a percent and then I'm going to underline it font group and underline So then I'm going to put this to the outside. So I'm going to copy this So I said what I'm going to do with a colon I pulled it to the inside and now I'm going to remove This at the end of colon and I'm going to put this on the outside and then this is what we did So now we're doing what we said we were going to do So we're going to say this times this And that'll give us the monthly gross income So So now you can kind of see what they're doing Let's do some indentations before I discuss it here a little going to go I'm going to highlight these home tab alignment indent. I'm going to put my cursor here. Let's spell it right Let's spell it at least more writer. I'm spelling it writer than it was It's more writer than it was before I feel like So let's let's then indent this one again. So there we have it So you can see kind of the idea so the bank is going to say, okay This is your gross income and this is the rate that we're going to be able to apply to it The 38 percent and again you want to talk to your financial institution on that to think about What we think you can afford and that's going to include the principal interest taxes and insurance Now they might alter the rate for basically other debt that might be included in that rate And have kind of like a standard rate that would include Basically other other debt in it for example as well So you want to you know get an idea or feel for you know the kind of standard calculations that your financial institution might use And then you might need to adjust it then for things like the monthly property taxes and the insurance And why would you do that because this number up top is including if we're including The the property taxes insurance and other debt in that number Then what I want to do is pull out this stuff that's being being applied to it and just look at the amount that's applied to the loan So that I can then use the loan amount to back into how much how much loan I can pick up So let's see what that looks like. I'm going to say okay included in that number Is the is the the monthly property taxes and insurance. That's what they kind of included in this rate So I'm going to subtract that out so I can get to just the mortgage payment amount if they're applying to the mortgage payment So monthly property taxes and insurance And then I'm also going to take out other debts meaning this number also this percent might include You know other debt that they kind of packed into that percent number meaning this number is including other debt as well That you might have so again I want to pull that out if it is included pull out the other debt So that I can get then to the actual amount that's related to just the mortgage payment. So this would be the affordable monthly affordable monthly mortgage payment And so now we're just looking at the mortgage payment amount So this is just an estimate an approximation You want to look at your financial institution to get it to get a feel for what their calculation looks like what is included in this number So that you can basically get down to trim it down to we're getting down to the amount that's actually for the the mortgage payment Once we have the mortgage payment Then we can back into how much loan we might be able to get Once we know how much loan we might be able to get then we can consider the down payment to back into the actual home Purchase price. So we're working kind of up from the income level to the purchase price So I'm going to sum this up which will subtract it because these two are negative numbers the way I formatted it So summing this will subtract it out. So there's the 860. So this 860 I'm going to say that's the amount that we're assuming is applied to the actual mortgage That I think the bank would be willing to basically Give us a loan for so now I'm going to format this. I'm going to select this. Let's make it Let's make it font. Let's make it this blue. If you don't have that blue you go to more colors Standard and there's the blue. That's the excel is fun guy blue And then home tab font group. Let's make some borders around it So there we have that. Okay. So now I can I can I can back into to the loan amount Now there's a couple of ways you might do this. So so for example, you might say, hmm I don't I don't know how to really back into the loan amount, but I know the payment calculation So you so you might say that you might use that calculation So I'll show you how you can use a tool to do that if that if that other comes up The other way you want to look at it. You say, well this calculation This is the actual payment and if I know that calculation, it's equals pm t There's the payment calculation If I was to calculate this number, that's the end number I want to get from the payment calculation And the the thing that I'm trying to get to is this present value And that's how you can kind of determine. Well, I can say, well, is there a function for the pv function the present value? Yeah, that's the function that you could use But let's use this payment function first to just kind of see and I want to also use this goal seek To see to get an idea of of how this would work. So I'm going to say let's Estimate a loan amount first. I'm going to say well, what if the loan Was 100,000 and I'm going to try to use like a like a similar to an algebra Technique to figure to kind of back into what the what the loan amount should be. I'm going to I'm going to adjust this loan amount I'm going to start out with a loan. I'm just going to put here I'm just going to pretend it was for 100,000 And then I'm going to try to recalculate This number the 650 the payment and then I'm going to change this loan using a tool called goal seek To be the proper to be the proper tool to get to that payment. So in other words, I'm going to use the payment calculation payment calculation Using this number and the information on the left. I'll get something other than 860 and then I'll change this number using a tool and excel to kind of back into and force this number to be Uh, you know the the proper number. So we'll change this number to the proper number using kind of like algebra You know, you there's one unknown. We don't know and I'll just use the trial and error kind of method Of the algebra, right? I'm just going to keep on plugging in numbers until we get to the right number But I'll use excel to do that. And so it's a useful tool to be able to see that's called goal seek So I'm going to go over here I'm going to first use a payment calculation to try to get to this payment But it'll be different because I'm just guessing the loan amount here just made up a loan amount I'm going to say negative PMT Shift nine the rate is going to be then this rate over here at the nine percent And then that's a yearly rate whenever we have a rate. It's going to be a yearly rate So I've got to say I want to make it monthly dividing it by 12 And then comma the number of periods is going to be 25 For the years, but that's years now what months. So I'm going to take that 25 b6 times 12 And then comma the present value now That's the one we're looking for that we just guessed that we're going to then change. That's the 100 000 I'm going to say enter So there we have something. It's not quite right. So now I want to make this number be 860 by changing this number, which I could say, okay Well, what if it was 101 000 and so on and so forth or I can use a tool Called goal seek to do that. I don't need to be on any particular cell to use it So it's useful to know where this tool is. So I'm going to go up to the data up top And then I'm going to go to the forecast and the what if analysis What if Goal seek, let me tell you a what if scenario What if if I set this cell I want to set this cell right there to be and I have to hard code this cell I want to set that cell to be 860 I can't click on the 860 here. I got to type it in I want to make that 860 and I want to do that by changing By changing this cell so change this cell to whatever it needs to be to make that cell 860 that's what we're saying. Okay, okay And then it just does trial and error and then it kind of backs into our loan amount So that's one way that you can do it and I just want to show that goal seek way That's not the most optimal way because we'd like to use another formula to do it But I just want to show that goal seek because that could be useful A useful tool. So let's do it another way Another way we can do it is the call this the loan Or or mortgage mortgage Amount Is to say, okay. Well, if I use this formula what I'm looking for is that pv the present value That's the loan amount So I could just say well just reverse this and use the pv function and that should get me there So I'm going to say, okay I'm going to say negative so that instead of equals so I'll flip the sign That's the way at the easiest way to do it's probably not the most proper way to do it because you put Should put the negative inside the formula somewhere But I typically put it in throat because it's pretty easy to do And so I'm going to say negative present value brackets I'm going to pick up the rate Which is the nine percent. That's the yearly rate again. So I got to divide it by 12 And then comma the number of periods is 25 That's years and I need months. So I'm going to multiply that times 12 comma And then the payment amount we have up top was the 860 So I'm going to say there's the 860 that should get me to the present value, which is the loan amount So I'm going to say enter there. There it is. There's the loan amount again Let's make this let's make this fancy put some blue some borders So, okay, so now we've got the loan amount. So now if if I assume That the bank's going to require me to put 20 down note that this could change as well So you got to talk to the institution This would be like a classical kind of standard, but we've gone through cycles Where the bank, you know had no money down and then they have too much money down and then they goes So it just depends on the cycle you would think that you know It would even out we kind of learn our lessons over the over the years But the cycle still happen, right? So you're going to have the rates are going to go up and down And of course the amount of the down payment that a bank will will accept due to all the conditions on the market As well as regulatory conditions will go up and down. So whatever the down payment is and whatever number they're using for this Calculation up top. That's what you'll have to talk to the financial institution, but 20% fairly standard So what if it was 20% so then I can say, okay, if I got the loan amount Now I gotta I gotta increase it to the to the affordable purchase price So this is going to be the affordable home affordable home purchase So I'm going to so I'm going to make this a header by selecting a couple of cells because I'm going to use two cells for calculations I think Home tab font group. Let's make that black and white And then so we first calculated the loan or mortgage amount up top. I'm going to put that in the outer column So that's where we start and then I'm going to take the the percent financed. So the per per percent Financed in other words, I'm trying to think about How much of the home purchase am I going to finance? Well, I'm putting 20% down. I'm not financing that I'm paying cash for that The rest 80% is the amount I'm going to finance I'll do the calculations just so we could see it here sort of be 80% but I'm going to say one One I'll put that in the inner column and then the down payment down payment Well, let's do it this way. Let's make this equal to The down payment percent which is 20 percent. This will equal the 20 percent So one minus 20 percent or 100 percent minus 20 percent if you want to think about it that way Would be the 80 percent. Let's make this a percent. I won't make this a percent. You could you can make that a percent 100 percent minus 20 percent But we'll keep that at one and then I'll make this an underline font group and underline And then this is going to be the percent financed Which is going to be one minus the 20 Making that a percent by going to the number group percentifying it That's not a word people don't like sometimes they get angry when I don't use non words, but percentified I feel like it's a cool word. I'm pretty I'm I think I'm going to get it into like the new word Oxford new word dictionary the oad Because it should go there. So in any case, let's go ahead and indent this one By going to the home tab alignment indent They got curiouser and curiouser. I heard in there, which wasn't a word From from some book or something Any case I got affordable home affordable Bull home purchase So now we're going to divide this out. I'm going to take this amount divided by the 80 percent And so that's how much home that we can purchase So let's double check that just to give me a double check figure because like yeah, you sure on that one. Let's give it a check figure Check that out. Let's check that out one more time. So I'm going to make this the header. This is my check figure check check And so this is going to be equal to the home price Which is going to be this amount And then I got to put a down payment. This is how I normally calculate it of 20 percent 20 percent they're making me put down Make that a percent underline it And so that means that I'm going to have the down payment Of equal to 128 99 times the 20 percent the 25 6 20 so that means the amount amount Financed or the loan you might call it the loan the mortgage is going to be equal to The 128 99 minus the 25 6 20 So there it is So we kind of double check this to that number And this to this number so we can kind of see it that way So let's format this. I'm going to select this whole thing make it blue font group. Let's make it Let's make it blue and bordered Let's make this blue and bordered blue and bordered and you could see the idea here being That we're starting off with the income. We're trying to think about how much The bank might loan us based on that information by using the method that whatever financial institution is going to use Which we're which we're approximating here. You want to talk to the financial institution We're trying to get the amount that would be applicable just to the mortgage payments So that I can then use that amount to back into the amount of loan They might be willing to give us then once we have the amount of loan They might be willing to give us then we can use that and think about the down payment Which they're going to require us to give to back into the affordable purchase price How much home we can actually purchase So now that we have that down next time we'll we'll just basically build An amortization table and the pivot tables related to this just to keep on practicing that Because any time you do these kind of calculations, it's useful oftentimes to get into more depth To have the amortization tables and then that information possibly broken out by year Because then you're going to want to do your own budgeting Not just look at this 38 but maybe think about your own kind of personal income statement And and and you know what's going to be the tax implications and the equity going into the future and so on And I think it's useful to be able to build the amortization tables And summary tables per year to do that