Mastering Excel. . . . For Beginners: #2 Loan Payment Calculator

1. Relative and Absolute: Hello again. Thanks for coming back today. We're gonna learn more about Excel. Obviously, this is an Excel class, but specifically we're gonna learn about relative and absolute references and how that's gonna help us. As we learn more about functions, we're gonna learn a couple of new functions that if statement and statement we're gonna use those are class example. We're gonna do a car loan calculator, kind of a simple one. And then for the ending class project, we're gonna do a more in depth loan payment calculator that can be used for car loans or for mortgage, or really any type of load that you could get. So keep on watching and you'll learn everything that you need to get these difference for sheets created. Alright. In this example, we're going to learn the difference between relative and absolute references. So as we learned last time and we have to, you know, we have values in ourselves, and if we want t put a formula or have a cell reference other cells, we just type equals, and then we can click on them and do some sort of operation and get some sort of result thes the J three. Our battleship values are references, so just as a review, that's that's what references are. Now we're gonna learn about the two different types of references, relative and absolute. A relative Restaurants, as its name implies, is relative to the cell that were writing in. So this cell, even though it's address, is J three and J four Excel is reading this like, hey, multiply the one that's two above the one I'm writing in by the one that's one above the run. I'm the one I'm writing in. So let's learn more about that through this car loan down payment estimator. So if we want to figure out what 20% is for each of these car values for our down payment, we can say equals 20% times our car value, and it will give us 20% of that, which is great. We are referencing the cell that is one to the right and three above the cell were writing in, and we're referencing the one that is just to the left of the seller. Writing in now, we learned last time that we can drag formulas and patterns down and excel. The same applies for formulas so I can drag and click on the little square and drag it all the way down, and Excel will automatically update. So we have F three and D six, F four and D seven five and D A is just adding one Teoh each of those cell addresses because we just drag it down. Now let's see if that makes sense. It'll So for this one, Yeah, that's right. That's we did that on ourselves. So that makes sense. What about this one? First of all, we're we're seeing an issue here, So we dragged it. It moved down its relative to the seller writing in. It's referencing the sellers one to the right and three above, the one we're writing in, which is not the one we want to be using. This one is good, though We do want to reference the cell that is just to the left of the one or writing in. So we need to introduce what's called an absolute reference, which Excel reads as this is the reference, no matter what. So when you say I want to multiply this by the down payment percentage for every single one of these we need to make this one in absolute reference. And the way we do that, we come here to me, clicking are, um, Formula Bar. And then we're gonna have our cursor touching. It just needs to touch the reference. Whether it's the beginning, the middle or the end, it doesn't matter. So that's the right one f three. And now what we're gonna do to make it an absolute reference is we're gonna put a dollar sign f dollar sign three. And that makes it absolute another way that we can do that as we can just click F four on our keyboard. And it will do that ourselves it by itself. So the dollar science means that it's an absolute reference so that when we click on this and we now drag this down, it will update all of these. And if we click in here and look at it, it's always referencing F three, F three, F three and d nine. It's stuck there, and this one's moving as we drag it down. So that's the difference between a relative and an absolute reference, and we're gonna need to get really good at this, because we might have some equations that air, you know, referencing quite a few different cells. And there may be one or two that we don't want to move. We want to be absolute references. And there might be 45 or six that we do want to move or the other way around. So we need to be good at understanding, you know? All right, this one I want relative, this one. I want a reference and being able to see that, um, in our formulas. And I mean, when we think about it and makes sense, you know, I'm putting a formula in right here, and I I wanted to be this times, whatever is to the left of where I'm writing every single time. So I absolutely want to multiply at this. And I relatively wanted to be the one to the left of the one I'm writing in 2. IF and AND Functions (1): all right. In this video, we're going to practice using relative and absolute references as well as we're going to learn some new functions. If you remember before functions are things like some and then you know, adding up a bunch of stuff and I'll add it up for you, or like, average or maximum or minimum, or all these other functions that are out there. Um, but today in this one, we're going to learn about the if function as well as the end function. For now, the if function basically said, we're telling Excel, right? If this something is true, I want you to do this. But if it's not true, then do something else. So, for example, if I want to find out if you know this cell is over 508 $100 over here, I mean to say equals type if open parentheses. And here's our prompts for the if function, the 1st 1 is this logical test, which basically means what do you want to be true? What do you testing to see if it's true or false or not? And what we want to know is if this value is greater than we have our alligator mouth greater than sign. We want it off. It's greater than $800. That's our test. Simple is that if that's greater than 800 so we're done with that. We have our tests were gonna press a comma, and now it's gonna say, All right, if that's true, what do you want this cell that you're writing in to be? Well, if it's true that I wanted to be, um, a one if it's not well, then we pursue Common that it says right, What do you want it to be if its faults and I'll put a zero cool presenter? Look at that. This is greater than $800 I made it a one. If I change this in, say, 600 changed it to a zero because it changed toe faults. I think that this is no longer true. G six is not greater than 100 so it says No, it's not true, so I'm going to do this one. Great. So that's the if function, we're gonna learn about the and function later because we're gonna use it a little bit later, and I'll make more sense later So what we have here is a spreadsheet of all the projects that we completed for the year. What? Or maybe your small business, Maybe your accountant somewhere, something. Anyways, we have we have some projects will be completed, and they all had a complete date. And we have an invoice amount. So what, we want a nose? Hey, how many of these projects were completed in the first quarter? Second quarter, third quarter, fourth quarter, and how many of them were over $700? So we're gonna use the if function to figure this out because we want to know if that's the key word. If this date falls, you know, somewhere in these, depending on which one we're doing for this 1st 1 we want to know if are complete date is less than this date or happened before the state, because Excel views this even though it's viewing it as a date, it's really a number to excel. And when it compares to of, um, we want to know if this one is less than this one, So that's what we're gonna do. So we're going to say equals if open our parentheses and all right, What is our logical test? What do we want to find out if it's true or not? Well, we want to know if this this date is less than less than sign that last day in the first quarter. Great, that's a water test, said our prison comma. Now, if that's true, what do we want to happen? Well, we just want to keep a tally. So we'll just say, if it's true, make it a one comma. If it's faults, we'll make it a zero. Cool. And then we're gonna close that. Parentheses. Press center. All right, work for that one. Now we're gonna have ruined in the last video. We're gonna drag this down, but it's gonna change. Wait. Need to go see about relative and absolute references in here. So this one as we drag this down is gonna move, which is what we want to happen. We all what? We want it to reference a new lying each time we dragged the sell down. But this one, we don't want that one. Don't move. So we want to make this an absolute reference because we always want to compare these two this 1st 1 while they were doing the first quarter statements. So we're gonna come in here to the F 22. I'm gonna hit F four. What? We're gonna type in dollar sign F Dollar sign 22 to make that an absolute reference. Great. Now let's drag it down. And it looks like we have one that happened before March in the first quarter, which is Looks like it's true. That's the only one that happened before March 31st. Great, that worked. Let's move on to the second quarter. Now, this is going to get a little bit tricky because we can't simply say, Hey, is this date less than this one? Because we also we want to make sure that it's actually greater than this one because of its less than this. That would be the first quarter. So if we simply say, if it's less than this one, we're going to get wrong results. This one, this value is also going to be showing up as a one, so we need to figure out a way to have two things be true. We wanted to be greater than this, but less than this. And this is this is where the and function comes in the and function says that all the all of these things have to be true in order for the whole thing to be true. If one of them is false, any of them, the whole thing is false. All of them have to be true. So let's see how what that looks like. So we're gonna say if we're still doing if we still want to find out if this date is between these two. So that's why we're starting out with If so, we opened it right? What is our logical test? All right. We have two things that need to be true now. We needed to be greater than this, but less than this. So now we're gonna dio and open parentheses. So we have a function within a function. So now it's prompting us for the and function inputs. All right, so what's the first thing? The first logical test? The first thing that we need to be true. What? We want this to be greater than this. And we can recognize beforehand that this is gonna be an absolute reference, just like we did over here. We don't We always want to be referencing this one. So let's just go ahead right now and hit at four and make that absolute, so don't have to do it later. All right? That's our first test. What's the second thing? That needs to be true? Logic test number two. Well, we want to compare the same date, and it needs to be less than this state. And again, we don't want this one to be absolutely Iskan stay relative. Because as we drag it down, we want that one to move. But we do want this one to be absolute, because we always want a reference that one. So again, we're gonna hit F four and make that absolute reference. All right, Do we have any other arguments that we need to be true? Nope. We just have those two. We just want to find out if it's between them. So we're gonna close the and so we just close to the and function, not the if function we're still now in the If function were in the logical test face or logical test was this. We wanted to know if this and this were true. Now that we have that, we can press coma. And what do we want to happen if it's true? We wanted to be a one. If it's false, we wanted to be a zero. All right? We already checked absolute and relative references, so we don't need to worry about that now so we can press enter, and we can drag this down. And let's look at our results. We have one right here. Yup. Looks like that's the only one that's between there. So it worked. Great. Now go ahead. And, um, either re watch this video so you can solidify this if function and the and function concept, or continue on to the next video, where we're gonna continue to go a little bit faster through these. We're gonna do the same thing for third and fourth quarter. Or you can do it by yourself and try it on your own. Um, and then check in the next video to see if you did it right 3. IF and AND Functions (2): all right. Hopefully, you had a chance to either re wash the previous video to solidify the IFT and the and function, or you had a chance to positive try doing the third and fourth quarter on your own. And we're gonna check those to see if the right or were you doing for the first time if you re watch the video together. So we're into the same thing we did with this one or unused if in the end function, because we have two things that need to be true someone saying equals if and what two things are they We want this date to be between these two dates, we need to to be greater than this, but less than this. So we need to use the end. What's the first thing we need to be true this date to be greater than this one? Now we can first. Either that's going to be an absolute reference over is gonna hit F four to make it absolute comma. What's our next? A logical statement. We want this to be less than this. This is also gonna be an absolute reference. So let's make that absolute F four great running clothes are and function. Now we have our logical tests for our if we have common. If that's true, both of those were true. We wanted to you one, if their faults. Zero great and we'll slide it down, all right. Looks like that's right. We have one. That's between those dates. Let's go into the fourth quarter. Same thing. If we're a logical tests, we have two things that need to be true. So if and first thing is that this is greater than our third quarter date and that's gonna be absolute, and then we have this being less than our fourth quarter date, which is absolute. So we're done with that comma. If it's true, we want a one. If it's false, we want a zero great will slide that down check to see if it's true. So we have 12345 and we have 1234 home. What's going on here? This one's not showing up, and maybe you can see it's because it's the same date as the date were referencing. And in a formula we're saying to make this a one. If this date is less than this somehow we need to also add if it's equal to whom. What we could either just say, Well, what we can dio I think so has a cool fix for this. We can say less than or equal to less than or equal Teoh. So now it'll check if this is less than or equal to that, and that's going to fix our problem cause that's what we want to do. Technically, we should have applied it to all of these cause it could have been less than or equal to these, Um, but this helps us to learn this new trick. So if we present her and now we drag this new one down, look at that. We now have five. Let's move over to the 700 column, So we dio if and we want to know if the invoice amount is greater than 700 and we wanted to be one. If it's true, zero, If its faults, we'll drag it down. We have four and four, so that worked out Great. Now it's total them up, so we have the some we're going to drag and highlight that. So this is actually called an array because it's referencing a group of cells that's called an array H four to H 11. Technically, you could also using Array and do all of those you could add all of that up. That's an array, but we only want to add up this. And as we learned with relative and absolute, it also applies to a raise. This is a relative array. So if we drag this over here, it's also gonna drag our array that way, too, and add up everything that we want. We're that That's great. So we've learned if statements if functions and functions the absolute and relative references and we've practiced it on, we have this cool little spreadsheet that you can use if you want. Um, I can't I can't stay enough how important these functions are to understand, if at all, you don't understand them. Go back and watch so that you can solidify this or keep practicing on your own to so that you can master when to use an F function. And you can master the inputs for that because it we use these a lot. If function is used a lot as well as the and function so practice it. Re watch these videos so that you can solidify it. Eso you could be a master of these functions. 4. Car Loan Project (1): All right, now we're gonna do our class project, and that is going to be a car loan payment spreadsheet. It could also be like a home loan mortgage spreadsheet or really any loan that you can get . Our inputs are going to be the price of whatever buying our down payment, our interest rate, what are actual loan amount is, And then that's gonna spit out what our monthly payment would be. How much we're paying interest, Principal. We're gonna have an option to put a little bit extra to principle, and then we'll have the remaining balance of our alone. By doing this, we're gonna be able to see over the life of the loan. We're gonna be able to add up how much interest we're paying. Um, how much total we're paying so that we can kind of figure out our cash flow and figure out where our money is going. And maybe if you know, let's see. What if I put $100 lecture to principal every month? How much interest on my going to save things like that? We're gonna be able to figure out. So here's a blank one that I've made. Go ahead and pause this video on. Make one of these yourself. It doesn't need to look exactly like this, but we need to have the same information right here, as well as a list of rose from one to however longer loan is in months. We're doing a car loans that's 1 to 60. If you're doing a mortgage, you would want to go one to, like, 360 cause that's a 30 year loaned in months. So go ahead and do that and then come back and will continue. All right, now you've done that. Let's go ahead and figure out what's going on. So we have our payment, actress and input or the press of whatever we're buying with our down payment. That's another input. Our interest rate is another input. Here. We're gonna have an equation. It's gonna be pretty simple. Our total amount of our loan is just going to be the price of our car minus our down payment. She's easy in the number of years, which is an input, and then over here we want to figure how many months that is. So we're going to do the years times 12 so we can get months which we're going to use for a function right here. We're going to learn a new function. It's called the payment function. It's used for loans. So we're gonna right, equals P M t. And then we're gonna have all these prompts. Now, these ones in brackets are optional when they can do different things. But we don't have to worry about them in order to use this. So we can We're not gonna worry about those at all. We're gonna worry about these 1st 3 that aren't in brackets. The 1st 1 is rate, which is our interest rate. Now, be careful. This is an annual interest rate and we're paying per month. So we need to figure out how much interest for paying for one month. So we're gonna do the interest rate divided by 12 and that's our rate can common an end, per that's the number of periods. How many payments are remaking. We're making payments once a month, and they're 60 months. So that's what our emperor is going. Click on that. The last argument is PV present value, just the value of our loan. So this is our monthly payment it's showing up in parentheses and red because its a negative value. Um, it's an accounting formatting format of that signifies that we're losing money. Money is going out. I don't want to see it like that. So I typically just put a negative sign and from to the PMT to make it just a pause. I like that visually better. Um, but you can do whatever you want. Now we want toe the monthly payments. Not gonna change throughout the whole life of our of our loan. So we're gonna be able to drag this all the way down. But we've got some values up here that we want to make absolute, because if we just drag it down right now, this is all going to drag down as well. So let's make thes absolute f four F four F four. And here's a cool trick. Instead of clicking this and dragging it all the way down, I can double click on it and it will populate it for me. It recognizes that this is the end of my list, and it brings it all the way down here. Great. Now let's figure out how much interest we're paying that amount is going to be equal to our loan amount. Times are interest rate. That's an annual interest rate. We need to get that in monthly. So we're gonna divided by 12. That's how much interest we're paying now. We can't just drag this one down because every month the remaining balance of our loan is gonna be changing. So we're gonna have to do something else for the rest of these lines, which will do in a minute, our principles going pretty easy. That's just whatever. We're not paying to interest. So ready to equals our loan amount or our monthly payment minus our interest. That's how much is going to our principal. Our extra two principal is just a nen put that we're gonna maybe put in one month, maybe not another month. So we don't put anything in there our remaining balance, though. Now, this is gonna get a little bit tricky if we put something in here. We want to subtract both of these from our total loan amount. If we don't, we just want to subtract principle from this amount. So what does that sound like? Sounds like we need. And if function so we'll do if Now, What kind of test do we want to to dio? Well, if there's nothing in this, then we just want to do these two. If there is something in this, we want to do these two minus this. So let's say if this cell is equal to zero and excel, if there's nothing in the cell like if we don't write anything in it, Excel views it as a zero, so we don't have to put anything for this to be true. So if this cell is equal to zero, that's our argument. All right, What we want to happen if this is zero or blank? Well, we want to do our loan amount minus our principal. All right, what if this is fault? What if this isn't equal to zero? What if we put a value in there? We want to do our loan amount minus the principle minus are extra to principle. There we go. That's our equation. And again, we can't drag this down because currently it's referencing this remaining balance our loan amount. But next month, we're gonna want a reference this remaining balance, so we're gonna have to write a different equation for this next line. Luckily, once we get this line in, we are going to be able to copy everything down and everything will go much quicker. 5. Car Loan Project (1): Now let's move on to the next line so that we can Now we can drag everything down. So we've already done this. All right, well, now, let's calculate our new interest balance for months to so that's gonna be the value of our loan right now, which is now our remaining balance multiplied by our interest rate. But we're doing it per month is an annual interest rate per month and divide by 12. That's our interest rate for this month. What's our principal? That's still going to be our payment, minus our interest. All right, Now, what is our, if statement going to be here? All right. It's still gonna be the same logical test. I think if this is equal to zero, all right, what do we want to do if that's equal to zero? We didn't put anything in there. We're going to do the remaining balance minus our principal. See, how are paying a little bit more to principle. That's because the remaining balance of our loan is a little bit less and we're paying a little bit less interest. So our value if we don't put anything in here, we're doing our remaining balance minus our principal. If we do put something in here, we want to do our remaining balance minus the principal, minus our extra to principle. There we go. Let's go ahead and make that a dollar amount. Great. Now let's see if we will be able to just drag these down. So let's look at this. All right. For the next month, what is my value? Gonna What is my formula going to be? Well, it's gonna be this remaining balance. So if I just dragged this down, this is gonna move down with it. So that's what we do want. We want this to move as we drag this down, so we're not going to do anything. Toe M 10 m 10 is going to stay the same. That's gonna be relative. What about M four? Okay, we do want to always multiply it by that. So that's gonna be a relative and absolute reference. So we'll make that one absolute and now leads DoubleClick and it's gonna go down. This is showing up as zero. Let's check it out because we don't have anything there yet, so we don't freak out yet. Hasn't broken yet. We still have toe drag all these down and then we'll see if it's broken or not. All right, let's look and see if we can drag this down. So this is always doing our payment amount minus our interest amount. So if we drag this down, if I drag it down one, it'll move to that one. And that one, which is what we want. So let's go ahead and drag that down. We'll double click. Don't go down. Don't freak out yet. These are looking like weird. This is looking weird. Let's get to this first. All right, this is going to be the big one. Let's see if we what we needed to do to be able to drag this down. All right, what do we want to move? And what do we want to stay the same. All right. We want to reference the remaining the previous remaining balance. So if I drag this down, the previous remaining balance is also going to drag down with it, So that's good. We want a reference the principal amount on the same line. So if I drag this down, this is gonna move with it. So that's good. We wanted we want to keep that relative. Same with the extra two principal. We want to keep that moving, so we don't need to do anything to that equation either. And we can double click doesn't go down. All right, let's kind of see what we're looking at here. So we have, ah, monthly payment of 2 39 interest that's gradually decreasing to basically zero at the end. We have a principle that's gradually increasing to almost the same as our monthly payment. And then this is going to be the big sign of whether you did this. Right or not, we have our balance, and it ends at zero. If it doesn't end it zero. That means you did something wrong. That means you need to go back. Maybe you forgot to put that your interest rate. We have an annual interest rate we needed invited by 12. Or maybe you I don't go back and look rewatched the video to see where you went wrong. Um, and hopefully you can get your final balance down to zero. So now let's see how much, um, total payment would paid total equals. All right, So that's gonna be we know this one right that's gonna be equal to the sum of all of those that array. So we paid in total $14,000 which isn't too much more than the loan amount. It's like $1000 more, so that's not too bad. Let's see how much total interest we paid that's going to be equal to the sum of this. Alright, paid $1300 an interest. That's not bad. That's not awful. If you do this with the house and you have 360 months and have a huge own him out, this amount is basically going to be the same amount that you paid for your house. I didn't really care much principle we paid. We're not gonna add that up or the remaining balance. That would be some crazy number that wouldn't really mean anything to us. But cool. We were able to figure out how much we paid in total, how much we paid in interest. Let's figure out if our extra two principal thing is working. So let's say this month we paid 500 extra principle. So once we hit enter, what should happen is over here. So currently we have $12,409. This value should change Teoh $11,909. So let's see if that happens. 11,909. Sweet. It worked. Let's go back down here and see what are interesting happened. All right, this is where, um our knowledge is a little bit limited. So when we did the some function, we just added up all of these. Now that we've messed with our ending balance and we now paid off are alone a little bit sooner. We paid our loan off two months sooner. See how these air Negative. That means we have now reached zero. We reached zero on month for 58. We paid off their loan two months earlier by paying 500 extra dollars to principle, which is cool. Um, but now, when we try to add up our total and interest, it's including these in our some. So if we wanted to get an accurate representation of that, we would need to highlight are a highlight Our equation showing us what are Ray is and we're going to grab this corner and move it up to only include the A positive amount. So that's how much we actually paid in interest. Same thing with this, Really? We paid off our alone that quick, so we really only paid that much. So now you have a cool car loan estimator so that you can meet before you buy a house. You can kind of figure out if you're going to be able to afford it or a car or, you know, you can figure out all right if I pay $300 extra every single month, how much quicker? I'm not gonna be able to pay off my loan. Me and my wife do this with our house that if we pay a certain amount per month, it's like, Oh, we're gonna be able to pay off our loan five years sooner than we normally would, of which is going to save us $30,000 in interest over the life of the loan. So this is a really cool spreadsheet that you can use to help you out with finances. So good luck again. Take some time to make this your own format of how you want. Um, and we'll see you next time