How to Calculate a Monthly Loan Payment in Excel (Mortgage, Car Loan, and More)
If you're not an expert in using Microsoft Excel, then calculating a monthly loan payment using the program can seem intimidating. The good news is that it's relatively simple to do.
In fact, Excel has a function automatically built into the program that calculates monthly payments for you. All you need to do is enter the specifics of the loan and you can calculate monthly payments for mortgages, car loans, student loans, and more.
Below we walk you through exactly how to calculate your monthly loan payment using Excel. If you would rather, InvestingAnswers also has a number of calculators that can figure your payments for you.
Calculating Monthly Payments with Excel (All Versions)
To calculate a monthly payment for a loan using Excel, you will use a built-in tool called "PMT, " or the "Payment" function.
The PMT function works the same across all versions of Excel, so the instructions below will work no matter if you are running an old or brand-new edition of the program.
The PMT function requires three data points to calculate a monthly loan payment -- the interest rate, the number of loan payments, and the amount borrowed.
- RATE Required: Interest rate of the loan
- NPER (number of periods) Required: The number of loan payments
- PV (present value) Required: The amount borrowed
There are two other optional data points you can use for your specific calculation, should they be needed:
- FV (future value) Optional: The final balance after all payments are made (usually $0)
- TYPE Optional: Use "0" or "1" to specify whether the payment is timed at the beginning or end of the month (assuming the loan payments are made monthly).
To use the PMT function, you will select the cell and type in "=PMT(" without the quotation marks. It will then begin asking you to enter in the following additional data points:
RATE: After typing the open parenthesis, Excel will first ask for the RATE, or interest rate on the loan. Here you will enter the interest rate in percentage terms for each period. So if you want to calculate a monthly mortgage payment using a 5% interest rate, you can enter "5%/12" or "0.05/12". The "/12" divides the annual interest rate into monthly amounts. (Caution: If you just enter "5/12" instead, then Excel will interpret this as a 500% annual rate paid monthly. Entering just "5" will result in a 500% interest rate each month.)
NPER: Once you enter the interest rate, type a comma to move to the next data point, NPER, or number of periods. This is simply how many payments you will make on a loan. For example, a 30-year mortgage paid monthly will have a total of 360 payments, so you can enter "360" or "30*12". If you wanted to calculate a five-year loan that's paid back monthly, you would enter "60" or "5*12" for the number of periods.
PV: For the PV (present value) data point, you will enter the amount borrowed. For example, if you have borrowed $150,000, you will enter "150000" into this section.
FV: Using the FV (Future Value) data point is optional. It usually comes into use if you are calculating a savings goal instead of paying down a loan balance. Since the vast majority of loans are based on paying the loan off completely, this is automatically defaulted to $0 for the PMT function. If you are instead saving money for a goal, then you can enter "0" for the PV and the balance you want after the last payment for the future value.
TYPE: Finally, using the TYPE data point you can specify whether the loan payments occur at the beginning of each period or at the end. The timing of the payment has an impact on the amount of interest accrued during the month.
Note: While you can enter each data point separately, it's usually better to have a cell dedicated to each figure as we do in the examples below. That way you can adjust your data within the cells easily to see how the monthly payment changes.
Example: Calculating a Monthly Mortgage Payment in Excel
Let's say you are shopping for a mortgage and want to know what your monthly payment would be. To calculate, all you need are three data points:
- Interest rate
- Length of loan
- The amount borrowed
We've inserted example data points below. Here, we are calculating the monthly payment for a 30-year mortgage of $250,000 at an interest rate of 5.0%:
To calculate our payment, we enter this data into the appropriate field in the PMT function. Note that because our mortgage is based on monthly payments, we will divide the interest rate by 12 (to give us the monthly interest rate) and multiply the number of payments by 12 (to give us the total number of payments):
As you can see below, the monthly principal and interest payment for this mortgage comes out to $1,342.05. This is shown in Excel as a negative figure because it represents monthly money being spent. If you wanted to show it as a positive figure, you can enter a negative sign in front of the amount borrowed.
Example: Calculating a Monthly Car Payment in Excel
Calculating a monthly car payment is similar to figuring a monthly mortgage payment. To start, you will need the interest rate, length of loan, and the amount borrowed.
For this example, let's say the car loan is for $32,000 over five years at a 3.9% interest rate:
As discussed above, you will enter this data into the PMT function to calculate your monthly payment. Since this loan is paid in monthly installments, the interest rate will need to be divided by 12 while the length of the loan should be multiplied by 12:
Again, the monthly payment amount will show as a negative because it is an outflow of money each month. In this case, the monthly car payment comes to $587.89:
Frequently Asked Questions
How can I be sure I've entered the formula correctly in Excel?
The easiest way to tell if you have the correct formula is to follow the examples above. You should get the exact same figures shown. You can also check your work against our mortgage calculator or auto calculator.
Why does my monthly payment show as negative?
Since a payment is considered a cash outflow, Excel shows the monthly payments as a negative number. If you feel more comfortable seeing a positive figure, you can adjust the formula by putting a negative sign in front of the amount borrowed.
How will changing the values within the PMT function change the monthly payment?
Each value changed will adjust the monthly payment. Higher interest rates will increase the amount of the monthly loan payments while lower interest rates will decrease them. Longer loan terms reduce the monthly payments while shorter loan terms increase them. Higher amounts borrowed will increase the monthly payments while lower amounts will decrease them.
Excel keeps giving me an error in the PMT formula. What do I do?
The most common reason for an error message is not putting a closing parenthesis when entering the PMT function. You may also run into incorrect amounts if you forget to adjust the interest rate or loan term to account for the frequency of payments. For example, if you will make monthly payments, you should divide the interest rate by 12. If your loan length is in years, but you will make monthly payments, you'll want to multiply the loan length by 12.