How to Use the PMT Function in Excel (Simple Guide with Example)
In this article, you will learn how to calculate the payment made each period using the PMT function in Excel.
What is the PMT function in Excel?
The PMT function in Excel is a financial function that calculates the periodic payment for a loan based on a constant interest rate, the number of periods, and the loan amount.
When is it helpful to use the PMT function in Excel?
The PMT function in Excel can be very helpful in a variety of financial scenarios, including:
- Loan payments: If you are considering taking out a loan (like a car loan, student loan, or mortgage), you can use the PMT function to understand what your monthly payments would be based on the loan amount, term, and interest rate. This can help you budget and determine if the loan is affordable for you.
- Investment planning: If you want to reach a certain financial goal in a set amount of time (like saving for a down payment on a house), you can use the PMT function to calculate how much you need to save each month, given an expected rate of return.
Remember that the PMT function assumes a constant interest rate and a constant series of payments, so it's not suitable for every financial scenario but it is quite versatile and powerful for many common situations.
Understanding the syntax of the Excel PMT function
The syntax for the PMT function is the following:
rate: The interest rate per period.
nper: The total number of payment periods in an investment or loan.
pv: The present value or the total amount that the series of payments is worth currently.
fv (optional): The future value or cash balance you expect to reach after the last payment is made. If omitted, it is assumed to be 0.
type (optional): This argument determines when the payments are due.
- Input 0 if payments are due at the end of the period (this is the default setting).
- Input 1 if payments are due at the beginning of the period.
How to use the PMT function in Excel
Let's say you're planning for your retirement and you want to know how much you need to invest each month to reach your target retirement fund. You retire in 30 years, with a goal fund of $1,000,000, an annual interest rate of 5%, and payments at the beginning of every month. Below is an image showing the setup and calculation of this example.
Note that since we are making monthly payments we have accounted for that by dividing our interest rate by 12 and multiplying our number of payments (nper) by 12, this can be seen in the formula. It is important to be consistent with units. Additionally, as seen in the formula, the pv argument is input as 0, meaning we are starting out with nothing in this retirement fund, you could also omit this argument to get the same result.
And so, the payment made per month (pmt) to acquire $1,000,000 for you retirement in 30 years is -$1,196.56. The answer is a negative number, indicating money you need to pay out or invest.
Go to the page LiveFlow‘s How to Guides to find more information about Excel and Google Sheets formulas and tips that were not covered here.
