The PMT function in Excel is used to calculate the periodic payment for a loan or an investment based on constant payments and a constant interest rate. Here’s a step-by-step guide on how to use the PMT function:
Syntax
The syntax for the PMT function is as follows:
PMT(rate, nper, pv, [fv], [type])
Arguments
- rate: The interest rate for each period. If you have an annual interest rate, you need to divide it by the number of periods per year (e.g., monthly, quarterly).
- nper: The total number of payment periods in the term.
- pv: The present value, or the total amount that a series of future payments is worth now; also known as the principal of the loan.
- fv (optional): The future value, or a cash balance you want to attain after the last payment is made. If omitted, it is assumed to be 0.
- type (optional): The number 0 or 1 and indicates when payments are due:
- 0 (or omitted): Payments are due at the end of the period (this is the default).
- 1: Payments are due at the beginning of the period.
Example
Suppose you want to calculate the monthly payment required to pay off a loan of $10,000 over five years with an annual interest rate of 5%.
- Interest Rate (rate):
- Since you are calculating a monthly payment, convert the annual rate to a monthly rate: `5% / 12 = 0.4167%` or `0.004167` in decimal form.
- Number of Payment Periods (nper):
- 5 years, with 12 months per year: `5 * 12 = 60` payments.
- Present Value (pv):
- Amount of the loan: `$10,000`.
Formula
Insert the formula into a cell in Excel:
=PMT(0.004167, 60, -10000)
Notes
- The present value (`pv`) is negative because it represents an outgoing payment.
- The result will be negative because it also represents cash outflow (payments you are making).
Getting started with PMT in Excel
- Open Excel and click on a cell where you want the monthly payment to appear.
- Type the function `=PMT(`.
- Enter the arguments as shown above, ensuring to use the correct syntax.
- Press Enter to calculate the result.
- Review the output, which should be the monthly payment needed to pay off the loan.
Other Considerations
- Ensure you are consistent with your rate and nper units (e.g., both monthly or both annual).
- Utilize Excel’s formula bar and function assistant to better navigate complex formulas, including PMT.
By following these steps and understanding the syntax, you should be able to effectively use the PMT function in Excel to calculate loan or investment payments.
Unlock Your Potential
Excel
Access
Power BI
