Microsoft Office Tutorials and References

In Depth Information

**Using the PMT Function**

Using Functions to Manage Personal Finances

Excel has many financial functions to manage personal finances. The following list

can help you determine which function to use for the most common personal finance

problems:

• To determine how much an investment will be worth after a series of monthly

payments at some future time, use the FV (future value) function.

• To determine how much you have to spend each month to repay a loan or mortgage

within a set period of time, use the PMT (payment) function.

• To determine how much of your monthly loan payment is used to pay the interest, use

the IPMT (interest payment) function.

• To determine how much of your monthly loan payment is used for repaying the

principal, use the PPMT (principal payment) function.

• To determine the largest loan or mortgage you can afford given a set monthly

payment, use the PV (present value) function.

• To determine how long it will take to pay off a loan with constant monthly payments,

use the NPER (number of periods) function.

For most loan and investment calculations, you need to enter the annual interest rate

divided by the number of times the interest is compounded during the year. If interest

is compounded monthly, divide the annual interest rate by 12; if interest is compounded

quarterly, divide the annual rate by 4. You must also convert the length of the loan or

investment to the number of interest payments per year. If you will make payments

monthly, multiply the number of years of the loan or investment by 12.

Using the PMT Function

Diane and Glenn plan to purchase a house in the next several years. Diane wants to

know how large of a mortgage she and Glenn could afford given their current income

and expenses. To calculate the costs associated with a loan or mortgage, you need the

following information:

• The amount being borrowed

• The annual interest rate

• The number of payment periods per year

• When loan payments are due

• The length of the loan in terms of the number of payment periods

In Diane and Glenn’s neighborhood, starter homes are selling for about $240,000. If

Diane and Glenn can keep to their savings plan, they will have saved $8,100 by the end

of the year (as shown in cell L6 in the Budget worksheet). If they save this same amount

for the next three years, they will have $24,300 in their home savings account to put

toward the down payment. Based on this, Diane estimates that she and Glenn will need

a home loan of almost $215,000.

Diane wants to know how much a mortgage of this size would cost in monthly

payments. To determine this, you’ll use the PMT function, which has the syntax

PMT(
rate
nper
pv,ƒ[
fv=0
]ƒ[
type=0
])