How to calculate interest on a loan

In this article, we will learn how to use the PMT function to calculate loan on payments in Excel.

Excel lets a person find monthly instalment on a loan amount using the function having principle amount or loan amount, interest rate per month and the period of payment.

The function calculates the payment for a loan based on constant payments and a constant interest rate.
Syntax:

Syntax for loan calculation formula:-
Rate – The interest rate per period.
Per – The period for which the interest rate is calculated.
Nper – The total number of payments.
Pv – The present value, the total amount that a series of future payments is worth now.
Type – The timing of the payment, either at the beginning or end of the period. Numbers 0 or 1 represent the payment date. The number 0 represents payment at the end of the period, and the number 1 represents payment at the beginning of the period. The default (empty argument) is 0. The calculation is at the end of the period.

All of the above might be confusing for some people, so let’s gear up & start learning its usage in excel with the example.

Consider a scenario, John has taken a loan of $100,000 from a bank at 6.5% per year interest in 5 years period. He needs to find out his monthly payment to the bank paying his loan.

So he added the following details in Excel.

Use the formula in B6 cell

You must be wondering why Interest rate is divided by 12 as Applying the formula in the cell.

John has to pay $1,956 per month. You must be wondering why this amount shown here is negative. The loan amount is positive which is credited to John. He has to pay that amount which is to be debited.

You can use minus sign(-) in the formula to get the result in positive sign(+)
Use the alternative formula for signs

Hope you understood how to calculate monthly installments on a loan using PMT function. Explore more articles here on calculating accounts function here. Please state your queries in the comment box below.