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.
The PMT function calculates the principal and interest payment for a loan, assuming consistent payments and a constant interest rate. It does not include taxes, insurance, or other fees that might be 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 type argument specifies when payments are due: 0 indicates payments are due at the end of the period, while 1 indicates payments are due at the beginning of the period. This argument does not control the compounding frequency of the interest rate, which is determined by how you input the rate argument (e.g., 7%/12 for monthly compounding, 7% for annual compounding).
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?
To determine how much you need to save monthly to reach $1,000,000 in 10 years with a 5% annual interest rate, you can use the PMT function.
The formula would be: =PMT (5%/12, 120, 0, -1000000)
- 5%/12 represents the monthly interest rate
- 120 represents the total number of monthly payments (10 years * 12 months)
- 0 represents the present value (no initial deposit)
- -1000000 represents the future value (the negative sign indicates an outflow or investment)
The result will be approximately -$6,439.88, indicating a monthly deposit of $6,439.88 is required.
