How to use the PPMT function
What is the PPMT function?
The PPMT function calculates the principal payment for a specific period for an investment based on repeated constant payments and a constant interest rate.
Table of Contents
1. Introduction
What is the principal?
The principal is the amount you have to pay back and interest is the charges you pay for borrowing the money.
What is an investment?
An investment is an asset or business acquired with the goal of generating income or appreciation, the purpose is to grow the money over time.
What are periods?
A payment period is the length of time between payments made on a loan or investment. For example, a loan with monthly payments the payment period would be one month. A loan with quarterly payments the payment period would be three months.
What is present value?
The present value is the initial amount that will earn interest/dividend.
What is future value?
The compounded amount after the calculated periods based on the given rate. It measures what a current capital (present value) amount will be worth at a designated future date.
What are periodic constant payments?
Periodic constant payments are payments that are made at regular intervals such as monthly, quarterly, or yearly and have the same amount each time.
What is a constant interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
What is the number of compounding periods per year?
The number of compounding periods per year refers to how often interest is compounded annually on an investment or loan.
Some common compounding periods:
- Annually - 1 compounding period per year
- Semiannually - 2 compounding periods per year
- Quarterly - 4 compounding periods per year
- Monthly - 12 compounding periods per year
- Weekly - 52 compounding periods per year
- Daily - 365 compounding periods per year
What is compounding?
Compounding refers to the process of generating more interest from interest that was previously earned. It causes interest to grow exponentially over time.
Related functions
| Function | Description |
|---|---|
| PMT(rate, nper, pv, [fv], [type]) | Returns the payment amount needed for borrowing a fixed sum of money based on constant payments (annuity) and interest rate. |
| PPMT(rate, per, nper, pv, [fv], [type]) | Calculates the principal payment for a specific period for an investment based on repeated constant payments and a constant interest rate. |
| IPMT(rate, per, nper, pv, [fv], [type]) | Calculates the interest payment for a specific period for an investment based on repeated constant payments and a constant interest rate. |
| ISPMT(rate, per, nper, pv) | Calculates the interest paid during a specific period of an investment. |
2. Syntax
PPMT(rate, per, nper, pv, [fv], [type])
| rate | Required. The interest rate. |
| per | Required. The period. |
| nper | Required. The total number of periods in an annuity.
Examples: |
| pv | Required. Present value. |
| [fv] | Optional. Future value, default value is 0 (zero). |
| [type] | Optional. When payments are due.
0 - End of period, default value. |
Use the same unit for rate and nper, the above example uses monthly payments. That is why the interest rate is divided by 12 and nper is multiplied by 12. There are 120 monthly payments in a 10 year period.
3. Example 1
If you take out a $200,000 mortgage loan with an annual interest rate of 4.5% and a loan term of 30 years, what will be the principal payment portion of your first monthly payment?
The payments are made on a monthly basis which affects the rate and Nper arguments. The rate is an annual interest and the Nper value is in years, we need to convert them to a monthly basis.
The PPMT arguments are:
- Rate is set to 5% (annual)
- Per (payment period) is 1
- Nper (total number of periods) is 30 (years)
- Pv (present value) is $200,000.00
- Fv (future value) and Type arguments are left blank.
The following formula divides the annual interest by 12, there are 12 months in a year. The Nper argument is multiplied by 12 to change it from yearly to monthly periods.
Formula in cell E3:
The calculate principal in cell E18 is 263.37. The data table below the arguments and the formula displays the calculated principal values for each period, along with the corresponding PMT (total payment amount) and the remaining balance after each payment. The PPMT value which represents the principal portion of the payment increases slightly with each period as the balance decreases.
The charts shows how the principal value (blue curve) increases as the interest portion decreases because the loan (black curve) is paid off month by month.
4. Example 2
You have a car loan of $20,000 at an annual interest rate of 6% for 5 years. Calculate the principal portion of the 24th monthly payment?
The payments are made on a monthly basis which affects the Rate and Nper arguments. The rate is an annual interest and the Nper value is in years, we need to convert them to a monthly basis.
The PPMT arguments are:
- Rate is set to 6% (annual interest rate)
- Per is 24 (24th monthly payment period)
- Nper (total number of periods) is 5 (years)
- Pv (present value) is $20,000
- Fv (future value) and Type arguments are left blank.
The following formula divides the annual interest by 12, there are 12 months in a year. The Nper argument is multiplied by 12 to change it from yearly to monthly periods.
Formula in cell E3:
The calculate principal in cell E18 is 321.50. The data set below the arguments displays the calculated principal values for each period, along with the corresponding PMT (total payment amount) and the remaining balance after each payment. The PPMT value which represents the principal portion of the payment increases slightly with each period as the balance decreases.
The charts shows how the principal value (red curve) increases as the interest portion decreases because the loan (black curve) is paid off month by month.
5. Example 3
You have a student loan of $35,000 with an annual interest rate of 5.8% for 15 years. Determine the principal portion of the 120th monthly payment?
In this example, the payments are made on a monthly basis, which affects the Rate and Nper arguments. The rate is an annual interest rate, and the Nper value is in years, so we need to convert them to a monthly basis.
The PPMT arguments are:
- Rate is set to 6% (annual interest rate)
- Per is 24 (24th monthly payment period)
- Nper (total number of periods) is 5 (years)
- Pv (present value) is $20,000
- Fv (future value) and Type arguments are left blank.
The following formula divides the annual interest rate by 12 to convert it to a monthly rate, as there are 12 months in a year. The Nper argument is multiplied by 12 to change it from a yearly period to monthly periods.
Formula in cell E3:
The calculated principal amount in cell E18 is $217.28.
The data set below the arguments displays the calculated PPMT (principal payment) values for each period, with the corresponding PMT (total payment amount) and the remaining balance after each payment.
The PPMT value which represents the portion of the payment that goes towards the principal. It increases slightly with each period as the remaining balance decreases.
The charts shows how the principal value (red curve) increases as the interest portion decreases because the loan (black curve) is paid off month by month.
Functions in 'Financial' category
The PPMT function function is one of 27 functions in the 'Financial' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form