How to use the IPMT function
What is the IPMT function?
The IPMT function calculates the interest payment for a specific period for an investment based on repeated constant payments and a constant interest rate.
IPMT stands for Interest Payment, reflecting that the function isolates just the interest portion of a periodic payment calculation based on an interest rate, periods, PV, FV, etc.
What is interest payment?
An interest payment is the amount of interest owed or earned on a loan or investment for a specific payment period. The IPMT function calculates the interest portion of a periodic payment on an investment or loan. Interest payments decline over time as the principal is paid off.
What is an investment?
An investment is the purchase of an asset with the expectation of generating income or appreciation in the future. For example, stocks, bonds, real estate, commodities, art etc.
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 principal?
The principal is the amount you have to pay back and interest is the charges you pay for borrowing the money.
IPMT function Syntax
IPMT(rate, per, nper, pv, [fv], [type])
IPMT 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. |
IPMT function example
The image above shows the IPMT function in cell E3 calculating the interest for the second month for a 10-year loan of 100 000.
Formula in cell E3:
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.
How is the IPMT function calculated?
PV = Present Value (loan amount or initial investment)
Rate = Periodic interest rate
Period = Payment period to calculate interest for
nper is the total number of payment periods or payments.
Functions in 'Financial' category
The IPMT 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