Repayment Calculator: Calculate Payments

Quick reference

• 00:04 In this exercise, we're going to calculate the repayment of a loan.
• 00:08 And we're going to draw up an amortization table at the bottom to show the opening
• 00:13 balance, interest, payment, and the closing balance for each period.
• 00:18 But let's get started on what my payment would be if I take out a loan based on
• 00:23 certain arguments.
• 00:25 So let's say I want to buy a car.
• 00:27 And it would cost around $22,000.
• 00:30 I may or may not have saved up for a deposit.
• 00:33 And let's say I saved up a deposit of $2,000.
• 00:36 That will of course reduce the amount of money I need to borrow and then pay back.
• 00:42 The interest rate,
• 00:43 well, I've done a little bit of research and let's say the institution I want to
• 00:47 borrow the money from is probably going to loan me the money at 8.5%.
• 00:51 And they're willing to agree to a loan period of five years.
• 00:56 And because I'm going to make monthly payments, I have to adjust the period of
• 01:00 five years to show the number of months, but that's easy enough.
• 01:04 We just put in 5 times 12 and that gives me 60.
• 01:07 Okay, one more thing to know about my interest rate,
• 01:11 that's an annual rate and the period of the loan is in months.
• 01:16 And the values I've indicated on the side, are the arguments for
• 01:21 the function we're going to use, which is pmt.
• 01:24 So, to calculate the payment, I start with = PMT.
• 01:30 My rate is the interest rate.
• 01:32 But remember that is an annual rate, so, when we click on cell C5,
• 01:37 we need to divide that by 12 to get the monthly rate.
• 01:42 And comma, and nper is the number of periods, so that's 60.
• 01:47 And we can click on that.
• 01:48 The present value is the 22,000 less our deposit of 2000.
• 01:54 So let's do the total amount minus the deposit to get the present value needed.
• 02:01 I have two other arguments, future value and type, and they're optional.
• 02:06 You'll use the future value argument if you want to make a balloon payment at
• 02:11 the end like a final deposit of $2,000 or $3,000 right at the end.
• 02:16 The type is whether you're making the payment at the beginning or
• 02:20 end of the month, but we can ignore that for now.
• 02:23 So when we hit Enter, we see a monthly payment of $410 in parentheses,
• 02:28 and that just means it's a negative number.
• 02:31 Because while the $22,000 we're borrowing is money coming in,
• 02:36 the 410 in parentheses is money going out.
• 02:39 So that's why it's shown as a negative here.
• 02:41 So, there are other financial formulas in Excel that use the same values here.
• 02:47 There's one that calculates present value.
• 02:50 And there's one where you calculate the number of periods.
• 02:53 Depending on what you're trying to figure out, you'll use some or all of these same
• 02:58 values, but the financial formulas work very similarly to one another.
• 03:04 So this 410 is my payment.
• 03:05 And of course we can change up any of the values above to see how things change.
• 03:11 If I decide that I want to see if I can buy a nicer model car,
• 03:15 maybe I change the amount borrowed to 30,000.
• 03:18 If my deposit interest rate and periods stay the same,
• 03:22 I'll be paying almost $575 a month.
• 03:25 If I go to another bank and maybe they're prepared to loan me money at a smaller
• 03:30 rate, 6.95%, we plugged that in and you see the payment goes down.
• 03:36 If I change the period of my loan to four and a half years,
• 03:39 well that influences my payment as well.
• 03:42 There are many different scenarios you can play with here to figure things out,
• 03:46 adjust the total amount at the top, and
• 03:49 you see how it affects your monthly payment.
• 03:52 Perhaps you haven't been able to save any money so
• 03:55 you can change the deposit to nothing.
• 03:58 And we're back to a 605 monthly payment.
• 04:02 Well, you might look at these numbers and say this is all good information,
• 04:07 but I know for a fact I can only afford a $550 monthly payment.
• 04:11 So, the question is, what's the value of the car that I can buy?
• 04:16 Well if we know what we want our payment to be,
• 04:19 we can work backwards from this formula to figure out the present value.
• 04:23 If you go up to the data tab, under the What If Analysis,
• 04:28 you can click on Goal Seek.
• 04:30 This is where you can tell Excel your goal for
• 04:33 a specific amount to see how it impacts another value in the formula.
• 04:39 We want cell C8, our monthly payment, to be 550.
• 04:42 Or in this case, -550 since it's a monthly payment.
• 04:46 And what we want to know is, if that is -550,
• 04:50 what could we change cell C3 to, which is the amount to borrow.
• 04:58 And if I can afford 550 a month for that period and that interest rate,
• 05:04 you can see I can afford a car worth 25,442, nice.
• 05:08 So, that concludes this part of the exercise.
• 05:10 In the next video, we'll do the amortization table so
• 05:14 we can calculate the situation every month, as well as the total for
• 05:18 the period in interest paid and payments made.

Lesson notes are only available for subscribers.