by David Hakala
Suppose you are shopping for a car. You know your budget and you have an idea what interest rate you will have to pay. You want to play around with some “what if” scenarios to see what your monthly payments may be. This is where Excel’s interest calculation functions come in.
You are going to use the built-in function PMT() to calculate a payment given three arguments. An argument is a variable whose value you supply. In this case, the arguments are the amount of the car loan, the number of months over which you will make payments, and the interest rate. Let’s see how PMT() works.
Open Excel and in any cell type the following:
=PMT(6%/12,36,75000)
The “equals” sign tells Excel this is a formula. The arguments are contained within parentheses and are separated by commas.
What we have done here is divided the annual interest rate by 12 because we are working in months and we want the monthly interest rate (0.5%). The 36 represents 36 monthly payments. The amount of the loan is $75,000. (Nice car!) It is called the Present Value of the loan, the starting amount before any interest is added.
Press the Enter key and the answer replaces the formula in the cell: $2,281.65. You might want to spread those payments out over five years. Just change the middle argument, “36” to “5*12” to get the new answer, $1,449.96. That’s better but you’ll still be working a lot of overtime.
You can also use the PMT() function to see how much you could save by paying off your credit card bill faster. First, calculate the payment for four years on a balance of $10,000 with an interest rate of 17%, and multiply the monthly payment by 48 to get the total you will pay.
Excel payment field
=PMT(17%/12,48,10000)*48 = $13,850.42 (monthly payment $288.55)
Now change “48” to “24” in both places to see what you would pay over two years.
=PMT(17%/12,48,10000)*48 = $11,866.14 (monthly payment $494.42)
The difference is $1,984.28, or about four of the higher payments. You might want to consider it.
David Hakala has perpetrated technology tutorials since 1988 in addition to committing tech journalism, documentation, Web sites, marketing collateral, and profitable prose in general. His complete rap sheet can be seen at http://www.linkedin.com/in/dhakala