A loan payment calculator is a useful tool that can help you plan your monthly payments for a loan, such as a mortgage, car loan, or student loan. You can use Excel to calculate the loan payment amount, given the interest rate, the loan term, and the loan amount. You can also use Excel to calculate other loan elements, such as the interest rate, the loan term, or the loan amount, if you have the other information.
The basic theory behind a loan payment calculator is based on the time value of money concept, which states that a dollar today is worth more than a dollar in the future, because of the potential interest that can be earned. Therefore, when you borrow money, you have to pay back not only the principal amount, but also the interest that accumulates over time.
Procedures
To use Excel to calculate the loan payment amount, you can use the built-in PMT function, which takes the following arguments:
- rate: the periodic interest rate
- nper: the number of payment periods
- pv: the loan amount (present value)
- [fv]: the future value (optional, default is 0)
- [type]: the payment type (optional, 0 for end of period, 1 for beginning of period, default is 0)
The syntax for the PMT function is:
=PMT(rate,nper,pv,[fv],[type])
For example, to calculate the monthly payment amount for a loan of $20,000 with an annual interest rate of 4.5% and a term of 5 years, you can use the following formula:
=PMT(4.5%/12,60,20000)
The formula returns -$372.86, which means you have to pay $372.86 per month for 60 months to pay off the loan. Note that the result is negative, because it represents a cash outflow from your perspective.
Example
To illustrate how to use Excel to calculate the loan payment amount, let’s consider a scenario where you want to buy a car that costs $25,000. You can get a loan from a bank with an annual interest rate of 3.5% and a term of 4 years. You also have to pay a 10% down payment upfront, which reduces the loan amount to $22,500. How much will you have to pay per month for the loan?
To answer this question, you can use the PMT function in Excel with the following inputs:
- rate: 3.5%/12, which is the monthly interest rate
- nper: 48, which is the number of monthly payments
- pv: 22500, which is the loan amount after the down payment
- [fv]: 0, which is the future value (optional, default is 0)
- [type]: 0, which is the payment type (optional, 0 for end of period, 1 for beginning of period, default is 0)
The formula is:
=PMT(3.5%/12,48,22500)
The formula returns -$499.66, which means you have to pay $499.66 per month for 48 months to pay off the loan.
You can also use an Excel table to display the loan payment schedule, as shown below:
| Month | Beginning Balance | Payment | Interest | Principal | Ending Balance |
|---|---|---|---|---|---|
| 1 | $22,500.00 | $499.66 | $65.63 | $434.03 | $22,065.97 |
| 2 | $22,065.97 | $499.66 | $64.41 | $435.25 | $21,630.72 |
| 3 | $21,630.72 | $499.66 | $63.18 | $436.48 | $21,194.24 |
| … | … | … | … | … | … |
| 47 | $1,035.11 | $499.66 | $3.02 | $496.64 | $538.47 |
| 48 | $538.47 | $499.66 | $1.57 | $498.09 | $0.00 |
The table shows the breakdown of each monthly payment into interest and principal components, and the balance of the loan after each payment. You can use the following formulas to create the table:
- Beginning Balance:
=22500for the first month, and=E2for the subsequent months, where E2 is the ending balance of the previous month. - Payment:
=PMT(3.5%/12,48,22500)for all months, which is the same as the formula we used earlier. - Interest:
=B2*3.5%/12for the first month, and=B3*3.5%/12for the subsequent months, where B2 and B3 are the beginning balances of the current and the next month, respectively. This formula calculates the interest based on the balance at the beginning of each month. - Principal:
=C2-D2for all months, where C2 and D2 are the payment and the interest of the current month, respectively. This formula calculates the principal as the difference between the payment and the interest. - Ending Balance:
=B2-E2for all months, where B2 and E2 are the beginning balance and the principal of the current month, respectively. This formula calculates the ending balance as the difference between the beginning balance and the principal.
Other Approaches
Besides using the PMT function, you can also use other Excel functions to calculate the loan payment amount, such as the IPMT (interest payment) and PPMT (principal payment) functions. These functions return the interest and principal components of a loan payment for a given period, respectively. The syntax for these functions is:
=IPMT(rate,per,nper,pv,[fv],[type])
=PPMT(rate,per,nper,pv,[fv],[type])
where:
- rate: the periodic interest rate
- per: the period for which you want to calculate the payment
- nper: the number of payment periods
- pv: the loan amount (present value)
- [fv]: the future value (optional, default is 0)
- [type]: the payment type (optional, 0 for end of period, 1 for beginning of period, default is 0)
For example, to calculate the interest and principal components of the first monthly payment for the same loan scenario as above, you can use the following formulas:
=IPMT(3.5%/12,1,48,22500)
=PPMT(3.5%/12,1,48,22500)
The formulas return -$65.63 and $434.03, respectively, which are the same as the values in the table we created earlier. To get the total payment amount, you can simply add the interest and principal components together:
=IPMT(3.5%/12,1,48,22500)+PPMT(3.5%/12,1,48,22500)
The formula returns -$499.66, which is the same as the result of the PMT function.
You can also use the IPMT and PPMT functions to create the loan payment schedule table, by replacing the formulas for the interest and principal columns with the IPMT and PPMT functions, respectively.
