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

adjust the formula: =PMT(.0375/4,5*4,–5000).

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

Not bad!

For example, suppose you want to invest money

annually in a CD that pays 6% in order to save

$50,000 by the end of its term, which is 15

years. Type =PMT(.06,15,,–50000).