 Personal finance, Excel, practice, problem, comprehensive problem, part number one, home financing. 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, 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've got the information on the left-hand side. We're gonna use that to do a couple calculations on the right-hand side. One, we're gonna look at the home purchase. We're gonna build the amortization table. We're gonna be building the year-by-year breakout, summarizing the amortization table. We're gonna construct our balance sheet which shows where we stand as of a certain point in time. We're gonna construct the income statement. These two financial statements are gonna rely in part on the home information that we calculated and the amortization table and the summary amortization table. We'll calculate the income statement a couple different ways just so we can see kind of a cash flow method on the income statement and more of an accrual method and discuss a little bit of the pros and cons between those two with regards to our personal income statements and then we'll put together an estimate for the property insurance that we might be needing. So I'm gonna go back to the left-hand side. We're gonna start off with the first component here on the home. So we got the practice tab. That's gonna have some pre-formatted cells on the right-hand side so you could spend less time formatting the Excel worksheet and just working through the practice problem. The third tab is gonna be the blank tab where we're gonna be focusing on some of that Excel worksheet information. It's a little bit more stuff on the left-hand side that you would have to populate. So we got a lot of data over here but you can certainly populate this data on the left-hand side and think of it more as a kind of a budget kind of tool or practice tool within Excel. Have your data on the left. We're gonna use that data to be building our tables on the right-hand side. So if you don't have this sheet, what I would do is you'd open up a blank sheet, I'd select the entire sheet, put my base formatting which would be right-click and we're going to format the cells. I would go to currency, brackets, get rid of the dollar sign, no decimals, that's my starting point at least, closing this back out. Then put all the data on the left-hand side which basically includes kind of our financial data here so there's quite a bit of it this time because it's more of a comprehensive problem. But you could put this data over here reformatting the cells to percentages where needed and then we're gonna make a skinny C column and then we're good to go. We're gonna take that data and we're gonna start off with our loan calculation. So what we got here just a summary, we've got basically our balance sheet type of information. This is stuff that in practice, you might gather together or you might use some kind of software to help you to gather this stuff together which we might talk about more in future presentations but note you might do bookkeeping with like a QuickBooks software or wave accounting or something like that and we've got the checking account, the savings account, the emergency savings. We also could use other software that would compile this data in terms of the ending balance. It wouldn't really track the entering of the data but it can compile the ending balance from financial institutions like banks or like other financial institutions like an E-Trade or something that might have an IRA in it. It won't have the car on it because if you put use software, it's not gonna be able to know the value of the car because it's not being tracked by the financial institution. So you'd have to manually put in, manually put in the value of the car that depreciates over time. I might discuss that more but we've got the car value on theirs for the two cars. We've got the liabilities, which would be the loans and these are also things that sometimes the financial software can kind of pick up because they're coming from financial institutions. We'll talk about that more later. We've got the income. We're assuming that we have W2 income for two individuals or one person that has two W2 incomes and so we've got the gross amount and the net amount we'll have to deal with. We've got the expenses that we have listed out. Obviously the income statement is something that you might use the software like a QuickBooks or an accounting software, Wave or something like that. We might talk about that more in the future. You could try to use bank feeds to construct this stuff or you might just kind of build it together from a budgetary standpoint. We've got the home purchase information. We're gonna say the home price was the 306. This is where we're going to start now. The rate was the 5%, years 30, down payment. It's gonna be 20%. And then the current value of the home we're gonna say is at the 125. So it's going up since the purchase time. The estimated value of appliances, furniture, clothing and household items, 21,000 jewelry valued at the 2,000. So that's what we have now. I'm gonna focus here. I'm gonna put my cursor on these items. I'm gonna right click and make that. Let's make it green to focus our eyeballs on it as we go through the calculations. So we're gonna do our standard kind of loan calculation. I wanna get the amortization table and I wanna break the amortization table out by year because that will help me with some of my calculations and putting together the financial statements. So we did this in a prior section. Let's just redo the calculation. You might get an amortization table from the loan provider. You can look up online for the amortization tables. But if you could put that data into your worksheet, it could be a useful tool to help you with your financials and your budgeting stuff. So let's do that again. I'm gonna say this is the loan payment. The loan payment calculation. I'm gonna select these two items. I'm gonna go up top. We're gonna do our formatting for a header, thought group, we'll make that black and white, black and white. And so this is gonna be the home price. So I'm gonna say home price, price. And this is gonna be equal to, and if the home was priced down here at the 306,200, there is that. We're gonna say the down payment. The down payment. That payment's down is 20%. Down payment percent is gonna be equal to, I'm drawing all this information from the data to the left so that you can change the data to the left and it'll change the rest of your information. That needs to be a percent. Now, so I'm gonna go to the home tab, numbers, percent to a fiat. I'm gonna underline it, font group, underline a fiat. And then we're gonna say this is gonna be the down payment. Then it's gonna be equal to the 306,200 times the 20%. So if we're putting 61,240 down, then we're gonna have to finance, or the loan amount at the start is gonna be the difference. It's gonna be equal to the 306,200 minus the 61,2. That's gonna be the 244,960. Now we'll do our payment calculation. This is something if we had already set up the loan, we'd probably have the payment information. But let's do that calculation for the payment. If I have this information up top, we could do the standard payment calculation. If we were trying to estimate this, we're gonna say negative PMT brackets. And this is gonna be the rate, the rate which is going to be, I'm gonna scroll down and pick that up down here. That's gonna be the 5%, comma, actually not comma, hold on a sec, there's the 5%, I'm gonna say that divided by 12 because that's a yearly rate and we need a monthly rate. And then comma, the number of periods is gonna be 30. I'm gonna pick that from my data down here again, 30. And then comma, and then the next one is gonna be the present value, not the home value, but the loan value. So there is that, closing up the brackets and enter. So we've got this amount. Now I messed up, hold on a second, you might be saying, what did you do? You messed it up somehow. See this second item here for the periods, I kept the 30, it should be 30 times 12, 30 times 12. And so there we should have it. There's the 1315, it's rounded because I took the pennies out. Now we can also do that online or double check it online. So I might go into an online tool, I'm not trying to promote this particular one but you can search for Google search and online tool for the loan calculator. I'm gonna say it was the loan amount is the 244960, so I'm gonna say that loan amount is the 244960, 30 years, it was 5% we said and just do the calculation. So there's the 1315, that's the same calculation we got to here, so we kinda double check our numbers. We can also create the amortization table here, which is great, but I can't really pull any information from this when we try to move that for other calculations for budgets or for the calculations of our income statement depending on how we're gonna do that. So I'm gonna select this one, I'm gonna make it blue and bordered. Let's put some borders around it, let's make it blue and we're gonna make our amortization table now as we've seen in the past. I'll do it fairly quickly because we've seen this in the prior section. I'm gonna put my cursor up top and we're gonna make this skinny item here, the skinny row, home tab, format, paint it and I'm just gonna put that over here on column F. Now we're gonna use this amortization table later so I wanna make the amortization table, I wanna make it broken out by year so I can use that for our financial statements which you could also use in a similar way as a budgeting format, so we'll try to tie this out then in a way that could be useful which is why I think it would be useful to do it in Excel. So I'm gonna say this is gonna be years, I'm basically copying this amortization table so it looks something similar to this, right? I'm gonna put the years included though instead of just the months so I can convert it to a year by year breakout. So then we got the months because it's gonna be in the format of months by month and then we got the payment and then we've got the interest and then we've got the loan or you might say the principal decrease, decrease and then, is that what they call it over here? Let's see what they call it just for the, they call it principal and then the Indian balance. So I'm gonna say loan decrease and so this is gonna be the loan balance so the Indian principal balance, there we go. These three, I'm gonna pull them down, notice I didn't use the wrap text here, I basically put them on two different lines because I don't want a fat one line here, I wanna keep it skinny but if I needed to make a table from it you can see that kind of causes the problem so we will make a pivot table from this so you can see that but if I'm not making a table, I'm gonna pull these down right there, I think using the two cells is good and then you can kind of hide the fact that there's two cells without making a fat one that messes everything else up, that'll mess everything else up, you gotta think about everyone else, all the other cells that are involved with this thing not just be selfish with the cell, so in any case, this is gonna be the alignment and center and this is gonna be the font group, we're gonna make this black and white and then I'm gonna make the years go down a bit, I'm gonna start with the months because we're doing this in months so I'm just gonna say zero, one, two, 30 year loan times 12, 360, 30 times 12 is 360 so I'm gonna take one, two and then I'm gonna buckle my shoe and then I'm gonna grab the fill handle right there and I'm gonna drag that down now that I got my shoe buckled, I can make that journey, I can make that journey all the way down to 360 and my shoe didn't fall off at all because I buckled it at two and so we're gonna say this is gonna be home, alignment and we'll center that and then I'm gonna do the years so I'm gonna put the years here so I can make this into a year by year calculation with a tricky formula, let's put it down here first, this will be the roundup, roundup formula, roundup little doggies, so we're gonna say roundups because we're gonna round the number up so I'm gonna take this number, I'm gonna divide it by 12 because there's 12 months in a year and then I'm gonna say comma so that should, this number will come up to something between one, zero and one which I round up to a whole number which you gotta put a point O one that tells it to round up to the whole unit so I'm gonna close that up and say enter I kinda double check it by adding some decimals and say yeah, round it up to a whole number and then I can put my cursor on it, I can double click on the fill handle which I just call a fill button at that point and it should copy it down. Let's center it here, we're gonna center it, I'm gonna pull it up too, I'm gonna put my cursor on the fill handle and drag it up just to put a zero up top so that's gonna be zero and now if I go all the way down, it gives me the years, so it gives me 12, 13s, 12, 14s, 12, 16s and if I go all the way down we should end up off the 30 years, there we go. Okay, so then, then what we'll do is I'll go to the loan balance to start things off and then we're gonna start off the loan at that 244.960 and then we'll do our payments which we calculated right here, that's gonna be the same all the way down so this is gonna be equal to the 1315, I'm gonna select F4 and the keyboard making an absolute dollar sign before the E and the six, you only need a mixed reference but an absolute one works. The interest is gonna be equal to the 244.960 times the rate which is not that 20%, it's way over here in our dataset, it's the 5% but that's the yearly rate so I'm gonna divide it by 12, divided by 12 so it's gonna be this number times the 5% divided by 12 now this 5% is outside of our table and therefore we want it not to move down when I pull it down so I'm gonna say F4 and the keyboard you only need a mixed reference but an absolute one works, dollar sign before the B and the 32 and enter the loan decrease then it's gonna be equal to the payment minus the interest and then the loan balance if this is the payment and this is went to interest means it poofs away in a puff of smoke because it's the rent on the purchasing power of the money the loan's only gonna be decreased by the 9 to 294 so that's gonna be the prior loan balance minus that 294 so there we have it now I know I don't need any absolute references there because these two are both inside the table I'm working on so that's good, they're good to go I want them to move down as I copy down so I'm gonna select those four and I can just double click the fill handle which is really a fill button at this point double click the fill button and then we're gonna scroll down and you can see there's a change of course between the interest and principle that's what's gonna kinda mess us up with our accounting when we put together our financial statements and it'll talk a bit about how that kinda messes us up and we record it and what to deal with that and how to think about that a bit I'm gonna select this whole thing and make it blue and bordered now select this whole thing and make it border blue border blue, thought group, border and blue looks excellent okay so we can check that to the online tool here and say does that line up I can check up a couple lines and say I'm at the end of year one I should be at the 241, 345 for example end of year one, 241, 346 about looks good now I'm gonna take this I'm gonna break it out on a year by year basis doing it two ways one with formulas, the second with a pivot table because that's what I'm gonna use to do my financial statements and whatnot I need a skinny M column to do this so I'm gonna put my cursor in the skinny F and go to the home tab clipboard format paint and make that skinny M, same skinny size and then I'm gonna take the headers up top just gonna copy those headers, control C, put that in N and there it is I don't need the months though cause we're not talking months this time so I'm gonna put my cursor on column O right click and I'm just gonna delete the whole thing and I'll make the years a bit more skinny skinnerizing them, they've been skinnerized and then we're gonna say this is going to be one, we'll say one, two, three I'm gonna take those three and I'm just gonna put my cursor on the fill handle and drag it down not too far this time just going down to 30 we're not going all the way down I didn't even buckle my shoes this time cause we're just going down to 30, do that barefoot you can do that barefoot so I'm gonna go then we're gonna go to the home tab we're gonna go then to the alignment and center it so now I wanna sum up the payments so I can do this I could sum up the payments here so I'm gonna do that calculation with the sum if calculation so I'm gonna say this equals the sum if now you can use either of these I think the sum ifs is more versatile so you might try to get used to using the sum ifs which is just a little bit different format now it has the sum range first so the sum range I wanna sum up everything in this column if there's like a one next to it so I'm gonna say I want this whole column I'm just gonna select the whole column pick it up that whole column and then I'm gonna say and I probably should stop it at the end of the table but this goes down forever so I'm just gonna take the whole column and then comma and then the criteria range is this range meaning I want you to look for a one in that range and if you have a one in that range I want you to include that into some stuff that we're summing and then comma and then the criteria is this one so if you find that one in that range then I want you to sum up the related number in the payment range you got it Excel, you got it and there it is 15 let's do it a couple more times this equals the sum if and then we'll copy the formulas across some ifs I'm gonna use the sum ifs with an S this time the sum range is once again this range so I'm sorry it's this range interest we're looking interest the interest column and then comma and then the criteria range is this range we're saying hey if you find a one in that range then I want you to sum the related interest column related to it comma and there's the one so we're saying that's the criteria if you find that in the criteria range right there I want you to sum up the related number in the sum if function let's do it one more time we're gonna do it over here the sum ifs with an S the sum range this time it's gonna be the loan items and then comma and then we're gonna say the criteria range is still the years and then the comma the criteria is one so if you see that one that's the criteria then you find that in the criteria range over here and you sum up the related number and the loan decrease range and it does it for us now I'd like to be able to copy that across so what I'm gonna do is delete these two and I'm gonna go to this first one again and say can I do some mixed reference stuff so I can just copy it across a little bit more easily the sum range that's gonna be this one I want that to move so I'm not gonna do anything to it the interest item this one is the criteria range I don't want that one to move so I'm gonna say F4 on the keyboard making it absolute and this one is the actual criteria I want it to be able to move down number wise but not to move to the right so I'm gonna put a dollar sign before the N making it mixed make it a mixed reference then I can copy this to the right to get to the same numbers I'm not gonna go to the loan balance cause it's a little different and so there it is and then I'm just gonna select those three and hit the double click on the fill handle or the fill button fill button little bit different calculation for the last one cause I wanna find the ending balance like right there so there's the ending balance so I gotta use the min formula so equals the min if and then we have the ifs min ifs and then the range this is the min range to loan balance and then comma the criteria range I'm sorry the min range and then the criteria range is this one that's right and then comma the criteria is the one so we're saying if you find that one in this range we want you to sum or take the min of the amount over here in this range so we hit okay so it takes the min balance right there at the end of the first year put my cursor on that one I'm gonna double click the fill handle button and then it copies it down we might wanna total this up copy it down total up equals the sum of these items totaling it up total it do you want it totaled I totally want it totaled total it then it should totally be total I'm gonna put some underline some fancy formatting select all this stuff and make that border blue so you might say there's an easier way to do that we could try to pivot table let's do the same thing cause I got a well formatted table over here I can't use the header cause I got these two rows but I could just use this header right there and just copy the whole thing I could do a pivot table and do the whole thing just way easy way easy so I'm gonna select this whole thing and it'll do it one more time and we'll do it pivot table style this time and I'll put that right underneath right here so I'm gonna go insert pivot table insert the pivot table I'm gonna put my cursor on existing worksheet and I wanna put it right there there's where it's gonna go right underneath the other one and okay pivot table so all I gotta do on the pivot table so I'm gonna pull the years down it tries to put them in the sum because it's a number but I wanna put it over here in the rows so it gives me the rows and then I'm not gonna take the months but I just want the payment the interest, the decrease and the balance and there it is it basically does it just right there for us I just need to format it now so I'm gonna hit the dropdowns here you could do this up here you could hit right click on the cell up top and then you wanna go to the pivot table options and hold on a second I'm gonna go to the cell up top and I'm gonna go to the value settings the value settings which you can also do here hitting the dropdown and then go to the value field settings or right click and go to the value field settings it's in the sum area so that's what I want I'm gonna go to the number format now and I'm gonna make them currency, bracketed remove the dollar sign, decimal, down, down and okay and okay I'm gonna do the same thing here I'm gonna right click I'm gonna say I'm not trying to format the cells here but I'm trying to, I wanna do it down here value field settings it's different so the sum is what we want number formatting it's gonna be the currency, brackets, dollar sign, gone, decimal, down, down let's do it again okay, okay right click I'm gonna say value field settings we're gonna say this is going to be the sum that looks good currency, brackets, dollar sign, gone, decimal, down, down okay, okay the last one's a little bit different so I'm gonna right click this one value field settings this time I don't want it to sum I want it to take the men the men and then the number formatting this part's the same currency, brackets, dollar sign, gone, decimal, down, down and okay and okay so you could adjust the labels if you wanted to at this point but I'm gonna leave them as is they also made the cells a little bit fat so we're gonna skinnerize the cell it fattened up the cells we're gonna skinnerize the cells so we're gonna put our cursor from N to R this is how diets should work when you skinnerize your belly they should just select them and then you just drag it like that and before we wrap this up note that we took we left out one last month down here so if I scroll all the way down you'll note we missed the last payment so if I scroll all the way down here we stopped it at $359 so I'm gonna actually add one more payment and so there we have it so that takes it down to zero so if I go back up that will affect this last month down here the last month is now at zero and the pivot table notice didn't refresh automatically so you gotta right click on it and refresh the pivot table and then it'll pick up so I'm gonna go on the pivot table here we're gonna go into the pivot table analysis and then in the data group we've got the change the data source so if I click on this item you could see that it goes down all the way down here but it leaves out that last row so it's stopping at the 362 so I could reset the whole thing or I could just say I want that to go down to 363 and then okay and then if I go back up top again we could check it out we could check it out and see what we have here so now it's refreshed and going down to zero now I'm gonna skinify these rows again making them a little bit more skinny and so there we have those and so it has now been adjusted this is future me talking to you from a future point in the problem here noting that we stopped this at the 359 instead of going down to the 360 so we left off that last payment which is gonna cause us a problem when we convert it to the pivot table so we'll fix it at the end of the practice problem so you can either include that last payment which will take your amortization down to zero which is usually the indication that we have done it properly or you can keep it at the 359 if you so choose and we'll see it when we have a problem with the pivot table and we'll use that to kind of refresh our pivot table at a future point.