Syntax. PPMT(Rate,Per,Nper,Pv,Fv,Type)
Definition. This function determines the part of an annuity that is used for the repayment of a loan that is repaid according to the principle of annuity repayment.
Arguments
Rate (required) The nominal interest of the loan.
Per (required) The appropriate number for the desired period.
Nper (required) Specifies the total number of periods in which the loan is repaid.
Pv (required) Contains the loan amount.
Fv (optional) Evaluates loans that have not been fully repaid after the total number of periods. Such an effect might happen when mortgages are disbursed with a fixed interest period.
Type (optional) Specifies whether the payments become interest effective at the start of the periods (Type = 1) or at the end of the periods (Type = 0). If this argument is omitted, Excel uses the value 0.
Background. The repayment of loans can take place in various ways. One way is for the debtor to pay the same amount each period. This amount is composed of a repayment part (which increases as time passes) and an interest part (which decreases as time passes). The change in this composition is because the loan that needs to be repaid shrinks over time. This version is called annuity repayment. The total amount is called annuity, even though its payment does not necessarily happen annually.
The repayment part of the first annuity results from subtracting the interest part from it. This part is the loan sum multiplied by the nominal interest rate specified for the period. The repayment parts of the next periods are (after a few intermediate mathematical steps) determined from:
(Repayment part of the first period) · (1 + Nominal interest rate)Period number–1
Note
In loan agreements, an annual interest rate is very frequently formulated as a nominal interest rate, but the payment takes place intra-annually. In such cases, you need to determine the intra-annual period interest rate by dividing the annual interest rate by the number of payments per year. The total number of periods results from the periods per year multiplied by the number of years.
Example. In the “Repayment Calculation (Annuity Repayment)” example in the description of the PV() function, the following data was specified: A debtor takes out a loan in the amount of $176,121.76, which he pays back at 5.5 percent with $1,000.00 per month over 30 years. The repayment plan shows an interest part of $791.64 in the 18th month. Therefore, the repayment part for this month is $208.36. You can check this result with the function PPMT(), even though there might be rounding errors.
=PPMT(5.5%/12,18,30*12,-176121.76)
(The correct numbers are derived from references to the cells in which the data are located).
Rounding errors occur frequently when the integrated functions are used. After all, they cannot take into consideration what actually happens at the bank counter: In the real world, money is calculated with only two decimal places. If you are entering data for a repayment plan month by month, you need to use the function ROUND() to show what is actually happening.