How to Use Excel PPMT Function (3 Suitable Examples)

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.

Excel PPMT Function Quick View-Excel PPMT Function

In this article, we’ll discuss the instances and tricks to enable you to use the PPMT function more efficiently.


Download Excel Workbook


Excel PPMT Function: Syntax and Arguments

⦽ Function Objective:

 Return principal payments for a given period

 ⦽ Syntax:

 PPMT(rate, per, nper, pv, [fv], [type])

PPMT syntax

 ⦽ 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.


3 Suitable Examples to Use the Excel PPMT Function

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.

Capital payment-Excel PPMT Function

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.

Formula

Press ENTER and Drag the Fill Handle. All the sequential periodic payments appear similar to the below image.

Fill Handle

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.

Cross-check-Excel PPMT Function


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.

Argument values-Excel PPMT Function

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.

Formula

Press ENTER then Drag the Fill Handle. You’ll see the series of periodic payments shown in the following picture.

Fill handle

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).

Cross-check-Excel PPMT Function


Similar Readings


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.

Module insertion

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

Macro code

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.

Result-Excel PPMT Function

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

deferentiate between PPMT PMT IPMT-Excel PPMT Function

Read More: How to Use Excel PMT Function (4 Quick Examples)


⧭ 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.


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. You can check out my other articles on the Exceldemy website.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo