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.

**Table of Contents**hide

**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])`

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

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

**Similar Readings**

**How to Use Excel PRICE Function (3 Appropriate Examples)****Use IPMT Function in Excel (8 Examples)****How to Use FV Function in Excel (4 Easy Examples)****Use NPV Function in Excel (3 Easy Examples)****How to Use SLN Function in Excel (3 Examples)**

**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 |

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