How To Create A Loan Amortization Table Using Excel

This article will explain how to create a loan amortization table in Microsoft Excel using a 30 year mortgage as an example. It will show you how make a dynamic table so that you can easily make changes based on the loan amount, interest rate, length of the loan, and even additional principle. I will take you step by step so even if you have little or no experience with Excel you will be able to create a very useful loan amortization schedule.

1. Open a new worksheet and put the following headings in Column A, Rows 1-4:

a. Rate

b. Number of Years

c. Loan Amount

d. Monthly Payment

2. In Column B, Rows 1-3 enter the following figures:

a. 5%

b. 30

c. $100,000

Your worksheet should now look like the first picture below.

3. In Cell B4 enter the following formula exactly as it is here:

a. =PMT(B1/12,B2*12,-B3)

This is the Payment function. The first part (B1/12) is the rate. It is divided by 12 because the payments are made monthly. The second part (B2*12) is the number of payments. It is multiplied by 12 for the same reason. The third part(-B3) is the present value of the loan. It is negative because it is the amount that you owe, so to you it is a negative value. If you entered the formula correctly, you should see a monthly payment of $536.82.

4. In Row 1, Columns E-I enter the following headings:

a. Month

b. Interest Payment

c. Principle Payment

d. Additional Principle

e. Remaining Principle

Your worksheet should now look like the second picture below.

5. In Column E, Rows 2-4 enter the numbers 0,1,2 (one number per cell). Highlight those three cells and drag them down until you get to 360. How to drag: after highlighting the three cells there will be a small black box in the bottom right hand corner of the cells. Click on it and hold. You can now drag the mouse down the column and it will automatically fill in the numbers for you.

6. In Cell I2 enter the following formula

a. =B3

7. In Cell F3 enter the following formula

a. =I2/12*$B$1

The $ symbols tell Excel that it is an absolute reference, so that when we drag the formula it will always refer to the same cell.

8. In Cell G3 enter the following formula:

a. =$B$4-F3

9. In Cell I3 enter the following formula:

a. =I2-G3-H3

10. Highlight Cells F3 though I3 and drag them down all the way to month 360. If you have done everything correctly, the principle remaining (Cell I362) should be $0.00.

Your worksheet should now look like the third picture below.

11. Enter a dollar amount into the Additional Principle column and notice how the rest of the cells update automatically. The Remaining Principle column will turn negative at the end. The point at which it is $0.00 or negative is the point at which the loan is paid off.

12. Play around with changing the interest rate, number of years, and loan amount.

I hope this step by step guide to making a loan amortization table in Excel has been helpful. If you have any questions feel free to ask. This table is designed for fixed rate mortgages, but will work for any loan with a fixed interest rate.

For information about mortgages check out my other articles: