How to Calculate a Loan Payment, Interest, or Term in Excel
Due to its functions and features, Excel is a great application for budgeting and managing your money. If you already use it for finance, make your spreadsheet even more efficient by calculating loan items like payments, interest, or terms.
Maybe you’re considering a new car loan and want to know the payment up front. You can use Excel to adjust the interest rate and payment term to see what you can afford. At the same time, you may have payment information on an outstanding loan and want to see your interest rate or payment term.
With a few simple functions and your data, you can easily get basic loan calculations in Microsoft Excel.
Calculate a Loan Repayment in Excel
For many people, affording a new car means knowing how much the monthly payments will be. To figure it out in Excel, you just need the basic loan information and a handy function.
RELATED: 7 Essential Microsoft Excel Functions for Budgeting
Get the annual interest rate, the number of payments you want, and the total loan amount and enter them into your sheet. Select the cell where you want to calculate the monthly payment; this is where you will insert the PMT (payment) function.
The function syntax is
PMT(rate, number_payments, loan_amount, future_value, type). The only arguments required are the first three for interest rate, number of payments, and loan amount.
To get the monthly payment amount for a loan with 4% interest, 48 payments and an amount of $20,000, you would use this formula:
As you see here the interest rate is in cell B2 and we divide it by 12 to get the monthly interest. Then the number of payments is in cell B3 and the loan amount is in cell B4.
By making slight adjustments to the constants, you can see what your payment would be if you had a different interest rate, made more or fewer payments, or changed the loan amount. As you adjust these numbers, the formula updates automatically.
For example, the monthly payment may be more than you can afford. By increasing the number of payments, you can see how much the monthly payments decrease.
RELATED: How to Calculate Percentage Increases in Excel
Formula to Calculate an Interest Rate in Excel
Maybe you have an existing loan and want to quickly see the annual interest rate you’re paying. As simple as calculating a payment with basic loan details, you can do the same to determine the interest rate.
Get the loan term, monthly payment, and loan amount and enter them into your sheet. Select the cell where you want to see the interest rate. You will then enter the formula for the RATE function.
The function syntax is
RATE(term, payment, loan_balance, future_value, type) where the first three arguments are required for the term (in months or years as explained below), payment amount, and loan balance.
Using the same example as above, we have the term of 48 months with the monthly payment of $451.58 and the loan amount of $20,000. You would use this formula:
Here the details are in order in the corresponding cells of the formula. We add
*12 at the end because we want annual interest rate (12 months).
You can also enter the term of the loan in years instead of months and adjust the formula as follows:
E2*12 portion multiplies the number of years in cell E2 by 12 for the number of months in the term.
How to Calculate a Payment Term in Excel
A more practical loan calculation that can help you is to determine the payment period. You can see the number of months for a loan according to the details.
Gather the annual interest rate, monthly payment, and loan amount and place them on your sheet. Select the cell where you want to see the term, then use the NPER function to find the payment period.
The function syntax is
NPER(rate, payment, loan_amount, future_value, type) where the first three arguments are required for rate, payment, and loan amount.
To use our same example, we have an annual interest rate of four percent, a payment of $451.58, and a loan amount of $20,000. Then use this formula:
Cell H2 contains our interest rate and because it is the annual rate, we divide it by 12. Then H3 and H4 contain the other details.
Optional arguments for loan calculations
As mentioned with each function above, the
type arguments are optional. Here is a brief explanation of each if you want to include them in your formula.
Future value: The amount you want after the final payment. Since it’s assumed to be zero because you’re paying an amount you owe, we omitted the argument. This can be a useful argument to use in a formula to calculate an investment rather than a loan.
Type: This indicates when payments are due and is either 0 for the end of a period or 1 for the start of a period. If the argument is omitted, the function uses 0 by default.
You can probably find a loan calculator with a Google search or even on your lender’s website. But if you want to perform calculations in your own financial workbook or budget spreadsheet, these functions and formulas make it easy for you.
RELATED: What is Money in Excel and How Do I Get Started?