BEST Financial Formulas on Excel | For Business & Finance Professionals

in this video we'll create scenarios to  determine whether you should pursue a project  

create a full loan schedule and calculate  how much you need to buy your dream car  

all using excel's financial formulas and to apply  these formulas we'll be using our latest startup  

idea a lemonade stand so let's get into it and  try not to skip around so you don't get lost  

so our plan is to work on this lemonade stand  project for four years as that's the time we'll  

be in university after which we'll get a full-time  job but we're not quite sure if this is right for  

us so first we need to determine how financially  viable it is but because we're not quite sure what  

our cash flows will look like we actually created  three different scenarios a best case a base case  

and a worst case and obviously in year zero it's  going to be negative that's because we need to  

have startup investments right whether that be  a lemonade stand whether that be the equipment  

and so on after which will hopefully start to  generate money now we'll use the choose formula  

on excel to be able to change scenarios instantly  so for that up here on the choose scenario we  

would type the one the two and the three and  that would hopefully give us these different  

cash flows so let's press one for now and from  there down over here we'll go equals choose  

press the tab key the index number is going  to be the scenario that we want to choose  

and then press the f4 key as that's going  to lock it for us press the comma key  

the value number 1 is the best case the value  number 2 is the base and the third one is the  

worst case now close the brackets press enter and  then from there you'll go ctrl c and then move it  

across to the other side ctrl v and now you're  going to have all the different cash flows for  

for the scenario number one as you can see here if  we change that to scenario number two then that's  

going to change to these ones over here and just  to make sure somebody doesn't put anything silly  

like a four in here which is actually just going  to break the cash flows for us we're going to do  

a data validation so for that just go to alt avv  and from there you're going to put allow a list  

because that's what we want and the numbers  we're going to want is just one two and three  

one comma two comma three press the enter key  now you're gonna see that you can only press the  

the different scenarios that you have here from  the drop down list in case somebody tries to put  

something else like a four here you're gonna see  that they get a pop-up sign so that's what we want  

all right so to assess whether the project is  viable under scenario two which is the base  

case scenario all we gotta do is go alt equals and  that's going to sum the cash flows for us and if  

it's positive that's good to go but unfortunately  that's not quite right and that's because of this  

concept called the time value of money which  basically says that a dollar today is worth more  

than a dollar in the future that's because you can  invest that dollar and hopefully grow it over time  

and so if you have a dollar in the future you  actually need to discount it back to the present  

value and for that we'll use the discount rate and  the formula that we'll use to discount these cash  

flows is called the npv which is short for the net  present value so let's go equals npv press the top  

key from there the rate is the discount rate that  we got press the comma key the value number one  

it's actually going to be from year  one to year four get those values  

close the brackets and then plus the negative  value which is on here zero press the top key and  

that should give you a thousand four hundred and  two and you might wonder why we didn't put all the  

values together for the mpv formula so instead  we added the zero at the very end here and the  

reason for that is if we put all of them together  it would essentially be accounting for year zero  

as year one and so all the years would actually  be misplaced by one which isn't quite accurate  

so we've got a positive mpv now what does that  mean in short it means that it's expected to  

earn a profit and so you should go ahead and  pursue that project on the other hand if it's  

negative you obviously shouldn't but usually when  we pursue a project we don't just want the dollar  

value of it but we also want the percentage  return and for that we'll use the irr which  

is short for the internal rate of return so once  on the cell we'll go equals irr press the top key  

and the values are just these values over here  press enter and that should give you that 13  

that 13 represents the annual return of the  lemonade stand now is that good usually you  

have to compare versus the discount rate so if  the irr is greater in the discount rate that means  

that the project is expected to be profitable and  so you should go ahead and pursue it what we just  

did with the mpv and the irr is what's known as  capital budgeting and there is a third method to  

this which is known as the payback period which is  simply how long does it take to recover the cost  

of the investment and companies use these methods  to assess whether it's worth pursuing new projects  

these projects might range from opening up a new  office a new factory or even a new product line  

so now that it's all dynamic we can go ahead and  change the different scenarios and everything is  

going to change with it as you can see if we go  to the worst case scenario which is scenario three  

you'll see that all of the figures are actually  negative so we shouldn't go ahead with the project  

under those assumptions all right so we've  determined that this project is viable and we  

want to go ahead and get started but we're college  students so we don't have any money and so we need  

to go to a bank and ask for a five thousand  dollar loan which is our base case scenario  

and here's the terms that the bank will be  offering us we got a four year loan for five  

thousand dollars at five percent paid monthly  so to convert everything into monthly we'll go  

equals five percent divided by twelve and for  the number of payments is going to be equals to  

four for the four years times twelve now on the  output side we have some important information  

that we should know to do with this loan and  so for the monthly payment we'll go equals pmt  

which is short for payment press the tab key the  rate that we want is the monthly rate remember  

press the comma key the number of periods is the  48 press the comma key the pv is the present value  

