Microsoft Excel Functions Used for Calculating Monthly Loan Payments
This attentiveness comes at the cost of long, complicated formulas when crunching the numbers by hand. However, you can ease that burden by using Microsoft Excel 2013’s collection of financial functions to quickly extrapolate payments, loan amounts, interest rates and more.
1. Entering Pertinent Data
Start setting the stage by entering the pertinent variables, as shown in the image above. You’ll need at least three pieces of data, usually the interest rate, number of payment periods and the monthly payment or loan amount.
A previous article, entitled How to Calculate How Expensive of a Car or House You Can Afford Based on Your Monthly Budget walked you though calculating the maximum monthly loan amount you can afford, based on a budgeted monthly payment, so this article starts there and assumes you know the interest rate, number of payments and monthly payment. But don’t worry; this article will explain how to calculate all of the other variables as well.
This example uses an annual percentage rate (APR) of six percent, a five-year loan and payments of $400. To convert the APR to a monthly rate, simply divide it by 12 or enter the formula “=6%/12” in cell B2, as shown in the formula bar at the top right of the image. Similarly, multiply the number of years by 12, or enter “=5*12”, to calculate the number of monthly periods. Also note that the payment amount should be negative, because it represents an expense.
2. Calculating Loan Amount
Enter the formula
=PV(B2,B3,B4)
in cell B1 to calculate the present value of the annuity, which represents the original loan amount. You could also enter the data directly into the formula using the format
=PV(InterestRate,#Payments,PaymentAmount)
such as
=PV(6%/12,5*12,-$400).
To avoid a circular reference when using this result in subsequent calculations, paste it as a value in the same location. To do this, highlight the cell, press “Ctrl-C” to copy it, click the “Paste” drop-down arrow from the Home tab’s Clipboard group and press “V.”
3. Calculating Interest Rate
Use the formula
=RATE(B3,B4,B1)
to calculate the interest rate, given the other available data. This formula follows the format
=RATE(#Payments,PaymentAmount,LoanAmount).
You may need to format the cell to see the non-rounded value (right-click the cell, select “Format Cells” and increase the number of decimal places in the resulting window). Copy and paste this result as a value, as you previously did, to use it in other calculates without risking a circular reference.
To find the APR, multiply this result by 12 or enter
=RATE(B3,B4,B1)*12
but use a different cell, so you don’t affect subsequent calculations. Note that the APR is different than the annual percentage yield (APY), which factors in the effects of monthly compounding. To calculate the APY, enter the formula
=POWER(1+RATE(B3,B4,B1),12)-1
to see the decimalized version. Click the “%” sign in the Home tab’s Number group to convert it to a percentage. Again, you might need to increase the number of decimal places to see the exact figure.
4. Calculating Number of Payments
Insert the formula
=NPER(B2,B4,B1)
to calculate the number of payment periods, given the other data. To enter data directly in the formula, use the format
=NPER(InterestRate,PaymentAmount,LoanAmount).
Paste the result as a value to use it in other formulas.
5. Calculating Payment Amount
Enter the formula
=PMT(B2,B3,B1)
to calculate the monthly payment, which is useful when you want to calculate a monthly payment for a particular purchase. This function uses the format
=PMT(InterestRate,#Payments,LoanAmount).
For later use, paste this figure as a value.
Note that house payments typically include other expenses, such as escrow and mortgage insurance, which are not included in this calculation.
6. Calculating Total Amount Paid
Multiply the monthly payments by the number of payments, using the formula
=B4*B3,
to calculate the total amount paid after the final payment. The difference between the loan amount and this total amount is the interest-only total, which amounts to $3,309.78.
7. Calculating Future Value
Enter the formula
=FV(B2,B3,B4)
to calculate the future value of the series of monthly payments, following the format
=FV(InterestRate,#Payments,PaymentAmount).
This figure offers an important investment comparison. Basically, if you invested each of those monthly payments in an investment vehicle offering the same interest rate, you would have $27,908.01 at the end of five years.
Feel free to change the interest rate to match any alternative investment. Because you pasted the previous figures as values, they won’t change when you update the interest rate. Just remember to enter the annual rate as a monthly one by dividing it by 12. For example, if you entered =2%/12, you’d get a future value of $25,218.94 by making 60 payments of $400, given the two percent annual interest rate.
If you’re interested in calculating exactly how much of a particular payment is attributed to interest or principal, see the upcoming article, Calculating Interest and Principal Payments for Each Monthly Loan Installment.
