 Personal Finance Excel Practice Problem. Saving for down payment on a home. Prepare to get financially fit 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 work this from a blank sheet. If you do have access, there's three tabs down below. There's an example tab, a practice tab, and a blank tab. The example tab in essence being an answer key. Let's take a look at it now. We've got the information on the left-hand side. We're going to be saying that we're saving up for the down payment on a home. Our starting point, which we can vary and alter once we have our worksheet set up, will be that we're going to save $7,500 for six years at a rate of the 6%. We're then going to see how much down payment we would have after the six-year time period. We'll calculate that a few different ways. Then once we have that, we can think about how much we might have to finance considering that we need to put 20% down and assuming we can finance basically the rest. Once we have that, we'll then construct our amortization tables and our yearly basically summary of that amortization table. After that's all set up, then we could alter some of our data on the left-hand side and run different scenarios with it. That being one of the big benefits of doing this information or working these kind of things in Excel. The second tab, the Practice tab, is going to be a tab that has some pre-formatted worksheets. If you would like to use it, then you don't have to do as much formatting. The third tab is going to be the Blank tab, which we're just basically going to add the formatting working in essence from a blank sheet just from this scratch, working from that scratch, working from scratch on the left. You can add that scratch. If you don't have this worksheet, I would suggest selecting the whole thing right-clicking, formatting the sales to currency, brackets, no dollar sign and remove the decimals, that's my starting point. And then whenever I need to vary from that, I'm going to vary from that. I also make it bold by the way, but you don't really need to. I think it's easier to see on the presentations that way. And then you could just add this information making that a percent and we're good to go, ready to start. Okay, so we're going to say that we're going to put 7,500 down each year to save for a down payment on the home. We've got these six years and the rate is going to be 6%. So we can do the easiest starting point is just a future value calculation. And this is also the most flexible because it allows us to change the years more easily, but we'll double check it a few different ways practicing our future value calculation. So we're going to say future value, and let's just call it FV. This is an annuity formula. I'm going to do it by saying negative instead of equals, which is probably not the most proper way to do it. But the fastest, I believe, future value, we're going to pick up the rate. This is per year. That's we're going to keep it per year. We're not going down to months. So it's going to be easy as something that's really easy to do like counting to three. That's how easy it is. And then we're going to say that the next one is going to be the number of periods, which is 6. And so that's in years two. So then comma and then it's an annuity. So we're going to be picking up the payments and not the present value because we're going to put that 7,600 in each year for six years. So we're going to say enter. And there it is. Let's make that blue and bordered. That is an estimate. We'll just probably pennies in there. Well, hold on a second. I don't want to make it white right there. That's not what I want to do. There are pennies, but we're going to round it right there. Now we're going to double check that because I'd like to see. Let's make it borders. That's what I wanted to do. That's the other thing, blue and borders. Let's double check it with an actual annuity calculation so I can envision what is actually happening. I want to see my money grow. So I'm going to go to the home tab. Let's paint brushy this skinny and make this one a skinny. And to see how does this growing money thing happen? We'll spill the table on it. Periods and investment and then income. These are my headers of the table, by the way. Balance. So I'm going to, I'm going to make this a little bit larger because I can't see the words, all the words that I typed. I want to see those words so it tells me what's in below them. That's what tells me what's under them. So I'm going to then go to the home tab. We're going to go to the font group. Let's make this a black and white up top alignment and center. And then we're going to have six periods. I'm just going to say one, two, three, four, five, six. Now you could use an auto fill to do that, but I just kind of like typing them in if there's only six periods because it's fun. Muy divertido. We're going to select those home tab. We're going to go alignment and center it. We can make this one a little bit skinnerized it. Let's skinnerize it because it's, we don't need that much space for numbers. And then we're going to put 7,500 in per period. So I'm going to say this is equal in the 7,500. We're going to put in each year, I'm going to say F4 on the keyboard, putting a dollar sign before the B and four, making it absolute. In other words, you only need a mixed reference, but an absolute works because I want to copy it on down. I'm going to do that by just double clicking the fill button handle button thing. And it just copies it down. Boom. And then we're not going to have any income in year one because you got to be careful on when you're putting the 7,500 and how the annuity table works. So you put it in like at the beginning or end of the year. So here we think about it as no income happening in year one. That's how the annuity normal annuity formula works. And then in year two, that's when we're going to have the income that's going to be generated. So just be aware of the annuity calculation beginning periods and how that's working. That's one reason it's nice to double check your annuity future value calculation with an actual table. So we're going to say this is going to be equal to the 7,500 times the 6%. And that's how much we're going to earn in the next year. We're going to say this is going to be equal to the prior balance, the 7,500 plus the sum of the 7,500 we're going to put in at the end of year two, we're assuming, and the earnings that we had during year two of the 450 close up the brackets and enter. Then we'll do it again. We could copy it down, but I'm going to do it a couple of times so we can see how this thing works. 15,450 times the 6% tab. Now this equals the prior balance of the 15,450 plus the sum of the 7,500 we're going to put in at the end of year three and the 927 that we earned during the year. Enter and then it closes up the brackets formula. Let's do it a couple more times. This is this number times the 6%. And now we have equals the prior balance of the 23,877 plus the sum of the 7,500 we're going to put in at the end of the year. That's how the annuity formula is thinking. And then the fourth 1,433 that we earned. Notice you might be thinking, hey, I'm going to put the 7,500 in during the year. It's an approximation. This is kind of that we have to kind of think about it that way if you're using a normal annuity or you could try to break it out on a monthly basis and what if you want to get more precise and whatnot, but it is an estimate in any case. Let's stop this from here and let's try to copy it down because this is getting tedious. Let's delete this. You're going to delete. I'm going to delete it. Are you sure? Yeah. I can do it again. So if I try to copy, can I just copy this one down? If I copy that down, this is a problem. That's a problem. So let's delete that. I think I need an absolute reference or mixed reference. You need something if it's in the data set outside of your table that you're working on, that's when you really need to generally absolutize it. That's B5 or make it a mixed reference. I'm going to say F4 in the keyboard making an absolute dollar sign before the B and the 5. You only need a mixed reference, but an absolute will work. I'm going to select these two cells. And now I should just be able to double click the fill button. And there it goes, summing it on down. So we're ending at the 52, 315. We've kind of double checked that number right there. So we see how it's calculated in a bit more visual way. So let's go ahead and bracketize this one. And just to see it another way, you can also break this down to an annuity of one, a series of annuity one calculations, which is another way to just kind of understand these present value calculations. So I'm going to put my cursor on the F column. And I'm going to say the paint brush. And let's just do it another way, which is kind of an overkill. It's already dead and you keep on killing it again. You can't kill it again. You overkill it. This is over killing, but I want to make sure that this we've got this down cold. This needs to be down cold. We've got to overkill it until it's cold. Okay. I don't know what I'm talking about. Sorry. But anyways, we're going to do this another way. We're going to select these items. We're going to go to the home tab alignment and center. And then we're going to go to the, to the bucket, make it black and white. Let's make the investment column a little bit larger. And let's do the periods again, which are one, two, three, four, five, six. And this will be the total down below. I'll just put the 7,000 five. Let's do it the same way. I'll just say equals this number. And that way I don't need to absolutize it. I'll just copy that one down because it'll copy the same relative number like that. And then we'll just take, we'll just do the future value for, for whatever timeframe that we are in. So in other words, this 7,500, if we put it in at the beginning, we'll be in there for, we're going to say the next five years to year two through six. This one will be in there for the next three through six. So let's do, let's do that kind of calculation using the present value of one to see how much we'll have at the end of each, of each time frame. So we will do that thusly. Hopefully that, I like saying thusly. It makes you sound, makes you sound smart when you say thus, thusly. Any case equals the future value. Let's make it a negative future value brackets. And then we're going to pick up the rate, which is all the way on the left now. It's going to be that 6%. I'm going to say F4 on the keyboard to make it absolute. You only need a mixed reference, but an absolute one works comma then the number of periods. This time I'm going to do it one by one. And so what I'd like to do is this one's going to have five periods that we're going to put this singular investment in that is going to have time to grow over. So I'm going to calculate that this way because I would like to be able to, instead of just typing five, in other words, I want it to be able to copy it down. So I want to pick up this number six and then minus this number one. And so that'll come up with five, which are five periods. And when I copy it down, I would like it to keep that number six and then subtract two, which would be four, six minus three, three and so on and so forth. That means this last one, that six, needs to be an absolute reference. That's in cell L7. So I'm going to say F4, it could be a mixed reference, but an absolute one works. And then comma, we're not going to use the payment because this will not be an annuity this time, but we're just going to use a series of payments of one. So another comma and there is the present value, which is going to be the 7,500 and enter. Now we could copy that down. And now I've got the series of payments. This one's going to grow after five, until we get to year six by the 10,037. This one will grow from 7,500 to 9469. This one will grow to 8,933. And as we get closer to the endpoint, that investment that we put in is not going to grow as much because it doesn't have as much time if it grows at that steady rate of the 6%. So we sum up at the bottom here. That's another way we can think about getting to that 52,315. I know we did that already, but this is another way to see it. And so you want to be able to see it multiple ways, don't you? Got to look at it from different angles. Okay, that has been thoroughly, it's cold. It's been overkill to the point that it's really cold at this time. So now let's take that and let's assume that we're about 20% down and think about how much loan we could get then. So I'm going to select column K. I'm going to go to the home tab and paint brushy and then put that right here in O for a skinny O. Skinny O. And then we're going to say this is going to be the home purchase amount. So I've got 52,315 to put down. That's what I'm going to throw down on it. So I'm going to then let's make this black and white, home tab, font group. Make this black and white. And I'm going to say that I'm throwing down payment. I'm throwing down 52,315. And that's what I'm throwing down on the table. And then I'm going to say that the down payment rate is 20%. So we got 20% down payment. Let's put, let's make that a percent and underline it. And so that means that the home price, price could be equal to the 52,315 divided by the 20%. So I should be able to purchase the 261,574 if I could finance the rest of the home. If I was able to put 52,315 down. Let's check that. Let's double check that. Check figure. Check it out. Let's do it the normal way because I kind of backed it down. That's not the way you normally do it. Normally you go home price is going to be home price. Usually you start with the home price, which is that number. And then you do the down payment percent, down payment percent of the 20%. Let's make that a number percentized font underline. That's going to be the down payment. Not righteous down payment. Multiplying this times that. That's how much we put down. That's matching up. That looks like what we would expect. And that means that the, that the finance amount financed is going to be equal to this number minus the down payment. So we're going to have to finance 209,260. So assuming we can get that financing then and we can throw down the 52,315,3 then how much home could we purchase? Let's assume the rate at this point. We could use the same rate to kind of tie this all out. Let's, let's assume that the rate is six. This is 6% on the home too. It might be different, but we'll, we'll choose the same 6%. Let's, because I'll tie everything together. That will really tie the room together like a good rug does that rug really tied the room together. And so then we're going to say that is the years are going to be 30 years. And then the payment. So now we can calculate the payment. So now if we're going to do that, let's see how much our payment would be if we could finance now the 209,260, which we got because we're trying to get as much house as we could after we put the 20% down. So we're going to say negative payment. PMT, negative instead of equal. Our payment calculation is going to be the rate. Now this rate is a yearly rate. Now we're talking months. So I want to take that rate divided by 12 this time to make it a monthly rate, comma. Number of periods is going to be 30. That's in years. That's in months. So times 12 and then comma. The present value, which is going to be the loan amount, not the home price, but the amount of the loan because we put we threw down 52,000, 315 on the down payment. That was that's a down down payment. That down payment is down. So there's the 1,255. Now let's let's make this a skinny. Let's do our amortization table. We'll construct this whole thing from that little set of data that we had. Let's make this blue and bordered. Now we've seen these amortization tables in the past. I'm going to do this a little bit more quickly here, but just we're just going to tie everything together just like that. Just like that rug does with the room. It ties it. That rug really tied the room together. So then we're going to say that we have the let's put our headers up top. It's going to be year month payment interest and then loan D. Hold on a second. Decrease notice I have two columns here because I don't I don't want to use the wrap text and I still want a long header loan balance. Let's move these down. So they're down here. I'm going to take these are going to control X or cut them and put them right there. And then let's make this a header thing by going to the font group, making it black and white and centered. I'll make these two a little bit skinner eyes. I'm going to skinner eyes them to make them skinnier. That's what you call it. That's what I call it. They're going to be skinner eyes. We're skinner eyes in the columns. And then let's let's do an auto fill. Taking this down 360. 360. Not degrees really, but kind of like whenever you hear 360, I kind of feel like it's degrees because it's like doing it. 360 on like a snowboard or something. I'm going to center that. And then we're going to say there it is and then the years are going to be equal to zero and this is going to be equal. We're going to do our roundup our fancy roundup to pick up the years roundup round him up. What do you want to round up? I want to take that number. That's what I want to round up and divide and then comma rounded up to the whole number rounded up. Round them up. The little doggies round up the little doggies and then I'm going to double click on this one. And it's going to double click on the fill button. Fill handle button and there we have it. The roundups that is that's what we have when that's the it I was referring to. Okay, so then on the payments. Well, let's make the loan balance is going to be the loan balance is that 209 260 and then the payments are going to be equal to that 1255 F4 on the keyboard make it an absolute you only need a mixed reference but an absolute will work. The interest is equal to the 209 260 times the rate of that 6%. That's outside of our table so we need to make it absolute so we can copy it down you only need a mixed reference but an absolute works dollar sign before the Q and the 12 and then we'll divide that by 12 because that would be the yearly rate and we need the monthly rate and then we'll subtract these two out payment minus the interest that's the decrease in the in the balance loan decrease or the principal decrease you can call it and then we got the prior balance minus the loan decrease and that's our new item let's copy these down these four double click the fill button fill button double click and it should be zero at the bottom notice we can always double check this kind of thing using a trusty loan calculator online if we wanted to I'm not promoting this particular calculator but there's a lot of them out there and you could you could plug this stuff into the loan calculator and say okay what if I had my loan of 261 574 actually no that's not the loan amount I have my loan of 209 it's a 30 year the rate is at 6% monthly calculate it throw it down and there's our one two five five about because we rounded it and you can create your amortization table however and so you could double check your amortization table and that's what I would use it for so for example after the third payment we're at or let's check this to 10 41 after the third payment and check that out we could say okay third payment is 210 about so it looks like it's calculating but this amortization table is actually tied into this data set which you can't do as much so if I change this data set the whole thing is going to change which you can't run those kind of projections that are all integrated as easily with the loan calculator thingy so let's go but it's a great tool to double check and look up some other stuff sometimes to narrow things down I'm going to make this blue and bordered this is another thing we can't do with the loan calculator as much as give that year by year breakout which we'll do next we'll do that next and that will be done thusly we're going to select column R and let's make that I want to copy that skinny home tab and paint brushy the Y to make it skinny Y because I want to save some room with column Y by making it skinny and then I'm going to copy the headers on over get rid of the months we don't need the months and then I'm just going to make the year by year breakout from year one to down to 30 years because it's a 30 30 year loan 30 30 year and then we're going to say center that and I'm going to put some zeros here just because I'm going to use a pivot table later and zeros you shouldn't have like blank cells in there that's just best practices and I like to practice the best stuff so then we're going to say okay so then I'm going to some if equals to some if brackets the range I want to pick up is this is the kind of criteria range I would call it they just call it the range right there and I'm going to say f4 on the keyboard because we're going to want to copy that to the right and then comma the criteria is that one so I want you to say hey thing if there's this thing which is a one in that range then I want you to sum up the some range related some range which is they're going to be this payment range right there and I also want to be able to copy this to the right and so this this cell right there that number one I wanted to move down but not to the right so I need to put a dollar sign before the Z and not the three it's going to be a mixed reference is what that's called the you you I do want to move over the VV next time so I'm going to say okay and boom boom bang bang call pulling over over and there's the 12486 let's check that number because that's that should be the sum of these bad doggies those bad doggies summed up 12486 there it is looks good and then I'll do the men equals the men ifs brackets and then we've got the men ifs bracket range we want to take this one on the range that's not where my home is and on that range but it's another range home on the rain this is going to be the criteria range which is this one and then is that the criteria and then comma and then the criteria is the one and we can copy that down boom and we can copy these down boom we can total it up down here copy it down total it up that's what we do in excel most of the time copy it down total it up copy down total up here we go we're going to sum it up with the total and copy that across not all the way to the end because this one is like a balance column and then I'm going to make that blue and bordered blue and border border blue border blue there's the border there's the blue let's now do the same thing with a pivot table as we have seen in the past I can't select this header that's what the downside is when you add a table but don't need it I don't need it I'm just going to select all this stuff do the same thing in pivot table format just to show you different options on how you can do this stuff pivot table they're super cool and impressive if you can pivot a table post people don't know how to pivot we're going to insert pivot table and I'm going to put it in the existing worksheet I'm going to put it right there it's like a pivot table holy moly I'm going to drop the price on the home 30% right there because I'm impressed by the pivot way you pivoted that table and then I'll add the payment the interest the decrease the balance and then we'll do the formatting on these as we've seen in the past I know I'm doing this quickly but this is kind of a recap just so we can put all this data together in one place here we're going to brackets get rid of the dollar sign decimal down down let's get in the rhythm on this one this time we're going to hit this one value field settings we're going to go to the number group we're going currency bracketed dollar sign gone decimal down down ok ok next one value field settings format currency brackets dollar sign gone decimal down down and ok ok last one a little bit tricky because it's not a sum one but instead a men thing so then we're going to go to the number formatting this is the same currency brackets dollar sign gone decimal down down and ok ok ok I said ok how many times so I'm going to let's make this a little skinier I already said ok ok so then there it is so now we've got this set up so we got all that kind of built up from that first piece of information and once you have something like this set up you can change things of course I can say well what if I put down the nine thousand or something like that then this is going to populate for us and I believe it should it should all work through here if we got everything tied out the whole schedule ties out the pivot table will not you might have to refresh the pivot table like right click and refresh the pivot table that's kind of like the downside of the pivot table but you can recreate it if it gets finicky on the pivot table as well so that's the benefit of excel you can run different scenarios on that you can also change the rate pretty easily that the number of years is a little bit more difficult because we calculated it these years here if you were dependent just on this as the calculation for the years it would easier to calculate and change the years as well but just note how whatever your scenario is if it's going to be complex if you're talking about things that are going to happen multiple years into the future like a home purchase that you're going to do in five years and then the things can you're going to have a home for 30 years your calculations your estimates can get quite complex quite quickly and excel has the flexibility to look at it from different angles in a way that you can't really get so much if you're kind of piecing together other tools often times