# 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

- 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.)

- 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

- 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.

- 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.