Plan payments and savings with Excel

Applies to
Microsoft Excel 2002

How much will you need each month for your mortgage or car payment? How much could you save by paying off a credit card balance in two years instead of four years? How much should you save each month, and how long would it take, to pay for college tuition or a vacation? How much could you gain on your savings over a period of time? Microsoft Excel formulas make it simple to get answers to these and other important financial questions.

Excel contains prewritten formulas called functions; this article looks at some uses of the PMT function.

Calculating your mortgage payment

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 can do that by using an Excel function and by supplying arguments, information that tells the function what to calculate. In this example you use the PMT function, which calculates loan payments using regular, identical payment amounts and an unchanging interest rate.

To calculate a mortgage payment

  1. In any cell in a worksheet, 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.
  • The interest rate of 6 percent annually is divided by 12, because you are calculating monthly payments and so need a monthly interest rate.
  • The number of payments over the 30-year mortgage period is multiplied by 12, because you will make 12 monthly payments each year.
  • The 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.)
  1. Press ENTER to display the monthly mortgage payment: ($1,079.19). Since the result is a sum that must be paid out, it is displayed as a negative number, indicated by red font color and parentheses.

Determining the costs of paying off a credit card sooner or later

You can also use the PMT function to find how much difference it would make, in monthly payments, if you were to pay off a credit card balance faster—for example, in two years instead of four years.

Imagine a balance of $5,400 and a 17 percent annual interest rate. Suppose also that nothing more will be charged to this account while the balance is being paid off.

To calculate what you need to pay each month in order to be finished in two years

  1. In any cell in a worksheet, type:
=PMT(17%/12,2*12,5400)

The arguments are the same as before:

  • The interest rate is again the annual rate, 17 percent, divided by 12 to give a monthly rate.
  • The number of payments over the two year period is multiplied by 12, because you will make 12 monthly payments each year.
  • The present value is the credit card balance due before interest, entered as 5400.
  1. Press ENTER to display the monthly payment.

As you see, it will cost ($266.99) a month to pay the balance off in two years.

To calculate monthly payment required to be finished in four years

You can easily find the monthly payment for four years by changing just one number:

  • In the formula you just used, replace 2*12 with 4*12:
=PMT(17%/12,4*12,5400)

The monthly payment over four years would be ($155.82).

Compare the results

The monthly payment over four years ($155.82) looks good in contrast to ($266.99), doesn't it? But look at the totals:

Two years: 24*266.99=6407.76
Four years: 48*155.82=7479.36

Those smaller monthly payments would cost more than a thousand dollars! That is the difference in interest paid for four years instead of two years.

More information

There are a number of other functions you can use to help you determine such things as down payments, how much savings you can build up over several years, and how a starting deposit can control the amount you need to save to reach a goal. For more information about functions in Excel, see Building formulas faster.