IPMT (rate,nper,pv)

Description:

This function returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. For a more complete description of the arguments in IPMT and for more information about annuity functions see PV function.

Syntax: IPMT (Rate, Per, Nper, Pv, Fv, Type)

• Rate : is the interest rate for the loan.
• Per : is the period for which you want to find the interest and must be in the range 1 to nper.
• Nper : is the total number of payments for the loan.
• Pv : is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
• Fv : is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
• Type : is the number 0 (zero) or 1 and indicates when payments are due.

Remarks

• Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
• For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers.

Example

The IPMT function can be used in any database where you have stored values pertaining to interest rate, the period, the total number of payments for the loan, the present value or the principal value, the face value and the type which indicates when payments are due. The function reads these options and calculates the value for IPMT depending upon the parameters that are supplied. The function can be built with either the 3 compulsory parameters,or then the 2 optional parameters can be added one by one or both together.

This function returns the calculated numeric value as its result.

 

Example 1: IPMT (rate, per, nper, pv)

=IPMT (9, 3, 6, -1150)	-returns the value for IPMT (10348.98)
=IPMT (10, 2, 6, -2150)	-returns the value for IPMT (21499.88)
=IPMT (14, 3, 5, -1100)	-returns the value for IPMT (15395.46 )

 

Let us take the example of a database in PowerOLAP called "Effective Finance" where we have values for rate, the number of payment cycles and the payment value. We shall use this example in this case to find the value for IPMT by calculating the result based on the parameters supplied.



In the example above, we wish to calculate the value which will determine the interest payment for a given period for an investment based on a constant the rate of interest, a constant number of payments in an annuity and the present value.

The formula above writes values into the "InvestmentPlanningModel" cube to the "FinancialDataFunctions" dimension into the member named "IPMT" by creating the appropriate value for this member, based upon the parameters that are given as input to this function, namely the references for the members "RATE", "PER", "NPER", and "PV". The result is returned by calculating the IPMT from all these parameters and the calculated numeric value is the result that is returned by the function.

 

Example 2: IPMT (rate, per, nper, pv, fv)

=IPMT (4, 6, 2, -1100, -800)	-returns the value for IPMT (4398.05)
=IPMT (9, 3, 4, -1200, -100)	-returns the value for IPMT (10684.16)
=IPMT (10, 2, 5, -1500, -300)	-returns the value for IPMT (14998.88)

 

Let us take the example of a database in PowerOLAP called "Effective Finance" where we have values for interest rate, the period, the number of payment cycles, the present value and the future value. We shall use this example in this case to find the value for IPMT by calculating the parameters supplied to the function, which are references for the interest rate, period, the number of payment cycles in an annuity, the present value and the future value of the amount.



 

The formula above writes values into the "InvestmentPlanningModel" cube to the "FinancialDataFunctions" dimension into the member named "IPMT2" by creating the appropriate value for this member, based upon the parameters that are given as input to this function, namely the references for the members "RATE", "PER", "NPER", "PV" and "FV. The result is returned by calculating the IPMT from all these parameters and the calculated numeric value is the result that is returned by the function.

 

Example 3: IPMT (rate, per, nper, pv, fv, type)

=IPMT (4, 2, 6, -1100, -800, 1)	-returns the value for IPMT (879.61)
=IPMT (9, 4, 3, -1700, -100, 1)	-returns the value for IPMT (1068.42)
=IPMT (10, 2, 5, -1500, -300, 1)	-returns the value for IPMT (1363.53)

 

Let us take the example of a database in PowerOLAP called "Effective Finance" where we have values for rate, the number of payment cycles and the payment value. We shall use this example in this case to find the value for IPMT by calculating the parameters supplied to the function which are namely the reference for the interest rate, the period, the number of payment cycles in an annuity, the present value and, the future value of the amount and finally the type of the investment.



The formula above writes values into the "InvestmentPlanningModel" cube to the "FinancialDataFunctions" dimension into the member named "IPMT3" by creating the appropriate value for this member, based upon the parameters that are given as input to this function, namely the references for the members "RATE", "PER", "NPER", "PV" and "FV" and "TYPE". The result is returned by calculating the IPMT (interest payment) for all these parameters and the calculated numeric value is the result that is returned by the function.