 Personal finance, Excel practice problem, estimated monthly cost, purchase of condominium. 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're basically building this from a blank sheet here. If you do have access to the worksheet, there's three tabs down below. Example tab, practice tab, and a blink tab. The example tab in essence being an answer key. Let's take a look at it now. We've got the information related to the purchase of a condominium on the left-hand side. We're gonna be populating the estimated monthly costs and then take a look and build the amortization table and then build a summary table with formulas and with the use of a pivot table. On the second tab, the practice tab, we have some of the cells that are pre-formatted. So if you don't wanna spend as much time formatting the cells, you can use this worksheet where the cells are already formatted. And then if we go to the tab to the right, this is where we will be working in the blank tab. Now, if you're just working on a blank sheet, you might wanna repopulate this information. What I would suggest to do is select the whole sheet first, format the sheet, and then use specialized formatting for these cells as needed, for example, the percent and so on. The format that I use, if I was gonna select the whole sheet as I right-click, format the cells, I usually go to the currencies, what I'm usually on. And then the bracketed numbers, I remove the dollar sign, and that's basically gonna be our starting point for the most part. And then I'll make any adjustments we need to be making by adding percentages when necessary or adding decibels when necessary. So our information on the left-hand side, purchase a condominium, the mortgage is gonna be 250,000 years 25. The rate is 8% property tax per year is gonna be the 2200 property insurance per year, 600, and the association fee for the condominium is gonna be $300. We're focused in on what we think the monthly expenses related to the purchase will be. We'll have more comprehensive problems later on in the section. So I'm gonna start off with a header, which I'm gonna call monthly housing payments. Monthly housing payments. If I misspell anything, I apologize. I'll go to the spell check at some point in time. I'm gonna make this cell a little bit wider. Note that I might not need to go all the way past here, meaning I might have some overlap and be okay with that. I'm gonna imagine that I'm gonna be using these two cells in my calculation down below. So I'll widen that cell if I need to, but just as a general rule, I'm gonna say, okay, that looks pretty good to start out with. I'm gonna highlight from here from D to F. I'm gonna make this a header, which I usually do by going to the home tab font. I go to my bucket. I'm gonna make it black and then the text white so that the headers will kind of stand out with the black and white header. So then inside, I'm gonna say, first we've got the monthly mortgage payments. That's gonna be the first thing that comes to mind when we got this loan. That's gonna be a monthly call, so we're gonna have to spend. So we're gonna say, all right, monthly mortgage payments. And now here, I'm gonna put this in the outer cell so I could make this a little wider, but I'm gonna put this in the outer cell because I'm gonna have some subcalculations later on inside. So I'm not gonna widen it yet. I'm gonna use a payment calculation to do this. Now I usually do that with a negative instead of an equal. So I'm gonna say negative instead of equal. Technically, you should probably put the negative actually inside the formula, but this is kind of the easiest thing to do in my opinion. So it'll come out to a positive number. So I'm gonna say negative and then the PMT shift nine and now we've got our little box here to calculate our payment calculation. So I'm gonna pick up the rate, which is over here. Remember, you always want the information somewhere else in your data set so that you can change your data set and your whole worksheet will change for you instead of hard coding the 8% inside the formula, in which case it's a lot more difficult to change the formula. Now this is for a year. So every time we talk about a rate, we're talking about yearly rates because we don't talk about monthly rates because they would often be quite small and we wanna talk about reasonable rates. So the convention when we talk about rates is a yearly rate, but the payments are monthly. Therefore, I have to make that a monthly rate. So I'm gonna take that and divide it by 12. Notice even though that might be a small rate here, it's fine in Excel to calculate it. Excel can calculate it exactly by using that ratio. And then I'm gonna say comma, that takes us to the number of periods. We're gonna say this is a 25 year loan that we want to make then into monthly payments. So I'm gonna pick up the 25 and then multiply it times 12. For 12 months a year, there's 25 years. So then I'm gonna say comma, we've got the present value, that's gonna be the actual loan amount. So that's gonna be the 250,000 and there it is. So I'm gonna say enter, that's the 1,930. Now I typically like to verify that double check it when we calculate the amortization table, which we will do shortly. Now the other things that we have involved here when we purchase the home that we wanna make sure that we take into consideration are things like property taxes, if there's any association fees and any insurance, for example, so that we're making sure we kind of budget out the full amount that's gonna be in place. You also might have maintenance that you wanna average out and stuff. But in any case, we're gonna say this is gonna be the property taxes per month. Property taxes per month. Now if we've estimated the property taxes per year as we've done here, I'll just divide it by 12. So I'm gonna do the full calculation here so we can see it. I'm gonna pick up that amount or pick up that text by using an equals. I'm gonna pull this to the inside. That's why I put a colon up top by the way, because that means I'm gonna do a sub-calculation and I'm gonna pull the sub-calculation inside. These little technical things on how to build the table is kind of nice to know because it'll help you to build and read tables if you can use these kind of sub-colons or sub-titles and whatnot properly. So I'm gonna say this is gonna equal the 2200 for a year and then I'm gonna say the months in a year, months in a year are 12, which I can just hard code because that number is what it is. I'm gonna put an underline here by going to the home tab, font, crewmen, underline, put a line underneath it, known as an underline. That's what we call it. And then I'm gonna just copy this one down here. I'm gonna get rid of the colon because, and I'm gonna pull this to the outside. This is the sub-calculation. I'm just gonna divide these two out. This is gonna be equal to 12,000, 1,200, or 2200 divided by 12. I'm gonna stop saying just. I keep saying just. I'm just gonna do that. That's just what I'm gonna do. That's just what we do. And then I'm gonna select these and we're gonna go up top here, home tab, alignment and let's indent it. So I'm gonna indent it. So now it's a sub-calculation with a colon. It's inside and then I indented it and you might double indent this last one to show that now we've pulled that back out to the outside. There's our sub-calculation formatting. Then we've got the same thing for the property insurance. I'm gonna say property insurance per month calculation colon sub-calculation down below because we got it for a year again. So I'm gonna equal this year. I'm gonna pull that description even over in my formula. Notice it's too long. Now I need more space. So now I'm gonna pull this column D out a little bit, put my cursor between D and E, pull it out right there. That looks good. And the amount then per year is going to be that $600. And then I'm gonna say this equals because I already have it up top the months in a year 12 and this equals the 12. So I could use some formulas since I already did that. Save me some type in time. Home tab font group and underline. And this I'll just copy this down and delete the colon. Gonna control C, paste it down here. Here's the, that's what I said I was gonna do. Here's me calculating it and here's me doing it. Here's the end result, which I'm gonna pull out into the outer column. This is gonna be equal to the 600 divided by 12. I said I was gonna do it and then I did it. That's how I do things. I say, this is what I'm gonna do. And then I do it and then there it is. So there's the home tab alignment and dent. Let's indent this one again. Home tab alignment and indent again. So then we have the association fees. That's on a per month basis. So I don't need to do any special calculations. It was $300 per month. I'll just put that right into the outer column. No sub calculation necessary this time. And so that'll be the $300. Let's put an underline here. And so we're gonna say that the costs that we have monthly housing payments or costs, let's say, payment sounds like it's just a mortgage payments. Let's keep it there. Cause these are all related to the housing payments. So I'm gonna sum this up. Equals the trustee sum equals SUM. The trustee sum function, control nine. I'm gonna hit the up arrow and then I'm just gonna hold shift and we're just gonna do that. Stop saying just stops. We're just doing that. That's just what we do. And then I'm gonna double, let's double underline this home tab font group and double underline it. And then I'm gonna make this blue. Gonna select the whole thing for some formatting purposes to format it nicely. Let's right click on it. And then we can go to this bucket here or you can go to the bucket up top. If you don't have this blue, you go to this, this more colors down below standard and there's the blue right there. Why do you use, why do you use that blue? Cause that's what the accounting is fun or the Excel is fun guy used. And so that's what I use. There's no real reason. I'm gonna go to the home tab font group. We're gonna hit the dropdown. We're gonna go down to the all borders. Let's put some borders around it. There it is. Looks wonderful. It looks just spectacular. Now let's start to let's build our amortization table so that we can just practice building it. So what I wanna do is I wanna make the same space between here and the next thing I'm gonna do. I'm not gonna build anything right next to this one in the next cell. I wanna make this a skinny cell then. That needs to be a skinny. So I'm gonna put my cursor on this skinny so I have the same width of the skinnies and then go to the home tab, clipboard, paint brush it. And then I'll just paint brush you this one. So now I got the skinnies. I'm gonna hide from C to F cause I don't need this information here. Actually I might need that 19. Let's hide it for now. I'm gonna put my cursor on C. I'm gonna drag on over to F and then right click and hide as I build my amortization table. So here we go. Amortization table. So I'm gonna put, I'm gonna do my headers first. Now note, I think I'm gonna need two rows to have some headers. I'm not gonna wrap the text because if I wrap the text then it makes this cell one like wide and I don't like that. So I'm just gonna use, I'm just gonna basically use the two rows here. Unless I was building an actual table that I was gonna insert a table, we will insert a pivot table and I'll show you kind of the problem with that. But if you were inserting the table then you want the header all in one row even if you have a really long header and you probably have to wrap the text. So I'm gonna say that first we're gonna say this is gonna be the year. These are gonna be my headers and then months and then payment, payment. That's fine and then interest and then here's where I need the two. I'm gonna call it loan decrease. I used to call it principle amount but I always misspelled principle and principle. So now I call it loan decrease because I can, unless I spell that more accurate more times and people don't tell me. You just did the wrong principle, whatever. You know what I was talking about. That's what I tell them but then they still pick on me so I don't do that anymore. Any case, I'm gonna highlight this now and we're gonna then go to the home tab. We're gonna go to the font group. Let's make this our header stuff making it black and white, black and white and then we're gonna go to the alignment and center it. So there we have it. Now we're gonna need a number of payments. So how many payments do we need? We got 25, we got 25 times 12. We're gonna need 300 payments monthly. I'm gonna start at zero here and then one and then I'm just gonna select these two. That's just, we're just gonna do that. That's just what we're gonna do. Okay, and then I'm gonna select these two. I'm gonna put my cursor on the fill handle. That's the fill handle right there. Grab it, grab it, get a good grip. Get a good grip, put your finger, chalk up your left finger because we're pulling that thing down to 300. So don't let go, don't let go. We're going down to 300 down here. You can see the little number thing and we're going right there, right there. Boom. Let's center it while we're here. Home tab alignment and center. There we have it. Now we're gonna do a fancy little calculation for the years because like these first 12, I'd like to know it the year by year. Otherwise I gotta like divide it out down below. So this is a fancy little fancy maneuver that you might not be aware of that we're gonna do right here right now. So pay attention. So we're gonna say this is gonna be equals two and round up, round up and then shift nine. And so I'm gonna round up and I'm gonna take this number, I'm gonna divide it by 12, divide it by 12 but I'm gonna round it up. So that should be that. So it's gonna round up to one here because it's 112, right? See, and then comma. Now you gotta round it to the point one digit. That'll tell you to round it to one. That's what it means to round to one with the roundup. So let's do it, boom. And I like to add a couple of decimals to see if it does it correctly. And then if I copy it down, see how this is all now one, because it's rounding up, it's rounding up 412s, rounded up to one. See, pretty fanciness. I'm gonna take this all the way down and see if it does it correctly all the way down. I think it is, I think it is super impressive. It's not just impressive, it's super impressive, I feel like. Now let's get rid of the decimals number group, removing the decimals. That's the wrong way. I increased them and then I'm gonna center it in the alignment. Okay, so there's that. Then we need the payment, we need, let's pick up the loan balance on period zero. Let's do that first, that equals 250,000 and then I need my payment calculation, which I've hid. So let's unhide it to get my payment calculation. I'm gonna put my cursor on B and go on over to H, B to H. Bah, bah. And then right click and unhide. And that's that, there's the amount. Not the full, not this amount down here, but this amount, because we're just talking about the amortization table. I'm gonna say this equals that amount. I wanna be able to copy it down. So I'm gonna make it absolute by selecting F4 on the keyboard or dollar sign before the F and the two, you only need a mixed reference, but an absolute reference will do and it's easier, so that's what we'll do here. Calculation for the interest is gonna be equal to the prior balance and then I'm gonna multiply that times the rate at 8%. That would be for a year. So if I entered that, that'd be if it was for a year, but this is only a month. So I've gotta take that whole thing and divide it by 12. I don't need brackets because it's the order of operations. So I could put brackets around this, but I don't need to because it's gonna multiply before it divides. That's how it works. I learned that. So then this is gonna be a subtraction of 193.0 minus the 1667. There's rounding involved here because we took the pennies off, be aware of. And so that's gonna be the loan decrease or the principal decrease, right? And then this is the loan balance or the principal. And so, but not like a principal at a school, you know what I mean? It's like the loan principal. That's why I don't use it though. So this is the 250 minus the 263. So there's our new loan balance. Now we're gonna do this over and over again. If I copy this down, I should be able to copy it down, but it's gonna have a problem, I think. If I copy this down, we made absolute, but this is the problem. What did it do? It moved this cell down. So anything that's outside of my table, I'm gonna undo, I need to make absolute. So this is referencing something outside of my table, meaning that 8%, that out that B5 needs to be absolute. So I'm gonna say F4 in the keyboard. This one is fine because both items are inside of the area I'm working with. It's not coming from my dataset, no absolute necessary. So now I can select these. I can drag it down or I could just double click on the fill handle at this point. And then it populates for us. We're gonna go all the way down to the bottom. And if we get down to zero down below, then that tells us, that's a good indication that we did it correctly. Now we also know, you might also say, hey, you can build this kind of thing with an online tool. And you can, like you could check online tools to build this. So here's a loan calculator. I'm not advertising this website or anything, but you can find many websites, 25,000, and it'll calculate this. I like to use this as kind of like a double check for me because Excel is way more flexible if you learn how to build it in Excel. So notice that this does have some of these breaks here to kind of help you out, but once you get this in Excel, you can do a lot more with it. So I would use these online calculators kind of like as a double check, but if you really want to dig down on something, I would put it in Excel because you got more flexibility. And let me try to show you what I mean by that. So if I go back on over just to check this, this came out with, this came out with 8%, first couple payments, the payments of the 1.66, the payments, the interest is the 1.66491 for the second one. Let's just check that 1.66491. So yeah, so it rounded there. Okay, so that looks good. I'm gonna make this, I'm gonna make this all blue and put some borders around it. Let's put a border around it. And make it blue. So this kind of confirms to us that this calculation was correct. That's one reason I like to do it. If you just get this number, which is often the only number you will get from if you're talking to like a broker or something like that, then that's not really given you a whole lot of information because it's nice to have that, but you'd also like to know if you're doing a full calculation, you're gonna wanna know what the interest is per year and the difference and the breakout between the interest and the principal as we'll see in future presentations will also be broken out differently as the years pass. So you might want to see that, for example, on a year by year basis. So now let's try to summarize this table. And this is again, one of the things you can do in Excel, you can't do as easily with some of these other tools that you see online. Let's try to summarize on a year by year basis the payments, the interest, the loan decrease and so on. So what I'm gonna do is I'm gonna copy some of these headers. I don't need the month header. I'll copy this whole thing. Well, first let's copy the skinny. I'm gonna take this skinny right there and I'm gonna go to the home tab, clipboard and take that skinny and make the same size skinny on end. And then I'm gonna take these headers. We're gonna copy it, copy the headers and I'll put that on O. But I don't need the months, I just want the years. So I'm gonna take this whole column and delete it, delete that column. So there we have it. And now we want the years to be one, two, three down to the 20. So I'm gonna select those three. We'll put the cursor on the fill handle, bring it on down to 20. Did I say 20 or 25? Was it 25 years? Let's say it was 25. Is that what it was? I can't remember. Yeah, 25 years. I know what I'm talking about. Let's center this thing, home tab, alignment, center. Okay, so now I want the payments for the whole year. Now you could copy these or you could just take one payment divided by times 12 because the payments will be the same but the interest will change from year to year. So I'd like to get a calculation that can pull this from this table. So we'll do it this way. You can also do this with a pivot table which we'll see shortly. But I can do it this way. I could say it, let's use an if. It's called a sum if calculation. So what I wanna do is say, if you see that number over here on the years, on the years, not the months, I want you to sum everything in this column related to this one that has a one next to it. So that's gonna be our kind of matching calculation. I'm gonna use this one to tie out to this column and sum everything in this column where this one is tied out to this column. Let's see if that makes sense. So I'm gonna say this is gonna be equal to the sum if calculation. So there it is, sum if and then we've got the first one is the range. So we want to select the range and so this isn't the sum range. This is the range of conditions that basically that I need which I want this ones in. So that's gonna be this range. Now you probably should not, should just select the table here but I'm gonna select the whole column cause there's nothing underneath it. So I'm just gonna pick this up. Be careful of doing that because if you put something underneath it which I'm not gonna do then that could mess up your formulas. But I'm gonna say there it is. So there it is. So that's a nice easy calculation. I'm gonna say F4 on the keyboard so I can copy that one down because that range isn't going to change when I copy it down and across. So I'm gonna say comma and then the criteria is this one right there. I want you to search that range for this number one and then I want you to, and I'm gonna say comma once you find everything without one in it I want you to sum not that range of ones. I want you to sum everything in the payments column. So I'm gonna select the whole column which again, be careful of selecting the whole column if you have something underneath it. So there is that. So there it is. I'm gonna say, okay, let's enter it and see if it does it. So it should have summed these. So it looks like it did it correctly. Let's try it again with the interest here because the interest will be different from year to year. And it looks like I should be able to copy this down and there it is, it's doing the same thing. And it's super impressive. If you're not impressed, like you should be. It should be, you should be impressed. So this is gonna, let's do it again. This is gonna be equals sum, if, so then I'm gonna select the range, which is this. The range is h's, h's, f4, that's what? That's f4, not just a four, f4, to make it absolute dollar signs. And then comma, the criteria is gonna be this number one again, that number one comma. And then the sum range is gonna be this time the interest column, column K. And then enter. I think that's gonna do it. Notice this one, did I? Yeah, so that looks good. So then that should have summed up these. So that looks good. If I copy it down, boom, it should still, now it should sum up these, all the twos. So you get this nice table, which can tell you what the interest is. That'll help you to calculate your tax implications and whatnot on a year by year, because it's gonna change from year to year. You gotta be careful of that. And then we'll do the same. Let's do it again for the loan decrease in the principle. So this is gonna be equals to sum if, sum it if, and then we're gonna say the range is gonna be this one, like we did before, f4, so we can copy it down, comma. And then the criteria is gonna be this, and then comma. And then we're gonna say the sum range, this time is the loan decrease. And so I'm gonna say, there it is. Now I could select f4 to copy this down. The fact is it's gonna be the same column because I choose the whole column. So the f4s, the absolute's not really doing anything. So I'll keep that as it is, and then I can copy that down. And the loan decrease then should be this. If I sum these up, there it is. That looks good. The next year ones. So there's the three, five, four, four. Looks good. Okay, so this one's gonna be a little bit different the last one, because I want the lowest balance. Now let's do this again. This time I'm gonna delete this, and I'm gonna say, is there a way that I can fix this one to copy it to the right? Copy it to the right. See, I can't do it because it did something funny there because it moved over this cell to the right. And I wanted to keep it at one. So I just need to fix that by making a mixed reference. So this one right here, it's moving it to the right. That's a problem. So I want it to move down, but not to the right. So that means that the three needs to move, but the O needs to say the same. So I'm gonna put a dollar sign before the O, but not the three, which is called a mixed reference. And then I can copy this one to the right, I think. And then I also should be able to copy it down. I could just double click, boom. And so now you've got a lot more information and a nice condensed table. This one, we want the ending balances as of the end of each year. So I want like this one. That's the ending balance as of the end of the year. Ending balance. So that means I need a min function. I need to give me the min. So I'm gonna do a min if calculation equals min. Men's if. Men if, there it is. That's the one we want. Men's if, take the smallest one if. And then we've got it a little bit different on the range kind of things, the last one. So the min range that we want is gonna be this one. So I'm gonna select the loan balance range and then comma for the criteria, the criteria is going to be this item. Oh, I'm sorry, this is the criteria range. The criteria range is gonna be H and then comma and then the criteria, which is the one. So there we go and then enter and it picks out the lowest one right there. The 246.7. So I'm gonna copy that down and you get this nice table summarizing this on a year by year basis, getting your ending loan balance at the end of each year and then your yearly interest calculations and payments, which you can get the payments fairly easily but the decrease in the loan, which you can also think of as possibly an increase in your equity as the loan balance basically goes down. In other words, you can think of basically the equity being the difference between the value of the home and the amount that is financed if the value of the home stayed the same then the amount that you pay down on the loan, the decrease in the principal is gonna be an increase like in the equity or value of the home. Although you can't really realize that until you actually sell the home or refinance the home. And then of course, if the home went up in value or the condominium, then that could you could think about that as like an increase in equity in essence as the value goes up. Those are the two kind of factors that are gonna widen the gap between the value of the home and the amount that's you owe on it paying down the principal portion of the loan number one and hopefully the value going up. Now I can also do the same table even easier in some cases by making a pivot table. So let's just do that one real quick. I'll just make a pivot table. Now on a pivot table, I'm just gonna select all the data but I can't select this first row. I told you this was kind of a problem of me doing this two column thing. So I can't pick up that first column but that's okay. I'm just gonna pick up these as the headers and then go all the way down. I'm just gonna insert a pivot table. So we'll select that whole thing and then I'm gonna go all the way back up. I'm gonna put the table inside the same area. So I'm gonna say insert and let's say a pivot table. A pivot table and maybe I'll put it underneath. Let's put it down here. I'm gonna put it in the existing worksheet and I'll put it, let's put it right there, right there in that cell in O29. So I'm just gonna put it there, boom. And then it gets a little bit tricky to do this but once you do this a few times it's pretty easy, I want the year. So the year, it puts over in the sums because the year's a number, but I wanna pull that over into the rows, that's the only tricky part and then everything else will populate as you would think once you do that component. Meaning I don't want the months because I just want the years. I now want the payments, the interest, the decrease and the balance which they populate as we would expect on the right hand side summing up the same data. It's kind of ugly in the format but it gives us that same data nice and easy. Now the pivot table is a little bit tricky because you gotta refresh it and when you change the data on the right hand side it could be a little bit more tricky than something like this where the formulas will update automatically. In other words, if I change the data on the right hand side, the pivot table, you gotta update it, it could be a little bit more finicky to kind of update than formulas. So that's one thing to just kind of keep in mind but you can make a new pivot table easy if something happens to it, if there's a problem with it by just doing this again, right? That pivot table is pretty easy, probably easier than the formulas once you get this kind of down and then we just wanna format these cells. So I gotta do a little bit more to finish this up. One way to do that is each of these areas I wanna format the cells. I'm gonna hit the dropdown, value field settings and I'm just gonna change the format by going to the number formatting and then go, I like going to currency, the brackets and then get rid of the dollar sign and I'm gonna get rid of the decimals. So now I'm gonna say okay and okay. So now it's formatted closer to what I like to see. This one I'm gonna do the second ones. I'm gonna hit the dropdown. I'm gonna go to the value field settings and then we're gonna go to the number format. I'm gonna say this should be currency, brackets, dollar sign, removed, decimals gone as well. And okay and okay. So that one looks better. Let's do the third one. Second from the bottom, value field settings, number format. And I'm gonna say that this is gonna be currency, brackets, get rid of the dollar sign, get rid of the decimals and okay, okay. Now the last one, I gotta change a little bit because I don't wanna do the sum. I wanna do the min balance. So I'm gonna do the field and I want the min here. Not the sum but the min. And then the number format and then currency, brackets, no dollar sign, decimals gone, okay and then okay. So there we have it. So now this table should mirror the table that we had up top. You can see. And that's just another way that you can build it. I'm gonna make these cells a little bit smaller so that they're not so wide. I don't like them. It's just a waste of space. Stop wasting space like that. Now then you can of course, you can wrap the text here but be careful of wrapping the text because it causes kind of issues because this whole cell will get fat, right? And everything else will look funny. So I don't like that. That's why I don't like doing it unless I have to but you have to do it with the table sometimes. So that's gonna be, that's generally it. Now again, so this summarized data on the right hand side often useful when you do more advanced calculations and you're trying to think about what's gonna be the tax implication from year to year, not just in year one, what's gonna be the equity kind of calculations on a year to year type of basis. It's also kind of nice if you're comparing different loans to try to see a summary of the loan, the year by year breakout of the loans, which again is difficult to do if you use the online tools. The online tools are great but I would use them as an estimating tool if you get more into more complex kind of things. That's my general idea. So we'll keep on practicing putting together those amortization tables in future presentations.