Amortization Schedule in Excel

Amortization schedules help you calculate and visualize monthly payments. These may be for cash loans, mortgages, and other financing arrangements. The math can get a little tricky, so it’s a great idea to build these using spreadsheets. Let’s learn how to build an amortization schedule in Excel.

How to Build an Amortization Schedule in Microsoft Excel

Let’s say that you’ve taken out a loan to finance the purchase of a new car. Of course, like any loan, this one has terms. You’ve borrowed $30,000 over 5 years, at an annual rate of 4.00%. You’re now curious what your monthly payment will look like, and when you’ll have the loan fully paid off based on that payment amount.

Begin by inputting these numbers into a blank Excel worksheet. Here, we’ll use cells G1, G2, G3, and G4.

Then, add some columns. Remember, your payment will consist of two parts: the principal and the interest. You’ll need to calculate these values for each of the payments.

Construct a layout that looks like this. It’s the framework for your amortization schedule. Now, it’s time to start adding formulas.

The first step is to calculate the payment. For this, you can use the PMT (Payment) function in Excel. Keep in mind, this formula only works with constant interest rates.

PMT will utilize three inputs:

• Annual interest rate divided by the total number of annual payments
• The total number of payments
• The amount of the loan

Click into the first blank cell in your Payment column. Then, type an = sign. This tells Excel that you’re inputting a formula. Then, insert the PMT formula as follows:

=PMT($G$1/$G$3,$G$2*$G$3,$G$4)

Hit Enter, and Excel returns your payment amount: $552.50.

Now that Excel has calculated your payment, you need to then find the principal and interest components of the payment. For the first step, Principal, we’ll use a modified version of the PMT function called PPMT. PPMT, as you might guess, stands for Principal Payment. Place the formula here in cell C2. Type =PPMT, and continue by inputting the following formula:

($G$1/$G$3,A2,$G$2*$G$3,$G$4)

The complete PPMT formula is:

=PPMT($G$1/$G$3,A2,$G$2*$G$3,$G$4)

As you can see, it’s identical to the standard PMT function, with the added inclusion of the payment number associated with the given payment. Hit Enter to return your Principal amount: $452.50.

Now, arrow over to cell D2. This is where you’ll calculate your interest, using, you guessed it, the IPMT function. The IPMT function, beyond its first letter, is identical to PPMT. In this case, your IPMT formula reads:

=IPMT($G$1/$G$3,A2,$G$2*$G$3,$G$4)

Hit Enter again, and Excel returns your Interest: $100.00.

Finally, you need to compute your Remaining Balance. This is a very simple calculation: simply add your Principal in cell C2 to the Principal Amount of $30,000:

=SUM(C2+G4)

Hit Enter once more. Think about what this is telling you. After your first payment is made, you’ll owe $29,547.50 against the original $30,000.

Now, it’s time to calculate the payment amounts for month 2 and beyond. Worried about inputting formulas again? Don’t be. Because we used $ signs in the formulas, we can simply click and drag to repeat the calculations. Dollar signs in Excel lock in cell references, ensuring you’ll see accurate data every time.

To copy formulas, click and drag to highlight the range A2:E2. Then, drag the lower right corner of the selection downward. Excel will copy your formulas down. You’ll need to change only the Remaining Balance formula. In cell E3, delete the existing value and input:

=E2+C3

This takes the remaining balance from the prior month and decreases by the principal paid in the current month.

Finally, to complete your amortization schedule, you can click and drag downward again. Remember, you’ll have a total of 60 payments against this loan. From here forward, all formulas automatically update correctly.

As you can see, it’s easy to build a loan amortization schedule in Excel using formulas.