Functions In Excel

1
Functions In Excel
2
A Function

• Performs a predefined operation
• A function
• Accepts one or more arguments as input
• Performs the indicated calculation
• Returns another value as output

3
Main Functions

• Financial Functions
• Statistical Functions
• DATABASE Functions

4
Financial Functions
5
Annuity Functions

• An annuity is a series of constant cash payments
  made over a continuous period
• A car loan or a mortgage is an annuity
• Benefits received each period is an annuity
• Cash you pay out is represented by a negative
  number cash you receive is represented by a
  positive number
• A 1,000 deposit to the bank would be represented
  by the argument -1000 if you are the depositor
• A 1,000 deposit to the bank would be represented
  by 1000 if you are the bank

6
Annuity Functions - PV

• Syntax
• PV(rate,nper,pmt,fv,type)
• Rate   
• Interest rate per period
• 10 percent annual interest rate yearly benefits
  to MA degree
• Nper   
• Total number of payment periods in an annuity
  43 years
• Enter 43 into the formula for nper
• Pmt   
• Payment (benefit) made each period
• Cannot change over the life of the annuity

7
Annuity Functions - PV

• Fv   
• Future value, or a cash balance you want to
  attain after the last payment is made
• If fv is omitted, it is assumed to be 0 (the
  future value of a loan, for example, is 0)
• Type   
• Is the number 0 or 1
• Indicates when payments are due/received
• 0 or omitted at the end of the period
• 1 at the beginning of the period

8
Financial Functions

• NPV
• IRR
• PMT
• IPMT
• Be consistent about the units for specifying
  rate and nper
• Monthly payments on a four-year loan at 12
  percent annual interest, use 12/12 for rate and
  412 for nper.
• Annual payments on the same loan, use 12 for
  rate and 4 for nper

9
Internal Rate of Return (IRR) and Net Present
Value (NPV)
10
PMT and IPMT

• PMT- This function calculates the payment for a
  loan based on constant payments and constant
  interest rate
• IPMT- This function calculates the interest
  payment based on periodic , constant payments and
  a constant interest rate

11
NPV

• NPV(rate,value1,value2,)
• Rate is the rate of discount over the length of
  one period
• Value1,value2, must be equally spaced in time
  and occur at the end of each period

12
IRR

• IRR(values,guess)
• Values are an array or a reference to cells that
  contains numbers for which you want to calculate
  the internal rate of return
• Guess is a number that you guess is close to the
  result of IRR

13
PMT

• PMT(rate,nper,pv,fv,type)
• Rate is the interest rte of the loan
• Nper is the total number of payments for the loan
• Pv is the present value
• Fv is the future value
• Type is the number 0 or 1 and indicates when
  payments are due

14
IPMT

• IPMT(rate,per,nper,pv,fv,type)
• Rate is the interest rate per period
• Per is the period for which you want to find the
  interest and must be in the range 1 to nper
• Nper is the total number of payment periods in an
  annuity
• Pv is the present value
• Fv is the future value
• Type is the number 0 or 1 and indicates when
  payments are due

15
Calculating NPV and IRR With Excel -- The Excel
Worksheet.
16
Buying a Car
BMW M5
17
What Will the Monthly Payments Be?

• Amount of loan, pv
• Interest rate, rate
• Number of payment periods, nper
• Term of the loan
• Future value, fv
• Future value of a loan0
• Type, when payments are due

18
Payment Function

• PMT(rate,nper,pv,fv,type)
• Returns the payment per time period
• Cash you pay out is represented by a negative
  number by Excel
• Cash you receive is represented by a positive
  number
• Keep the interest per period and the payment
  periods in the same units
• To calculate monthly payments on a four year loan
• Use 12/12 for rate and 412 for nper

19
Payment Function, cont.

• If you want the payment as a positive number
• Enter the loan amount (pv) as a negative number
• PMT(.08/12,512,-15000)
• Place a negative sign before the function
• -PMT(.08/12,512, 15000)

