Repayment Calculator: Create an Amortization Table

Quick reference

• 00:05 In our last lesson, we figured out we could buy a car worth a little more than
• 00:09 $25,000 based on our ideal monthly payment of 550 a month.
• 00:14 The terms include a 6.95 interest rate for 4 and a half years.
• 00:18 Now, we want to create an amortization table.
• 00:21 I can start off with adding periods, and I need 54 of them.
• 00:25 So, add the first three to indicate to Excel your pattern.
• 00:30 Select those first three, and drag the fill handle down for 54 periods.
• 00:35 And I'll add a few extra here just to show you what happens if you have them.
• 00:39 Let's assume we signed all the paperwork in October and
• 00:42 the first payment will be due at the end of that month.
• 00:45 We can use the EO month function to get the payment date, as the same month.
• 00:51 So we put in a 0.
• 00:52 So the next payment is one month later.
• 00:56 So what we can do is use the EO month formula,
• 00:59 select the date above, and enter a one to get one month later.
• 01:03 So we see November 30th.
• 01:05 If we copy this formula down by double-clicking on the fill handle,
• 01:09 the formula adds all the end of month dates through the end of the list of pay
• 01:13 periods.
• 01:16 So let's say I want this formula to give me some kind of indication that the period
• 01:21 of the loan has expired, okay?
• 01:22 Let's see if we can get the end of the loan.
• 01:25 End-of-month year, EO month of the first month,
• 01:30 but it will continue for another 54 months.
• 01:34 Now, end of this month is a 0.
• 01:37 So we want the end of the previous one, which will be 53.
• 01:41 So we just need to enter D6 minus 1,
• 01:45 that takes us to the end of March 2025.
• 01:50 And if we look at the number of periods 54,
• 01:53 the 54th payment would be due the end of March 2025.
• 01:56 So that's correct.
• 01:59 Now, can we adjust this formula to show us where to stop?
• 02:03 Yes.
• 02:04 Yes, we can.
• 02:05 We can say if that end of the month is still smaller than or
• 02:10 equal to and still before the end of the loan period and
• 02:14 lock the reference with dollar signs.
• 02:18 Then, show me the end of the month B141, otherwise just type out stop.
• 02:25 So we do that and the first one seems to be working fine.
• 02:32 Let's copy it all the way down.
• 02:35 And they're at 55, we have our stop.
• 02:37 Our loan only extends to there.
• 02:41 So I can delete these few extra rows.
• 02:44 We leave 55 for now, so you can see how the loan is supposed to close and end,
• 02:49 all right?
• 02:49 Now, an easy part, the opening balance is the amount we
• 02:54 borrow from the bank, minus any deposits.
• 02:58 I'm not going to copy that formula down, so
• 03:01 I don't need to lock any references with dollar signs.
• 03:05 How we calculate the interest amount.
• 03:08 Well, the interest is whatever we owe at the beginning of the month
• 03:12 times the interest rate that we agreed on.
• 03:15 So lock the interest rate with the dollar sign, but remember that's an annual
• 03:21 interest rate, so we have to divide by 12 to get the monthly value.
• 03:26 And we've got 147.35 for the first month.
• 03:30 My payment is the 550.
• 03:33 So we lock that with dollar sign and
• 03:36 closing balance will be opening balance plus interest, plus payment.
• 03:42 And remember it's plus, because the payment is already negative, so
• 03:46 don't subtract that.
• 03:48 And there's my closing balance.
• 03:51 The opening balance next month is the closing balance from the previous month.
• 03:57 The interest this month is the same calculation I used for the previous month.
• 04:01 Same with the payment and same with the closing balance, so
• 04:05 I can copy these, and paste them in the next line.
• 04:08 And now at this point, since we've got our fixed references in
• 04:14 place with dollar signs, and these cells refer to the ones above,
• 04:19 I can copy my formula all the way down.
• 04:22 So we select the four cells.
• 04:24 Double click on the film handle and now watch what happens.
• 04:29 Our last period is 54, so our last payment is on that date.
• 04:33 After the last payment, the closing balance is 0.
• 04:36 So that's how to check if your amortization table
• 04:39 is done when your closing balance ends in 0.
• 04:42 In other words, I could really stop there, so we can delete the extra formulas after.
• 04:48 Now to calculate your total interests, I just add a sum,
• 04:53 hold Shift, click on the last value, close parentheses and
• 04:59 then Ctrl+C and Ctrl+V to copy and paste that.
• 05:04 And that's my total payments for the loan EMI total interest for the loan.
• 05:09 It would be nice if I could convert this into a table because then I add
• 05:13 the interest column and use structured references.
• 05:17 And as my table extends, my sum will always be correct.
• 05:20 But the problem with the table in this instance is that my formulas have to be
• 05:24 consistent all the way down.
• 05:26 It's only consistent from the second row down and not the first.
• 05:30 So that's a little bit of a problem, but this all still works.
• 05:35 So, let's try this on something else.
• 05:37 Let's try it on a house.
• 05:39 I want to buy a house.
• 05:40 Let's make this an easy one, a million dollars.
• 05:44 Let's dream big, interest rate is 6.95%.
• 05:48 Obviously, we're not going to pay this off in five years.
• 05:51 Let's make it 20.
• 05:52 Now, that'll be my monthly payment, and my loan still start somewhere in October.
• 05:59 Now, it ends in 2040.
• 06:02 I can use the same amortization schedule except I can't stop at 54 payments.
• 06:06 I have to copy down and make it 240 payments, so
• 06:10 that's 240 monthly payments over 20 years.
• 06:14 So we extend this down, it's more or less there, let's see.
• 06:18 Okay, yep and there is my stop.
• 06:21 So I can delete the extras, and I can delete these formulas.
• 06:26 And now, my interest formula is easy enough to update, I can copy and
• 06:30 paste the formula.
• 06:31 And if I buy a house of a million dollars and pay it off over 20 years,
• 06:37 I'll pay 1.8 million total of which 850,000 does interest.
• 06:42 So that is an amortization table.
• 06:45 I really hope it helps you out with your next big purchase.

Lesson notes are only available for subscribers.