IPmt() Function in MS Access

IPmt() Function in MS Access is used to return a double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate. 

Syntax :

IPmt (rate, per, nper, pv [, fv ] [, type ])

Parameter : This method accepts six parameters in the syntax, as mentioned above and described below :  

• rate : It is in Double specifying interest rate per period. If annual percentage rate (APR) is 5 percent and monthly payments, the rate per period is 0.05/12. 
• per : It is in Double specifying payment period in the range 1 through nper.
• nper : It is in Double specifying total number of payment periods in the annuity. For example, if monthly payments done on a five-year car loan, then the loan has a total of 5 * 12 = 60 payment periods. 
• pv : It is in Double specifying present value, or value today, of a series of future payments or receipts. 
• fv : It is optional. It identifies future value or cash balance you want after you have made the final payment. Default value is 0. 
• type : It is optional. It specifies when payments are due. 0 is used if payments are due at the end of the payment period, or 1 if payments are due at the beginning of the period. If omitted, 0 is assumed. 

Returns : It returns in double specifying the interest payment. 

Note : The rate and nper arguments must be calculated using payment periods expressed in the same units. For example, if rate is calculated using months, nper must also be calculated using months. 

Example-1 : 
Interest paid in 10 per of Rs. 100000 when annual rate is 8 % and TermInYears is 5. 

SELECT IPMT(0.08/12, 10, 5*12, -100000, 0, 0) AS Interest_Paid ;

Output :

Interest_Paid
582.796530417576

Example-2 :
Interest payment calculation for every account-id in a table of Accounts. 

Table – Accounts 

AccountId	LoanAmount	AnnualRate	TermInYears
11001	100000	0.10	5
11002	120000	0.10	6
11003	150000	0.10	4
11004	900000	0.10	6

SELECT IPMT([AnnualRate]/12, 10, [TermInYears]*12, -[LoanAmount], 0, 0) AS INTPaid 
FROM Accounts;

Output :

INTPaid
733.18850390563
905.149502978079
1051.90923080389
6788.62127233559