BEST Financial Formulas on Excel | For Business & Finance Professionals #Finance

in this CashNews.co 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 CashNews.co i made over here if you want to know more about Valuation methods specifically the discounted Cash Flow check out this CashNews.co 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