How to Calculate a Loan Payment in Microsoft Excel

You can use Microsoft Excel to calculate loan payments. This can be done for car loans, mortgage loans, and other types of interest-bearing loans. The format for this is =pmt(rate,nper,pv,[fv],[type]).

Instructions

1. Launch Excel and open a new document. Type "=pmt(" into a cell.

2. Enter the annual interest rate in another cell. In the =pmt formula, you should reference this cell and divide by 12 to make it a monthly interest rate.

3. Enter the number of periods. This is the term of your loan (in months).

4. Enter the present value as a negative number. That is the original amount of the loan.

5. Close the formula after the present value by finishing the parentheses. You can also enter a fourth entry which is the future value. This is usually zero, since most loans will be completely paid off at the end. The exception is a balloon payment, an oversized final payment. Then finish off the parentheses.

References

• Investopedia: Balloon Payment
• ExcelTip.com: Formulas to Calculate Loan Payments in Microsoft Excel