Calculating Extra Loan Repayments In Excel Part 1

This is Part One of a three part series on using excel for loan calculations for beginners. I’ve used Excel 2007, but all calculations can be performed just as easily on earlier versions.

In Part One we will set up our loan or amortisation table, in Part Twowe’ll tweak some of the formulae and pull out some information into a mini report and run some scenarios, and Part Three we will link our results in our mini report to our budget and have a look at how running scenarios will affect our cash flow.

Check out the PMT tutorial before starting this one to create the initial data table.

Of course, making loan calculations can be done easily using free online loan calculators, but that’s not as fun as doing it yourself in excel, and this way you can link your information into your budget to run some dynamic financial scenarios.

I’ll start with the same information from the PMT exercise. All results are based on the assumption that the interest rate remains fixed throughout the entire loan term and that interest is calculated monthly and payments made monthly. It does not take into account bank fees.

Then we’ll add some headings for the amortisation table.

As the repayments are monthly and there are 360 months over the term of the loan, we’ll be entering the numbers 1 – 360 down the column. Use the fill – series button to complete the column quickly.

The beginning balance in the first row (period 1) is the same as the principle amount. We’ll use a formula to put that amount into our table so if we choose to change the principle amount later on, then our whole table and results will change automatically. The dollar signs in the cell reference makes it absolute: it locks in the principle amount so that it wont move. Use the F4 key to insert the dollar signs into your formula automatically.

We’ll use the same formula for the payment amount.

We will add an extra line under our data to show the extra repayments, and use the same formula in our table to link to this amount. For the moment I’ll leave it as $0.

And then total payment is a simple addition formula.

We can also summarise this total payment amount using an addition formula under our data so that we can link this to our budget later on.

To calculate the interest amount for the month, we need to multiply the monthly interest rate by the beginning balance at the start of the month. So that we can fill our formulas down, we will need to lock in the cell reference for the interest rate in our formula using the F4 key to insert dollar signs into our reference, making it absolute.

To calculate the principle portion of the monthly repayment, subtract the interest amount from the total payment amount.

The ending balance is just a matter of subtracting the principle amount from the balance at the beginning of the month, as it’s only the principle portion of our repayment that comes off the loan balance.

Row two is slightly different. The beginning balance is the same as last months ending balance, so put a formula in to point to L3. As we want this formula to change as we fill down the rest of the table, we’re not going to lock it in as an absolute reference.

The rest of the row can be copied down by selecting across the row from payment amount to balance and grabbing our fill handle and dragging down. Have a look at the formulae in row 2 by double clicking. You should find that the absolute references still point to our initial data, but the rest of the formulae have changed appropriate for row two. The ending balance has also reduced by the principle portion of our payment.

Now fill the rest of the table all the way down to period 360 by selecting all of row 2 from beginning balance to end balance and filling down using our fill button.

You should find that at the bottom of the table, after the last (360th) monthly payment, the end balance is $0.

In part 2 we’ll edit some of these formulas ready for entering extra repayments, set up a mini report and look at how changing some of the variables will affect total interest paid and the length of the loan period.

Have you read these posts?

1. Calculating Extra Loan Repayments In Excel Part 2
2. Calculating Extra Loan Repayments in Excel Part 3
3. Using The PMT Function In Excel To Calculate Monthly Debt Repayments
4. Tutorial: Building A Basic Budget In Excel (For The Absolute Excel Beginners) Part 2
5. Tutorial: Building A Basic Budget In Excel (For The Absolute Excel Beginners) Part 1