Microsoft Office Tutorials and References

In Depth Information

**Creating formulas to calculate values**

Two other functions you might use are the
NOW
and
PMT
functions. The
NOW
function

displays the time Excel updated the workbook’s formulas, so the value will change every

time the workbook recalculates. The proper form for this function is
=NOW()
. To update the

value to the current date and time, press the F9 key or display the Formulas tab and then, in

the Calculation group, click the Calculate Now button.

The
PMT
function is a bit more complex. It calculates payments due on a loan, assuming a

constant interest rate and constant payments. To perform its calculations, the
PMT
function

requires an interest rate, the number of payments, and the starting balance. The elements

to be entered into the function are called
arguments
and must be entered in a certain order.

That order is written as
PMT(rate, nper, pv, fv, type)
. The following table summarizes the

arguments in the
PMT
function.

3

Argument

Description

rate

The interest rate, to be divided by 12 for a loan with monthly

payments, by 4 for quarterly payments, and so on

nper

The total number of payments for the loan

pv

The amount loaned (pv is short for present value, or principal)

fv

The amount to be left over at the end of the payment cycle (usually

left blank, which indicates 0)

type

0 or 1, indicating whether payments are made at the beginning or at

the end of the month (usually left blank, which indicates 0, or the end

of the month)

If Consolidated Messenger wanted to borrow $2,000,000 at a 6 percent interest rate and

pay the loan back over 24 months, you could use the
PMT
function to figure out the

monthly payments. In this case, the function would be written
=PMT(6%/12, 24, 2000000)
,

which calculates a monthly payment of $88,641.22.

TIP
Because the payment calculated by the
PMT
function represents money that lows out

of your bank account, the result is a negative number. If you want the result to be expressed

as a positive number, multiply the formula’s result by -1.

You can also use the names of any ranges you defined to supply values for a formula. For

example, if the named range NortheastLastDay refers to cells C4:I4, you can calculate the

average of cells C4:I4 with the formula
=AVERAGE(NortheastLastDay)
. With Excel, you can

add functions, named ranges, and table references to your formulas more efficiently by

using the Formula AutoComplete capability. Just as AutoComplete offers to ill in a cell’s

text value when Excel recognizes that the value you’re entering matches a previous entry,

Formula AutoComplete offers to help you ill in a function, named range, or table reference

while you create a formula.