Highline Excel 2016 Class 24: Financial Functions: PMT, RATE, NPER and FV 12 Examples

welcome to Highline Excel 2013 class video number 50 hey if you want to download this workbook and follow along for a week 10 click on the link below the video in this video we want to have an introduction to financial functions and we're going to look at PMT rate NPR and future value now we have nine different examples and some of them are directly related some of them are not they're kind of a selection of financial examples I might do in my finance class so for this class it's not a finance class or even in my business math class we talk more about that the theories of finance and what not what you want to take away from this class is that there are financial functions in Excel and also that we want to be able to think about cash flow now let's go over to this note sheet now we're going to talk about the PMT function and that calculates the amount of your payment on a loan and other periodic payments we'll also talk about the future value function that tells you how much if you put some money in a bank or you have a savings plan how much you'll have later in the future we'll also talk about the rate function that calculates your interest rate for a period and we'll talk about the nper function that calculates the total number of periods for example we'll use it to calculate how many periods or how many months it will take us to pay off our credit card control home the one finance concept that we'd like to kind of understand after watching this video is the cash flow matters in finance now when you have a monthly payment on a loan to you that's cash going out of your wallet meaning I'm writing the cheque and sending it to the bank that's negative if the cash is going out it's negative but to the bank when you send in your check to them the cash is coming in it's a positive so we'll always ask the question when we're talking about cash flow which way is the cash going is it coming is it going out of my wallet or into my wallet out of the wallet or purse for that matter is negative into the purse or wallet is positive all right let's go over to our first sheet PMT and we want to see five amazing examples for the PMT function now here's the situation we have a car there's the price that's how much we borrowed and we need to figure out what our monthly payment is now when you go to get a loan they almost always quote you the annual rate and they say how many years the loan is for but if our goal is to calculate monthly payment we can't use the annual rate or the number of years we have to figure out what the monthly rate is and the total months for the loan the reason why is if we're calculating monthly payment everything has to be in the same units month all right now I already did this first formula here the amount we need to borrow well we take the price of the car - the down payment so that's the amount we borrow and now we need to figure out what the payment is at the end of each month that's going to be our monthly payment ah but first we have to calculate our monthly interest rate well we simply make a formula equals the total annual rate and we need to chop it into 12 pieces so what do we do we divide by the number of periods in the year now for most consumer loans or many consumer loans we're doing monthly later in this video we'll have an example where the corporation is buying some money and it's quarterly we pay quarterly payments but here and you're all divided by 12 that's our monthly rate now number of years we need total number of periods so we say hey years times number of periods in our year which happens to be 12 all right so that's what we use our period rate which is our monthly rate and our total number of periods now let's go ahead and use our PMT function now there's a bunch of arguments here and there's notes over there but the rate the rate is always going to be the period the interest rate so we're going to say monthly comma nper means number of periods and in particular it means total number of periods so that's 60 comma the present value present value just means on the day you went out and borrowed alone how much was the loan worth what is the present value of that loan well it's twenty four thousand seven hundred and ninety nine ah but here's our first cash flow the this was a rate number appeared so we didn't need to worry about whether it's negative positive but this is a cash flow and this is kind of tricky because you never really see it it just you borrow the bank gives it to them the person you're buying the car from when you go out and borrow the money from the bank that's a positive to you you say hey Bank can I have some money and they give it to you so that is a positive even though for that's fraction of a second you get it and then you give it to the car dealer it's still positive from your point of view all right now the remaining arguments future value that's if you had a balloon payment at the end and type means the type of loan it is most consumer loans you pay your amount at the end of the period also for a lot of finance things like Savings Plans you always save at the end of the period so when you have periodic payments that means payments that are the same amount each period and the time period between each payment is the same most of the time it's at the end of the period an example of when you have a beginning of the period payment is like on a lease now here's the deal we can put the zero in but the default for payment is end of the period because many loans and payments are at the end of the period so guess what I don't need to put that in khammam in a I'm backspace and get rid of that not only that but since there's no future value I can backspace and get rid of that so for most payments on consumer loans that's all you have to put in we'll see examples of those last two arguments a little bit later all right - why is it Linus these functions understand cash flow that means every month 485 dollars and 22 cents is coming out of your wallet and in to the bank's wallet now just out of curiosity if we didn't have that PMT function and in my finance class we learn the annuity formula for this particular finance calculation and that's what it would be like so instead of having to calculate that and type that out each time much nicer to have the P oh that is amazing look at how easy that is now what if the payment happened to be at the beginning of the period well it'd be the same exact thing period rate monthly rate by the way the bank would love for you to use this gigantic annual rate because then you'd be paying them a lot more all right so rate total number of periods present value it's positive for that little fraction of second comma to skip over future value and then here if it's at the beginning we have to put a 1 close parenthesis now if you pay right when you get the loan your first payment that means you're paying something early so the total payments will end up being less look at that we're going to pay three dollars less each month for all 60 periods just because we paid 30 days early or one month early that's a fact that comes from compound interest the power of compound interest in a finance class we talk about that here but all you need to know here is your loan payment it's going to be a little bit less each month just because you paid one month early now let's go look from the lenders point of view the loan amount is still going to be the same price minus down payment the monthly interest rate is still going to be hate that annual rate divided by the number of periods for the year and the total number of periods will be years times periods per year which would be 60 so we come over here and this is going to be from the point of view of the lender so the rate period rate number of periods same for both the borrower and the lender but the present value is going to be negative because the bank when they loan this to you it comes out of their wallet all right so now that means if this is negative here if the cash flow here is negative then that means the PMT is going to be positive and sure enough for the bank that's coming in each month now here's the whole formula here I accidentally had a minus sign there so I'm going to put that as a positive all right so let's go look at our third example Oh sometimes you have a balloon payment at the end so we're going to buy a car for 50k the down payment is 5,000 looks like I forgot to put the green there for formula so we subtract those two and we have to borrow 45,000 we're going to have a balloon payment at the end that means we're going to be paying off a little bit every month but at the end of the loan there's exactly $5,000 we still have to pay all right our period rate which is monthly is hey annual rate divided by number of periods per year our loan is for three years so here we're going to say equals three years times twelve periods per year and now we can do our monthly payment equals PMT the rate 0.44% comma the number of periods 36 the present value this is from our point of view of the borrower so it's positive comma the future value now at the very end of the loan from your point of view do you have to pay 5,000 to the bank yes you do so that's why it's negative when we borrow the money this 45,000 was coming into our wallet even though it immediately went out at the end we're paying 5,000 to the bank so that's why we put it has a minus the last argument that default is end of the period and that's what type of loan is in control enter and there's our minus 1225 and 21 cents it's - because each month that's how much we're paying wow that's a lot of money just for that $50,000 car now let's go look at our next example all right so this example where corporation we go out and borrow a million bucks the annual interest rate is eight point five percent we're going to do it for six years and there's four periods per year so we're paying quarterly now here's the thing we're going to put off paying any payments for one year now the bank who gives us the loan is not going to do it for free that means the $1,000,000 that we borrow and start using it's sitting in that bank account as alone and every three months every quarter the interest is accrued that means by the time we start making payments one year later the amount that we actually owe is going to be a lot more than a million dollars so that's where the future value function comes in we're going to say hey million dollars please show us what the amount we owe will be in one year so here we're going to use the future value function oh but we have to calculate the rate in nper so annual rate divided by four periods per year so or quarterly rate will be two point one three percent that's our period interest rate our total periods equal six times four that's the total number appears now we have to be careful there we're actually not going to use that number because we have two calculations here here we have to figure out how much do we owe after one year which means four periods of interest payments accrued on top of this million bucks when we get to the period payment or the quarterly payment we're only going to have five years left all right so we just have to be careful about that when we make our calculations all right we're going to see how to use the future value function the rate well the period rate is 2.3 percent comma total number of periods now I'm going to be careful I know it's 4 because it's only 1 year but I'm not going to click there I'm actually going to say total number of periods is 1 year times 4 that way for this template if I come back later and in that cell I put a 2 which means we put the payments all for 2 years this formula will calculate correctly with the total number of periods so the total number of periods is just 1 times 4 which is 4 comma we don't have any payments that we're making here but we do know what the present value is so we get the million dollars and it is positive now the type is N and what do you think the cash flow will be here for future value it's going to be negative because this is a loan account so that means if we borrowed 1 million dollars and we didn't pay any of the interest after one year that's how much we'd have to pay back to the bank on this for this particular calculation the cash flow is negative because if we paid it back we'd be paying it out of our wallet to the bank now you ready we're going to calculate the period payment equals PMT the rate still this two point one three that's the period rate for the quarter comma number of periods we have to be careful here the loan was for six years but now we're making this calculation one year out so it's actually five and we need to take that five times the number four which represents quarter so in parentheses remember we're always building our formulas robustly so they update so instead of putting a five there something I'm going to say six minus one that way if we change that number one year's payment is put off to two this nper right here will calculate correctly if that was two then then the number of years for this nper payment would be four right 6 minus 2 would be 4 but for us right now 6 minus 1 is 5 now that's years and the units always have to be the same so for NPR we have to say times 4 that will give us our 20 I believe it is so watch this we could do our little trick as we click around in arguments I could highlight just what's in the argument I can hit f9 that's the evaluation key and see that yes it gives me total number of periods 20 for the rest of this loan control Z comma now the present value this is just like making a calculation for a loan right but think about this even though for this calculation right there when we calculated it in the green cell it was - that's because if we paid it back it would have come out of our pocket but now we made that calculation and this is going into the future so we have to make this present value positive because remember when you go to the bank and say hey can I have some money they give it to you so that's where we're going to put a minus sign to convert it back to a positive f9 we can see sure enough it is a positive control Z cash flow sometimes get tricky and Finance you always have to break it down to think about exactly what's going on all right PV we have that there is no balloon payment at the end and our type is end of the period so I'm going to leave both of those arguments off close parentheses and enter Wow 67,000 320 $8.25 obviously not for a consumer loan but borrowing a million bucks for a corporation or some business might not be a big deal at all that's going to be the quarterly payment all right we have one last example on this sheet here we have we want to calculate adjusted APR when there are points and fees now if you go out and borrow $200,000 for a house the contract you says hey there's points and fees and a lot of us don't think about that but points mean they're going to actually take some of this so even though you owe the 200,000 the person initiated all this is going to take 1% of that not only that but there's a 750 dollar fee on top I wish they wouldn't disguise it this way and they just say boom here's our feet but that's the way it goes it's no problem that's why we're learning how to do this finance because so we can calculate what it should be now since we borrowed two hundred thousand we're actually going to have to make our periodic payment here based on that two hundred thousand but when we go to figure out what the adjusted APR is that's where we take into consideration these alright ready the period rate equals five percent annual rate notice I have it as a decimal here it does not matter if you have it as a percent or a decimal so that divided by number of periods enter total periods this is a 30-year home loan so thirty times twelve will give us 360 the PMT equals PMT the rate that's our period rate comma total number appearance the present value that's the full 200 thousand and it's positive because we go to the bank as a hey give us some money and it plops into our wallet right but we immediately give it to whoever we bought the house from no balloon payment at the end and this is n so we leave those last two arguments off enter so one thousand seventy three dollars and sixty four cents now actual cash received this is where we get to figure out actually how much money we got because that immediately went to someone else for servicing our loan so two hundred thousand times well it's 99 percent one minus the point zero one and we could highlight this in f9 and see yes you enough 0.99 so the actual cash received is 99 percent of that two hundred thousand control-z again we're linking it to the cell excels golden rule as if formula inputs can change you put them in the cell and refer to them in the formula with the cell reference all right that'll give us the full amount that we are received before the extra fee so 198,000 control-z but there's one last a little bit they're going to subtract 750 enter so they're one hundred ninety seven thousand two hundred and fifty that's the actual cash we received all right the period rate oh we have to kill I thought we knew the period rate but no we need to figure out the actual period rate because we thought we got two hundred thousand but the cash flow is actually a hundred ninety seven two fifty that's how much we got so check this out now we're going to use the rate function rate always calculates the period rate that means the monthly rate for us so nper it's still the same 360 notice how future value PMT rate they all have the same basic arguments it's just or solvent for a different piece we're solving for the period right here so nper that still means total number of periods PMT that's a cash flow from our point of view so we put it in as negative the present value that's why we had to calculate the actual cash received from because from our point of view this is how much we used to give to the person that we bought the house from these extra fees went to the person who serviced the loan so there it is and it is positive that comes into our pocket cash flow is important here the PMT is negative the actual cash received is positive we do not have any future value balloon payment at the end we don't have this is a consumer loan so it's at the end and we don't need to put a guess in here so we're simply going to close this off and it's zero I love that we're dancing no that's number formatting don't get tricked by a number format now we know the eraser is the general number format but don't forget as you go forward since it's one of the last videos for this class control shift plus either tilde or brave accent either one of those that's the keyboard for general number front control-shift grave accent or tilde there is our period rate and notice it's a little bit bigger that period rate we used up here now we can calculate our adjusted APR so equals well period rate how many periods in our year 12 so there we go that will give us our adjusted APR and it is a little bit bigger than our 5% up here point zero five point zero five and a bunch of extra change so the adjusted APR is bigger than our annual interest rate not only that but you want to remember that this 200,000 that we borrowed part of that went to pay off the points in the fees whatever was left over was what we were allowed to use for our house now here's our PMT and it's based on the whole amount even though we had to break that apart pay some fees and and pay the house that one thousand seventy three dollars and sixty four cents we're going to send that amount in every month now our next example we want to go over to the sheet amortization because that one thousand seventy three when we send it into the bank part of it gets used up as interest and part of it is a reduction of our loan so let's go over and build an amortization table I'm going to click on the sheet' amortization here's our details and there's our PMT now every month you send it in we'll have a column for payment we'll have a column for period interest that means how much the bank takes as interest each period and then how much the bank reduces your loan by now before we can calculate this we need 360 rows because the amortization table will break this apart into its two pieces interest in loan reduction 360 times now watch this here's a great trick for putting numbers into this first column I need 0 all the way to 360 so I type 0 control-enter and this when you point your cursor to that fill handle right-click and drag down and then back up a special menu pops up and you point to series immediately it asks you where do you want to put this series of numbers in rows which would put them this way or in columns which would put them this way that's what we want we want 0 1 2 3 4 5 6 to 360 in this column the step value 1 because I want to go 0 1 2 3 and the stop value 360 I absolutely love this trick okay and just like that control down arrow it put in all of those numbers control home now we have x 0 because when we go to the bank we borrow exactly 200,000 so the balance at x 0 is equal to hundred thousand now for each period we in this column want the payment amount so I'm going to and I'm going to do a cool trick here I'm going to type minus the payment now for the amortization table I don't want to show it as a negative I want to show it as a positive so I need to do double negative but notice I didn't put an equal sign there control-enter f2 ah anytime you put a minus sign or a plus sign Excel will convert it to a formula by putting an equal sign before the minus plus or at symbol f4 to lock this all the way down control-enter double click and send it down control down arrow 1070 364 exactly 360 times every month I'm sending in the same amount now with my cell selected I'm going to highlight the whole table control asterisks on the number pad and then I'm going to use my all borders now let me add some color here double click and send it down I'm going to add some color to this whole row here all right now that's the amount that we send into the bank part of its going to go as interest and part of its going to go as loan reduction well the most important calculation in this whole table is period interest and here's how it's calculated well when you went to the bank and borrow 200 dollars it was sitting in there for a whole month until they calculate an interest so at that point they take the period interest rate and multiply it by the 200,000 so you're ready hey how much was sitting in the account for the whole month two hundred thousand that's what you own so we have to calculate that amount times the period rate now I'm going to lock this f4 all right control enter so for this first period you sent in 1070 three dollars and 64 cents and they took 833 dollars that seems like a lot of interest the reason why is because you owed them two hundred thousand dollars a lot of money as we go down the amortization table the actual amount of the balance will get smaller and smaller and that's why as you send in more and more payments your interest will go down and your loan reduction will go up so our loan reduction equals the PMT minus the period interest control-enter Wow I sent in 1073 and they only let me take off two hundred and forty dollars from my balance yep that's the way it works early years high interest low loan reduction later years high loan reduction low interest all right so our balance equals one still above minus one cell to my left control enter let's see if we got all of our cell references right those are relative all the way down that one's relative because as we copy the interest down when it gets down to here it will be looking at the balance from the period above which will be getting smaller each time and thus the interest will get smaller actually let's cut down copy this down a few rows notice one two three different formulas I'm going to just drag it down a few rows double click this the reason the interest is going down is because our balance the amount that was sitting in the account for the whole month is getting smaller each period this is getting bigger each period because the interest getting smaller now watch this I'm going to highlight all three formulas and double click and send it down can send all three formulas down control down arrow on this last number better be zero exactly perfect so in the last payment we send in 1073 about four dollars is interest about one thousand seventy bucks is loan reduction control home that's an amortization table every time you have a PMT for a loan some of its interest some of its loan reduction now we can calculate the total interest and that would just be alt equals for the sum function click in the first cell control shift down arrow shift enter to put the formula in the cell and push the cursor back up I'm going to copy this formatting with my paintbrush this one I'm going to use I actually could have done the paintbrush there too that's a lot of interest and I want to show you another option to there's a function called cume PMT I which would be the cumulative payment interest so let's see if we can copy this ctrl-c ctrl-v and I'm going to try a different function equals qumy qumy which is kisi um for cumulative I for interest in PMT for payment so the rate nper that's the total number of periods the present value it is a positive the start period is going to be one and the finished end period is going to be 360 and the type is going to be in this requirement notice there's not a square bracket around type length it was with PMT so we have to put it in it's going to be end closed parenthesis and control enter and we get the same exact number that's another financial function that's pretty cool now we want to go over to our next sheet how long and look at the painful truth about credit cards how long to pay off your credit card balance so it says seven thousand five hundred that's your balance and up in the corner it says your minimum payment is a hundred and twenty five a lot of people pay that and think about it that would be awful not only is it tons of interest but it's gonna take a long time so let's figure out how long now we have the present value that's a loan a credit card balances like a loan to you that's a positive we have our annual rate number of periods per year there's our PMT with those inputs we could use the nper function to figure out total number of periods the rate we're going to have to calculate it right inside of our argument so the rate function calculates 18% divided by 12 the PMT that's - that's a positive it's a loan coming in that's going out of our wallet so it's - the present value sure enough that's 7,500 we don't have a balloon payment at the end and we can leave the argument off because it's got the square brackets and we know the default its end oh that hurts a hundred and fifty four months if we take that and divide it by 12 that'll tell us it's going to take a lot of years and if you build an amortization table for this that'd be a lot of interest how long too long don't pay your minimum balance now we have one last sheet for this kind of introduction to financial function video FV annuity end now an annuity just means regular payments in regular intervals usually for say like savings plans or withdrawal plans so here's our question we have a savings plan if we put 50 bucks in the bank at the end of each month for 50 years how much we have in your ty well if it's 50 years you're starting when you're still a teenager which is when you should start saving 50 bucks it doesn't seem like a lot the variable that affects compound interest the most is time years now we're going to start off with 0.3 as our assumed annual interest rate that's a lot 13% if you look back at financial history over the the long haul the averages with stocks you can get about 13% we'll start here and we can move it down I'm shooting this in 2013 certainly 2013 we had rates higher than even 13% for this year but between 2008 and 2012 there was some pretty bad years there so that's why it's a formula input we start with 0.3 make our calculations figure out what we'll have when we retire if we put in 50 bucks each month for 50 years and then we can change it alright equals period rate 13 divided by number of periods per year total periods equals 12 times 50 and then future value equals FV after using these financial functions you kind of get the hang of these different arguments the rate is always period rate comma the nper total number of periods the PMT oh this is going to be negative right because we're putting it in the bank so I'm going to put a minus sign right there and click on 50 that makes sense because when on the day we will retire the future value better be coming to us which is positive no present value that would mean some amount you started off with in the bank a lump sum and the type is going to be end Wow that is amazing two almost three million dollars that's the power of compound interest and check this out even if we assume point one you still have eight hundred about eight hundred and sixty thousand dollars and that's a lot of money when you retire because on the day you retire you don't spend it all it sits there and keeps earning interest while you withdraw regular amounts yeah so just out of kicks look here we're lucky we have the future value function so we don't have to do this annuity formula which is beautiful but that's a lot easier especially if you're doing finance and doing this kind of stuff day in and day out now let's just assume something let's say you get this on the day you retire and we want to figure out how much we can withdraw each month we're also going to need to know what the present value is because we're going to have withdrawals each period well that's the amount we got on the day we can retire but if we immediately put it back in the bank and just let it sit there then that would become the present value and guess what we're going to call this negative and so I'm going to go minus this I'm going to change this formatting come up here currency and now we want to figure out what the PMT how much we get to withdraw each period so in this cell do a little formatting here so in the cell we're going to do equals PMT the rate 5% divided by 12 comma the nper 35 years times 12 the present value it's that amount right there and I don't want to have anything left over I could put an amount there if on the day I died I wanted some leftover to give to my kids and the type will say I'm going to do begin so we're going to go comma comma and then begin because I want some right now and so that's a lot of money 4300 bucks each month based on the fact that we had eight hundred and sixty-six thousand on the day we retired now we could do one step further total withdrawal and I'll come up here and change the column width copy I'm going to paste this here just to get the format and then change the formula escape equal this times what times thirty five years times the twelve months and boom from eight hundred and sixty-six thousand on the day we retired since we get to withdraw assuming a five percent annual rate that's pretty conservative fourth out about four thousand bucks each month that's the total amount we will have drawn over the period so even when you retire and have eight hundred sixty six thousand dollars on the day you tire just from putting 50 bucks a month away you're still only have 4,000 each period and that's a total of 1.8 million you know we could actually do one final calculation I'm going to scroll over here and say total put in and that just means how much did I actually put in guess what this is going to be really small 50 bucks times the 50 periods times the 12 months you got to be kidding 30,000 and total withdrawals after all those years 1.8 million that's the power of compound interest all right we have one more calculation here boy is it fun playing around with these financial functions what if I wanted a million bucks on the day I retire then I'm going to plan to save for 35 years we're going to assume a rate of 13% no problem remember how much do we have to put in each period to get this million dollars if you're putting a certain amount in or there's a cash flow that's always the same size and the same time periods between each cash flow we can use the PMT now the PMT function is the function we've seen the most throughout this video we saw it for loans now we're seeing it for investments right or even up here how much do we get to withdraw once we have eight hundred and sixty six thousand here the question is how much do I have to put in the bank to get a million dollars so my target is in the future a million bucks all right period rate equals thirteen percent divided by 12 enter total number of periods equals twelve times 35 years and now we'll simply do equals PMT always being careful about our cash flows there's our period rate there's our total number of periods the present value well I if I had some in the bank I would put it there but I don't have any right now and the future value I want it to be positive so there it is I can skip over the type I'm going to assume this is at the end not only that but this is going to come out negative as a cash flow because that's how much I have to put in each period the power of compound interest if I only got point 10 so 10% annual return which over 35 years at least history says that that's possible history is on average a good guide for the future but the future is unknown so we don't know all right so there we go 263 bucks per month for 35 years and I can have a million bucks if I get that 10% all right that's a lot of fun stuff with financial functions the only big important function we didn't look at is present value and that has to do with the present value concepts present value of future cash flows and that will save the for a finance class all right hey there is a link below in the show more area for the finance series I have at YouTube which is a hundred and eleven videos it's a basic kind of finance corporate finance class that goes over some accounting and lots of cool finance stuff all right we'll see you next video

Channel: ExcelIsFun

Views: 25,019

Rating: 4.9342103 out of 5

Keywords: Highline College, Excel 2016, Mike Girvin, Busn 218, Spreadsheet Construction, Highline Excel 2016 Class, excelisfun, Advanced Excel, Finance functions, PMT function, FV function, NPER function, RATE function, Period Rate, Future Value, How Long To Pay Off Loan, Adjusted APR, Loan Balloon Payment Excel Loan Payment, Excel Finance Class, CUMIPMT function, Loans payments In Excel

Id: 9_HMM9wqfYM

Length: 41min 5sec (2465 seconds)

Published: Thu Jul 07 2016