TutorialsSoftwareMicrosoft ExcelExcel Tutorial: How to make a loan amortization table

Excel Tutorial: How to make a loan amortization table

Written by Marc Roberge | 02 June 2010

Excel Loan Amortization 1

In this tutorial, I will show you how to create a fully automated loan amortization table in Microsoft Excel that will show how much of your payment is being applied towards the principal and how much is interest.

We will be using the “loan_calculator” we built in our tutorial titles “Excel Tutorial: How to make a loan payment calculator using the PMT function”. If you didn’t follow that tutorial or don’t have the “loan_calculator” file, you can download it here:

This is what our worksheet looks like.

Let’s add some titles in cells E1, F1 and G1.

We’ll also plug in our Total Amount from our Loan Calculator into cell H1 by typing “=B10*-1”. We’re multiplying the value in B10 by –1 to convert it to a positive value. Don’t forget to press the Enter key.

In cell D2, type in the number “1”…this is the bare minimum number of payments one could apply against a loan.

In cell D3, type in the following formula (=if(D2<$B$7,D2+1,””):

What this formula does is check whether the previous payment number is smaller than the total number of payments to be made (=IF(D2<$B$7….). If it is, increase the current cell number by the payment number in the previous cell +1 (…, D2+1…). If it is greater than the total number of payments make the cell blank (…,””).

Next, we will transfer our Payment Amount to our amortization table. In cell G2, insert the following formula:

This checks to see if there is a payment number in column D or if the cell is blank. If the cell is blank, we make the selected cell blank as well. Otherwise, print our payment amount from cell B9. Our cell reference to B9 is of the absolute type which means that the column (B) and row (9) references are preceded by the $ sign…which forces our formula to ALWAYS point to cell B9, regardless where our formula is on the worksheet. Again, we multiply the value in B9 by –1 to make it positive.

In cell H2, we will type in the following formula:

Again, the leading if statement with the first two comma separated statements simply verify if there is a numbered payment in column D. The last statement in the formula simply subtracts the payment amount from the remaining balance of the total owing amount.

Our next formula will calculate which portion of our payment is interest. In cell F2, insert the following:

Besides the leading If statement, the formula reads as Periodic Interest Rate * (Total Amount - (Payment Number-1)*Payment Amount.

Calculating how much of our payment is applied against the principal only requires a simple subtraction. The formula to enter in cell E2 is:

Select the E2:H2 cell range. Using the copy handle (the small black square on the bottom right of the selection indicated here by a blue arrow), copy the field down one row.

You should get the following:

Now, select the cell range D3:H3 and, using the copy handle, copy down to row 650. I realize this seems a bit excessive, but this will allow amortization of long term loans including 25 year mortgages paid bi-weekly. You can always extend this further if you need to.

The amortization table automatically displays all payments with amounts applied towards principal or interest and balance owing.