How to calculate the payment, interest or term of a loan in Excel

Thanks to its functions and different characteristics, Excel is ideal for calculate the payment, interest or term of a loan to be able to manage our money. Therefore, Microsoft Excel is ideal for keeping track of our finances and being more effective by calculating different aspects of it.

Excel has a lot of very useful and interesting features like the COUNT function, hundreds of templates for any kind of work, error checking and extremely useful basic functions for different occasions.

Calculate a loan payment in Excel

There are many people who, when purchasing something, need to know what the monthly payment will be. To find out in Excel, we just need the basic loan information and a handy function.

We must obtain the annual interest rate, the number of payments we want and the total amount of the loan and enter them on the sheet. We are going to select the cell where we want to calculate the monthly payment and this is where we will have to insert the function.

The function syntax is PMT. It will be much easier to understand through the following example.

To get the monthly payment amount for a loan with four percent interest, 24 payments, and a total amount of $20,000, we would use this formula.

=PMT(B2/12,B3,B4)

Here we can see that the interest rate is in cell B2 and we divide it by 12 to get the monthly interest. Next, the number of payments is found in cell B3 and the loan amount in cell B4.

Here the only thing left to do is some adjustments in the constants, in this way we can see what the payment would be, if we had a different interest rate, made more or less payments or changed the total amount of the loan. As you adjust the numbers in the cells, the formula will update automatically.

Adjust monthly payments.

For example, if the monthly payment is more than it is possible to pay. When we increase the number of payments, we can see how much the monthly payments will decrease.

Calculate interest rate

Perhaps we already have a loan and want to see what the annual interest rate we are paying. It’s as simple as calculating a payment with basic loan details. We must have the loan term, monthly payment and loan amount and enter them on the sheet. We select a cell where we want to see the interest rate and then enter the formula for the RATE function.

Calculate interest rate automatically.

Here we will use the RATE function and the same example as above. If we have a term of 48 months with a monthly payment of $451.58 and a loan of $20,000, we will use the following formula: =RATE(E2,E3,E4)*12

Now we see the details that are in order in the corresponding cells of the formula. We add a 12 to the end, since we want the annual interest rate (12 months).

Calculate interest rate.

If we want to enter the term of the loan in years instead of months, we will have to modify the formula as follows: =RATE(E2*12,E3,E4)*12

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

Calculate a payment term in Microsoft Excel

Determining the term or payment period is extremely useful. We can display the number of months for a loan depending on the details. We will need to gather the annual interest rate, the monthly payment and the loan amount, we put all this data in the spreadsheet.

Outcome.

We will select the cell where we want to see the term and we will use the NPER function to see the payment period. In this example we have an annual interest rate of four percent, a payment of $451.58 and a loan of $20,000, we will use the following formula: =NPER(H2/12,H3,H4)

Cell H2 has the interest rate, and since it’s the annual rate, we’re going to divide it by 12. Then H3 and H4 have the rest of the details.

As you can see, calculating the payment, interest or term of a loan in Excel is extremely simple. Remember that if you have any questions, you can leave it in the comment box that you will find a little further down.

Share it with your friends!