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