In this article we will learn about how to calculate the loan amortization schedule in Excel. To calculate loan payment we will use the “RATE”, “NPER”, “PV”, “PMT”, “PPMT” and “IPMT” formulae. All these formulae will help to create the amortization table in Excel.
PMT: Returns the regular monthly payment on the loan (principal + interest) when the interest for each of the monthly payments is constant. Calculates the payment for a loan based on constant payments and a constant interest rate.
Syntax of “PMT” function: =PMT (Rate, Nper, -Loan Amount)
RATE: Returns the percentage of interest on the loan, when the number of payments is constant. Rate is calculated by iteration can have zero or more solutions.
Syntax of “NPER” function: =NPER (Rate, Pmt, -Loan Amount)
PV: The present value, the total amount that a series of future payments is worth now. Returns the current value for a series of payment with a constant interest rate.
Syntax of “PV” function: =PV (Rate, Nper, Pmt)
PPMT: Returns the amount on the principal for a given period for a loan based on periodic, constant payments and a constant interest rate. And also returns the sum of the principal within the monthly payment (the monthly payment is comprised of the principal + interest).
Syntax of “PMT” function: =PPMT (Rate, Which Period, Nper, -Loan Amount)
IPMT: Returns the interest payment for a given period for a loan based on periodic, constant payments and a constant interest rate. And also Returns the amount of the interest within the monthly payment (the monthly payment is comprised of the principal + interest).
Syntax of “IPMT” function: =IPMT (Rate, Which Period, Nper, -Loan Amount)
Let’s take an example:
- The Bank has approved $300,000as the loan amount
- Interest Rate is 5% floating rate
- Number of Months 360 (12 x 30)
- We need to calculate the payments to be made every month
- We need to calculate themoney we are paying towards interest each month
Determining a monthly payment
- If there was no interest rate, monthly payment would be ($300,000 / 360 = $833.33)
- We can calculate the the monthly payment by using the PMT function
Let’s have a quick look at the arguments of this function.
- Cell B4 has the Interest Rate which is divided by 12, as it is the annual rate which is
- Cell B5 has “nper” which is calculated by multiplying Number of years the loan is
required by 12 months. 10 is the number of months as shown in above example.
- Cell B3 has Present Value.
- Type is for payment at the end of the period, for which we will enter‘0’ or we can
also omit it. For payments made at the beginning of the period we will enter 1.
- Cell B6=PMT(B4/12,B5,-B3,0)
- The monthly payment is $ 1,610.46
The PMT function is used to calculate the periodic payment for a standard amortizing loan.
Lets take another example -
See Figure 2 below:
The monthly payment is $ 5,150.17 for a loan amount of $50,000 and an interest rate of 6.50% for a period of 10 months.
This figure shows the formulae which have been entered in the required cells.
In Figure 2a, Principle Balance in cell F11=Loan Amount
Principle Balance in cell F12=F11-C12(Interest)
Let’s calculate the Interest, Principal, Cumulative Principal & Cumulative Interest
- Interest is calculated in cell B12=$B$4/12*F11 as per figure2b.
- Formula for Principal in cell C12= Interest in cell B12 is subtracted from
Payments in cell $B$6.
- Formula for Cumulative Principal in cell D12=C12+D11
- Formula for Cumulative Interest in cell E12 =B12+E11
After entering all the formulae:
- You need to select cell B12 to F12
- Then with your mouse, click and hold the square dot on the lower-right corner of the
- Then drag to extend the selection to the number of payments till Principal Balance
The process of interest calculation based on the remaining balance continues until the mortgage is paid off. So each month the amount of interest decreases and amount to pay-off the loan increases. After 10 payments, the mortgage is fully paid off.