20
Payment Function, Examples

• Monthly payment on a 10,000 loan, annual rate of
  8, term is 10 months, payment at the end of each
  month
• PMT(.08/12, 10, 10000) -1,037.03
• For the same loan, if payments are due at the
  beginning of the period
• PMT(.08/12, 10, 10000, 0, 1) -1,030.16

21
Saving Regularly - PMT

• Use PMT to determine how much to save each month
  to meet a specific goal
• Want to retire with one million dollars in 40
  years
• Earn 10 interest on your savings
• How much must you save each month?

Keukenhof, The Netherlands
22
Retiring a Millionaire

• PMT(.10/12, 4012, 0, 1000000) -158.13
• Multiply the returned payment by nper to
  calculate how much is paid over the duration of
  the annuity
• 158.13480 75,900
• Note, the pv 0
• fv 1000000

23
Statistical Functions in
Excel
24
Statistical functions

• Excel has a wide variety of built-in
  statistics functions that give, the standard and
  mean deviation of a data sample, and the mean,
  median and mode of a set of values.

25

• 1. Average Function
• Returns the average (arithmetic mean) of
  the arguments.
• Syntax AVERAGE(number1,number2,...)
• Number1, number2, ...    are 1 to N numeric
  arguments for
• which you want the average.
• Example Data 10,7,9,27,2
• AVERAGE(A2A6) Average of the numbers
  above (11)
• AVERAGE(A2A6, 5) Average of the numbers
  above and 5 (10)

26

• 2 . Median Function
• Returns the median of the given numbers. The
  median is the number in the middle of a set of
  numbers that is, half the numbers have values
  that are greater than the median, and half have
  values that are less.
• Syntax MEDIAN(number1,number2,...)
• Number1, number2, ...    are 1 to N numeric
  arguments for which you want the median.
• Example Data 1,2,3,4,5,6
• MEDIAN(A2A6) Median of the first 5 numbers
  in the list above (3)
• MEDIAN(A2A7) Median of all the numbers
  above, or the average of 3 and 4 (3.5)

27

• 3. AVEDEV Function
• Returns the average of the absolute
  deviations of data points from their mean. AVEDEV
  is a measure of the variability in a data set.
• Syntax AVEDEV(number1,number2,...)
• Number1, number2, ...    are 1 to 30 arguments
  for which you want the average of the absolute
  deviations. You can also use a single array or a
  reference to an array instead of arguments
  separated by commas.
• The equation
• Example Data 4,5,6,7,5,4,3
• AVEDEV(A2A8) Average of the
  absolute deviations of the numbers above from
  their mean (1.020408)

28

• 4 . STEDEV Function
• Estimates standard deviation based on a
  sample. The standard deviation is a measure of
  how widely values are dispersed from the standard
  value.
• Syntax STDEV(number1,number2,...)
• Number1, number2, ...    are 1 to 30 number
  arguments corresponding to a sample of a
  population. You can also use a single array or a
  reference to an array instead of arguments
  separated by commas.
• The equation

29

• Example
• Suppose 10 tools stamped from the same machine
  during a production run are collected as a random
  sample and measured for breaking strength. The
  example may be easier to understand if you copy
  it to a blank worksheet.
• Data 1345,1301,1368,1322,1310,1370,
  
  1318,1350,1303,1299
• STDEV(A2A11)Standard deviation of breaking
  strength (27.46391572)

30

• 5. CORREL Function
• Returns the correlation coefficient of the
  array1 and array2 cell ranges. Use the
  correlation coefficient to determine the
  relationship between two properties. For example,
  you can examine the relationship between a
  location's average temperature and the use of air
  conditioners.
• Syntax CORREL(array1,array2)
• Array1    is a cell range of values.
• Array2    is a second cell range of values.
• The equation

31

