Page 3 of 15PREVNEXT

Plan payments and savings by using financial formulas

PMT arguments

Calculating a payment with PMT function arguments: interest rate (6%/12), number of payments (30*12), and present value, the starting amount of the mortgage (180000).

Say that you're shopping for a home, and you want to figure out how much you would pay each month for a $180,000 30-year mortgage with a 6 percent annual interest rate.

You could do that by using a prewritten Excel formula called a function, and by entering arguments, information that tells a function what to calculate. In this example you would use the PMT function, which calculates loan payments using regular, identical payment amounts and an unchanging interest rate. You would type:

=PMT(6%/12,30*12,180000)

The equal sign tells Excel that this is a formula. Within the parentheses are the arguments, separated by commas.

interest rate of 6 percent annually is divided by 12, because you are calculating monthly payments and so need a monthly interest rate.

number of payments over the 30-year mortgage period is multiplied by 12, because you will make 12 monthly payments each year.

present value is the starting amount of the mortgage loan before interest. (This value is entered without a thousands separator: 180000 instead of 180,000.)

Pressing ENTER would display the monthly mortgage payment: ($1,079.19). The result is a sum that must be paid out, and thus a negative number, which in this example is indicated by red font color and parentheses.

This function would be a great place to use cell references as arguments, so that you could easily see how changing the values changes the result. For more information, see the practice session at the end of the lesson.

Tip     Excel has its own names for the arguments in every function. For the functions discussed in this course, the argument names are listed in the Quick Reference Card. You don't have to remember them. In the course "Find functions and enter arguments," you can learn how to get help with arguments and how to discover functions for all sorts of calculations.

Page 3 of 15PREVNEXT