 Personal finance practice problem using Excel Home Loan Comparison. 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. We're basically going to build this from a blank sheet here. But if you do have access, we've got the three tabs down below. We've got the example tab, the practice tab and the 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 populate that and build our tables on the right-hand side. We have three different loan scenarios that we're going to be constructing amortization tables and then summarizing that data. As we think through it, we want to think about the practical use of this. Clearly, if you are thinking about different kind of loan structures, you might be running different scenarios and you might be changing factors, for example, with the amount, the number of years and the rates, for example. You can do that with online software. If you were to look up loan calculators such as this one, you can then construct amortization tables, for example, in this format. But you have a little bit less structure and if you want to do a side-by-side comparison, you can change the data over here to run different scenarios, but you can't see them side-by-side quite as easily. However, you can create different tabs and toggle back and forth between different tabs and try to do a side-by-side in that format. But if you can do it in Excel, you have much more capacity to make those changes a little bit easier. So we could, for example, have this information on the left-hand side and then just run one loan scenario on the right that ties into this data on the left, and then we can change this data on the left to change the loan calculations on the right-hand side. Or we might want to have the side-by-side. I might want to run two different loan scenarios side-by-side so I can see them side-by-side. So we might have two different loan scenarios so that I could put the amortization tables right next to each other. And we're going to hide some cells to show how to do that. We can also summarize this data on a year-by-year basis as opposed to on a payment month-by-month basis, which is another way that it would be very nice to do some comparisons, and we'll show how to do those kind of comparisons as well, building our table. So on the second tab here, we've got some pre-formatted worksheets. So you can work on this one and it'll be a little bit easier to work with because you don't have to format too much of the Excel formatting. And then on the Plank tab, we're actually going to build this from scratch. So this is the scratch that we have on the left-hand side that we're going to build from. So if you don't have the Excel worksheet at all, you can just build this scratch on the left-hand side. What I would first do is select the entire worksheet, format the worksheet on your underlying formatting, which I would usually make currency. Don't do this if you have this worksheet, but currency. And then I would usually have the brackets and I usually start with no decimals. And then I would build and add any more information I need, including making a percent type of cell here, a percent type of cell here, and adding decimals when needed. So that's what we have this time. We've got a blank sheet. Everything is formatted thusly, except for this tab on the left-hand side. This is going to be our source data that we'll then use to pull over. Remember that you always want your source data somewhere separate than your calculations. You don't want to hard-code the calculations so that we can change the source data on the left-hand side. Now I'm going to do a quick recap of this source data so we can see it again, and then we'll calculate the payment. So I'm going to say, let's say we have the amount, which is going to be equal to for one, two, and three. I'm going to say three loan scenarios. I'll just call it the loan scenario at the 168. And then we've got the amount over here at the 223. And then we're going to have the amount at the 199. And then we're going to have the years. So let's put the years. And I'm going to say this is going to be equal to the 15 years. This is going to be equal to the 30 years. And this is going to be equal to the 20 years. Now note, if you have this information on the left, you might just put like another, you might just construct it on the left here. If you're building the table from scratch, I'm just reworking it so that we can calculate our payment calculation on the right-hand side. So then we're going to say that the rate is going to be the rate for the R3 scenarios is going to be equal to the 6.5. I'm going to make that into a percent by going to the home tab number group and percentifying it, which isn't a word. But I like it. I think it should be a word and I'm working on it. I'm working on making it a word. I'm going to make it happen. I make stuff happen. So then I'm going to go to F3. This is going to be equal to the 7%. And then we need to percentify that number group percentifying it percentified. And then we got to decimalize it percentified and decimalized. I didn't really need a decimal, but we'll put one there anyways. So it has the same number of decimals as the other one consistency. That's what we shoot for here. Number group percentifying this one and we'll add a decimal 6.5. Now we got the new thing, which is the payment. Now this is the payment calculation that you can make. And this will just give you the payment. We want more information than that. Typically we want the actual amortization table because we want the breakout between the interest and the principle and to see that fluctuation at least on a year by year basis. So the payment calculation, I do it this way. Instead of hitting equals, I hit negative. So it'll flip the sign and make a positive number. It's probably more proper to put the negative sign in the formula, but this to me is the fastest way to type it. So negative PMT shift 9. Then I've got the rate argument, which is simply going to be up one. That's going to be that 6.5%. But that's a yearly rate because we talk about rates in years. We don't talk about like monthly rates or daily rates because they'd be really small. And so we usually talk in years. So I got to divide that by 12 to get the monthly rate because we're going to have the payments in months, of course, like normal. So then comma, the number of periods, I'm going to say up up is going to be that 15 in E2. Once again, that's in years, though, I need to make months because I'm doing this on a monthly thing. So times 12, so 15 times 12. And then comma, the present value is going to be up, up, up that 168,000 168,000 is it and enter. So we got the payment here. I could add decimals, but I'm going to keep it rounded at the 1463. Let's do the same thing. Now you could copy this formula to the right, but let's calculate it again. Because we're practicing our payment calculation and then I'll copy it over just to kind of show that we could do that too. But let's practice it again. This is negative PMT shift nine rate up once that's a yearly rate. So I'm going to I'm going to divide it by 12 to get the monthly rate comma to the next argument. That's going to be the number of periods. Number of periods is 30 years, but I need months. So I'm going to take that multiply times 12 comma and then up up up. There's our present value the loan amount and enter. So there's the 1484. And then next we have negative PMT shift nine rate up once that's a yearly rate divided by 12 for the monthly rate comma to the next argument number of periods up to that's the years 20, but I need months so times 12 to get to the months comma up up. And there's the present value the loan amount to get us to the 1448. So notice these two we both got the 1448 even though we have kind of substantially different terms in terms of the years and the rate. Now I could copy this across this way. So if I delete this one, delete it. What? No, it's okay. It's okay. And so I'm going to then copy this across put my cursor on the fill handle and copy it across. Okay, it's back. It's back. And let's make this let's make this blue. We'll go to the font up top and make it blue and bordered blue and bordered. So there's our information. Let's build our amortization tables. I need a skinny because I don't like to you don't want to build anything right next to the table over here. So I'm going to copy the skinny by going to the home tab skinny column that is and then the format painter and paste the skinny for a skinny H skinny H. Okay, now we'll build our amortization table amortization table construction time. So what I'm going to do is now note that I'm going to have a couple headers that are going to be longer than like the width of a column that I would like. We could wrap the text, but that makes these cells fat and we don't want to do that because it kind of makes everything else look funny. So I'm just going to predict that I need two rows, two rows. So I'm going to say the first one's going to be the year, the year, the month and the payment and then tab and then we've got the interest. And then you could say principal decrease, but I say loan decrease because it's easier to spell, but I even spelled that wrong. I noticed that I spelled it loan like that a couple times. So I mean, I am just spelling's not good. Spelling's not my forte, my forte, my forte. So this is going to be loan balance balance. And then we're going to make this black and white. So we'll select this and make this a header thing. We're going to go to the home tab font group and make it black and white, black and white. And then let's center it alignment and center. And then we'll make this a little skinnier. Okay, so we're going to add up the months now. So there's 15 years. So 15 times 12 is 180. So I need to go down 180. So 180, it's like a 180 degree like turn around one to 180. We're going to do 180. So here we go. This is a super cool move. So 180 kick flip. So there we, it's going to be right there. 180. And then we'll alignment and center it. That looks good. And then we're going to do our years with our fancy trick, fancy trick on the years, which is a roundup roundup. Roundup little doggies, which are really cows. For some reason they call them dogs in the songs sometimes. Roundup the little doggies. Roundup. And then brackets. I'm going to go to the right, divide it by 12. And then, and then I got to say what category do they want to round. So I got to hit comma and the number of digits is going to be point one rounding it. And that tells it to round it to the nearest whole number, a whole number. Don't give me any half or partial numbers. So then we'll, I used to add some decimals to make sure it does it right. And then if I copy it down, see how it all, this is all in the year one that helps us when we do the pivot table for example. And so I'm going to copy that all the way down, all the way down to 180, throwing up, throwing out the 180 kick flip. And then I'll send, we're not really kicking or flipping anything, but still sounds better when you throw on the 180 kick flip. And then we'll do that just to 180 is pretty impressive in and of itself though for me, I feel like. But I'm going to then format this one. I'm going to put the zero up top because that'll help us out with the pivot table. I shouldn't have a blank cell for the pivot table because that kind of messes things up. So I'll put a zero up top this time. Haven't done that in prior presentations and I was not optimizing what I should be doing by not doing that. I'm going to do it. So now this is going to be the loan balance of we're going to start with this one, this first one, the 168. And then you might put zeros up here because again, you shouldn't really have any blank cells. If you're planning on making a pivot table out of it, which we will do. So I'll put some zeros up top just to just to fill the space. And then I'm going to say this is going to be the payment, which is equal to the payment we calculated here. So there is the payment. And then let's say F4 on the keyboard so we can copy that thing down, copy it on down. And then the interest is going to be equal to the prior loan balance times the interest rate, which is the 6.5. That is a yearly rate. So I got to divide it by 12. I need to divide it by 12. That's a key component. Don't forget that. Don't forget that piece. And this is going to be equal to the payment minus the interest, which is the loan decrease or the principal decrease. If you would like to call it that Lee, call it that Lee, that bus Lee. And then what this is going to be equal to the prior loan balance minus the 553. There it is. Now we should be able to copy this down, copy it down. But there's going to be a problem in Houston. Houston will have a problem. Houston, we haven't actually the problems wasn't in Houston. They just telling Houston that we have a problem. It was in space when they had a problem, but they were just telling Houston of the problem. Houston wasn't the one with the problem, apparently. In any case, the point is that this interest item is outside of our data in the in the data set. It's not in our table. So we got to make that an absolute reference so that when we copy it down, it'll it'll it won't go down. This cell won't go down. So I'm going to put my cursor in E3 and select F4. So there's a dollar sign before the E dollar sign before the three. You only need a mixed reference, but an absolute is easy and it works in this case. We don't need to do that here because everything's inside of our area that we're working on. It's not in the data set, nothing in the data set here. So those will copy down and move down as desired. So I'm going to select these four. We're going to double click. We don't have to drag the fill handle. We just got to double click the fill handle button. Call it a fill button. They don't call it a fill button. It's still a handle, but you're really using it like a button when you do that. There's a zero at the bottom, which indicates that we have done everything was done properly, hopefully. And so there it is. Let's make it blue and bordered blue and bordered blue and bordered, not the letters. If you don't have this blue, you go right here standard and it's that blue. That's the Excel is fun guy blue. Excel is fun guy blue. So there we have it. So so now this one, of course, will flex if I change the change anything here, like the rate to to like 8% or something, everything will change. And that's the point. Now, of course, if I change the number of years, it becomes an issue because it's only 180. So you'd have to extend it down a little bit, but everything will basically populate and adjust with that. Now we could also want the year by year table. So I'm going to build the year by year table to see like a side by side thing. So and we also might want to see it broken out on a year by year basis. So first of all, I'm just going to build this another table right next to these two with these two scenarios. So instead of like instead of adjusting my data back and forth between the scenarios, I got a side by side of the amortization tables. So let's do that. So I need another skinny over here, the zero. So I'm going to put my cursor on the H. I'm going to format paint it. I'm going to hit that over here with the skinny. And so I'm going to try to do this a bit faster. So I'm going to copy my headers. That'll make it faster. Just going to copy that. And I'm going to put that right here. And then I'm going to hide this data from H to N, putting my cursor on H, scrolling over to N, and then right click and hide. And then let's do this again. This is going to be how many periods we got 30 periods on this one. So this will be 30 times 12, which is 360. This is the full 360. We did the 180 last time, 180 kickflip. But now we're doing a full 360 rotating all the way around. We're going to select these three. We're going to go all the way down to get that full rotation, getting that full rotation going right back to the same stance that we were in when we started the maneuver. We started the maneuver. We're going right back all the way around 360 instead of 180. So there we go. And I'm going to go to the home tab alignment center it. And then that looks good. We'll do our roundup, roundup equals the roundup little doggies, which are actually cows. And then we're going to pick up that one to the right and then divide it by 12 because there's 12 months. And then comma, the number of digits is going to be 0.1, which says rounded up to the full, a whole number. Poor Favore, please. And then I'm just going to copy that down double clicking this time on the fill button, which is called the fill handle, but we're using it like a button. So it's a fill button in this case. Fill button populates it all the way down to to the 30 years just like that. No problem. And then I'm going to put a zero up top fill in the blank space, put some zeros here to fill the blank space. The loan balance is going to be the three, three or the two, two, three. The payment, which we calculated this needs to be centered. That looks off. Something's off. I can't stand it. Oh, my God, that's so much better. Okay. The payment then is going to be equal to the 1484. And then we're going to say F4 on it, dollar sign before the F and the four, you only need a mixed reference, but an absolute one works. And then the interest is going to be equal to the two, two, three times the 7% this time. That's the yearly rate. So we're going to divide it by 12 divided by 12. And this is outside my data set. So that needs to be absolutized, which isn't a word, but it's what we're making it one again. We make what we make words here. It could be a mixed reference, but we're using an absolute. It's easy to do. This one then is going to be equal to the payment minus the interest. That's the loan reduction or principal reduction. If you so choose that terminology, the two, two, three thousand minus the 183. We're going to select those four sales and use the fill button. We'll just double click on the fill button, the fill handle button. And then all the way down, it should go down to zero at the end of the day. It's a long day. It's a 30 year day. But at the end of the day, there it is at zero into the day, the end of the day. So we're going to go back up top. And then let's make this home tab font group blue, blue ties it and bracken. My is it blue ties and bracken. All right, let's do it again. Let's make these a little skinnier. They should be a little skinnier. I feel like those are way too thick for this stuff. There's too much blank space in there. Okay, I'm going to take the skinny. Oh, skinny. Oh, we're going to go to the home tab and format paint it and skinny up the V skinny up the V. And then we'll take the headers again. We'll put that up top headers and then do a hide hide from O to you. Oh, oh, and then hide. Okay, let's do the same thing. This one's going to go to 20 years, 20 years, 12 times 20 is 120. I could have done. I could do that in my head. I'm not so dependent on computers that I couldn't do that one in my head yet. But I'll get there. This is a one. This is a 240 240. So we'll select these three. We're going down to a 240. Then she lands sideways. If you do a 240, but then you like finish the rotation on the ground. 240 is dangerous. They were going for a 180 but over rotated to the 240. I'm getting mixed up. 240 concentrate constant. There it is. Got it. Got it. Alignment center it. And then we're going to say this is going to be equal to the roundup roundup rounded up. And then we're going to say this is going to be this one divided by 12 and then comma to the digits of point one saying to the whole number. That's what that means. And then we'll just copy that down. There's our years. We'll do double clicking on the fill button fill button. And then we're going to say to fill handle, but it's a button center in it. They didn't know it was actually going to be a button when they called it the handle. But then it became a button. And now it's called filled handle. Even though it's also a button doesn't really look like a handle at all. That's what happens in these crazy times telling you. So we got the one. We got the 11199 payment is going to be equal to the 1484. And then we're going to say F4 on the keyboard so we can copy that down. You only need a mixed reference, but the absolute one works. Interest is going to be the 199 times the rate of the 6.5. We're going to F4 that one because it's outside our data set then divided by 12 to get it down to the monthly amount. We got the loan decrease, which is going to be the amount of the payment minus the interest loan balance. Then it's going to be the prior balance minus the loan decrease or principal decrease. You could call it the principal balance at the end. If you want to call it principal stuff instead of loan stuff, then I'm going to select those four. Double click the fill button fill button. And so now it's down to zero at the bottom. We're going to go back up top and just do some formatation blue and bordered blue and bordered. There we have it. Now if I was to unhide from going from G to W now we've got these three unhide and we can kind of see them on a side by side basis, which is something that it's a little bit more difficult to do if you were jumping back and forth from tab to tab. Although you could do a tab by tab jump back on here if you're going to use it like if you're going to calculate this on software. But what you also can do in Excel is to make these into a year by year breakout, which could help you with what you feel like your equity might be from year to year as well as your interest. So I'm going to do that on a side by side basis. So I'm going to first I'm going to hide some cells to the right to the to the right of this one and we'll break it out on on just the equity. So I'm going to put my cursor on O and I'm going to scroll over to this side to AB right click and hide. And so now we're here. So now what I'm going to do is I'm going to just try to break this out in a year by year breakout. So I'm going to I need another skinny in the AC. So I'm going to put my skinny here skinny H home tab clipboards paint it and skinny AC skinny. You can't even see the AC because the ASCII doesn't fit in the skinny because it's so skinny. But that's OK. And so then what we're going to do is take the same headers. These headers right here. Let's take those. I'm going to take those and then I'm going to delete. I'm going to delete the months. We don't need the months. Now we'll do this a couple different ways. I'll do it with a pivot table and we'll do it with formulas. So we're going to delete this and we'll just make a few of these on a side by side basis. So I need years from year one to and bring it on down to this one's. What was the first one? It was like 15 years 15 years fitting 15 15 fitting 15 fitting. OK, pronunciation, pronunciation, the loan balance. So then what I'm going to do is I'm going to say some if calculations. Then this is our some if calculations equals the sum. We would like you to some if and then brackets the range criteria. I want you to look inside this range. I'm going to select the whole column. And so be careful if there was something underneath it. You would want to not select the whole column but just the actual range. But I'm going to select the whole column because it's easier. And then comma and then we've got the criteria. The criteria is this one. So I'm going to say if you find this one in that column, then comma some the related range, which is the payment range. This range right there where my home is my home's on that range my home's on that range home on the range. And then we're going to copy that down. And so that that looks good. That looks good. I could have done that by just multiplying the payment out times 12 though. But I'm going to do it over here with the interest where she gets more interesting. So I'm going to say some it if some if brackets the range. We want you to take a look at that range. I range and then comma and then the criteria is that one again and then comma. So if you see that one in that area, then sum up the related item in this range, the interest range where my home is my home's on the interest range. Home on the range. And then I'm going to copy that down. And then so then if I sum these down to check it double check. There's the 10 7 19 amazing. Amazing. Let's do it again equals the sum if and then brackets the range the I range and then comma. And then the criteria is that one and then comma and then the sum range is now the loan decrease or principal decrease, whatever you want to call it. There it is. And if I if I add these up to check it double time double checking 6843 there it is. Now you could do this a little bit easier. Let's do it one more time by copying this across until you're going to delete it after all that work. It's okay because I'm going to do it faster this time. So I'm going to copy this across this one this I when I copy it to the right. I don't want this range to move. So I'm going to f for it f for making an absolute dollar signs the interest range or this one is the payment range. I do want that one to the move to the right. So I'm not going to do any f for stuff on actually know this one is the criteria that criteria I wanted to move down. This is the tricky mixed reference but I don't want it to move to the right. So that means that I'm going to put a dollar sign here but not there. It's not absolute but mixed. That's where the fanciness comes in. That means you could copy it to the right and down. And then on the this range I want that to move to the right to L next time. So I'll leave that as is so I could copy it down. It looks like it's doing the right thing. I could copy it to the right not to the loan balance but it looks like it's doing the right thing. It's doing the right thing. That's what we do here. We do the right thing. That's what we do. I'm always trying to do the right thing. That's what they that's what I that's how I was taught. So there we go. So now we're going to say there it is. That looks good. Okay. So now let's do the minimum loan balance here. This is going to be equal. This one's a little bit different. We want to take the minimum balance. So the minimum balance equals the men if little bit different on the on the criteria ordering here but we're just going to take the men range which is going to be the men. The men range which is this one first and then comma and then it wants the criteria range which is going to be this one and then comma and then it wants the criteria which is this one. So same kind of thing but they a little bit different ordering on there. And so that took the lowest one which is right there. I think I was right. I didn't really read it but I'm pretty sure it was right. And so then I'm going to copy it down with the fill button fill button. So there we go. Now you could also do that same thing with a pivot table which is a little faster. There's pros and cons to it. Notice that I can't pick up these two things for the header because I got two rows. That's the problem with with having these two rows and a header instead of wrapping it. So there's pros and cons of that. So I'm just going to use this as the header. That's okay. Copy that all the way down. I'm just going to do a pivot table this time pivot table. Same thing different way different way. And I'm going to put that right underneath here. So I'm going to say insert a pivot table pivot table. And I'm going to put it in the existing area. I'm going to put it right there. That's where I want it. Let's put it. Let's put it down one. I thought she wanted it. No, I want it. I want it there. So I'm going to say okay. And then I'm going to add my criteria the year. I got to pull this to the left pull that to the left. And then the other I don't want the months payment interest decrease. There we go. But now it's formatted ugly. That's so that's so messy looking. So what we do is we fix it up. We go into each of these. I'm going to hit the drop down value. Let's number format and make that currency brackets. Remove the dollar sign and decimals and okay and way better way better. Do it again. Do it. Do it again. Do it for the next one. So that one's not ugly either. So we're going to make brackets red and get rid of the decimals on that one. So much better. Okay. Two more times. The last one's a little bit tricky. A little bit more trickier. So we're going to say just doing the same thing here. Ultra vase. Ultra vase. Lo mismo cosa. And lastly this one. This one's the tricky one. We want to make that a min. Not a sum. A min. Not a sum. And then the number format. This part's the same. Lo mismo. And we're going to say get those. Get that out of here. And there we got it. So they're good. I'm going to skinny up the columns a bit. Skinning them up. Skinning up. Now these notice that these down here. Are rounded so so you know I could wrap the text. Now I might want a total column. The reason I did this is I might want like a total down here. We've got the totals right. So that gives us the grand total of the payments and the. And the interest and the loan balance. So that we could do that here. We could do that total here if we wanted to. Summing this up. It's not like we did. It's not like we. Couldn't do that here and then just copy it across. So that could be useful. A useful tool. And let's make this blue and bordered. So there we have it. So that's something you can't really see on the other. On the other. You know if you do it over here quite as easily. And we can update these now let's do it. Let's do the other ones for the other table. So I'm going to do it again. I'm going to unhide these cells. I'm going to put my cursor from N to 80. Nad. Right click my. I had a car that was had a license plate of 658 Nad. 658 Nad. Okay. Unhide. No one cares about your car. I love that car. It was a Volkswagen bug. I just told you no one cares. Okay. So now we're going to hide from the. On over. To a H. Right click and hide. And then we'll hide. Do I need to hide these? I don't need to hide that. I'll just keep it right here. We just need to add a skinny. I'm going to do this faster. We're running just going way long. This is way long time frame. Let's go to the O here. I'm going to copy the skinny O and put that on the AI. The AI now it's just an A because you can't see the eye. And then I'm going to take the headers. We're going to put that up top copying and pasting. I'm going to get rid of the months because I don't need the months on this one. Months are not what we're looking at here. Get rid of it. Years are going from one. How many years was it again? 30. This is the long one. This is the long one. So here we go. One, two, that gives us R. And then I'll take the fill handle and I'll get a good grip on that handle with my pointer finger. And I'll drag it down. Whoa, that's way to sick. I'm not going 360. I'm just going down to 30. Don't get carried away. Don't get carried away. All right. So then we're going to go to alignment and center. And then we'll do the same thing here. So we're going to do some if equals to some if summit. If I'll tell you, I'll tell you when you sum it. This is how it works. You're going to take this range right there, the P range and then comma, the criteria is one. If you see ones in there, then comma, you're going to sum up some range, which is the payment range. That's what I want you to do. And so let's let's actually copy this across. So how can I copy it across the PP? I don't want that to move. So I'm going to say F4 on the keyboard. This one's the tricky one. I need the mixed reference that one. I don't want it to move to the right. I wanted to move down though. So I got to put a dollar sign before the lettering, but not before the number. That's the trickiness. And then the RR is the resting and relaxation. You can just you can just sit there resting relaxation RR and then enter. So then we're going to say copy this to the right. Does it still work? Does it do what it's supposed to do? It does. It does do what it's supposed to do just the way we had planned that it would do what it was supposed to. How ironic. I'm going to copy that down with a fill button. So I think that works. And then we got the slightly different calculation equals the sum. Now this is the men if the men if and that one's for the last one where we're going to take. We're going to take this time the men range men range. There's men on the range and then comma. And then we've got the criteria criteria range, which is this one and then comma. And then we got the criteria, which is that number one right there. Boom. Bam. And there it is. Let's copy that down. So that's the men 20 to 20, which should tie out to the 227 35. Let's put a total down here this time. Total sum it up. Sum it up. Little darling. It's been okay. Stop. This one doesn't need to be some that doesn't make any sense. And then we'll make that blue and bordered home tab font group border blue, border blue, border blue. Let's make a pivot table out of it. Same thing. I can't copy the header up here just from here on down from there down. And we'll just construct a pivot table just like we did last time, which is doing the same thing in a different format. So you can choose whichever one's best for use, choose what's best for use. And so I'm going to say insert pivot. And then we're going to put that in an existing worksheet right there, right there. That's where we want it. I'm not changing around this time. I know where I want it. I know. And then I'm going to pull this to the left. I know where I'm, I know what I'm doing. So we're going to then adjust the formatting on these same kind of layout. Let's just adjust the formatting number format. And we want currency brackets. None. Decimals gone. Boom. Bam. Better. Boom. Bam. Better it is. And then do that here. Format. Currency. Brackets. Dollar sign gone. Decimal down down. Boom. Bam. Better. And again. Again. Do it again. We're going to say currency brackets. Dollar sign gone. Decimal down down. Boom. Bam. Better. Lastly we got the bit of a tricky one down here. We need the men. Then the number. And then currency brackets. Dollar sign gone. Down. Down. Decimal. Okay. Okay. And we have it completed. I'm going to make it skinnier. So there's another way. We've basically did the same thing in another format so we can see a side by side now between these two. We could do a difference if we wanted to on a year by year breakout between the two scenarios for interest in the loan decrease and so on. And then let's do it one more time. Uno, vase, moss, pour, favor. I'm going to unhide. I'm not sure where I need to unhide. So I'm just going to select the whole thing and unhide. One more round. Why? Because I didn't hear no bell. I didn't hear. I only stop when bell when there's like a bell that happens. So I'm going to make this little skinnier. So then on this last one we're going to do the same thing. So I'm going to I'm going to hide the stuff to the right again. Hiding to the right to a in right click and hide. And then we'll do this one more one more round. This skinny right there skinny V. Home tab clipboard paintbrush it and the skinny AO. And then I'm going to copy the headers again, paste that up top. We're running long on the time. We're going to speed this up but not but do it precisely. So precisely and accurately and and fastly but not too fast. So people get upset. So this is going to be years one, two. We're going to this one is going to be going down to 20 years. 20 years you'll recall. So we're going to take that on down to 20. Don't get carried away. We're not doing an amortization table. We're just going down to 20. Just going down to 20. So we're going to go home tab and then alignment and center payments then are going to be. So we've got some if and then brackets. We're going to take the range which is going to be this range. I'm going to select all of W. I'm going to select F4 this time F4 because I want to make that absolute as we go comma. The criteria is going to be that number one. That's the tricky one. I want to make a mixed reference because I want it not to move to the right dollars on before the letters. But I do want to move down at no dollars on before the number comma and then the sum range in this case is going to be the payments. And so that and we don't need an absolute reference on the payments. Why why you might ask we don't need one because we're going to we want that to move to the right. So I'm going to say enter. And so there we got it. If I move that to the right and copy it over then looks good looks good. We can copy that down selecting these double clicking on the fill button and then the loan the loan is going to be equal to the men. If men if it's with an S and then the men range is going to be this one a B comma the men criteria range is going to be over here on the years and then comma and then the criteria is going to be the one again enter. Let's copy it down with the fill button fill handle button and then total it down here total and sum up total it and some it. Total some total some copied across this is going way long. I don't need a total over here that don't make no sense. I told you that I told you that last time. So I'm going to go home tab font group brackets and blue. Let's make our make our our our pivot table now do it fast because you're this is taken too long people got things to do. And you're expected them to go through this. We already did this. I know how to do it. So let's take this whole thing and go to the insert pivot table and then existing and we'll put that right there. That's where we want it and OK. I'm going to take the years down copy that to the left and then payment interest decrease balance. And then we're going to format this thing format the values number formatting currency brackets dollar sign gone decimal down down OK OK. Next one value field number format currency brackets dollar sign down down down decimal OK. Next one value field number currency brackets dollar sign gone down down decimal OK OK. One more time but a little bit more tricky on the last one because we don't want the sum we want them in number formatting currency brackets dollar sign gone down down decimal OK OK. So I think I got this everything correctly. I'm going to make a little more skinny skinny up top weight. Now I made that one wide. I don't want to make that wide. I want to make these skinny. That's the opposite thing. OK. So now I'm going to unhide everything unhide everything right click and unhide. And so now you can see this one's to do. I don't need this down here. So now you can see now you got these side by side kind of things which you can't. So now I can adjust the data over here on a side by side scenario. I could see the side by side in terms of the amortization tables and possibly more useful for longer projections. I could see the side by side and make these little skin here on a year by year breakout and I might even then get more complex and look at the differences make another column taking out the differences of each scenario on a year by year. Possibly then doing projections for taxes and you know what the equity value is going to be and so on. Given that information so you could do that once you get it set up. It's a lot more information that you could pull and run scenarios than just using the online table. It's easy to do the online table quickly but then you can't really tie it into anything else. You're always kind of bouncing back and forth to all these different resources and then trying to tie them together. If you can network your worksheets together for whatever you're working on in such a way that once it's set up you can just change your data and then do your side by side comparisons look at different scenarios that way. That's the better way to go in the long run typically.