Loan amortization is the process of spreading out a loan into a series of fixed payments over time. These payments cover both principal and interest, ensuring that the loan is paid off in full by the end of the term. Excel provides functions like PMT and IPMT to help calculate monthly payments and the interest portion of those payments.
Calculate Monthly Payments and Interest Using PMT and IPMT Functions
The PMT function in Excel calculates the monthly payment for a loan based on a constant interest rate and a fixed number of periods. The IPMT function calculates the interest portion of a payment for a given period.
In other words, the PMT function in Excel helps you find out how much you need to pay every month to pay off a loan. The IPMT function shows how much of each payment goes towards interest.
PMT Function
Syntax:
PMT(rate, nper, pv, [fv], [type])
- rate: The monthly interest rate.
- nper: The total number of payments.
- pv: The present value or principal of the loan.
- fv: The future value of the loan (optional, default is 0).
- type: Indicates when payments are due (optional, 0 = end of period, 1 = beginning of period).
IPMT Function
Syntax:
IPMT(rate, per, nper, pv, [fv], [type])
- rate: The monthly interest rate.
- per: The period for which you want to find the interest.
- nper: The total number of payments.
- pv: The present value or principal of the loan.
- fv: The future value of the loan (optional, default is 0).
- type: Indicates when payments are due (optional, 0 = end of period, 1 = beginning of period).
Example
Below is an example dataset showing a loan with a principal amount, annual interest rate, and term. This dataset will be used to demonstrate how to calculate monthly payments and interest using PMT and IPMT functions.
| Principal | Annual Interest Rate | Term (Years) | Monthly Payment | First Month Interest |
|---|---|---|---|---|
| $100,000 | 5% | 30 | =$PMT(0.05/12, 30*12, -100000) | =$IPMT(0.05/12, 1, 30*12, -100000) |
Exercise
Calculate the monthly payments and first month interest for the following loan:
Use the PMT and IPMT functions to determine the monthly payment and the interest portion of the first payment.
Solution
To calculate the monthly payments and interest for the loan, follow these steps:
Apply Functions:
Monthly Payment: Use the PMT function to calculate the monthly payment.
=PMT(0.04/12, 20*12, -200000)First Month Interest: Use the IPMT function to calculate the interest portion of the first payment.
=IPMT(0.04/12, 1, 20*12, -200000)