• Example Suppose given two data sets
  regarding the population density death rate of
  a particular area. Find the correlation between
  the two.
• DENSITY 300,200,400,500,600
• DEATH RATE 9,7,12,14,16
• CORREL(A2A6,B2B6) 0.997176

32

• 6. RANK Function
• Returns the rank of a number in a list of
  numbers. The rank of a number is its size
  relative to other values in a list. (If you were
  to sort the list, the rank of the number would be
  its position.)
• Syntax RANK(number,ref,order)
• Number    is the number whose rank you want to
  find.
• Ref    is an array of, or a reference to, a list
  of numbers. Nonnumeric values in ref are
  ignored.
• Order    is a number specifying how to rank
  number.
• If order is 0 (zero) or omitted, Microsoft Excel
  ranks number as if ref were a list sorted in
  descending order.
• If order is any nonzero value, Microsoft Excel
  ranks number as if ref were a list sorted in
  ascending order

33
Database Functions in
Excel
34

• Introduction
• In this information age, the major challenge for
  every organization is to keep proper database in
  order to prosper in the future.
• Databases are store-houses of information.
• Provides latest information.
• Database is an organized collection of related
  information about a particular subject or
  purpose.
• Information in database is stored in rows and
  columns
• Ms-Excel also contains a good database which is a
  collection of information that is organized so
  that it can easily be accessed, managed, and
  updated

35

• What is a database?
• A database is a collection of logically related
  data designed to meet the information needs of
  one or more users
• A database defines a structure for storing
  information. Databases are typically organized
  into tables, which are collections of related
  items. You can think of a table as a grid of
  columns and rows. Egs Oracle, DB2, and SQL
  Server.
• A database is a collection of information that is
  organized so that it can easily be accessed,
  managed, and updated

36

• Computer D a t a Hierarchy pyramid

Traditional databases are organized by fields,
records, and files. A field is a single piece of
information a record is one complete set of
fields and a file is a collection of records.
37

• To access information from a database, you need a
  database management system (DBMS). This is a
  collection of programs that enables you to enter,
  organize, and select data in a database.
• The following figure shows the basic layout of a
  database table

38

• 1. DAVERAGE Function
• Averages the values in a column of a list
• or database that match conditions you specify.
• Syntax DAVERAGE (database,field,criteria)
• Database    is the range of cells that makes up
  the list or database.
• Field    indicates which column is used in the
  function. Field can be given as text with the
  column label enclosed between double quotation
  marks, such as ----- as a number that
  represents the position of the column within the
  list
• Criteria    is the range of cells that contains
  the conditions you specify

39

• 2. DSUM Function
• Adds the numbers in a column of a
• list or database that match conditions you
  specify.
• Syntax DSUM (database,field,criteria)
• Database    is the range of cells that makes up
  the list or database.
• Field    indicates which column is used in the
  function. Field can be given as text with the
  column label enclosed between double quotation
  marks, such as ------ or as a number that
  represents the position of the column within the
  list
• Criteria    is the range of cells that contains
  the conditions you specify

40

• 3. DMAX Function
• Returns the largest number in a column
• of a list or database that matches conditions
• you specify.
• Syntax DMAX(database,field,criteria)
• Database    is the range of cells that makes up
  the list or database.
• Field    indicates which column is used in the
  function. Field can be given as text with the
  column label enclosed between double quotation
  marks, such as ----- or as a number that
  represents the position of the column within the
  list
• Criteria    is the range of cells that contains
  the conditions you specify

41

• 4. DMIN Function
• Returns the largest number in a column
• of a list or database that matches conditions
• you specify.
• Syntax DMIN(database,field,criteria)
• Database    is the range of cells that makes up
  the list or database.
• Field    indicates which column is used in the
  function. Field can be given as text with the
  column label enclosed between double quotation
  marks, such as ----- or as a number
  that represents the position of the column within
  the list
• Criteria    is the range of cells that contains
  the conditions you specify