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:
Determining a monthly payment
Let’s have a quick look at the arguments of this function.
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
After entering all the formulae:
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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.