How to Use the PMT Formula in Excel

The PMT Formula calculates the payment for a loan based on constant payments and a constant interest rate.

Formula Explanation

Rate (required): The interest rate for the loan.

Nper (required): The total number of payments for the loan.

Pv (required): The present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv (optional): 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 (optional): The number 0 (zero) or 1 and indicates when payments are due. 0 (zero) for when “at the end of the period” and 1 (one) for “at the beginning of the period”

Formula:

Example:

On January 1, 2020, Jupiter Company bought equipment through a loan for $1,000,000. The Loan has different options available as per the below table. Interest rates differ depending on the option selected. The loan financing company needs to provide the monthly, annual, and total payment information to the borrower.

Solution:

The below table provides insight into the monthly, annual, and total payment requirements while taking into consideration the paydown of principal and interest. The payment formula is used to calculate the payments made monthly based on the interest rate, loan amount, and a number of periods as shown below.

The amounts for option A, B, and C varies significantly on a per annum basis when you take a look at the total interest payments under options A, B, and C.

Options A and B are far more affordable from an interest expense perspective compared to option C but the total cash outflow is much greater, almost double, at $16,000 per month versus about $9,500 for option C.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!