How do I build a loan amortization schedule in Excel with PMT, IPMT and PPMT?

Use these three functions to build an amortization schedule in Excel: PMT, IPMT, PPMT. This walkthrough follows a template workbook. The template assumes you’re making monthly payments. The payments are the same at the start, and will be the same each month as long as the rate stays fixed and no extra principal is paid.

The key thing you need to know about debt is that every payment has two components: Interest, and principal. Early on, a big chunk of each payment covers interest. As the balance gets lower, a smaller amount of each payment is interest. This is because the less you owe, the less interest you’re charged.

Amortizing a Loan Payment

There’s a formula for amortizing a loan payment. And it’s ugly.

It’s P x r x (1+r)^n divided by ((1+r)^n - 1) (P principal, r rate, n payments).

Thankfully, you don’t have to memorize this because Excel has a PMT function you can use instead.

PMT requires five arguments. We’ll only cover three required:

• ”rate” is the interest rate on the loan
• ”nper” is the number of payments you’re planning to make
• ”pv” is the starting value of the loan.

The scary part is that often rates are listed on a yearly basis, while payments are assumed to be monthly. So the annual rate has to be divided by 12 to be accurate, and the loan term, measured in years, has to be multiplied by 12 to get the right monthly schedule.

One quirk of PMT is that it’ll spit out a negative number by default. If you put a minus sign in front of the function or put in your loan balance as a negative number, it’ll give the correct answer instead.

Here’s how PMT works: If you have a $100,000 loan at 7 percent, for 12 months, your formula is =PMT(.07/12,12,-100000). Your monthly payment will be $8,652.67.

Broken Down into Principal and Interest

Of course, if you want to do the amortization schedule by hand you have a lot of other stuff to do. Every payment needs to be broken down into principal and interest.

Key thing to remember about interest: Each month, interest is computed based on what the balance was on the 1st of the month.

The formula is the annual rate of your loan, divided by 12 months to convert to a monthly rate. That rate is multiplied by the opening balance of the period you’re calculating.

This balance declines each month as a portion of each payment is allocated to repaying the balance.

You can calculate the interest charge using IPMT. There are six arguments for the IPMT function. We’ll focus on two of them.

• Per stands for period, and is the period number you’re looking to get the interest for. (1 for the first period, 12 for the twelfth period). It must sit between 1 and nper.
• Nper is for the total number of periods your loan runs.

This time we’ll assume the same $100,000 loan at 7 percent, just one year again. The interest for the first month on this loan is =IPMT(.07/12,1,12,-100000). That gives you $583.33.

Change the period to 2 and you’ll get the interest for the second period, $536.26.

Notice this interest figure is less than the one above? That’s because a portion of the first payment went towards principal. So the interest for the second month is lower because interest is calculated on a lower balance.

Once you’ve computed the interest, you can see what was applied to the principal balance. That’s total payment, minus the interest portion.

For the first month, the principal payment is $8,652.67 minus $583.33, or $8,069.34.

Another way to do this is to get the principal repayment directly with PPMT.

• rate is the interest rate.
• per is for period number. Must be between 1 and nper.

Now we’ll fill out the PPMT formula for the amount of principal we paid in the first month. =PPMT(.07/12,1,12,-100000) gets us $8,069.34. Matches the subtraction formula.

=PPMT(.07/12,2,12,-100000) gets $8,116.41.

One last way to check your calculations is to add up the interest and principal payments for each month. You should get the same amount as the PMT you calculated for the payment. For example, the interest of $536.26 in Month 2 plus $8,116.41 in principal totals $8,652.67, which is exactly the PMT you calculated for payment amount.

A Linked, Dynamic Spreadsheet

Sure you could manually write the interest and principal amounts into the interest and principal columns. Or, you could set up a calculation in every cell so the whole repayment schedule populates itself. Here is my view.

The template includes a one-year loan amortization schedule on a Fixed Schedule sheet. You can see how the payment amount stays steady. The interest portion gets smaller, until the last month. At the end, the loan is repaid.

That schedule is only for one year, but many amortizing term loans will be longer than a year. Just add periods if you need them and hide the ones you don’t use.

Here’s the easiest way to get a linked title. Use the ampersand - which acts just like CONCAT in Excel - to join some text and the term into a title. Since the title’s linked to the amortization schedule, if you change the loan term, the header will, too.

The problem with using an amortization schedule is you might not want all the information you get. When you start off with an amortization that shows 30 years of payments and you only want two, it’s a little silly to show everything past two years. That’s when conditional formatting can come in and really help you out. All those rows get hidden.

This is key: highlight the entire calculator section. The max size of the range. On the Variable Periods sheet it’s B15 through F375 - thirty years of monthly payments.

Next, click on the Home tab and choose Conditional Formatting-New Rule. Then pick “Use a formula to determine which cells to format” from the list. For the next part, you have to put in a formula that looks at the number of months to show, and turns unwanted months’ font white. It will only hide on a white background.

Testing time. This rule tests whether the month number in column B is at least as high as our month count plus one. The plus one, it turns out, allows for month zero, which doesn’t have a payment.

This formula has an anchor because I want the total months, and that’s what’s in cell C8. Keep in mind that if your goal is to have that formula pick up total months, the whole thing needs to be locked. So it’s $C$8. Instead, the B column in row 15 only has a locked column, $B15, since B holds month numbers.

In plain English, that formula says “If any of the months in column B is equal to or greater than C8 plus 1, then make the font white.” Otherwise, show the row. So those months are hidden.

One way I see to make a good backup is by throwing each calculation inside an IF statement so that only when the month is within the term does the calculation have something in it. For the other cells, a blank is what’s in double quotes. Take the month-one interest in E16: If this month is less than total months plus 1, do this IPMT. If not, return “”.

You can even let the user make extra payments to principal balance. It’ll let them shorten the term of their loan. Of course, this means a bigger change to the earlier worksheets in the workbook.

The Additional Principal Paydown sheet handles this. We assume here that you will pay extra principal every month, $500 by default, but you can put whatever you want into each month.

The sheet uses an IF statement to pull in the extra amount of principal, but only if it is less than the prior balance minus principal repaid this month.

With these additional payments to principal, the IPMT and PPMT formulas would break. So both are dropped: interest is the opening balance times annual rate/12; principal is schedule payment minus interest.

Those extra principal payments will adjust the balance, too. Your balance would drop not only by the amount of principal repaid, but the extra paydown.

Of course, there are other adjustments you might want. For variable interest rate loans, the Variable Interest Rate worksheet lets a user adjust the annual rate in any of the months they choose. The user will go month by month and put in what that rate is. It is an annual rate.

On this sheet, everything from the extra paydown sheet still applies. Only the scheduled payment formula is changed.

In this formula, the payment for month one gets the initial payment formula. But if the rate stays the same, use the simple route: copy it from the row above.

However, once the rate changes, the PMT calculates the scheduled payment based on the new rate. The formula is wrapped in a MIN function so once the loan is repaid, the payment becomes zero.