The Microsoft Excel PPMT function is a tool that calculates the payment on a basic amount during a defined period of time. The amount may be upon a loan or just an investment in some business that is supposed to return a value at a pre-settled interest rate. The payment that is to be made against the principal amount is to be periodic and should remain constant over a period of time. MS Excel PPMT function is able to calculate this payment that is to be made against the basic amount that has been put to use under a constant interest rate for a specific period. PPMT originally tracks the amount that is to be paid right from the balance. It therefore follows a constant schedule of payment for a definite principal amount.
When you borrow a certain amount from a financial institution you agree upon a certain rate of interest that you would be required to pay at the end of a certain period. This time period is specified during the agreement and is the bounding or limiting factor. Once the deal is made and you are paid the desired amount you are asked to repay it according to the deal. While you repay the portion of a principal amount the interest also gets reduced accordingly. The Microsoft Excel PPMT Function is one such Excel function that is incorporated in MS Excel that helps a person, or in this case, and Excel consultant, to calculate the payment that is due within a period of the loan. The term during which the loan repayment is made can be used as a defining parameter along with the loan amount taken. Similarly if you invest a sum of money in some business and you expect an interest amount from it periodically then you would require this PPMT function of MS Excel to calculate the amount that you are supposed to receive and plan accordingly.
The syntax followed by the Excel PPMT function is described as follows:
PPMT (rate, periods, number of periods, Present Value, Future Value, Type)
This syntax includes generally 6 parameters.
Rate: rate stands for the interest rate. The interest rate for the loan is input to the function through this parameter. This is the rate at which the payment is to be made at a specific period. The rate remains constant. It is agreed upon while the amount was first borrowed while striking of the deal. This interest rate remains constant throughout the period of the loan.
Period: It is the actual time which would be iterative. That means the interest would be paid against the principal amount keeping in purview the time. This may act as a small chunk of time like a quarter or a month during which the repayment is to be made. The actual range of period should be within 1 to total number of periods. The period acts as an indicator of the total amount that has been repaid of the principal amount invested.
Number of periods: This is actually the total number of payments made. The loan is repaid in small chunks of time periodically. The number of time blocks or terms during which the loan repayment is done is taken through this parameter in the PPMT function of MS Excel. This is a value that is calculated in a financial year which is actually the number of periods in a single year.
Present Value: The PPMT function incorporates this parameter which acts as a variable that can be used to calculate the investment is due to return in coming periods. That is the total amount that would be received from the future payments.
Future Value: It is a variable that signifies the amount that the investment would earn you since the last payment has been dished out. During the initial phase of the loan this value is assumed to be zero.
Type:Type is just a simple indicator that denotes the time when you are due to receive a payment on your invested amount. It may have only two values, either zero or one.
There are certain limitations of this Excell function which must be considered before use. Firstly the units that are used while calculating the interest should be same and considered for a definite period of time. It may be calculated for a quarter or a month or annually. Therefore the time period should be properly earmarked.
This function is available in all the latest versions of MS Excel. It is compatible with Excel 2000, 2003, 2007, 2008, 2010 and 2011. It is accessed from the functions tab available in the tool bar of the MS Excel.