0:03

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.

Â