Microsoft Office Tutorials and References

In Depth Information

**Other Financial Functions**

2

•
If necessary, scroll the worksheet

to the left using the horizontal

scrollbar.

PMT function

assigned to

active cell E4

•
Click the Enter box in the formula

bar to complete the function

(Figure 4–16).

What does Excel display after I

click the Enter box?

Excel displays the monthly payment

$1,748.80 in cell E4, based on a

loan amount of $235,000.00

(cell C6) with an annual interest

rate of 5.75% (cell E2) for a term

of 18 years (cell E3), as shown in

Figure 4–16.

monthly

payment

Figure 4–16

Other Ways

1. Click Formulas tab on

Ribbon, click Financial

button on Ribbon,

select PMT function,

enter arguments, click

OK button

2. Click Insert Function

button in formula bar,

select Financial category,

select PMT function,

click OK button, enter

arguments, click OK

button

In addition to the PMT function, Excel provides more than 50 additional ﬁ nancial

functions to help you solve the most complex ﬁ nance problems. These functions save you

from entering long, complicated formulas to obtain needed results. Table 4–1 summarizes

three of the more frequently used ﬁ nancial functions.

Table 4–1 Financial Functions

Function

Description

FV (rate, periods, payment)

Returns the future value of an investment based on periodic, constant

payments, and a constant interest rate.

PMT (rate, periods, loan amount)

Calculates the payment for a loan based on the loan amount, constant

payments, and a constant interest rate.

PV (rate, periods, payment)

Returns the present value of an investment. The present value is the total

amount that a series of future payments is worth now.

Range Finder

Remember to check

all formulas carefully.

You can double-click a

cell with a formula and

Excel will use Range

Finder to highlight

the cells that provide

data to the formula.

While Range Finder is

active, you can drag the

outlines from one cell

to another to change

the cells referenced in

the formula, provided

the cells have not

been named.

To Determine the Total Interest and Total Cost

The next step is to determine the total interest the borrower will pay on the loan

(the lending institution’s gross proﬁ t on the loan) and the total cost the borrower will pay

for the item being purchased. The total interest (cell E5) is equal to the number of payments

times the monthly payment, less the loan amount:

=12 * years * monthly_payment – loan_amount

The total cost of the item to be purchased (cell E6) is equal to the price plus the

total interest:

=price + total_interest

The steps on the next page enter formulas to determine the total interest and total

cost using names.