Microsoft Office Tutorials and References

In Depth Information

**Using Other Functions**

Using the Financial Functions

The Financial functions are used by accountants

and the like, to perform common tasks such as

calculating the accrued interest on a security,

depreciation on an asset, future value of an asset,

or the monthly payment on a loan. Because most

of the Financial functions are used by financial

experts who understand them, I won’t dwell on

too many. There are a few Financial functions,

however, that just about anyone might use, and

in this section, I explain more about them.

PMT

The PMT function can be used in three ways—to

calculate the monthly payment on a loan, the

interest rate you might earn on an investment,

or the monthly deposit needed in order to reach

some future total deposit amount. You feed the

PMT function various information such as loan

amount, interest rate, number of payments, and

so on, depending on the result you’re looking

for. Let’s take a closer look.

Figure 3-14

Use the PMT function to calculate loan payments.

Type
=PMT(.0725/12,6*12,–10000)
and press

Enter. It’s important that all the arguments are

based on the same interval. The first argument

is the interest rate, which needs to be converted

to its monthly equivalent. So you need to divide

the yearly interest rate of 7.25% (which is .0725

in decimal form) by 12 to calculate the interest

rate per month. The next argument is the

number of monthly payments. Because the loan is

for six years, you take 6 times 12 to calculate

the number of months. The last argument is the

present value of the loan, which for cell C8 is

$10,000. Notice that the loan amount is entered

as a negative. You don’t have to do that, but if

you don’t, the PMT function returns a negative

value because of the way the function performs

its calculation. Although a negative payment

amount might be pleasing to you, it probably

won’t be acceptable to your bank. So be sure to

enter the loan amount as a negative value.

To use the PMT function to calculate a loan, use

the following syntax:

=PMT(Rate,NumberofPayments,LoanAmt)

For example, suppose you are looking at buying

a used car for somewhere between $10,000 and

$12,000. You’ve found several loans and you

want to compare them (see Figure 3-14). The

first loan amounts have already been calculated,

so you’ll enter the formula in cell C8 for the

second loan, which is for six years at 7.25%.