Microsoft Office Tutorials and References

In Depth Information

**Understanding Excel functions**

Function

A function is a predei ned formula. An example is the SUM function that calculates the total

of a list of numbers. You can use a function on its own preceded by an equal sign or as part of a

larger formula.

Function structure

Every function has two components: a name that always appears in uppercase and arguments

that appear within parentheses. Arguments are the inputs the function uses to perform the

calculations. An example is SUM(A1,B2,C3), which adds the values of the cells A1, B2, and C3.

Function types

Excel of ers a variety of function types, such as math and trig; however, statistical and i nancial

functions are most common; these are shown in the following tables.

FUNCTION

DESCRIPTION

Common Statistical Functions

AVERAGE Returns the average of its arguments

COUNT Counts the numbers in the argument list

MAX Returns the maximum value of the arguments

MEDIAN Returns the median value of the arguments

MIN Returns the minimum value of the arguments

MODE Returns the most common value of the arguments

STDEV Returns the standard deviation based on a sample

STDEVP Returns the standard deviation based on an entire population

Common Arguments for Financial Functions

rate

The fixed rate of interest over the term of a loan or investment

nper

The number of payments or deposit periods over the term of a loan or

investment

pmt

The periodic payment or deposit

pv

The present value of a loan (the principal) or the initial deposit in an

investment

fv

The future value of a loan or investment

type

The type of payment or deposit: 0 (the default) for end-of-period

payments or deposits; 1 for beginning-of-period payments or deposits

Common Financial Functions

FV(rate,nper,pmt,pv,type)

Returns the future value of an investment or loan

IPMT(rate,per,nper,pv,fv,type)

Returns the interest payment for a specified period of a loan

NPER(rate,pmt,pv,fv,type)

Returns the number of periods for an investment or loan

PMT(rate,nper,pv,fv,type)

Returns the periodic payment for a loan or investment

PPMT(rate,per,nper,pv,fv,type)

Returns the principal payment for a specified period of a loan

PV(rate,nper,pmt,fv,type)

Returns the present value of an investment

RATE(nper,pmt,pv,fv,type,guess)

Returns the periodic interest rate for a loan or investment