Excel for Finance and Accounting Intermediate Tutorial

Finance For The Future
12 followers
6 Video Views·Apr 26, 2026
Welcome to LearnIt Training.
The exercise files for today's course
are located in the video description below.
Don't forget to like and subscribe.
Hello, welcome to LearnIt's Microsoft Excel
for Finance and Accounting part two course.
My name is Elissa Smith, and I am an IT facilitator
with over 25 years of experience teaching users like you
how to maximize their productivity
using tools like Microsoft Excel.
Now in this part two course,
we're going to spend our time
exploring more of the functions
that help you become a financial genius in Excel.
Just kidding, but we are going to explore things
like the date and time functions
that are part of Microsoft Excel.
We'll also look at how to validate data in cells
using data validation.
Now, some of the functions we're going to look at
are VLOOKUP and XLOOKUP.
We'll also be exploring some of the scenario management tools
like Scenario Manager and Goal Seek.
And then when it comes to financial functions,
we're going to spend a great deal of time looking at those
including the PMT function, NPV, IRR, the IPMT and others
as we begin to teach you
how to make your own loan schedules.
We're also going to explore some of the templates
that come with Microsoft Excel that you can personalize
and make into your own loan schedules as well.
So join us for this course.
Looking to support our channel and get a great deal?
Become a member today to unlock ad-free videos.
That's right, your favorite courses without a single ad.
Interested in a specific video?
Purchase one of our ad-free courses individually.
Looking for even more?
Gain access to exams, certificates,
and exclusive content at learnitanytime.com.
More information can be found
in the video description below.
Hi, everyone.
Let's talk about the date time functions
that are part of Microsoft Excel.
They're actually an entire category
and today I'd like to introduce you
to two of the most basic ones.
Keep in mind that dates are very critical
to reporting financial information,
so having knowledge of a few of these date time functions
can be very useful.
I'm in a practice file called date-time.
Feel free to open it up and use it to follow along
as we do this activity.
I'm gonna start by clicking in cell C5.
This is a very basic expense report
and I'd like to put the current date into this cell.
There are a couple of different ways to do that.
One of them is a function.
I'm going to hit an equal sign and type the word today.
Now, if I don't even know what today's date is,
as soon as I type today with an opening parenthesis,
Excel will get the current date off of my computer
and when I hit enter,
it puts the current date into the cell.
Now, it's important to note that this is a date
that will update every time you open the file,
so keep that in mind.
It will change.
What if you need to know the date and the time?
Because the exact time can also be useful to know.
For this, we're gonna do an equal sign
and type the word now, indicating the date with the time.
I have an opening parenthesis.
I don't need to do the closing parenthesis
because Excel will capture the current date and time
off of my computer.
Now, you're gonna see the date with the time.
It does use a 24-hour time clock,
but please remember, you can always click on this date.
That is, again, because of the formula.
Go up to your number group on the Home ribbon tab,
click on your different number formats,
come down to More Number Formats,
and then from the Format Cells box, you can come to Date,
and you'll see that if you go through this list,
there are some date and time styles
that include a 12-hour time clock
that you could reformat that particular function answer to.
And remember, these are both dates that will update.
Now, is there a way to insert a date that will not update?
There is a keyboard shortcut that I'm a big fan of
that you can use to insert the current date into a cell.
I'm gonna click in cell A8, and then on my keyboard,
I'm going to do the following keyboard combination
for a PC computer, the Control key with the semicolon.
Now, the semicolon button is usually located
next to the letter L, as in lettuce,
on most laptop keyboards and normal keyboards.
So I'm gonna do Control, semicolon.
You'll see the current date is inserted into the cell.
Now, when I click on this cell,
you'll notice that this date is not a formula.
When I look at the formula bar, it's just a date.
So this is a keyboard shortcut
that will capture the current date off of your computer.
If you'd also like to include the time,
I'm gonna double-click after I've put in the date
and double-click right after the date
and hit a space bar to get my cursor in that cell.
Then to do the current time on my keyboard,
I'm gonna do Control, Shift, semicolon.
So three keys all at once, Control, Shift, semicolon.
This will insert the current time in the cell.
This date and time keyboard shortcut does not update.
So the next time I open this spreadsheet,
the date and time that are located in cell A9
will stay there, they will not update.
So keep these in mind when you start
recording financial information in your spreadsheets.
You can either use functions that will update
with equal sign today and equal sign now,
or you can use keyboard shortcuts like Control, semicolon
to insert dates that don't update.
Welcome back, let's talk about data validation.
Now, first of all, what is it?
It's a way to verify that what's being entered
in your cells is correct.
Data validation can be added to blank cells
or existing cells.
If you add validations to existing cells,
the contents of the cells will be grandfathered in,
meaning the data validation will not apply to them.
But if you go over the top and enter in something,
then the data validation will come into play.
Right now, I have a practice file
called Data Validation Open.
Feel free to use it to follow along.
Now, I'm gonna click in column H.
This is where I'd like to enter my validation.
My validation is that I don't want incomes
under 15,000 entered into the spreadsheet.
So they need to be 15,000 or greater.
For them to be validated in column H.
Now, I'm gonna start by highlighting the cells
that I'd like to apply the validation to.
I'm not gonna include cell H1,
but I'll highlight cells H2 down to H19.
Then, I'm gonna come up to the Data ribbon tab
and come over on the right-hand side
to the Data Tools group
and locate the Data Validation button.
It's usually right next to Remove Duplicates.
If I click on the button,
it's going to open up the Data Validation box.
I wanna start on the first tab at the left,
which is Settings.
Now, right now, you'll notice that it tells me
that my validation allows for any value.
This means there are no validations.
But I want to come in and tell it
that I only want to allow whole numbers in these cells.
Then, I need to pick the criteria.
In this case, they need to be whole numbers
that are greater than or equal to.
Then, I'll type in 15,000.
You don't need to worry about decimals or dollar signs
because those are just formatting.
Now, it's important to let your users of your spreadsheet
know what the validations are.
So, to do that, I'm going to come and fill out
the other two tabs that are part of the Data Validation box.
We're now gonna come to the middle tab,
which is Input Message.
Just like this name implies,
this is a message that will come up
as soon as a user clicks in the cell
to let them know what they need to enter.
So, I'm just gonna put an income amount,
and we're just gonna open the box up again.
And then below, I'll type in the text
to help let them know what they need to type.
You must enter an income amount, 15,000 or higher.
Very simple.
Now, what if they do enter the wrong thing?
That's what the third tab is for, Error Alert.
With the Error Alert tab,
there are three styles of error alerts,
but let me warn you that the only one
that actually keeps them from entering an incorrect thing
is the first one, which is the default, the Stop style.
Again, I need a title,
which I'm gonna call Incorrect Income Amount,
and then an Error Alert.
You have entered an incorrect income amount.
It must be 15,000 or higher.
Now, to try out my Data Validation, I'll now click on OK.
The first thing I'll see is if I click in any of the cells
in column H where I applied the Data Validation,
again, the input message comes up.
Now, if I go to a cell in that, again, column H,
and start typing in a value
that is not going to meet the Data Validation criteria,
it'll allow me to type it in, but as soon as I hit Enter,
that's when I'll see the error message come up.
And notice here, I can retry it.
I can also type the wrong thing again.
I can hit Cancel, but it will not allow me
to save an incorrect value in the cells
until I put the right thing in.
To turn off a Data Validation, you highlight the cells
where the Data Validation's been applied,
go back up to Data Validation.
In the bottom left-hand corner,
you will see a Clear All button.
This will remove all the validations,
so the validation rule will be cleared,
the input message and the error alert will all be taken off.
As soon as I click on OK,
you'll notice that all those things are gone.
So again, it's a great way to ensure
that the data that's being entered in your cells
is validated.
Hi, everyone.
I wanna introduce you to one of the most famous functions
in Microsoft Excel called VLOOKUP,
which stands for Vertical Lookup.
It's been around for a long time
and it is used all over the place.
Here's the concept.
Based on a column at the far left-hand side of my dataset,
I can use the VLOOKUP function
to find something on the far left column,
and then go over on the same row
and locate another piece of information and return it,
hence the name Vertical Lookup.
Now, why are these so popular?
Because you can use these
to quickly update information in several places,
from shipping cost, for example,
to quantity number, to even a customer number.
VLOOKUPs are used all over.
Well, let's do a very basic example.
I'm in a practice file right now called VLOOKUP.
Here's my situation.
In column A of my table, I have customer numbers.
I would like to be able to type in a customer number
in cell M2 and have it return the annual cost of tickets
that my customers are spending from column I,
because I don't wanna have to do the eyeball work
to look it up.
This is a good basic scenario for a VLOOKUP function.
So what I want to do is click in cell N2.
Please remember, you can use the practice file.
It's called VLOOKUP if you'd like to follow along.
Now, this is one that the first time you try it,
it's a great idea to use the Insert Function button
to help step you through the different arguments.
So after clicking in cell N2,
I'm gonna come up to the Insert Function button
on the far left-hand side of my formula bar,
which of course is the FX button.
Now, to get this function,
I need to type in VLOOKUP, all one word,
at the top portion of the Insert Function box
and click on OK.
And again, it stands for vertical lookup,
but they've cut it down to just V
to help save space in the name.
Now, VLOOKUP function by default
has four different arguments.
You'll see that the top three, lookup value,
table array, and column index number are all bolded.
The fourth argument isn't, which means it's extra.
You don't have to do it or optional,
but I wanna show you why it's important to remember.
So the first thing I need is a lookup value.
So in cell M2, I've actually entered
what the customer number is that I'll be looking for.
So I'm gonna click in cell M2 as my lookup value.
The second thing I need is the table
where my lookup value, and again,
what I want returned is located.
In this case, it's the table to the left,
cells L1 all the way over to J23.
So I'm highlighting that.
You could also use keyboard shortcuts to select it.
Does this table array have to be in the same workbook file?
It does not.
It's very common for the table array
to be in a separate workbook file.
Now, the third thing I need is a column index number.
Notice right here that if you look at the description
in the function arguments box,
it tells you that it has to be matching value
that will be returned, but also that it has to be a value.
It cannot be a letter.
A lot of people will say, well, just put letter I
because that's where you need to return the information from.
The problem with that is that you can't use a letter.
It has to be a value.
So therefore, what we're going to do
is count over the number of columns,
one, two, three, four, five, six, seven, eight, nine,
and return that it's the ninth column of data
we need returned based on the match.
That's how you get a column index number.
Now, this fourth value or fourth line
in the function arguments box is range lookup.
And like I said, it's optional,
but here's the important thing to know about range lookup.
If it's omitted, that means that Excel will assume
you're trying to do an approximate match.
That means that if your lookup values have decimals
or you might have similar text,
you can get, again, not an exact match.
So if you need to absolutely match the right customer number
and make sure it's on the correct row,
then it's very important right here
that you type in the word false
so that it always has to do an exact match
on your VLOOKUP value.
So I'm gonna click on OK.
Now, based here on the fact that I typed in customer 12
for the number, I'm gonna come in and look for customer 12.
It's Peter Boggins.
Then I'm gonna count over nine columns
and notice the value it's returning, 1686.
If I come in and I type in a different customer number,
like 19, which is Susan and Scott,
I can see how it's updating, again,
my yearly cost cell based on that.
So when we look at the formula,
again, what you have is function name,
then you're going to have your lookup value,
your table array, your column index number,
and because we want it to be an exact match,
we have the word false.
And that, my friends, is a very basic introduction
to the VLOOKUP function.
Welcome back.
I want to show you a type of function
that can replace a VLOOKUP.
One of the challenges of VLOOKUP functions
is that you can only look up a value from left to right.
This means that the column containing the value you look up
should always be located to the left
of the column containing the return value.
That's not always how our data is set up.
So what Microsoft has are two other functions
that you can combine to basically make an improved VLOOKUP.
This is called the index and match function.
Now I have a practice file for this called index and match.
Feel free to open it up and use it to follow along.
We're going to look at both functions separately
and then combine them together.
I'm on the index sheet of the workbook to start with.
If you click in cell A2,
you're going to see an example of an index function
and then we'll make one of our own.
Now the goal of an index function is to retrieve the value
at a given location in the range.
So you'll see right here,
my goal is to return this number one,
but let's look at the function first.
I'm going to double click on cell A2.
You'll see the function has three major arguments.
So equal sign index,
and then the range or the table array
that I'm looking through for the location
of a specific value.
Then I'm providing a row number.
So I count down four rows.
Then I'm going to go over six columns
and whatever is in that position in the table
will be returned.
In this case, it's a number one.
Let's try one of our own.
I'm going to click in cell B2.
Again, it starts with an equal sign
and then the word index.
Feel free to use the formula autocomplete
to help you along.
The first thing I need is again,
my range of cells or table array.
So I'm going to select those cells
by using keyboard shortcuts or left dragging.
It's A4 through G58.
Then I'll do a comma.
In this case, I want to return the 845
that's in cell G8.
So that means I need to go down five rows.
So I'll type in a five.
I also need to go over seven columns.
Notice I'm not doing a column letter,
I'm doing a column number.
Then I'll hit enter.
And we'll notice the answer is 845.
Now there's another again function
that you can use called a match function
and it's a little bit different.
To see this example, go to the match sheet in the workbook.
You'll see that I have an example
of a match function in cell D2.
Now, first of all, what is a match function designed to do?
It has one purpose and that is to find the position
of an item in a range.
So its answer is always going to be a vowel value
because it's telling you at what point
that particular item is located in a list.
Now, my goal is to find where this 25 is located
in this count of different products.
So if you double click on cell D2,
there is a match function for you to look at.
Again, it starts out with the function name of match
and then the lookup value, which is 25.
Then your list range, which is B2 through B5.
And then right here, you see this zero.
This is the match type.
And this is a little bit interesting
because if you need to exactly find where that value is,
you'll put a zero.
You can also do a negative one or a positive one.
Now, if you do a positive one,
it will find the largest value less than or equal
to your lookup value if your list is in ascending order.
And if you do a negative one,
it will find the smallest value greater than
or equal to your lookup value
if your list is in descending order.
But you can see based on the information I've provided
in the function, the location of the 25 is in first position.
Now, if you combine these two functions together,
you're able to create a function that's not based
on the VLOOKUP problem of having everything
be from left to right.
So you'll see an example when you go to the index
and match combined sheet.
And if you click in cell H2,
let's take a peek at this function
and then we'll create one.
It's two functions combined.
You'll see in this example that I've started
with the INDEX function.
Remember, its goal is to retrieve a value
at a given location.
Its range is A1 through D11.
But where I would normally have the row portion
of my INDEX function, I start my MATCH function,
which again, its goal is to just retrieve the value
at a given location.
I should say, its goal is to find the position
of the item in the range.
So in this case, the MATCH function is locating Chicago
for me in the list.
This is telling me that if I go into column B,
B1 through B11, it's going to help me know
where Chicago's located.
Then after the MATCH function,
I provide the column number for my INDEX function.
And by doing this, I get the location
of the Chicago customer total.
Now let's try this with Phoenix.
Again, same function, but trying to find
the Phoenix customer total.
And if we look, it's right here in cell D6.
So I'm going to hit an equal sign and start my function,
which is INDEX.
Again, you can use the formula auto-complete
to help you along.
Now, the first thing I need to do is provide my table array.
So it's going to be cells A1 to D11.
Then I'm going to hit a comma.
Now, normally I would have a row number at this point
to have it look down, but instead I'm going to start
my nested function, which is the MATCH function.
It replaces the row number.
Now I'll get another opening parentheses,
and now I need to provide what I'm looking up.
In this case, it's the city, Phoenix.
Now I need to provide the table array
or the list for the MATCH function.
It's going to be cells B1 down to B11.
Then I'm going to hit another comma.
What type of match do I need to do?
This is letting you see the zero, the positive one,
or the negative one.
I'm going to do a zero because I want to do an exact match,
followed by a parentheses.
Now, because this is still not done,
I need to provide the column number of my INDEX function.
I need to do another comma.
That comma is really important, by the way,
and easy to forget.
In this case, I need it to find the location of, again,
where Phoenix's customer numbers are in column four.
And so that's what I'm going to include.
And then I'll do another parentheses to finish out
the entire function, and I'll hit Enter.
So based on the information I've provided,
what it's doing is looking through the array,
but then it's going into the MATCH function
and finding Chicago.
Once it finds Chicago and we know its position,
then it goes back out to the INDEX function.
I should say it's finding Phoenix.
Apologies, guys.
And it's going over to the fourth column
in that table array and returning what's in that cell.
So it's more complicated than a VLOOKUP.
However, like I said,
it doesn't have the constraints of a VLOOKUP,
and that's why these are so popular.
Try one out, and again, you have the practice file
so that you can play with it on your own there.
Hey, everyone.
Do you ever get frustrated with VLOOKUP
and overwhelmed with INDEX and MATCH?
Well, Microsoft has created a new and improved function
that does very similar things called XLOOKUP.
Let's take a peek at it.
There is a practice file for this called XLOOKUP.
Please feel free to use it to follow along.
I'm gonna come into my spreadsheet and click in cell L5,
and then I'm gonna go up to the FX button
just so we can see the different parts of this function.
Then we'll try one out.
You'll see that the XLOOKUP function
has five potential arguments, but only three are required.
The first part of the function is the lookup value.
This is the last name that's located in cell K5.
The second portion is my lookup array.
This is where, again, my lookup value is located.
You'll see over here it's A5 through A60.
Then the return array.
This is what I want returned
that's next to my lookup value that's in my lookup array.
In this case, it's columns D and E.
Another great thing about XLOOKUPs
is they can actually return more than one item
as opposed to VLOOKUPs that can only do one.
Now, there are two additional arguments with this function
that we're not using because they're not required,
but I want you to see what they are.
The first one is if not found.
This means if there's no matching value found
based on the other parts of the argument,
you can actually have a message here or you leave it blank.
The fifth option is match mode.
Now, this is again where I think XLOOKUP
is way better than VLOOKUP
because remember with VLOOKUP,
we have to tell it false if we're doing an exact match.
Otherwise, it will do an approximate match.
Right here, you'll see that the great thing about XLOOKUPs
is they default to an exact match.
So you don't have to put anything here.
Otherwise, you can use values like negative one
and positive one to find approximate values
that are either larger or smaller
than the value that you're putting in
or what you're looking for.
Now, let's try one of these on our own.
I'm gonna come in beneath cell K5 and in K6,
I'm gonna type in my lookup value this time,
which is the last name of Bilko.
And I want it to return the sales
and the location for my last name of Bilko.
So I'm gonna create again an XLOOKUP function.
Let's go up and click on our insert function button
and open up the insert function box.
This function's name is XLOOKUP, all one word.
After I type it in, I'm gonna click on go.
It'll find it for me in the library
and I'll double click on it.
So the first thing I need is my lookup value.
This is of course cell K6.
Second thing is my lookup array.
Well, these are my last names.
I'm not gonna include cell A4
because it's just a column heading,
but I can use control shift down arrow
and get the range, which is A5 through A6.
Now the return array is the part again
of my data that I want returned.
In this case, it's the sales and the location column.
And to select this area, you can either left drag
or use your keyboard shortcuts.
I'm gonna click in cell D5
and then I'm gonna use my control shift down arrow,
arrow to the right and then arrow down
to select D5 through E60.
So I won't only return the sales,
but also the location for Bilco.
Now we know that we don't need match not found
because I should say if not found
because we know there's a match for Bilco
and match mode, if you leave it blank,
is set to an exact match, which is what we want.
We'll click on okay.
We'll see that it's returned to sales
and to harbors is location.
Let's check it out.
It's finding Bilco for me
and it's going over to column D and E
and it's returning two pieces of information.
So again, why is this better than index and match
and VLOOKUP in my opinion?
Number one, it's simpler.
Number two, it can return more than one item
as we're seeing here.
It's returning both sales and location.
Number three, it defaults to an exact match.
So those are just a few of my thoughts,
but please try it out.
I think you'll find that you can use it
in a variety of settings.
Maybe you don't have to be so dependent
on index and match.
Hi everyone, what do you do in Excel
when you need to arrive at a specific answer
or compare information?
Well, on the data ribbon tab in Excel,
there is a toolkit that helps you do that.
It's called What If Analysis.
We're going to basically explore two of the options
under the What If Analysis today.
Scenario Manager and Goal Seek.
You can also follow along with me
by using the practice file for this.
It's called Goal Seek and Scenario Manager.
Remember there's a link to the practice files
inside the description of the course.
What we wanna do is go up and select the cells
that we want to do a scenario for.
I'm on the Scenario Manager sheet right now.
Now what the Scenario Manager allows you to do
is plug different sets of values
into the same cells in your spreadsheet,
and then actually create a report that compares them.
So I'm gonna start by highlighting the cells
that I want to be able to plug different values into.
It's going to be cells A4, B4, and C4.
Then I'm gonna go up to the data ribbon tab
and go to the What If Analysis button.
It's in the Forecast group
on the right-hand side of the ribbon.
I'm gonna click and then come down to Scenario Manager.
It's the very first option in the menu.
Now you'll see that right now there are no scenarios,
so I need to start by clicking on the Add button
at the top right.
You name each of your scenarios.
I'm gonna call this First Try.
You can see the cells that it's going to allow me to change,
and then I'm gonna click on OK.
Now right now it's taking the values
that are already in the cells, which is great for me.
So I'm gonna go ahead and click on OK.
And now you'll see that the Scenario Manager box
is open again.
I'd like to add another set of values.
So again, I'm gonna click on Add and call this Second Try.
So you can continue adding more scenarios.
The next thing I need to do though
is type in what I want my new updated amounts to be.
So I can type in a different amount that I'm borrowing,
a different, again, term,
and also a different interest rate.
You can use decimals or you can use percentages,
whatever you're comfortable with.
Then I'll click on OK again.
Let me make sure I get the right term in there.
Now I wanna do one more.
This'll be my third try.
Again, same set of cells,
and I'll type in again a different amount
that I'm borrowing, followed by a different term,
or I could maybe use a similar term
to the one I already have, and a different interest rate,
making sure that I have enough zeros up here.
Now once I'm finished, I'm gonna click on OK.
I'll see all three of my scenarios in the box.
Now these save with your file,
so you don't have to worry about having them be deleted
if you close the file.
Let's say that I wanna see how the second try
is going to impact the amount that I'm borrowing
and my monthly payment.
I'm gonna select the scenario that I want,
and at the bottom of the box, there's a Show button.
This'll allow me in the same set of cells
to see the different scenarios.
Let's try the same thing with the third try,
and then I'll click on Show.
So this is allowing me to toggle between all three scenarios
without having to copy and paste anything.
Now the other neat thing is that you can create a report
that compares all of them at once.
In the Scenario Manager box, there is a Summary button.
When you click on Summary, you have two different choices.
The first one is to do a scenario summary report,
or you can do a pivot table.
We're gonna do the top one.
Also, we know that cells A4 through C4
are changing D4 through F4,
so I can see the resulting cells in my report.
So I'm gonna highlight those three cells,
D4, E4, and F4.
Notice again those absolute values on the cell references.
Then I'll click on OK.
My scenario summary report's gonna be placed
in a separate sheet in my workbook,
and what's really great is you see the current values,
each of your scenarios, along with the resulting cells
and how they've been changed,
and you can compare them side by side.
One additional thing you'll see here as well
is that there's outlining that allows you to expand
and collapse different levels of the report
at the row and column level by clicking on the letters.
Sometimes the outlining is more value than others,
but it is a way for you to condense the report.
But your scenario manager will always be back
on the original sheet you started in,
and when you go back to the What-If Analysis button,
you'll see the scenarios are always stored
inside the spreadsheet.
There's a limitation to how many scenarios
you can save a certain group of cells,
but it is a great way to allow you to plug the same values
or different values into the same cells.
Now let's look at one more, again, What-If Analysis tool
that's available called the Goal Seek.
This one's a little bit simpler.
I'm gonna go to my Goal Seek sheet in my practice file.
Now again, it's the same information,
but this time I'm trying to arrive
at a monthly payment of $2,000,
but I don't know what, again, I can borrow based on that,
so I'm gonna let the Goal Seek help me to find the answer.
First of all, what I wanna do is come in
to the What-If Analysis button again and go to Goal Seek.
It's the second option down.
Now what cell am I trying to set?
Well, it's cell D4.
So if you haven't already clicked in D4, do so.
Then the value I wanna set it to is 2,000.
Now the cell I'm going to change
is going to be the amount I'm borrowing.
So you can see here that you can set one cell
and change another, and they all have to be part
of the same formula.
Then I'll click on OK.
Now based on that, and it will show me
in the Goal Seek status box, it reports back
that if I set D4 to 2,000,
then it sets my term to 257,965.
So it's a very simple tool,
but it does provide a helpful option
when you're trying to arrive
at a specific answer in a formula.
So as always, try it out.
Welcome back.
Let's explore two more Excel functions,
the present value or PV function
and the future value or FV function.
Now I'm in a practice file called PV ampersand FV.
It's in the practice file,
so feel free to use it to follow along.
And I'm starting on the present value hyphen PV sheet.
Now, first of all, what is the PV function?
The PV function can be used
to find the present value of a loan.
So we return the principal amount of a loan
based on a steady interest rate, regular payments
and a set number of period for the payments.
So what I want to do is come to the sheet
and I'm going to click in cell.
Notice right here, E2,
and that's where my PV function will go.
This is a great time to use the insert function box as well
because this is a newer type of function.
So I'm going to type PV for present value,
locate the function and then click on okay.
Now there are five different arguments for this function,
but the bottom two are again extra.
Let's start with rate.
Well, this is the interest rate.
So I'm going to click in cell A2
where I see my interest rate.
The NPER is also important,
but here's one more thing I need to mention.
I want this to be broken down by month.
Therefore, I'm going to come in and add the ability
to have it show by a monthly amount.
So I'm going to go ahead and divide this by 12
because my payments will be made
on a monthly basis per year.
For the NPER,
this is going to be the part of the argument that's required
and it's the total number of payments.
But again, I'm making monthly payments
and right now the term is in years.
So I'm going to multiply it by 12
to represent the monthly payments.
The PMT is going to be the payment amount.
And again, this number is going to be negative
once I enter it into the formula
because it's an amount that I owe.
Then you'll see that there's an FV and also a type.
Now FV stands for future balance.
This might be a cash balance you want to attain
after the last payment is made.
And type is a logical value
where the payment's made at the beginning or at the end.
So if it's made at the beginning, you enter a one,
and if it's made at the end of the period,
you do a zero, so it's omitted.
We're just going to assume that it's at the end,
so we're gonna leave it omitted,
which we could also have put a zero in.
So you can see here how I'm breaking it down by month
and I'm gonna click on OK.
And again, based on this,
this is showing as a negative value,
that's why it's in red,
because it's an amount that I will owe.
So again, this is the amount that I will owe
based on a 20-year loan period broken down by month
and $800 payments.
Now let's go ahead and try out something else, future value.
Future value is different.
The future value function calculates the future value
of an investment based on a constant interest rate.
So it's very similar arguments,
but it's kind of doing the opposite.
In this case, you're investing, you're not owing.
So right here, you'll see that I have it set up.
I'm on the future value or FV sheet,
and in cell H2, we want to create a future value
to see what this will amount to.
You're gonna see I have a 12% interest rate
that I'm earning at.
I'm making 12 payments.
My payments are negative
because they're money that I'm putting into something.
And also maybe there's a cash balance, a present value,
and then the one represents the payments
due at the beginning of the period.
All right, let's try this out.
I've clicked in cell H2 again
after going over all those different argument parts,
and we're going to the insert function box.
This time, I'm gonna type in FV for future value,
locate it, and then again, we see that very similar
to the PV, this has five arguments.
The first one is my interest rate.
So I'm gonna go ahead and come in,
and I'm going to type in what my interest rate's going to be.
So I'm gonna click on cell A2
because that's where my interest rate is.
If I know that this is gonna be broken down by months,
I'm gonna divide it by 12.
The next thing I need is the number of payments.
Well, I'm doing 12 payments,
so I'm gonna go ahead and just click there
because I'm not going to be doing this over 12 years.
It's actually 12 months.
I'm not going to multiply it by 12.
Then my payment amount, you'll see here, is negative.
I'm going to click in cell C2 for that.
Then I have a PV.
This would be if there's a lump amount of money
already in the investment.
That could be my present value amount, and there is,
so I'll put that in.
For the type, because again, I wanna make sure
that this is being made at the beginning of the period,
I'm going to go ahead and type in a one
because that's the case.
Then I'll click on OK.
So based on all this information,
the interest rate, number of payments,
also that the payments are gonna be $1,000
and that there's a constant value already there
or a cash value of 1,000,
and the payments being made at the beginning,
you can see what the future value
of the investment will be.
Pretty cool, right?
So try it out.
Welcome back, everybody.
Let's explore another one
of Microsoft Excel's financial functions
called NPV or net present value.
Now this function will calculate
the net present value of an investment
by using a discount rate and a series of future payments,
negative values, and income positive values.
In the spreadsheet that I'm using,
and it's a practice file, by the way,
called net present value-NPV.
Feel free to open it up and follow along.
You're going to see that we have outlined
the different data required.
We have the annual discount rate.
Then we have the initial cost in year one,
and then the returns.
And notice they're negative
if they're money that's been invested,
and then they're positive if they're, again, income.
I'm going to put my formula in cell D2.
So I'm gonna hit equal sign
and then NPV for net present value and double click.
Now you're going to start by entering in the discount rate,
which is cell A2, then a comma.
The next thing I need are going to be
the different future payments
and also income over, again, a very steady period of time.
So we're doing yearly here.
So I'm gonna click in cell A3, do a comma,
and then I'm going to click in cell A4, do a comma,
cell A5, comma, and finally cell A6.
Then this is all included in parentheses,
but I don't need the closing parentheses.
I'll hit enter.
So based on, again, the annual discount rate
and then the different future payments
and the different income,
this is the current present value of the investment.
Super fun function, try it out.
Welcome back, everybody.
Let's look at another newer type of financial function
called the XNPV function.
Now this function is only available
in the newest versions of Microsoft Excel.
So that will be Microsoft Excel 365 for PC and Mac,
and then also Excel 2021.
If you have an older version of Excel,
this particular function will not be available to you.
Now, what exactly does the XNPV function do?
It returns the net present value
for a schedule of cash flows
that is not necessarily periodic.
Remember what the NPV function
that we've also done in this course?
It uses a very periodic schedule of cash flows.
This is when your schedule of cash flows is not periodic.
You can use the XNPV function for that.
I have a practice file.
It's called XNPV.
Feel free to open it up and use it to follow along.
I'm gonna click down in cell B9.
That's where I want to put my XNPV function.
I'm gonna start with an equal sign
and type in the name of the function, XNPV.
You can use the formula autocomplete to help you with it.
And also I'm gonna use the insert function button
so we see each of the three different arguments
that are part of this function.
The first one is a rate.
Now this is not actually a interest rate.
In this case, it's a discount rate
to apply to the cash flows.
I'm gonna be using 7% here, so I'm gonna put in 0.07.
The second argument is going to be the values.
This is the series of cash flows
that correspond to the schedule of payments,
like you see here in the box.
And I'm gonna go ahead and highlight cells A2 down to A6.
Now the third argument are the dates,
and this is the schedule of payment dates.
But again, what makes it different
is they are not periodic, right?
They can have different amounts of time between them.
So now I will highlight my dates,
which are B2 through B6.
Then I'll hit Enter.
And right here, you'll see my answer.
Now, if I don't like all the decimal places,
please remember you can go up to the Home Ribbon tab,
click on the dollar sign, and you can round that answer,
keeping in mind when you round decimal places,
your values can be rounded up or down.
But this is, again, a simple example
of the XNPV function.
Welcome back.
Let's look at another financial function
called the IRR function.
Now what this function does
is it returns the internal rate of return
for a series of cash flows
represented by the numbers and values.
Now these cash flows don't have to be even
as they would be for an annuity.
However, they must occur at regular intervals,
so monthly or annually.
The internal rate of return is the interest rate
received for an investment
consisting of payments, negative values,
and then income, positive values
that occur at regular values.
If you look at our data here, it matches that.
So in cell A2, and by the way,
I'm using a practice file called IRR function
for this activity.
Feel free to open it up and follow along.
You'll see that in column A,
we have, again, the setup costs for the business,
and then the next five years of net income.
You can see that.
The first thing I wanna do is figure out
the investment return after four years,
and then after five years.
This is, again, a good use case for an IRR function.
So I'm gonna click in cell B8
and start by typing my equal sign,
and then the name of the function in this case is IRR.
You can use the formula auto-complete,
but I'm also going to open up the insert function box
just so we can see the description
on these different arguments.
It's fairly simple.
The first one we need are the values,
and notice it tells us this is an array
or a reference to cells that contain numbers
that you want to calculate the internal rate of return.
A couple of things to know about these different values.
You have to have at least one positive
and one negative value
to calculate the internal rate of return.
Also, the IRR uses the order of the values
to interpret the order of cash flows.
So be sure you enter your payment and income values
in the sequence that you want.
And in this case, we've done that by year.
So I'm gonna go ahead and highlight
the first four years worth of data.
In my case, looking at the setup here,
that means that I want to do A2 down to A6.
Now, the guess field is optional,
and what it allows you to do
is basically have a guess that's close to your IRR.
We're going to admit it for this activity.
Then I'll click on OK,
and I'll see that I have my IRR right here.
The reason I'm getting the formula audit triangle of death
is because I didn't include cell A7,
but that's what we're gonna do down in cell B9.
Now what I want to do is calculate
the internal rate of return after five years.
So same process, equal sign, IRR,
and this time when I select my values,
I'll do A2 all the way down to A7 and hit Enter.
And again, we can see that internal rate of return percentage
that's being calculated for us.
It's a fairly easy function to do,
but keep in mind you have to have the right setup
so that it can work.
Hey everyone, I want to show you
how to start building your own loan schedule,
or what we call a loan amortization schedule in Excel.
Now there are lots of templates on the internet
and in Excel that will do this for you,
but because we want you to be familiar
with the functions you use,
we're going to do it on our own.
I have a practice file open
called Loan Schedule with PMT Function.
Please use it to follow along.
Now notice up in cells B3 through B6,
I have information that I'll need to use
in the PMT function,
which stands for payment function, by the way.
We're gonna use it to calculate
the total amount of periodic payments
that stay constant through the entire duration of the loan.
Basically my monthly payment.
So I want to click in cell B9
because that's where my PMT function will go,
and I'm gonna start by typing my equal sign and PMT.
Now this is another one that's good to use the FX button
or insert function so that you get an idea
of what the different arguments are.
The first argument is the interest rate,
which is in cell B3.
Now the tricky thing here is because I want it
to be broken down by month,
I'm gonna divide it by cell B5,
which represents 12 payments per year.
For the second argument, NPER,
this is the term of the loan,
which is of course cell B4.
But again, it's a two-year term,
but a payment every month.
So I'm going to multiply that by cell,
again, B5 representing that monthly breakdown.
The third required argument is PV,
which is the amount I'm borrowing,
which will be in cell B6.
The two additional arguments will add more details,
but we just need the first three for this formula to work.
And I'll click on OK.
Now you'll notice that when the formula result
is in the cell, it might be negative or red,
and that's because it's an amount
that you'll be paying back to your borrower.
Now, is this a formula that you can drag?
Unfortunately, it isn't,
because it needs to have absolute references.
So to fix that, what I'm gonna do
is come into the formula itself in the formula bar,
highlight all the cell references,
and then hit my F4 key up on my function keys.
This adds absolute references
in front of all of the cells
that are referenced in the function,
and then I'll hit Enter.
Now I'm gonna go back to the original formula,
get the black crosshair for the autofill, and drag it down.
And now I'll see, again, the monthly payment
for each month for this loan.
And this is, again, all being done through the PMT function.
Try it out, it's a fairly simple one.
Hi, everyone, I want to introduce you
to another financial function in Excel
that can be important when you're billing out
a loan schedule.
This is called the PPMT function.
And it calculates the principal portion
of a loan payment for a given period of time
based on a constant interest rate and a payment schedule.
There are four required arguments for this function,
but we're gonna show you an example and try it out.
So first of all, just notice
that I have a new practice file open.
It's called PPMT function.
Feel free to use it
so that you can try this particular function out.
I'm gonna click in cell C7.
This is where my PPMT function is going to go.
Now, the first thing I need to do is type an equal sign
and then enter in PPMT.
Make sure you get both the P's in there.
And then I'm gonna do my opening parenthesis.
Now, I'm also gonna go up
and use the insert function button
to make it a little bit easier
to see the different arguments of the function.
First thing is my interest rate.
And you can see up here in cell B1,
I have my 8% interest rate.
But we want to slow down here
because this particular principal amount
needs to be broken down by month
because I'm making monthly payments.
Therefore, I need to come in and divide it
by those 12 payments that I'll make in a given year,
which is cell B3.
Now, the PER is a little bit different here.
The PER is the first payment
for the period that I'm gonna start paying.
And it happens to be in cell A7.
So I'm gonna enter that in.
Then the NPER is the total number of payments
made during the time that I have the loan,
which is going to be, of course, cell B2.
But again, it's 12 payments in that period of time.
So now I need to multiply this by cell B3.
Now, I'm not done because I need to finish out
by doing the fourth required argument, which is PV.
This is how much I'm borrowing, which is cell B4.
Now that I've got all those portions filled out,
I'm going to click on OK.
Now, right now the function's working.
However, there are a few parts of this function
that need to be absolute or fixed references.
The B1 divided by B3, I need to go select that
in the formula bar and F for it
to make it an absolute reference.
And then the B2 times B3, including the B4 at the end.
The only part of the function that will not be fixed
is the A7, because I always want it to go back
and refer to that, again, breakdown.
Then I'm gonna hit Enter.
Now what I wanna do is go and select my formula,
drag it down, and what I want you to see
is that this principal amount changes
as I make more payments on the loan,
because my loan amount is getting paid off
further and further as I put more money
towards the principal.
Again, a very important part of figuring out
a loan schedule is separating the amount
that you're paying between interest and principal.
Hey everyone, let's look at a function
that focuses on the interest of a loan.
This is called the IPMT function.
And basically what it does is it returns
the interest payment for a given period
of an investment or loan based on periodic
constant payments and a constant interest rate.
So what we're going to do is open up
the practice file called IPMT and try it out.
You'll see here that there's some set data for us.
We have an interest rate, the period of time
for which we want to find, the amount of interest,
the loan's term, and then also the value of the loan.
I wanna start by figuring out what the first month
will be for interest.
To do this, I'm gonna hit an equal sign
and then start typing in my function, IPMT,
and then double click on it to get, again,
the formula autocomplete to kick in.
Now with this function, if we go up and click
on the insert function button, you'll see
that there are four required, again, arguments.
The first one is going to be, of course, my interest rate.
So I'm gonna click in cell B2 in this case.
And again, because it's a monthly payment,
I need to divide this by 12.
The next thing I need is, again, the period
for which I want to find the interest.
In this case, it's the first period of the loan
or the first month.
So I'll click in cell B3.
Then the next thing I wanna do, or in this case,
I could just type in the amount,
but I wanna click in the cell.
The next thing I need is the number of payments.
This is the total number of payments for the investment,
which is three years, but again, it's broken down by month,
so I need to multiply that by 12.
Fourth argument is the present value.
In this case, it's, again, cell B5.
So now that I have all these pieces in my function,
I'm gonna go ahead and hit Enter.
Now, what we'll see is this is formatted
as a negative value in red,
and that's because this is the interest,
so this is the amount of money that I owe.
Now, the second one we wanna do is to figure out
the interest due in the final year of the loan
that has yearly payments and a constant term, right?
So again, I'm gonna type an equal sign
and my function, IPMT,
and again, if you wanna use that formula autocomplete,
feel free.
The first thing I need is my payment or my interest rate,
so I'm gonna come in and select, again, cell B2.
That's where my interest rate is.
Then I need the number of, again,
the period for which I want.
In this case, it's going to be period three,
so I'm actually just gonna type that in
because that means it's the third year.
Then I'm gonna come in and do my number.
This is the total number of payments.
In this case, that's going to be cell B4,
and finally, the present value of the loan,
which is the 8,000.
Then I'm gonna click on OK to finish out the function,
and we'll see that in this case,
during the last final year of the loan,
the yearly payment on the interest will be $292.
Now, this is, again, rounded,
so if I were to come up to my Home ribbon tab
and I were to increase the decimals,
we'd see that it would add a few decimal places
onto this formula, so this one's all about the interest.
Hello, let's look at another financial function
that helps you to locate cumulative interest paid on a loan
between a start period and an end period.
This is called the C-U-M-I-P-M-T function,
or CUMIPayment, like cumulative payment,
but really, it's interest that we're focused on.
I have a practice file here for this.
It's called C-U-M-I-P-M-T, the name of the function.
Please use it to follow along with me.
Now, this function is a lot like the PMT functions
that we've already used.
Also, be aware that this is a fairly new function.
It works in Excel for Microsoft 365,
Excel Microsoft 365 for Mac,
Excel for the web, and Microsoft Excel 2021.
So, how do you use it?
Well, in the practice file, I'm gonna click in cell B5
and then hit an equal sign
and start typing in the name of the function, CUMIPMT.
Feel free to use the formula autocomplete
to help you type that in.
And then to see the different arguments,
we're gonna go up and use our friend
the Insert Function button
because it helps to describe the different arguments.
Right here, you're gonna see that the first one is rate,
which is the interest rate, which is in cell B2.
But because I'm breaking it down by month,
I'll need to divide it by 12.
The number of payments or term
is going to be in cell B3,
but again, we're breaking it down by month,
so I'm gonna multiply that by 12.
Now, the next thing we have is what's being borrowed,
the present value.
That's in cell B4.
This is where it gets a little bit different.
I have to tell it what period of time
to calculate the interest for.
For me, it's the second year,
so I'm gonna do months 13 through 24.
Now, there's one final part to this function
that is not accounted for in the Insert Function box.
So, up in my formula bar,
I'm going to put a comma zero.
This is called type.
What happens here is if you're accounting
for the interest being all the way
through the end of the payment period,
it's going to need to be a zero.
If it's going to be happening at the beginning,
you'd put a one, and you have to add this,
because if you don't, you can get a number sign error message
in the formula.
Then I'm gonna click on OK.
Again, it's negative, right,
because it's interest being calculated on the loan.
Let's try one more.
This is going to be interest paid
in the very first month of the loan,
but we're gonna use the function to calculate it.
Again, I've clicked in cell B6.
I'm hitting an equal sign.
We're gonna type in the name of the function,
and then we're gonna get the opening parenthesis.
Same thing.
Let's go up to the Insert Function box,
and again, very similar arguments.
Interest rate divided by 12, right,
and then we need the term,
which is in cell B3, multiplied by 12.
Then we need the present value being borrowed,
which is cell B4.
This time, though, my start period will be one,
and my end period will be one,
because I'm only accounting for the interest accumulated
during the very first month.
In addition, I only want it to account
through the end period of the payment,
especially since this is the first month.
So what I'm gonna do is rather than putting it here
in the Function Arguments box,
I'll go up to the Formula Ribbon tab,
type a comma zero, and then I'll click on OK.
And you can see here, this is just calculating
the first month's worth of interest.
Very cool, again.
Keep in mind, if you do this in Excel for the web,
and you want to get the proper format,
you'll need to select the cell where the formula is located,
go up to the Home Ribbon tab to the Number group,
and then format it as a number and make it general.
But again, the reason these values are negative
is because interest is something
that you will pay on the loan.
Very cool function, try it out.
Hey everyone, after spending so much time
looking at the different functions
that are involved with a loan schedule,
it's important to note that there are actually
several pre-built tools that come from Microsoft
that will do a lot of the functions for you
and give you a head start.
A lot of these are gonna be contained
in the Microsoft templates.
Now keep in mind, some organizations choose not
to give employee access to these templates,
but if you do have the right licensing
and they're available, they might be another opportunity
to save time when it comes to setting up a loan schedule.
To access these, we're gonna go up to the File Ribbon tab
and come down to New, but rather than just opening up
a new blank template, I'm gonna type in the word Loan,
and then do a search.
And you'll see that there are several different types
of templates built around loans inside of Excel.
There's three that I wanna show you.
The first one is the Loan Calculator.
Now this one, when you click on it,
it will tell you about it.
It's provided by Microsoft.
This is important to note because it means
that it's been vetted by the Microsoft team,
and you can also see a description of what it does.
I'm gonna click on Create.
Now you'll see that there's already information fed in here,
but notice I can come into any of these cells.
It gives me a description of what the items are,
and then I can type my own information over the top.
So for example, if I come in here,
I can actually change the values for the loan amount,
for the interest rate, also for the term,
and even, for example, when the start date
of the loan will be, and based on that,
it will actually begin showing me information.
I can even adjust, for example,
the monthly payment that I'm going to make,
and it will update the entire loan schedule below
based on that information.
This saves me having to make my own loan schedule.
Now another one that's similar to this,
if you go back to File and New again,
and search again for templates
based on the search topic of loan,
that I also like is the Loan Analysis Spreadsheet
or Worksheet.
This one, again, is a simple idea built by Microsoft,
but it lets you, again, put in your own interest rate,
loan term, and amount,
and then it breaks down the monthly payments
and it calculates interest.
So you can see here how I have the information
provided at the top that I can also title,
and remember, these are templates,
so the templates are like a stamp
that you base your own files on.
So you can name it, then you can come in
and customize it and save it
as a normal Microsoft Excel spreadsheet.
A final one to also look at
that's built around the idea of building a loan schedule
that's, again, provided by the templates in Microsoft
is going to be the one that's in this list
called the Loan Amortization Schedule.
This one's really great to use
when you're taking out a loan from a financial institution.
And you'll see here that at the top,
you can put in all the information around the loan,
even who the lender is,
and then down below, it builds your schedule.
So it's the same idea that we've been putting together
with all these different individual functions
that are the financial functions,
but the great thing about these spreadsheets
is they are pre-built,
and by basing them on a template,
you're saving yourself having to go through
and make the entire schedule on your own.
But by understanding some of the functions we've covered
in this particular second part of this course,
it will help you understand why the loan schedule
is being calculated the way it is.
So try some of these templates out
if you have them available to you,
because they might save you a significant amount of time.
Hey, everyone, thank you so much for joining us
for this Excel for Finance and Accounting course part two.
In this course, we've explored a lot of functions
that are part of Microsoft Excel
that can help you, again, do financial accounting.
We've looked at the XLOOKUP function.
We've also spent time in Microsoft Excel
exploring being able to build our own loan schedule.
We've also spent time exploring
some of the different financial functions,
for example, the PPMT function, the PMT function,
and even the MPV function.
All of these functions are the beginning
of being able to use Microsoft Excel
to craft your own financial data.
Please join us for the next course, the part three,
where we're gonna spend time exploring
this idea of financial accounting in more detail
with things like being able to create specialty charts,
like combo charts, pivot tables, pivot charts,
waterfall charts, lots of charts,
and also building out some of our own finance case studies
using Microsoft Excel.
Thanks for watching.
To earn certificates and watch our courses without ads,
check out LearnItAnytime.com.
♪
