Microsoft Office Tutorials and References
In Depth Information
Using Other Functions
Copying Formulas Across Rows
Skip an Argument with a Comma
You could type the formula
=PMT(.0725/12,6*12,–C8) and then copy
it across the row to calculate the monthly
payment for the various loan amounts you
are considering.
Notice that you type two commas after
15, which is the second argument,
NumberofPayments. The extra comma is to
indicate that you are not entering the third
argument for the PMT function, which is
CurrentValue. You skip this argument because
it’s not needed for this type of calculation.
Another reason you might use the PMT function
is to calculate the interest you might earn on an
investment, such as a CD (Certificate of Deposit).
Use the following syntax:
FV and PV
Two other useful Financial functions are FV and
PV, which find the future or present value of an
investment, given a specific interest rate and
an amount to be invested each period. Use the
following syntax:
=PMT(Rate,NumberofPayments,CurrentValue)
For example, to calculate the annual interest you
might earn on a CD with an interest of 3.75%,
five-year term, and an investment of \$5,000, you
type =PMT(.0375,5,–5000). Because you are
calculating the annual interest, you don’t have to
divide the interest rate by 12. If the interest rate
is calculated quarterly however, you’ll need to
=FV(Rate,NumberofPayments,PmtAmt,PresentValue,
Type)
=PV(Rate,NumberofPayments,PmtAmt,FutureValue,
Type)
Like the PMT function, all of these values need
to be roughly equivalent, so if you are making
quarterly payments, you need to divide the
annual interest rate by four. In addition, like the
PMT function, you need to enter the future or
present value as a negative if you want to get a
positive result.
The final reason to use the PMT function is to
calculate the amount you need to invest now, if
you want to reach some future amount over
time. Use the following syntax:
=PMT(Rate,NumberofPayments,,FutureValue)
The final argument for both functions, Type, tells
Excel when the payment is made: use a 1 if you
pay at the beginning of the month, and a 0 if
you pay at the end (you can also omit this value
and 0 is assumed). So let’s determine how much
you’ll have at the end of five years, if you invest
\$100 per month at 4%. Type this formula:
=FV(.04/12,5*12,–100). Hmmmm. It looks like
at the end of five years, you’ll have \$6,629.90.