IPMT
The IPMT function calculates the amount of interest to be paid on a loan where the interest payments and timing of payments are consistent.
Syntax
IPMT(Rate, Per, Nper, PV,FV?,Type?) - > Number
Arguments
Arguments | Datatype | Description |
---|---|---|
Rate | Number | The interest rate per period |
Per | Number | The period for which you want to find the interest. It must be in the range of 1 and the total number of periods. |
Nper | Number | The total number of periods |
PV | Number, Node name | The present value or initial investment. Cash outflows are considered as negative and cash inflows are positive. |
FV? | Number, Node name | The future or residual value. This is an optional argument and if omitted, it is considered to be zero |
Type? | Number | Indicates when the payments are made. Type is zero if payments are made at the end of the period and 1 if the payments are made at the start of the period. |
Example
Consider a $10,000 loan at an annual rate of 10% that is to be paid off in 1.5 years. All payments are made at the beginning of the month.
Periods | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Number of periods | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 | 18 |
Loan Amount | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 |
Type | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
Period | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 |
Rate | 10% | 10% | 10% | 10% |
To calculate the interest payments the following formula should be written in the IPMT node:
IPMT
(
[Rate]
/12,
[Period]
,
[Number of periods]
,
[Loan Amount]
,0,1)
Result
IPMT | ₹ 0.00 | ₹ -78.37 | ₹ -74.06 | ₹ -69.71 | ₹ -65.33 | ₹ -60.91 | ₹ -56.46 | ₹ -51.96 | ₹ -47.43 | ₹ -42.86 | ₹ -38.26 | ₹ -33.61 | ₹ -28.93 | ₹ -24.21 | ₹ -19.45 | ₹ -14.65 | ₹ -9.80 | ₹ -4.92 |
The interest payments for Period 1 is 0 as the payment is made at beginning of the month. Note the decrease in interest amount as the principal amount will be repaid gradually.
Last updated