PMT (rate,nper,pv)

Description:

This function calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax: PMT (rate, nper, PV) ;
             PMT (rate, nper, PV, FV) ; or
             PMT (rate, nper, PV, FV, type)

• Rate : is the interest rate for the loan.
• 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.
• The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.
• 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 PMT function can be used in any database where you have stored values pertaining to interest rate, 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 PMT 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: PMT (rate, nper, pv)

=PMT (9, 3, -1150)	-returns the value for FV (10360.36)
=PMT (10, 6, -2150)	-returns the value for FV (21500.01)
=PMT (4, 5, -1100)	-returns the value for FV (4401.41)

 

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 PMT by calculating the result based on the parameters supplied.



In the example above, we wish to calculate the value which will determine the Payment due for a Loan 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 "PMT" 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", "NPER", and "PV". The result is returned by calculating the PMT from all these parameters and the calculated numeric value is the result that is returned by the function.

 
 

Example 2: PMT (rate, nper, pv, fv)

=PMT (4, 2, -1100, -800)	-returns the value for PMT (4716.67)
=PMT (9, 4, -1200, -100)	-returns the value for PMT (10801.17)
=PMT (10, 2, -1500, -300)	-returns the value for PMT (15150)

 

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



span class=text3>The formula above writes values into the "InvestmentPlanningModel" cube to the "FinancialDataFunctions" dimension into the member named "PMT2" 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", "NPER", "PV" and "FV. The result is returned by calculating the PMT from all these parameters and the calculated numeric value is the result that is returned by the function.

 
 

Example 3: PMT (rate, nper, pv, fv, type)

=PMT (4, 2, -1100, -800, 1)	-returns the value for PMT (943.33)
=PMT (9, 4, -1200, -100, 1)	-returns the value for PMT (1080.12)
=PMT (10, 2, -1500, -300, 1)	-returns the value for PMT (1377.27)

 

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 PMT by calculating the parameters supplied to the function which are namely the reference for the interest rate, 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 "PMT3" 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", "NPER", "PV" and "FV" and "TYPE". The result is returned by calculating the PMT from all these parameters and the calculated numeric value is the result that is returned by the function.