Site icon Best Excel Tutorial

How to Calculate Loan Payoff

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.

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)

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.

Exit mobile version