Because of its features and functions, Excel is a great application for budgeting and managing your money. If you already use it for finance, you can make your spreadsheet even more effective by calculating loan elements such as payments, interest, or terms.

Perhaps you are considering a new car loan and want to know the payment in advance. With Excel you can adjust the interest and payment term to see what you can pay. At the same time, you may have payment information about a current 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 payment in Excel

For many people, paying for a new car means knowing what the monthly payment will be. To find out in Excel you only need the basic loan information and a handy function.

**RELATED:** *7 Essential Microsoft Excel Budgeting Features*

Get the annual interest, the number of payments you want and the total loan amount and fill it in your sheet. Select the cell where you want to calculate the monthly payment; here you insert the PMT (payment) function.

The syntax for the function is `PMT(rate, number_payments, loan_amount, future_value, type)`

† The only arguments required are the first three for the interest, the number of payments, and the amount borrowed.

To get the monthly payment amount for a loan with four percent interest, 48 payments, and an amount of $20,000, you would use this formula:

=PMT(B2/12,B3,B4)

As you can see here, the interest is in cell B2, and we divide that by 12 to get the monthly interest. Then the number of payments is in cell B3 and the amount borrowed is in cell B4.

By making small adjustments to the constants, you can see what your payment would be if you had a different interest rate, paid more or less, or changed the loan amount. When you adjust these numbers, the formula is automatically updated.

For example, maybe the monthly payment is 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 increments in Excel*

## Formula to calculate interest rate in Excel

Maybe you have a current loan and want to quickly see the annual interest you pay. As simple as calculating a payment with basic loan information, you can do the same to determine the interest rate.

Get the loan term, monthly payment and loan amount and enter them in your sheet. Select the cell where you want to see the interest rate. Then enter the formula for the RATE function.

The syntax for the function 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), the payment amount, and the loan balance.

Using the same example as above, we have the 48 month term with the monthly payment as $451.58 and the loan amount as $20,000. You would use this formula:

=RATE(E2,E3,E4)*12

Here, the details are in order in the corresponding cells in the formula. we add `*12`

at the end because we have the . want to *annual* interest (12 months).

You can also enter the loan term 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.

## How to calculate a payment term in Excel

Another handy loan calculator that can help you is determining the payment term. Depending on the details, you can see the number of months for a loan.

Collect the annual interest rate, monthly payment and loan amount and put them in your sheet. Select the cell where you want to see the installment and then use the NPER function to find the payment period.

The syntax for the function 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 a four percent annual interest, 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 since this is the annual interest rate, we divide it by 12. Then H3 and H4 contain the other details.

## Optional Arguments for Loan Calculations

As mentioned with each feature above, the `future_value`

and `type`

arguments are optional. Here’s a brief explanation of each if you want to include them in your formula.

**Future Value**: The amount you want after the final payment is made. Since it’s assumed to be zero because you’re paying an amount you owe, we’ve omitted the argument. This can be a useful argument to use in a formula for calculating 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 beginning 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 do some calculations in your own financial workbook or budget spreadsheet, these functions and formulas make it easy.

**RELATED:** *What is money in Excel and how do you get started?*