Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces around the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. Across the globe, millions of job advertisements requiring Excel skills are posted every day. At a time when digital skills jobs are growing much faster than non-digital jobs, completing this course will position you ahead of others, so keep reading. In this last course of our Specialization Excel Skills for Business you will build on the strong foundations of the first three courses: Essentials, Intermediate I + II. In the Advanced course, we will prepare you to become a power user of Excel - this is your last step before specializing at a professional level. The topics we have prepared will challenge you as you learn how to use advanced formula techniques and sophisticated lookups. You will clean and prepare data for analysis, and learn how to work with dates and financial functions. An in-depth look at spreadsheet design and documentation will prepare you for our big finale, where you will learn how to build professional dashboards in Excel.
Practice Video: Loan Schedule (PMT, EDATE)
Loading...
Из курса от партнера Macquarie University
Excel Skills for Business: Advanced
279 оценки
Из урока
Financial Functions and Working with Dates
This module introduces financial functions into our Excel knowledge base. Financial functions are often used in businesses and Excel has a variety of tools to help you with that. This week, you will learn to apply date formulas in calculations, create loan schedules with financial functions, as well as use depreciation functions.
Познакомьтесь с преподавателями
Dr Yvonne BreyerDirector, Learning and Teaching
Faculty of Business and Economics
In the last video,
we were introduced to some of Excel's financial functions,
and we saw how we could use them to calculate return on investments.
In this video, we're going to look at how we can use the payment function,
to calculate what the repayments would be on a loan.
More specifically, Alex wants to develop
a nice generic loan schedule that his customer
can use to create a loan schedule for any loan amount.
Now, to make life easier,
we've just put some values in to start off with.
And now, what we want to do is calculate the monthly payment,popppolls offcie
but there are a few things we need to do before that.
The customer has set the loans up so the payment frequency can either be monthly,
or annually, and we need to make sure that our schedule can cater for either.
So, the first thing we're going to calculate is
how many repayment periods we have per year.
To do this, we're going to use a simple IF statement.
So we're going to check if our payment frequency is equal to monthly.
If it is, then our periods,
PO, are going to be 12.
Otherwise, it's going to be one.
So, that has given us our periods per year,
and that makes it easier to calculate our number of repayment periods.
So, we're going to say equals,
take our term which is the length of the loan,
and multiply it by periods,
by years, and that gives us our total repayment periods.
We also want to calculate our rate per period.
Remember, the interest rate is calculated over the repayment period,
not necessarily over the year.
So we're going to type equals,
click on our annual interest rate,
and divide that by the number of periods per year,
which is fine as long as interest rates are annualized,
like in this example.
If it were annual,
we divide by one,
and we get our seven percent.
But this is the rate we're going to use to calculate in our payment calculation.
Let's now go and calculate what our monthly repayments are going to be.
So, we're going to type equals and PM,
and select our Payment Function.
Now, we worked out what our rates will be,
so we're going to click on the rate field.
We've also worked out our number of repayments.
So, we're going to click on NPER.
And our present value is the amount we're borrowing,
so we're going to click on our loan amount.
You will notice future value is an optional argument.
If you plan to have a residual amount left on your loan at the end,
then you would simply specify a future value.
But as we plan to pay off the full amount,
we're going to ignore that argument and just press enter.
Now, remember that payments are outgoing.
The money moving away from us,
so they would show up as a negative.
If you don't want that,
double click back on the formula,
and pop a minus in front of the payment. Problem solved.
If that's the case, we'd like to know what our projected interest is going to look like.
We know we're going to have to pay back $200,000 principal.
But how much are we going to have to pay back in the way of interest?
Now, to calculate that,
we're going to click into here and type equals.
We're going to take our monthly repayments, times our number of repayments,
so we're going to click on monthly repayments times by
180 to get the total amount we're going to pay back.
But bear in mind, that part of that is going to be our principal.
So we're going to subtract the principal amount from that,
and the total amount of interest we're going to pay on that loan is $123,578,
which is quite a lot.
So, that's how we could work out our basic repayment.
But what we'd like now is to work out a repayment schedule.
Which will give us the added option to make
additional payments and thereby bringing down this interest amount.
So what we're going to do is click into our cell, G19,
and pull through our loan amount,
so we can use that in calculations for our outstanding balance.
We are also going to pull through our first repayment date,
so we can use that to calculate our due dates.
We'll start by looking at our due date calculations.
For that, we're going to use our end date function.
And we looked at that earlier this week,
so we're going to type equals and select EDATE.
Our start date will be the date above.
So, I'm just going to click on B20,
and then the number of months to add is going to
vary depending on our repayment frequency.
So, we're going to pop a little IF in there.
And once again, we're going to check if our payment frequency is equal to monthly.
Then we're going to add one month,
if it's not, it's annual.
So we're going to add 12 months.
Close the bracket for your IF.
Close the record for EDATE.
And you can see that it has now added one month to the date.
But if I were to change this to annual, it adds a year.
And of course, all the other figures that depend on this have updated accordingly.
Let's put that back to monthly, though.
In a moment, I've got to copy this calculation down.
But I don't actually want the dates to go past the actual loan schedule,
so we're going to add another little bit to
his calculation to check if the previous balance is zero.
And if it is, to not display the data at all.
So double clicking back into this formula,
we're going to put in an IF.
We're going to check, is the previous balance less than or equal to zero?
Now, I've used the less than as well,
just in case it falls below the zero mark,
it shouldn't do, but just in case.
If it is, we're going to leave this blank,
if it isn't, we're going to use our EDATE calculation.
And again, don't forget to close your bracket.
And because the previous balance is zero,
it's now hiding the date,
but we'll copy that down regardless.
Okay, next thing we need to do is calculate our payment due.
Now, at its simplest,
that's going to be our monthly payment amount.
But it is possible that if they've made any additional payments,
that final payment might be less,
and we don't want to overcharge them.
So, we're going to need to use another little IF.
But something else we're going to do to make our life easier,
we're going to name these cells.
And I should have done that earlier, but now is fine.
So what I'm going to do is select the cells I want to name,
and labels on the left,
and I'm going to come up to my formulas tab and I'm going to click Create from selection,
and I want to use the left column,
so I'm going to say OK.
I'm then going to do the same for the rate per period,
because I'm going to be using this one as well.
In fact, in this one,
I'm going to use a different method.
I want to just call this Rate.
So I'm going to select the cell,
come in here and type Rate.
And we looked at name ranges in an earlier course,
so you should be fine with this.
Okay, now let's come and calculate our payment due.
We're going to put in a little IF over here,
and we're going to check if the balance from the previous period plus
the interest to you is actually less than the monthly payment.
So we're going to say balance plus interest,
which hasn't been calculated yet but we'll get there,
is less than the monthly payment.
If that is the case, there we're only going to pay the balance plus the interest due.
So, balance plus interest due.
If it's not, then we're actually going to pay the monthly repayments.
You can see by naming the range,
I don't need to worry about putting the dollars in,
and it's just much quicker to create the formula.
So I'm going to close that and press enter.
That's what I would expect in this case.
And I'm going to double click to copy that down.
Now the reason that's happened is,
where the cell is not,
it's been formatted to not show anything.
It just keeps the schedule a little bit neater.
And because I've got no balance over there,
I don't have anything due.
Once we complete the schedule,
that will all correct itself.
The next thing I need to do,
is calculate the interest due.
And that's very easy.
That's going to be the previous month's balance times my rate.
And again, I can double click to copy that down.
To get my principal,
I'm going to take the amount that was paid and remove my interest.
I'm also going to factor any additional payments,
so principal is going to be equals payments due,
less interest, but add in any additional payments.
And then to get my new balance,
I'm going to take my previous balance,
less any principal I just paid.
And then I can copy both of those two down.
And we now have our completed loan schedule.
You'll see the total payments we're making,
the total interest actually paid,
which does match our projected interest.
And we now have our completed loan schedule.
And the advantage of doing it this way,
is if I now decide, "Well,
I'd actually like to pay an extra $500 in the first
month," watch what happens to the values over here.
It actually adjusts to show the actual interest paid.
And now, we can add one last little calculation which is our estimated savings.
And that's going to be our projected interest less our calculated total interest.
And by paying $500 up front,
we stand to make an $916 saving.
And this is the saving made at the end of the loan.
And that's how you can use financial functions,
and some of the other techniques we've looked at over the weeks,
to calculate the completed loan schedule.
In the next video, we're looking at some other financial functions.
Ознакомьтесь с нашим каталогом
Присоединяйтесь бесплатно и получайте персонализированные рекомендации, обновления и предложения.
Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.
© Coursera Inc., 2018 Все права защищены.
