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
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.
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