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
• 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
• 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 ])