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])
- Rate – the rate of interest loans.
- Nper is the total number of installments
- PV – current value (for example the amount of credit)
- FV – value or future financial level you want to get after the last payment
- Type is the number 0 or 1 indicates when the payment takes place 1-payment in advance, 0-payment of arrears.
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)
- 7%/12 is interest rate per month
- number of rates is 15
- amount of credit is 100 000 (with minus becase that’s not your money and you have to pay it back)
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)
- 7%/12 is interest rate per month
- number of rates is 15
- amount of credit is 100 000 (with minus becase that’s not your money and you have to pay it back)
- future value is 0, because you want to pay back whole loan
- 1 means payments in beginning of the period
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)
- 5%/12 is interest rate per month
- 120 is number of month (10 years * 12)
- 0 is present value (you have any deposit now)
- 1 000 000$ is future value
Result is -6 439,88$ (with minus because you have to pay it to the bank).
