=PMT (rate, nper, pv, [fv],[type])

in,

• Rate – Required. Interest rate for loans.
• Nper – Required. Total number of payments for loans.
• Pv – Required. The present value, or the current total value of a series of future payments; also known as principal.
• Fv – Optional. Future value , or the cash balance you wish to reach after the last payment. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of the loan is 0.
• Type – Optional. The number 0 (zero) or 1 indicates the time when the payment expires.

In this article, we will introduce the following three examples in detail -

Table of contents

• Simple loan calculation
• Loans with balloon payment
• Payment at the beginning

Let's explore these methods one by one!

Download the Excel workbook below and follow the instructions to learn how to use PMT functions in Excel - Download the Excel workbook PMT-Function-in-Excel.xlsx

Table of contents

Simple loan calculation

Suppose you borrowed $10,000 with an annual interest rate of 5% and a term of 3 years . You can use the financial formula in Excel to calculate the amount you should pay per month . The PMT formula that can be used in this example is -

* =PMT(5%/12,3 12,10000)**

in,

• 5%/12 means monthly interest rate.
• * 3 12* indicates the total payment period (3 years and 12 months).
• 10,000 represents the current value of the loan.

After running this formula in Excel, it will provide the monthly payment amount of the loan, i.e. $300 .

Loans with balloon payment

Balloon payment refers to the borrower making a smaller periodic payment during the loan period, and a larger amount is required to pay at the end of the loan - Balloon payment. Let's consider a scenario where you borrow $20,000 with an interest rate of 6%, and a term of 4 years, but you have $5,000 in balloon payments at the end of the loan period.

The PMT function can be used to calculate the monthly payments you need to make -

* =PMT(6%/12,4 12,20000,-5000)**

in,

• 6%/12 means monthly interest rate.
• * 4 12** Indicates the total payment period.
• 20,000 represents the current value of the loan.
• 5000 is the end of the period of the balloon payment.

After running this formula in Excel, it will provide the monthly payment amount of the loan, which is $377 .

Payment at the beginning

The PMT function assumes that payment is performed at the end of each period by default . However, if the payment is made at the beginning of each period , you can specify this by using the optional type parameter in the PMT function.

• If type is 0 or omitted , payment is assumed to expire at the end of the period .
• If type is 1 , the payment is made at the beginning of the period .

Suppose you have a $20,000 loan with an annual interest rate of 5%, a payment term of 3 years, and the payment expires at the beginning of each period. You can use the PMT function as follows -

* =PMT(5%/12,3 12,10000,,1)**

in,

• 5%/12 means monthly interest rate.
• * 3 12* indicates the total payment period (3 years and 12 months).
• 10,000 represents the current value of the loan.
• The FV parameters are omitted.
• 1 is the type parameter, because payment needs to be paid at the beginning of the period.

Executing this formula in Excel will give you the monthly payment amount of the loan, i.e. $298 , considering that the payment is made at the beginning of each period .

The PMT function in Microsoft Excel is a valuable tool for financial modeling and planning, allowing users to easily calculate the periodic payments of loans or investments . In this article, we explore three basic methods: simple loan calculations, loans with balloon payments, and start-up payments, which enable users to master this powerful financial tool.

Learn more about formulas, check out our 101 Advanced Excel formulas and functions examples

The above is the detailed content of Mastering PMT Function in Excel – 3 Examples. For more information, please follow other related articles on the PHP Chinese website!