and so that's five thousand dollars that they're  giving us so it's a five thousand and you can see  

that the fv and the type actually have brackets  in them that means that they're not necessary for  

this formula to work and so we'll just close  it there because we don't have a future value  

press the enter key that's a monthly payment of  115 dollars that total monthly payment actually  

consists of the principal and the interest if  you don't know the difference the principal is  

essentially the actual loan amount that you're  paying back so if you borrowed 5000 how much of  

that are you paying back each month while on the  other hand the interest has to do with the fee  

that they charge for lending you that money  essentially so let's calculate both of them  

and i've just selected around a month in  month 24 just so you can see equals ppmt  

which is going to be for the payment for the  principal the rate is going to be that same rate  

the pair here that you can see is actually the  period so in our case we said we want month 24.  

press the comma key the number of periods is  up 48 press a comma and the pv is that same  

five thousand you can press enter there that's  going to give you the principal paid in month 24.  

on the other hand we have the interest for that  we'll go i p m t the rate is the same right here  

the periods is 24 again press  the comma number of periods is 48

and the pv is just the present value people  usually like to see the breakdown of how  

much are they paying in principle versus  interest similarly sometimes they like to  

see the total amount of interest that they're  going to be paying for that you can use this  

cumulative function so let's look into that  we'll go equals [ __ ] ipmt that's the one  

the rate is the 0.42 here press the comma key  number of periods is 48 the pv is the 5000  

coma the start period is actually the first  period because we want the cumulative so  

press the comma key there the last period is the  48 press the comma key and then we actually want  

the end of the period so we'll press the zero here  press the close and there you go one thing that  

would be beneficial for us to have is knowing the  remaining balance of the loan so how much have we  

paid how much do we need to pay back and so for  that we'll be using a loan amortization table  

so here we got the payment number which is  essentially which period are we in so we'll go one  

two and we'll actually drag it all the way down  to 48 once we go to here we'll go all the way down

for the payment amount we're actually going  to be equals we already have a calculator  

up here which is the monthly payment and  so we're actually gonna make it negative  

and select it there press the f4 key press  enter the reason i made it negative is so  

all the numbers are actually just on positive  here so it's just a lot easier to see for the  

interest that we have here it's just going to  be the monthly interest rate which is the 0.42  

times the loan amount which is going to be  5 000 press the enter key the principal is  

going to be the difference between the payment  amount and the interest so this one minus this  

one here and then the remaining balance is going  to be equals to the loan amount which is the 5000  

minus the principal which is the amount that we've  already paid back in period one for period two for  

the payment amount we can actually just select  it and double click here and it's going to fill  

down all the way to the bottom as you can see  by pressing control down and control backup  

for the interest is going to be equals to the  monthly interest rate over here we're going to  

lock it by pressing the f4 key times the remaining  balance because the interest is actually on the  

remaining balance it's not on the total amount  of the loan press the enter key for the principle  

again it's going to be the difference so one  minus the other and then for the remaining balance  

here what we're going to do is equals the current  remaining balance minus the principle press enter  

once we have these we can select all three and  then just double click down here and it's going  

to drag them all the way down now if we did this  exercise correctly when we press the control down  

arrow it should give us zero as the remaining  balance that is indeed the case so we did it  

well and one final check you can do here is to  sum all of the interest payments that you have  

and see if that's the same as the cumulative  interest so for that we'll just press equals sum  

and we're just going to sum all of these press the  ctrl shift down arrow to get to the bottom press  

enter so that number should be the exact same one  as this one and indeed that is the case very nice  

some other useful excel formulas include  the present value and the future value  

and suppose that at the end of university we want  to have ten thousand dollars saved up as that's  

the cost of a secondhand car that we want and so  we want to know how much money do we need today  

to make that happen and our bank is giving us a  seven percent interest rate to leave our money  

there now that seven percent is not realistic it's  just for this example so we'll go equals pv press  

the tab key the rate is at seven percent comma  the number of periods is four years comma key  

payment this is the monthly payment that we would  be paying in our case we are not paying anything  

monthly so we'll go with zero press the comma  key and the future value is how much we expect  

to have in the future so that's a ten thousand and  that equates to around seven thousand six hundred  

so that means that we need about seven thousand  six hundred to make our card dreams come true  

in four years time similarly let's say that we  want to find out how much this 7600 would give  

us in 45 years time which is when we hope to  retire and eventually buy an even better car  

say so for that firstly we're just going to  go equals negative 7629 so just link it here  

press the enter key and for the fe go equals fv  press the top key the rate is a seven percent  

number of periods is 45 for the years payment is  zero comma and the present value is this one here  

press the enter key that gives you 160 000  with which you'll probably be able to buy  

a decent car but it is in 45 years time if  you want to know more about excel formulas  

check out this other video i made over here  if you want to know more about valuation  

methods specifically the discounted cash flow  check out this video over here that's all for  

this one hit the like hit the subscribe if  you liked it i'll catch you in the next one