Loan payoffs are simple calculations that are required every time you take a loan from a bank or a financial institution. To prevent yourself from being cheated by others or to be able to help others with their loans, follow the steps below for the loan payoff calculation.
Loan data preparation
Input the details of the Loan Amount, number of years of loan repayment, Rate of Interest, and Period of Loan payment.
- Loan Amount: Principal amount borrowed
- Years of Loan repayment: Total no. of years in which loan is completely repaid.
- Rate of Interest: Per annum interest on the principal amount.
- Period: The span during which the loan payment is made (Ex: Each month, each quarter etc.).
Here, No. of Periods = No. of Years * 12 (monthly repayment) = 10*12.
Note: You can choose a shorter loan amortization period, e.g., 6, 12 or 30 months instead of 10 years.
Loan Payoff Calculations
Calculate the Rate of interest per period.
For the above figures, the rate of interest per period is = (8/12) % = 0.67%.
PMT Formula
Calculate the loan payoff per period using the Excel PMT formula.
=PMT( Rate, Nper, Pv, Fv, Type)
- Rate: Per period interest rate.
- Nper: Total no. of periods – Pv: Initial Value of the loan (has to be negative for PMT formula).
- Fv (optional): Fv stands for future value. If after paying off the last loan payment, you want to have any balance left it is the future value. Fv can be omitted (considered 0 if omitted).
- Type (optional):
0: If payment is to be done at end of period.
1: If payment is to be done at beginning of period.
Loan payoff can be simply calculated by using the PMT formula and using the correct variables.
Full formula is =PMT(E8,E6,-E4,0)
Calculate the loan balance:
Use the PV function to calculate the current loan balance. The syntax for the PV function is: =PV(rate, nper, pmt, [fv], [type]). For example, if you have made 12 payments on a 60-month loan, and the original loan amount was $10,000 with an interest rate of 5%, the formula would be: =PV(5%/12, 48, -200, 10000). This will give you the current loan balance.
Calculate the payoff amount:
To calculate the loan payoff amount, simply add the current loan balance to any outstanding fees or charges. This will give you the total amount needed to pay off the loan.
