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.
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. |
PPMT function Syntax
PPMT(rate, per, nper, pv, [fv], [type])
PPMT function Arguments
| 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.
PPMT function example
The image above shows the PPMT function in cell E3 calculating the principal for the second month for a 10 year loan of 100 000.
Formula in cell E3:
PPMT function not working
How is the PPMT function calculated?
Functions in 'Financial' category
The PPMT function function is one of many 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