The Excel PPMT function falls in the financial functions category. PPMT calculates periodic payments on loans or investments that have a specified interest rate and given period of time. The periodic principal amount of payments is sequential payments paid throughout the given period of time.
In this article, we’ll discuss the instances and tricks to enable you to use the PPMT function more efficiently.
Excel PPMT Function: Syntax and Arguments
⦽ Function Objective:
 Return principal payments for a given period
 ⦽ Syntax:
 PPMT(rate, per, nper, pv, [fv], [type])
 ⦽ Arguments Explanation:
| Argument | Required/Optional | Explanation |
|---|---|---|
| rate | Required | interest rate per period |
| per | Required | a specific period; must be between 1 and nper |
| nper | Required | total number of payment periods in a year |
| pv | Required | the current value of a loan or investment; it is the present value of series of future payments |
| [fv] | Optional | Â nper payments future worth; if not assigned function takes a default value (i.e.,0) |
| Â [type] | Â Optional | Payments behavior; whether payment is paid at the beginning (i.e.,1) or end of the period (i.e.,0). If not assigned function takes a default value (i.e.,0) |
⦽ Return Parameter:
Principal payment amount
 ⦽ Applies To:
Microsoft Excel version 2003, Excel MAC version 2011, and onwards.
How to Use the Excel PPMT Function: 3 Suitable Examples
Example 1: PPMT Calculates Capital Payments During a Period
The PPMT function calculates capital payments during a period against a loan or investment. It calculates any order of payments during a given period that lies between 1 and the total number of payments (i.e., equal to per*nper).
Other required arguments are offered during the assignment of the loan or investments as shown in the following picture.
➧ Paste the below formula in any adjacent cell (i.e., G6).
=PPMT($D$7/$D$9,F6,$D$8*$D$9,-$D$6)In the formula,
$D$7/$D$9; declares rate/nper as quarterly capital payment is calculated. It can also be weekly (rate/52), monthly (rate/12), or semi-annually (rate/2) capital payment.
F6=per
$D$8*$D$9; is the total number of payments (i.e., loan term*payments per year)
-$D$6=pv
There is no cell reference for [fv] and [type], they are assigned as the default.
Inside the formula, we use absolute references in certain arguments because we don’t want to allow Excel to change them in case of the Fill Handle execution.
We don’t assign any value to [fv] argument as we calculate future capital payments for a present loan amount.
➧ Press ENTER and Drag the Fill Handle. All the sequential periodic payments appear similar to the below image.
From the amounts in Capital Payment, you can notice that payment amounts are getting greater by the age of the loan.
You can check if the total payments turn out to be the same or not. Just use the SUM formula (i.e.,=SUM(G6:G9)) in any cell (i.e., G10). You’ll find out the sum of the capital payments is equal to the loan amount.
Example 2: Calculates Capital Payments to End Up a Balance
Similar to the previous example, the PPMT function calculates present capital payments to end up a balance in the future. Suppose we want a balance (i.e., $20000) to be accumulated at the end of a period. Thus, we want to calculate a series of present payments for a specific time and a specific number of payments.
We have other arguments specified by the following image.
➧ Type the below formula in any adjacent cell (i.e., C6).
=PPMT($D$7/$D$9,F6,$D$8*$D$9,$D$6,$D$10,$D$11)In the formula,
$D$7/$D$9; refers rate/nper as quarterly capital payment is calculated. It can also be weekly (rate/52), monthly (rate/12), or semi-annually (rate/2) capital payment.
F6=per, it can be any offered time period.
$D$8*$D$9; indicates the total number of payments (i.e., loan term*payments per year)
$D$6=pv
$D$10=[fv]
$D$11=[type]
We use absolute references for most of the cell references as we want them to be static except the loan term (i.e., per(F6)). Because we want the amount for each sequential period letting other arguments unchanged.
➧ Press ENTER then Drag the Fill Handle. You’ll see the series of periodic payments shown in the following picture.
If you want to cross-check whether the desired balance is acquirable or not, just sum the total capital payments using the SUM formula (i.e.,=SUM(G6:G13)) in any cell (i.e., G14).
Example 3: Excel PPMT Function Used in VBA Macro
Sometimes customers are subject to numerous periodic payments. Such as a 15 or 20-years loan term with monthly capital payments. We can use the VBA Macro code to calculate any particular capital payment due to a specific term.
➧ Hit ALT+F11 altogether and Microsoft Visual Basic window will open. Select > Insert (from the toolbar) > Choose Module.
➧ Paste the following code in the module and Press F5 to run the Macro.
Sub example_PPMT()
Range("C6").Value = -PPmt(0.07 / 12, 1, 15 * 12, 20000, 0, 0)
End Sub
The Macro code calculates the 1st capital payment for a monthly interest rate (i.e.,0.07/12). The total number of payments is loan term*payments per year (i.e.,15*12). Present worth is $20000 and other arguments are default (i.e.,0).
➧ Return to the worksheet, you’ll see the 1st capital payment in cell C6.
You can use any values for the arguments and then write the code accordingly, you’ll get the specified capital payment (i.e., nper(th)).
⧬ Differentiate Between PMT, PPMT and IPMT:
| Aspects | PMT | PPMT | IPMT |
|---|---|---|---|
| Function Category | Financial Function | Financial Function | Financial Function |
| Syntax | Â PMT(rate, nper, pv, [fv], [type]) | PPMT(rate, per, nper, pv, [fv], [type]) | IPMT(rate, per, nper, pv, [fv], [type]) |
| Return Value | Fixed Monthly Repayment Amount | Capital Repayment Amount | Interest Repayment Amount |
| Applicable to | Microsoft Excel version 2003, Excel Mac version 2011 and onwards | Microsoft Excel version 2003, Excel Mac version 2011 and onwards | Microsoft Excel version 2003, Excel Mac version 2011 and onwards |
⧠Things to Keep in Mind
🔄 The #NUM! error occurs when the per argument is less than 0 or is greater than the nper argument value.
🔄 The #VALUE! error occurs whenever non-numeric values are assigned to the arguments.
🔄 In case of calculating monthly, semi-monthly or quarterly payments must convert the annual interest rate to respective periods.
🔄 Insert a minus sign (–) before the PPMT formula or the pv argument as shown in the examples otherwise negative sign will appear before the payment amount.
Download Excel Workbook
Conclusion
I hope the above-described uses of the PPMT function intrigue you to use the function more efficiently. If you have further queries or feedback, please let me know in the comment section.
<< Go Back to Excel Functions | Learn Excel
