 So now let's do a practical application of working with functions inside of Excel and the one I'm gonna focus in on is designing out a monthly payment calculator for a loan. I Know super super interesting, but if you're like most adults You have loans and you need to figure out how to pay them And you need to probably figure out how long you're going to be paving them And you maybe you don't want to pay loans get out of debt Or you're like me and you Just bought a car. So now I have the luxury of trying to figure out, you know, how many Payments or what kind of monthly payments I can afford if I want to try and pay it off in say four years Well, I did get lucky. I got 2.39 Percent as my annual interest rate. So that's pretty nice How many payments do I plan on making a year? Well, there are 12 months in a year So I'm gonna say I'm gonna be making 12 payments in a year. You know, that's when I get paid might as well be doing that, right? Now this is where things get a little funky. Don't worry about that rate thing just yet But I'm saying what's my interest rate per period? Why what does that matter? It's because I just said I'm gonna make 12 payments per year So I have 12 payment periods per year. So what I'm asking is what is the interest rate per payment period? Now, how do I figure that out? Well, luckily, I start with the equal sign again in Excel This is telling Excel everything after this equal sign is a mathematical equation I need you to evaluate it out first and again, it would be B1 my annual interest rate divided by my B2 my payments per period and as soon as I hit enter I get this really ugly looking number But luckily, I know how to solve that if I select that number I have this numbers section that I can work off of and if I click on that Little percent symbol right there that turns it into a percent now I wish that I magically just now had zero percent interest every Month, but that's not really the reality of it. I only I have no decimal places being displayed here So what I have to do is again just like I did I clicked on the percent symbol I have the increased decimal symbol and if I click on this three times one Two three you see that we get zero point one at nine nine percent being applied So we keep on going How many years do I plan on paying this off or how many years do I hopefully? Want to be paying this off before it's done. I said I want to pay it off in four years So now I'm saying, okay Well, how many payments am I making how many total payment periods am I making again? I have 12 payments per year. I have four years that I want to be doing this I'm gonna instead of just doing 48. Yeah, I know that's a good number, but instead I'm gonna say all right Well instead I'm gonna go with B2 times B4 That way if I want to ever change this I can Now loan amount again, this is now how much did I finance for my car? Well, I Actually got $12,000 financed I know at 2.39% amazing But again if I want to jazz this up and I want to style this up if I select that again I have the dollar sign my account number formatting And I can do that Now monthly payment PMT. This is where we start to get into okay How would I even know to use these types of? Functions, you know, how would I know that there's a loan payment calculator function inside of Microsoft Excel? One is if I click on the formulas tab You notice that we have a function library that lets me do all of these different possible ones again Though they're not gonna be very beneficial if I don't know what they all do What I can do however is I can click on this insert function button and that actually gives me a dialog box menu that allows me to look for something now It's already up here But if I don't know what I'm doing and I need to just kind of wing it say for example, I I would really like to Know the function for calculating out the square root I type out square root and I hit enter guess what the first thing that pops up s q Rt is a function for calculating out the square root It's pretty nice, but again, I'm dealing with a loan payment Again super interesting soon as I hit enter notice the first option at PMT That's exactly what we're working with right here because context clues you can already guess But if I hit okay, I get a new dialog box that pops up I'm gonna just move it down here for a second so we can see and Rate in per PV guess what we happen to have those readily available to us Those are the things that we actually were referring to when we calculated out our annual interest rate or our interest rate per period Our total number of payments and our loan value. So the first one is I'm simply going to say inside of rate B3 B3 notice what it appears right beside there. I get a little sneak preview of what this is going to do in Per in per again. That's my 48 Which is in B5 Now present value present value notice. It's the only it's the last bolded number meaning. It's the last required parameter inside of my function after that I can just hit enter and I'm done But present value if I say again, it wasn't it was B6 I already get a little sneak preview of what these answers are You can see there's a little number there, but just to talk about them FV for example FV is a future value Say for example instead of this being a loan payment. It's a savings. I want to say that in four years time I want How many payments do I need to be making at 2.39% interest to make my balance? $15,000. I got to be paying $5,000. That's a lot But that's what future value does now type type actually is now saying when will my interest be applied Do I make my payment at the beginning of the year or the beginning of the month? You know March 1st, or do I make my payment at the end of the month March 31st? Again, we don't need to worry about those. We just need to go ahead and hit okay And what we're going to see is we're going to see a mathematical equation. We're gonna see the parentheses 262 and that is my kind of little accounting way of Showing I'm at a deficit now again I can format that to anything I want if I want to just change it to accounting style I can again the parentheses are to indicate. This is a negative number now The reason why we did it the way we did it is what if things change what happens if you know You're not as lucky or you're even luckier than I am and you you know finance a brand spanking new Car, you know you go out and you buy the latest Hyundai or something like that. Well, guess what you're probably gonna get something really low like maybe 0.9% interest and Guess what happens now? You're probably also not making $12,000 if you're doing like a the Alantra, you're probably spending more about $20,000 so suddenly guess what I can now see I can pay off a new brand spanking 2015 Hyundai Alantra or Toyota Camry or Prius or whatever By doing this mathematical equation