in this video we'll cover excel's most useful
Financial formulas across three levels first we'll go over basic functions on the time value of
money then we'll move on to intermediate functions on Capital budgeting like the MPV and the irr and
finally at the advanced stage we'll go over a loan schedule let's go first up we've got the present
value and the future value so let's take a look at some examples over here and you can download
this Excel file down in the video description suppose you want to get your parents their dream
car in three years time and you know that it costs a hundred thousand so over here we have as the
inputs the future value it's gonna be a hundred thousand and that's gonna be in three years time
and then we currently have a set amount of money but we think that we can invest it and hopefully
get an eight percent return maybe investing it in a fund like the S P 500 so over here for the
present value to calculate that we're gonna go equals PV here you're gonna see the description
just hit the top key for now and the rate is gonna be the eight percent which is what we think we can
invest it comma the emperor stands for a number of periods in our case this is gonna be the three
years hit the comma key again the PMT will just ignore that for now so hit the comma key and from
there you're gonna get the FV which is the future value in our case that's the 100 000 that we want
to get to in three years time so we'll close those brackets there and hit enter that should give us
a present value of 79 000 so it means that today we need to have 79 000 but we need to invest it
at eight percent to hopefully reach that 100 000 in three years similar to that we have the future
value let's suppose that here we want to be a bit more selfish and we know that with the 79 000
we could possibly retire quite well in 30 years time so we want to figure out how much money that
would be for us in 30 years if instead of buying the car we just invested it so as you can see over
here we have the present value at that exact same amount that's 79 000 we just put it as a negative
here and we said for 30 years which hopefully by then will be retired and lastly we think we'll
invested in that same fund at around eight percent so we'll go equals f v this time for future value
hit the top key there the rate for US is a rate percent comma and per number of periods the 30.
comma again and comma one more time and then the PV is going to be the 79 000 there close those
brackets and hit enter and you can see we're closer to a million right now at around 800 000
hopefully by the time we were to retire now one thing that we've assumed here which probably
isn't entirely accurate or realistic is that we're not going to be investing any money during
those 30 years but it's more reasonable to think that as you have a salary possibly you're going
to be investing a portion of that every year into that fund so over here you can see the FV
future value with recurring payments so we're assuming here that aside from all of the same
numbers here we're also going to have an annual payment a contribution of around two thousand
so basically every year you're gonna be paying a further 2000 into the fund and so the future value
there is going to differ so we'll go equals FV hit the top key again the rate is at same eight
percent comma number of periods is gonna be those 30 years and here's a big difference now we have
a payment for us that's going to be the annual payment of 2000. we'll hit the comma there and
finally we'll get the same present value close those brackets and hit enter and now you can
see that we do have a lot higher of a number and that's because every year we're going to
be contributing 2 000. now moving up a level to intermediate and here let's assume that we're
working at Nike and we're considering opening up a new store so let's go to control page down to
see the scenario over here and you can see that we've got all of these cash flows basically what
we want to assess whether it's worth investing in this project or we should actually just not open
up a new store entirely so you can see over here we're gonna have negative cash flows in the first
period That's primarily because we're gonna be renovate renovating the space Etc and from there
hopefully we're gonna have have all of these cash flows scale up as more and more people hear about
our store and hopefully like it here to decide whether this is going to be a viable project
for Nike we can go ahead and sum all of these cash flows by hitting the alt equals and just hit
enter there but unfortunately this isn't entirely right and that's because of this concept called
the time value of money which basically says that a sum of money today is worth more than a sum
of money in the future that's primarily because you can go ahead and invest it and hopefully grow
it over time and so right now this isn't entirely accurate instead we would need to Discount all of
these cash flows back to year zero so they can be accurately compared and for that we're going to
assume a discount rate of six percent which can be seen as a cost of capital or the cost of
financing for the company so over here under npv is what would first do the initial formula
which is equals npv here you're going to see the description press the top key there the rate is
going to be that six percent hit the comma key and the values for us we're actually gonna select
them from year one press a shift right arrow all the way to year five and then close those brackets
and just go plus and we're gonna add the year zero cash flow over there and you're probably wondering
why we went ahead and added the year zero one at the very end that's because where we to put it all
inside of this formula so inside of this red area what would happen is that it would think of year
0 as year one and so it would distort everything which wouldn't be entirely accurate so over here
you can see that we have a positive npv now based on this it means that the project is going to be
profitable and so we should go ahead and proceed now if we look closely at all of the periods here
there is a bit of a flow and that's because all of these dates are actually not on a yearly basis
but actually on a semi-annual basis so every six months so instead we need to be able to specify
the dates but the MPV formula doesn't quite allow for that so instead we'll use the X and PV so
we'll go equals X and PV hit the top key there now you can see that we have the reads the values
and the dates so we'll go first for the rates comma the values are all of the cash flows over
here and this time we can select the year 0 as well hit the comma key and the dates are gonna
be all of these date periods over here which we can now specify close those brackets and hit enter
the now you can see what that's going to look like as you can see it's a lot higher that's because
it's a lot more compressed in the time period but usually when you assess projects like this
you don't just want the dollar amount but you also want the percentage return so let's go ahead
and take a look at that through the internal rate of return here so we'll go equals irr hit the top
key there and the values for us are just gonna be all of the cash flows over here and the formula
is really just that simple close those brackets and hit enter you can see we get a 6.7 percent
now the general rule here is that the iftir is greater than the discount rate then we go ahead
and proceed with the project and if not we don't now that said same issue that we had with the MPV
and that's that we're not accounting for the dates as being not years but in this case in in every
six months so instead we need to use the xirr so we'll go equals x irr hit the top key there and
again we'll be able to select not just the values comma but we'll also be able to select the
dates which is what we want and close those brackets and then hit enter there now you can
see that it's also going to be a little higher just like it was with the X and PV based on
the numbers that we calculated here we feel comfortable telling our team to go ahead and
proceed with this project and thank you to nordpass for sponsoring this video nordpass
is a secure way to remember passwords instead of sharing sensitive data to other employees via
messenger or email which is both a liability and a waste of time in that you need to wait for them
to reply using note pass confidential information can be saved securely in one place and accessed
and updated by others when needed and in case you have new hires or departures in your company
you can easily give them access or remove access from them with nordpass in near moments on top
of that they also offer tools such as a custom password generator later so you don't waste time
and a password Health Checker to make sure your company is confidential information is safe so if
you're interested see northpass business in Action Now using a three month free trial with code Kenji
explains which you can find the link for down in the description below alright back to excel now
moving on to the advanced level and here let's suppose we need to get a loan to open up that Nike
store we mentioned earlier and so here's the terms that the bank is giving us on their inputs you can
see that there it's for a loan of 500 000 which we need to open up the store and they're giving us a
seven percent annual interest rate for a total of five years and so we need to convert this into a
monthly and that's what we'll actually be paying so we'll go equals the monthly interest rate is
simply the annual one divided by 12. hit enter there and the number of payments is the five years
multiplied by by the 12 months and hit enter there as well now let's find out the actual payment
amounts so over here down below as the outputs the monthly payment for us we can actually use a
formula called Equals PMT which is the payment for a loan press the top key there and so the rate is
the monthly interest rate comma number of periods is 60. come again the present value so right now
that's the loan amount for 500 000 and we'll just hit enter there and so you can see that we have a
monthly payment of around 9000 and that being said that monthly payment consists of both principal
and interest the interest you can see as a fee that the bank is charging us for letting us borrow
that money and on the other hand the principal is the actual amount that we borrowed so the 500 000
which will eventually need to pay back and so for the principle we're just going to go equals
ppmt hit the top key and so the rate is going to be the monthly interest rate comma the period
this is basically hey when do we want to see this in our case let's say we want to see it in the
first month so I'm just going to put a one there comma the number of periods is at 60 again comma
and finally we have the loan amount at the 500 000. we'll close those brackets and just hit enter
there now the other part should be the interest and so for this it's just going to be equals to
IP empty which is the interest payment on the loan in the top key there and it's the same concept
here so the rate is a monthly interest rate comma period is going to be dot 60. sorry in this case
the period is the one comma number of periods is at 60. comma and finally the PV is going to be
the 500 000 close those brackets and hit enter there and so basically the sum of these two should
equal to the total monthly payment so let's test that out we'll go equals this one plus this one
and so now I'm gonna go equals this equals this one up here and it says true and so that looks to
be correct for us one thing that's a bit tedious here is that every time we need to switch months
we actually need to go in and type the different month so instead it's probably a lot easier if
we just make a whole table showing us all of the payment amounts on each specific month so first
I'm just gonna delete this area over here and so we have a total of 60 payments now to make a drop
down like that I'm just gonna go equals sequence hit the top key there and so we want a total of
60 rows close those brackets and hit enter there and so you can see got it okay press Ctrl down
arrow that's going to take you to that 60 there exactly control up Arrow to go back up and so for
the payment that's gonna be equals to the monthly payment over here but I'm just gonna put it as an
as a negative so that it stays positive so it's a bit easier for us to see and we're gonna lock this
just by hitting the F4 key and hit enter there basically the Locking thing means that when I drag
this down so just double click there what it's gonna do is it's gonna stay um locked so inside
this formula inside the cell sorry and it's not going to be moving down so right there is fine for
us for the interest it's gonna be equals to the loan amount multiplied by the monthly interest at
least a month one and then the principle is simply going to be the difference so equals payment minus
interest and hit enter there that's the principle and so the remaining balance is how much of the
loan we've actually paid off in our case so we had the total loan amount at 500 000 and it says
here that in principle we paid around 7K so minus this one here and hit enter now moving on to month
2 over here and so this one is going to be based off of the remaining balance and not based off
of the loan amount like we did earlier because that's actually going down so we'll go equals the
monthly interest rate over here press the F4 key again this is going to lock it with the dollar
sign and multiply that by the remaining balance hit enter there and from here principle is going
to be the same function as above which is simply a subtraction of one minus the other so we'll just
double click there and drag it all the way down same thing goes for the interest we can just
double click it and drag it down for now the remaining balance is the part that we need to
adjust so it's going to be equals to the remaining balance of the previous month minus whatever we've
paid back this month over here hit enter there and now we can drag this down as well just by double
clicking and the best way to find out if we did this correctly is that by the time it's month 60
so year end of year five we should have paid for everything so go to control down arrow and you can
see that we have a remaining balance of zero as that should be the case meaning that everything
here in the calculation seems to be correct for more on Excel check out this video over here for
doing a discounted cash flow evaluation on a real company hit that like and that subscribe button
if you liked it and I'll catch you in the next one