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.