Microsoft Office Tutorials and References

In Depth Information

**Using Excel Functions**

Chapter 9

Working with Formulas and Functions

NOW:
If you enter the NOW function in a

cell, Excel will display the current date and

time. The date and time are dynamic in that

the current date and time will change

whenever you recalculate anything in the

worksheet. By default, Excel recalculates the

worksheet whenever any changes,

additions, or deletions are made. The NOW

function does not contain any arguments, so the

syntax is =NOW().

Today’s Date

A quick way to calculate the number of days

between two dates is to enter =NOW() or

=TODAY()in one cell and another date in

another cell. Then create a formula to subtract

the older date from the newer date. Excel

displays the difference in a date format, so you’ll

need to format the value as a number.

MONTH:
The MONTH function returns the

month number of a date serial number or

a date in a cell. The syntax is =MONTH(

serial number or cell address
). If you have a

date of 19-May-99 in cell C16 and you

enter =MONTH(C16), Excel returns the

value of 5 since May is the 5th month in

the calendar year.

Figuring with Financial Functions

Financial functions perform elaborate calculations

such as returns on investments or cumulative

principal or interest on loans. Functions exist for

calculating future values or net present values on

investments and for calculating amortization. Take a look

at the PMT function and how you can use it.

NETWORKDAYS:
The NETWORKDAYS

function returns the number of working days

between two dates. Working days exclude

weekends and identified holidays. The

syntax also includes an optional argument

where you can enter any additional number

of days to exclude. The syntax is =NET-

WORKDAYS(
StartDate, EndDate, optional

holidays
). For example, if you enter a start date

of August 15, 1985 and an end date of

March 23, 2007 and using the standard

number of holidays, Excel returns a result of

5,637. See Figure 9-13 for another example.

The PMT function calculates the payment for a loan

based on a constant interest rate. You will need to

enter the interest rate, the number of payments,

and the amount of the loan. The syntax is

=PMT(
rate,nper,pv,fv,type
) where rate is the interest

rate,
nper
is the number of payments and pv is the

loan amount. There are two other optional

arguments, including
fv
(future value), which Excel

assumes to be zero unless you enter a
fv, and
type
,

which refers to when the payment is due. The

following steps show you how you enter this function.

1.
In separate cells, enter a loan amount, an

interest rate, and the number of payments

you intend to make. See Figure 9-14 for an

example.

Figure 9-13

Calculating days worked.