PPMT (rate,nper,pv)

Description:

This function returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

Syntax: PPMT (Rate, Per, Nper, Pv) ;
             PPMT (Rate, Per, Nper, Pv, Fv) ; or
             PPMT (Rate, Per, Nper, Pv, Fv, Type)

• Rate : is the interest rate for the loan.
• Per : specifies the period 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.

Remark

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

Examples

The PPMT 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 in an annuity, 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: PPMT (Rate, Per, Nper, Pv)

=PPMT (9, 3, 3, -3650)	-returns the value for PPMT (3288.29)
=PPMT (10, 4, 6, -2150)	-returns the value for PPMT (16.15)
=PPMT (4, 2, 9, -2200)	-returns the value for PPMT (0.02)

 

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



In the example above, we wish to calculate the value which will determine the Payment on the principal based on a constant the rate of interest, a given period, 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 "PPMT" 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 PPMT from all these parameters and the calculated numeric value is the result that is returned by the function.

 
 

Example 2: PPMT (Rate, Per, Nper, Pv, Fv)

=PPMT (9, 3, 3, -3650, -400)	-returns the value for PPMT (3648.65 )
=PPMT (10, 4, 6, -2150, -2100)	-returns the value for PPMT (31.93)
=PPMT (4, 2, 9, -2200, -100)	-returns the value for PPMT (0.02)

 

Let us take the example of a database in PowerOLAP called "Effective Finance" where we have values for interest rate, the period for the payment, the number of payment cycles, the present value and the future value. We shall use this example to find the value for PPMT 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.



The formula above writes values into the "InvestmentPlanningModel" cube to the "FinancialDataFunctions" dimension into the member named "PPMT2" 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 PPMT from all these parameters and the calculated numeric value is the result that is returned by the function.

 
 

Example 3: PPMT (Rate, Per, Nper, Pv, Fv, Type)

=PPMT (9, 3, 3, -3650, -400, 0)	-returns the value for PPMT (3648.65)
=PPMT (10, 4, 6, -2150, -2100, 0)	-returns the value for PPMT (31.93)
=PPMT (4, 2, 9, -2200, -100, 0)	-returns the value for PPMT (0.02)

 

Let us take the example of a database in PowerOLAP called "Effective Finance" where we have values for rate, the period, the number of payment cycles, the present value, the future value and the type. We shall use this example in this case to find the value for PPMT by calculating the parameters supplied to the function, which are namely the reference for the above mentioned parameters and the calculated numeric value is returned by the function.



The formula above writes values into the "InvestmentPlanningModel" cube to the "FinancialDataFunctions" dimension into the member named "PPMT3" 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 PPMT from all these parameters and the calculated numeric value is the result that is returned by the function.