The PPMT function in Excel is a financial function that calculates the principal payment for a loan or annuity based on a constant payment schedule. It is an important tool for anyone who needs to calculate loan payments or forecast the future value of an investment.
The PPMT function uses the following syntax:
=PPMT(rate, period, number_of_periods, present_value, [future_value], [type])
The rate parameter is the interest rate for the loan or annuity. It is expressed as a decimal, with 0.05 representing 5%.
The period parameter is the number of the payment for which you want to calculate the principal payment. For example, if you want to calculate the principal payment for the fifth payment, you would enter 5 for this parameter.
The number_of_periods parameter is the total number of payments for the loan or annuity.
The present_value parameter is the current value of the loan or annuity. This is typically the amount of the loan or the initial investment in the annuity.
The optional future_value parameter is the future value of the loan or annuity at the end of the payment period. This is typically zero for a loan and the expected payout at the end of the annuity for an annuity.
The optional type parameter is a Boolean value that determines whether the payment is made at the beginning or end of the period. A value of 0 (or omitted) means that the payment is made at the end of the period, while a value of 1 means that the payment is made at the beginning of the period.
Using the PPMT function is straightforward. To calculate the principal payment for the fifth payment of a loan with an interest rate of 5%, a total of 10 payments, and a present value of $10,000, you would enter the following formula:
=PPMT(0.05, 5, 10, 10000)
This would return a principal payment of $938.10.
The PPMT function can be used in combination with the PMT function to calculate the total payment for a loan or annuity. The PMT function calculates the total payment, including both the principal and interest, based on a constant payment schedule.
To calculate the total payment for the fifth payment of the same loan, you would use the following formula:
=PMT(0.05, 10, 10000)
This would return a total payment of $1,136.41, which includes $938.10 in principal and $198.31 in interest.
One of the most common uses for the PPMT function is to create an amortization schedule for a loan. An amortization schedule is a table that shows the breakdown of each payment for a loan, including the principal and interest paid for each payment.
To create an amortization schedule in Excel, you will need to use the PPMT function along with several other functions, including the RATE, NPER, and PMT functions.
To create an amortization schedule, start by setting up a table with the following columns: Payment Number, Payment Date, Payment Amount, Principal, Interest, and Balance.
In the Payment Number column, enter the number of each payment, starting with 1 for the first payment. In the Payment Date column, enter the date of each payment.