I. Intro
The PPMT function calculates the principal portion of a loan with a fixed payment schedule and interest rate.
II. About the function
- Formula: =PPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning]
- Arguments:
- rate (required): The interest rate of the loan.
- period (required): The payment period in question. It must be between 1 and number_of_periods.
- number_of_periods (required): The total number of payment periods for this loan.
- present_value (required): The present value, or the sum of the current value of a series of future payments; also known as the principal.
- future_value (optional): The future value, or the cash balance you would like to receive after the last payment. If this argument is omitted, then it's assumed to be 0, meaning the future value of the loan is 0.
- end_or_beginning (optional): The numbers 0 and 1 represent whether payments are due at the beginning or at the end of a period respectively. If omitted, it's assumed to be 0.
- Example: =PPMT(1%,1,360,100000,0,0)
- Note:
- Ensure that the units for the interest rate and number_of_periods are consistent. For a four-year loan on an interest rate of 12% that is paid monthly, the rate should be 12%/12, and number_of_periods should be 4*12. For the same loan that is paid annually, then the rate should be 12%, and number_of_periods should be 4.
III. Steps
Use the PPMT function
- Select a cell and enter =PPMT.
- Enter the arguments in the cell. For example: =PPMT(1%,1,360,100000,0,0).
- Press Enter to get the result, which is -28.61259693 in this example.
250px|700px|reset
Delete the PPMT function
Select the cell with the PPMT function and press Delete.
IV. Use cases
Loan: Calculate the principal repayment amount for a specific period
For loans with constant payments per month, payment toward the principal portion gradually increases while the interest rate portion gradually decreases. The math can get quite complex, but the PPMT function makes it easy to calculate the principal repayment amount for the specified period.
- Formula used below: =PPMT(B2/12,1,B3*12,B4)
- About the arguments:
- To convert the annual interest rate into a monthly interest rate, divide it by 12.
- To get the payment amount of the principal portion for the first month, enter 1.
- For number_of_periods, convert payment periods into months by multiplying the loan term, 3, by 12.
- Select B4 as the principal.
- Press Enter to find the principal repayment amount for the first month.
250px|700px|reset