TutorialsSoftwareMicrosoft ExcelExcel Tutorial: How to make a loan payment calculator using the PMT function

Excel Tutorial: How to make a loan payment calculator using the PMT function

Written by Marc Roberge | 02 June 2010

View Comments

excelloan

In this tutorial, I will show you how to create a simple loan payment calculator in Excel using a few basic formulas and the PMT function. This calculator can be used by a borrower or lender to establish a payment amount based on an initial loan amount, annual interest rate, repayment term and periods.

Start by launching Excel and create this basic form in the cell range A1:B11:

Now, lets type in some “dummy” data so that we’ll know that our formulas and functions are working as we move along. Just for fun, let’s make the loan amount for $10000 at an annual interest rate of 6% (0.06) to be paid monthly over 2 years.

To calculate the Periodic Interest Rate, simply divide the Annual Interest Rate by the number of periods (Repayment Period). To do this, in cell B6 type the following formula and press the Enter key.

You should get “0.005” or (.5%) as a result.

In cell B7, we want to multiply the Repayment Period by the Repayment Term to get the total number of payments. Type the following formula and press the Enter key.

We now have all the information required to figure out what our payment amount will be. Select cell B9 and press Shift+F3 to open the “insert function” dialogue window. In the “Search for a function” field, type PMT and press the Enter key.

From the function description, we know that the PMT function uses the periodic interest rate (rate), total number of payments (nper) and loan amount (pv) to calculate the payment amount. Click the “OK” button.

Enter B6 in the Rate field, B7 in the Nper field and B1, which is the initial loan amount, in the Pv field and click “OK”.

So our payment amount for $10000 @ 6% over 2 years is $443.21/month.

In B10, insert “=B9*B7” to calculate the total amount to be paid (including interest) by multiplying the payment amount by the total number of payments.

And to know how much interest will be paid or earned, simply subtract the “Loan Amount” (cell B1) from the “Total Amount” in cell B10. In cell B11, type “=B10+B1” and press the Enter key. We use “+” instead of “-“ here because the value in B10 is actually a negative number (usually shown in red, with brackets or a – sign).

And there you have it. You can change any of the values in cells B1, B2, B3 or B4 to suit your needs.