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:

=PMT(B2/12,B3,B4)

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.

PMT function in Excel

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.

Adjust the conditions to change the payment amount

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:

=RATE(E2,E3,E4)*12

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).

RATE function in Excel using months

You can also enter the term of the loan in years instead of months and adjust the formula as follows:

=RATE(E2*12,E3,E4)*12

the E2*12 portion multiplies the number of years in cell E2 by 12 for the number of months in the term.

RATE function in Excel using years

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:

=NPER(H2/12,H3,H4)

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.

NPER function in Excel

Optional arguments for loan calculations

As mentioned with each function above, the future_value and 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?

Comments are closed.