*The tutorial shows how to build an amortization schedule in Excel to detail periodic payments on an amortizing loan or mortgage.*

An **amortizing loan** is just a fancy way to define a loan that is paid back in installments throughout the entire term of the loan.

Basically, all loans are amortizing in one way or another. For example, a fully amortizing loan for 24 months will have 24 equal monthly payments. Each payment applies some amount towards principal and some towards interest. To detail each payment on a loan, you can build a loan amortization schedule.

An **amortization schedule** is a table that lists periodic payments on a loan or mortgage over time, breaks down each payment into principal and interest, and shows the remaining balance after each payment.

To build a loan or mortgage amortization schedule in Excel, we will need to use the following functions:

- PMT function - calculates the
**total amount**of a periodic payment. This amount stays constant for the entire duration of the loan. - PPMT function - gets the
**principal**part of each payment that goes toward the loan principal, i.e. the amount you borrowed. This amount increases for subsequent payments. - IPMT function - finds the
**interest**part of each payment that goes toward interest. This amount decreases with each payment.

Now, let's go through the process step-by-step.

For starters, define the input cells where you will enter the known components of a loan:

- C2 - annual interest rate
- C3 - loan term in years
- C4 - number of payments per year
- C5 - loan amount

The next thing you do is to create an amortization table with the labels (*Period*, *Payment*, *Interest*, *Principal*, *Balance*) in A7:E7. In the *Period* column, enter a series of numbers equal to the total number of payments (1- 24 in this example):

With all the known components in place, let's get to the most interesting part - loan amortization formulas.

The payment amount is calculated with the PMT(rate, nper, pv, [fv], [type]) function.

To handle different payment frequencies correctly (such as weekly, monthly, quarterly, etc.), you should be consistent with the values supplied for the *rate* and *nper* arguments:

**Rate**- divide the annual interest rate by the number of payment periods per year ($C$2/$C$4).**Nper**- multiply the number of years by the number of payment periods per year ($C$3*$C$4).- For the
**pv**argument, enter the loan amount ($C$5). - The
**fv**and**type**arguments can be omitted since their default values work just fine for us (balance after the last payment is supposed to be 0; payments are made at the end of each period).

Putting the above arguments together, we get this formula:

`=PMT($C$2/$C$4, $C$3*$C$4, $C$5)`

Please pay attention, that we use absolute cell references because this formula should copy to the below cells without any changes.

Enter the PMT formula in B8, drag it down the column, and you will see a constant payment amount for all the periods:

To find the interest part of each periodic payment, use the IPMT(rate, per, nper, pv, [fv], [type]) function:

`=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)`

All the arguments are the same as in the PMT formula, except the **per** argument that specifies the payment period. This argument is supplied as a relative cell reference (A8) because it is supposed to change based on the relative position of a row to which the formula is copied.

This formula goes to C8, and then you copy it down to as many cells as needed:

To calculate the principal part of each periodic payment, use this PPMT formula:

`=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)`

The syntax and arguments are exactly the same as in the IPMT formula discussed above:

This formula goes to column D, beginning in D8:

To calculate the remaining balance for each period, we'll be using two different formulas.

To find the balance after the first payment in E8, add up the loan amount (C5) and the principal of the first period (D8):

`=C5+D8`

Because the loan amount is a positive number and principal is a negative number, the latter is actually subtracted from the former.

For the second and all succeeding periods, add up the previous balance and this period's principal:

`=E8+D9`

The above formula goes to E9, and then you copy it down the column. Due to the use of relative cell references, the formula adjusts correctly for each row.

That's it! Our monthly loan amortization schedule is done:

Because a loan is paid out of your bank account, Excel functions return the payment, interest and principal as **negative numbers**. By default, these values are highlighted in red and enclosed in parentheses as you can see in the image above.

If you prefer to have all the results as **positive** numbers, put a minus sign before the PMT, IPMT and PPMT functions.

For the *Balance* formulas, use subtraction instead of addition like shown in the screenshot below:

In the above example, we built a loan amortization schedule for the predefined number of payment periods. This quick one-time solution works well for a specific loan or mortgage.

If you are looking to create a reusable amortization schedule with a variable number of periods, you will have to take a more comprehensive approach described below.

