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.

Search JabSto ::

Custom Search