Site icon Best Excel Tutorial

How to Use Pmt Function in Excel

Simple PMT Formula

Simple PMT Formula

In this lesson, you can learn how to calculate loan installment using PMT function in Excel.

PMT function helps you to calculate the amount of the installment loan assuming a fixed interest rate and fixed monthly payment amount. ith this feature, you can also calculate the amount you need to deposit to save a certain amount at a certain time.

Payments calculated by PMT contain basis and exclude taxes and interest, and other fees associated with the loan.

PMT function syntax

This function has the following syntax:

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

How does the PMT function work?

The PMT function works by using a formula to calculate the periodic payment required to repay the loan. The formula takes into account the present value, the interest rate, the number of periods, and the future value (if specified).

The optional argument can be used to specify whether the interest is compounded annually or not. If the argument is FALSE, the interest is compounded annually. If the argument is TRUE, the interest is compounded monthly.

Examples of Pmt function

Here are some examples of how to use the PMT function in Excel:

Calculating monthly installment

Calculate the monthly installment of the loan in the amount of 100 000 $. Interest is 7% per annum, and the loan must be paid within 15 months.

In any cell, enter the function: =PMT (7%/12, 15, -100000)

Result is 6 982$

Calculating monthly installment paying in the beginnig of the month

Calculate the monthly payment amount for the same loan, if payments with payment in advance (in the beginning of the month)

Formula is: =PMT (7%/12, 15, -100000, 0, 1)

Result is 6 941,51$

How much to save to become millionaire?

You want to save $ 1 000 000 over 10 years. Every year you deposit a fixed amount. The interest rate is 5% savings. Using the PMT function, calculate how much you should deposit per month.

Formula is: =PMT (5%/12, 120, 0, 1000000)

Result is -6 439,88$ (with minus because you have to pay it to the bank)

Exit mobile version