In the *Period* column, insert the maximum number of payments you are going to allow for any loan, say, from 1 to 360. You can leverage Excel's AutoFill feature to enter a series of numbers faster.

Because you now have many excessive period numbers, you have to somehow limit the calculations to the actual number of payments for a particular loan. This can be done by wrapping each formula into an IF statement. The logical test of the IF statement checks if the period number in the current row is less than or equal to the total number of payments. If the logical test is TRUE, the corresponding function is calculated; if FALSE, an empty string is returned.

Assuming *Period 1* is in row 8, enter the following formulas in the corresponding cells, and then copy them across the entire table.

**Payment** (B8):

`=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")`

**Interest** (C8):

`=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")`

**Principal** (D8):

`=IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")`

**Balance**:

For *Period 1* (E8), the formula is the same as in the previous example:

`=C5+D8`

For *Period 2* (E9) and all subsequent periods, the formula takes this shape:

`=IF(A9<=$C$3*$C$4, E8+D9, "")`

As the result, you have a correctly calculated amortization schedule and a bunch of empty rows with the period numbers after the loan is paid off.

If you can live with a bunch of superfluous period numbers displayed after the last payment, you can consider the work done and skip this step. If you strive for perfection, then hide all unused periods by making a conditional formatting rule that sets the **font color to white** for any rows after the last payment is made.

For this, select all the **data rows** if your amortization table (A8:E360 in our case) and click *Home* tab > *Conditional formatting* > *New Rule…* > *Use a formula to determine which cells to format*.

In the corresponding box, enter the below formula that checks if the period number in column A is greater than the total number of payments:

`=$A8>$C$3*$C$4`

After that, click the *Format…* button and pick the white font color. Done!

To view the summary information about your loan at a glance, add a couple more formulas at the top of your amortization schedule.

**Total payments** (F2):

`=-SUM(B8:B360)`

**Total interest** (F3):

`=-SUM(C8:C360)`

If you have payments as positive numbers, remove the minus sign from the above formulas.

That's it! Our loan amortization schedule is completed and good to go!

Download loan amortization schedule for Excel

The amortization schedules discussed in the previous examples are easy to create and follow (hopefully :). However, they leave out a useful feature that many loan payers are interested in - additional payments to pay off a loan faster. In this example, we will look at how to create a loan amortization schedule with extra payments.

As usual, begin with setting up the input cells. In this case, let's name these cells like written below to make our formulas easier to read:

- C2 - annual interest rate (named
*InterestRate*) - C3 - loan term in years (named
*LoanTerm*) - C4 - number of payments per year (named
*PaymentsPerYear*) - C5 - total loan amount (named
*LoanAmount*) - C6 - extra payment per period (named
*ExtraPayment*)

Apart from the input cells, one more predefined cell is required for our further calculations - the **scheduled payment amount**, i.e. the amount to be paid on a loan if no extra payments are made. This amount is calculated with the following formula:

`=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")`

Please pay attention that we put a minus sign before the PMT function to have the result as a positive number. To prevent errors in case some of the input cells are empty, we enclose the PMT formula within the IFERROR function.

Enter this formula in some cell (G2 in our case) and name that cell *ScheduledPayment*.

Create a loan amortization table with the headers shown in the screenshot below. In the *Period* column enter a series of numbers beginning with zero (you can hide the *Period 0* row later if needed).

If you aim to create a reusable amortization schedule, enter the maximum possible number of payment periods (0 to 360 in this example).

For *Period 0* (row 9 in our case), pull the **Balance** value, which is equal to the original loan amount. All other cells in this row will remain empty:

Formula in G9:

`=IF(LoanAmount<>"", LoanAmount,"")`

This is a key part of our work. Because Excel's built-in functions do not provide for extra payments, we will have to do all the math on our own.

Enter the following formulas in row 10 (*Period 1*), and then copy them down for all of the remaining periods.

**Scheduled Payment** (B10):

You simply pull the *ScheduledPayment* amount (named cell G2) if that cell is not empty:

`=IF(ScheduledPayment<>"", ScheduledPayment, "")`

**Extra Payment** (C10):

Use a nested IF formula with the following logic:

