Table of Contents
### Calculating Principal Payments

##### Figure 3.2. This amortization table shows how the principal payment increases while the interest payment decreases over the life of a loan.

##### Figure 3.3. Using the `PPMT` function to calculate the principal component of loan payments.

Entire Site

When you make a payment on a loan, each payment is divided into two parts:

Part of the payment is for that month’s interest charge.

The remainder of the payment goes toward paying down the principal.

Each month you pay down the loan balance, or principal, by some amount. This means that the next month the interest charge will be less because the charge is calculated as the interest rate multiplied by the balance. The total payment amount is fixed, which means that each succeeding month less of your payment goes toward interest and more toward the principal. To calculate the amount that goes toward principal for a specific payment, use the `PPMT` function.

To see an example of this, please refer to Figure 3.2. This worksheet presents an amortization table for a $10,000 loan at 5% for 12 months. The three columns of data are

Principal— The amount of each payment that goes toward the loan balance. This is calculated with the

`PPMT`function. You can see that this amount increases for subsequent payments.Interest— The amount of each payment that goes toward interest. This is calculated with the

`IPMT`function (covered in the next section). You can see that this amount decreases for subsequent payments.Total— The total monthly payment, the sum of principal and interest. This amount stays constant for the entire term of the loan.

## Amortizing LoansLike the other loan-related Excel functions in this chapter, PPMT is applicable only to the standard amortizing type of loan. These are the most common type of loan, and specify equal payments over the life of the loan. There are other specialized types of loans, such as balloon loans and zero-interest loans, and the functions covered in this do not apply to these loans. |

The `PPMT` function uses the following syntax; you’ll note that most of the arguments are the same as for the `PMT` function:

PMT(rate, per, nper, prin, [fv, type])

The first four arguments are required. They are

`rate`is the interest rate for the loan.`per`is the period for which you want the principal payment. This argument must be in the range`1`to`nper`.`nper`is the term of the loan expressed as the number of payment periods.`prin`is the principal, the amount you are borrowing.

As explained earlier for the `PMT` function, both `rate` and `nper` must use the same time unit (usually months). The last two arguments are optional (as indicated by the brackets in the formula):

`fv`is the future value of the loan, or the amount still owed when you have completed payments. Because loans are almost always paid off in full, you will use`0`for this argument or omit it, in which case Excel assumes`0`.`type`indicates when payments are made. Use a value of`1`if payments are made at the start of each period. Use a value of`0`, or omit the argument, if the payment is made at the end of each period.

In most situations you omit both of these optional arguments.

To try out the `PPMT` function, you can add to the worksheet you created earlier for the `PMT` function (refer to Figure 3.1). Then follow these steps:

1. | Put the labels For payment #, Principal, and Interest in cells B7 through B9, in order. |

2. | Put the following formula in cell C8: =PPMT(C3/12,C7,C4*12,C2). |

3. | Put the following formula in cell C9: =c5-c8. |

4. | Format cells C8 and C9 as currency with two decimal places. |

A sample calculation is shown in Figure 3.3. You can see that for the specified loan, the first payment consists of $232.29 going toward principal and $73.33 going toward interest. Change the payment number to 60—the last payment for the loan—and you’ll see the amounts change to $304.22 and $1.39 respectively.