- If the sum of
*ExtraPayment*(named cell C6) and the scheduled payment for the current period (B10) is less than the balance remaining after the previous period (G9), return*ExtraPayment*; or else - If the difference between the balance after the previous period (G9) and the scheduled payment for the current period (B10) is greater than zero, return that difference; or else
- Return zero.

`=IFERROR(IF(B10 + ExtraPayment<G9, ExtraPayment, IF(G9-B10>0, G9-B10, 0)), "")`

**Total Payment** (D10)

If the sum of the scheduled payment (B10) and extra payment (C10) for the current period is less than the balance remaining after the previous period (G9), return that sum, otherwise return the balance remaining after the previous period:

`=IFERROR(IF(B10+C10<=G9, B10+C10, G9), "")`

**Principal** (E10)

Simply, subtract the interest (F11) from the principal (D10):

`=IFERROR(D10-F10, "")`

**Interest ** (F10)

If the balance remaining after the previous period (G9) is greater than 0, divide the annual interest rate (named cell C2) by the number of payments per year (named cell C4) and multiply the result by the balance remaining after the previous period; otherwise, return 0.

`=IFERROR(IF(G9>0, InterestRate/PaymentsPerYear*G9, 0), "")`

**Balance** (G10)

If the sum of the scheduled payment (B10) and extra payment (C10) for the current period is less than the balance remaining after the previous period (G9), subtract the principal portion of the payment (E10) from the balance remaining after the previous period; otherwise return 0.

`=IFERROR(IF(B10+C10<=G9, G9-E10, 0), "")`

If all done correctly, your loan amortization schedule at this point should look something like this:

Set up a conditional formatting rule to hide the values in unused periods as explained in this tip. The difference is that this time we apply the **white font color** to the rows in which *Total Payment* (column D) and *Balance* (column G) are equal to zero or empty:

`=AND(OR($D10=0, $D10=""), OR($G10=0, $G10=""))`

Voilà, all rows with zero values are hidden from view:

As a finishing touch of perfection, you can output the most important information about a loan by using these formulas:

**Scheduled number of payments:**

Multiply the number of years by the number of payments per year:

`=LoanTerm*PaymentsPerYear`

**Actual number of payments:**

Count cells in the *Total Payment* column that are greater than zero, beginning with Period 1:

`=COUNTIF(D10:D360,">"&0)`

**Total extra payments: **

Add up cells in the *Extra Payment* column, beginning with Period 1:

`=SUM(C10:C360)`

**Total interest: **

Add up cells in the *Interest* column, beginning with Period 1:

`=SUM(F10:F360)`

Optionally, hide the *Period 0* row, and your loan amortization schedule with additional payments is done! The screenshot below shows the final result:

Download loan amortization schedule with extra payments

To make a top-notch loan amortization schedule in no time, make use of Excel's inbuilt templates. Just go to *File* > *New*, type "*amortization schedule*" in the search box and pick the template you like, for example, this one with extra payments:

That's how you create a loan or mortgage amortization schedule in Excel. For hands-on experience, you are welcome to download our Amortization Schedule Sample Workbook I thank you for reading and hope to see you on our blog next week!

## 3 Responses to "How to make a loan amortization schedule in Excel (with extra payments)"

Dear sir,

Please i need a loan amortization excel sheet that has weekly repayment.

thanks.

Nice tutorial but what if your totals (e.g. in 1st tip, checking interest + principal payment) do not agree?

How do we handle rounding? Looks to me like the IPMT and PPMT result in rounded results that aren't easy to fix. But further down, where you show extra payments, it appears you're calculating the interest & principal application without use of the IPMT or PPMT functions. Looks to me like the if you want to avoid rounding issues, you should avoid the IPMT & PPMT functions, only use the PMT function to get the periodic payment, and then calculate the periodic amounts with regular arithmetic. Yes? No? Maybe? Thanks

Hi Ken,

In theory, the rounding error cannot exceed 0.5 cents (0.005 dollar). So, the Payment and Interest + Principal may not agree only by 1 cent or less. If the difference is bigger, then there is likely to be something wrong with your model or formulas.

I say "in theory" because in practice, Excel only shows the values rounded to 2 decimal places in cells. The underlying values returned by PMT, IPMT and PPMT are not rounded. To make sure of this, you can choose to show more decimal placed in formula cells.