Excel Functions

1
Microsoft Office 2003

• Excel Functions
• By
• Hani Almohair

2
Objectives

• Describe the PMT (payment) and FV (future value)
  functions
• Use the PMT and FV functions
• Use the IF function to help in the
  decision-making process provide decision making
• Define and use the absolute and mixed reference
• Use the Statistical functions , AVERAGE, MAX,
  MIN, COUNT and COUNTA functions
• Use the logical functions AND OR

3
Overview

• Learn to use spreadsheets as a tool in decision
  making
• Use financial functions
• Use statistical functions
• Use absolute, relative, and mixed cell references

4
Analysis of a Car Loan

• Can I afford it?
• How do I calculate for
• rebates
• Down payments
• interest rates
• the number of payments

5
Analysis of a Car Loan

• Set up a worksheet template with initial
  conditions
• Use a PMT function which requires interest
  rate/period, number of periods, and amount of
  loan
• Payment amounts and the number of payments are
  usually expressed in months, while interest rates
  are annual rates. Divide the interest rate by 12
  to come up with a monthly rate
• The amount of the loan (present value) should be
  expressed as a negative number.

6
PMT Function
PMT(B8/12B912-B7)
A mount of loan ( as a negative amount)
Number of periods( 4 years x 12 months/year
Interest rate per period ( annual rate divided by
12)
Remember A function is a predefined formula
7
How Much Money Will I Have at Retirement?

• The Future Value (FV) function to return the
  future value of a series of payments
• Use the FV function which requires the expected
  rate of return, the number of periods, and the
  investment each period.

8
FV Function (the rate of return)

• Use the FV function which requires the expected
  rate of return, the number of periods, and the
  investment each period.

Amount at retirement FV (rate of return, Term,
Periodic payment)
B14
B16
B15
B17
FV (A1, A2, -A3)
9
Inserting a Function

• Use the Insert Function command from the Insert
  menu
• Use the list box to select the name of the
  function
• functions categorized by function
• Let the Wizard help you enter the arguments

10
Isolate Your Assumptions

• Enter your assumptions (the arguments needed for
  the function) into cells and use those cells for
  your arguments
• For example, in the PMT function, enter the loan
  amount, number of payments, and interest rate
  into cells, then use those cells in the PMT
  function
• Change the values in those cells to test
  different scenarios
• Easier than editing the formula when you want to
  change on or more of your variables

11
Getting the most from Excel

• Relative versus absolute addressing in a
  worksheet
• Knowing the difference and when to use each when
  copying makes setting up your worksheet more
  efficient and more accurate
• Mixed references
• Either the row or the column is absolute the
  other is relative
• Dont forget to isolate your assumptions!!

12
Relative versus absolute addressing in a worksheet
PMT Function
PMT(B8/12B912-B7)
A mount of loan ( as a negative amount)
Number of periods( 4 years x 12 months/year
Interest rate per period ( annual rate divided by
12)
13
Mixed referencesEither the row or the column is
absolute the other is relative
FV function
FV(C5B6-D3)
Absolute reference to cell D3
Mixed reference to cell B6
Mixed reference to cell C5.
14
Using Functions in ExcelStatistical Functions

• Statistical Functions MAX,MIN, AVERAGE, COUNT
  and COUNTA
• Use functions instead of arithmetic expressions
• IF function enhances decision making
• allows for different results based on different
  conditions

15
Average Function

• (A1A2A3)/3
• AVERAGE(A1A3)
• AVERAGE(A1A3200)
• AVERAGE(A1A3C2)

MAX,MIN Functions
MAX(A1A3) MIN(A1A3)
16
COUNT and COUNTA Functions

• COUNT(A1A3)
• Returns the number of numeric entries.
• COUNTA(A1A3)
• Return the number of numeric and text entries.

17
The IF function

• Allows for different results, based on a
  condition
• for example, if you work over forty hours in a
  week, you will receive overtime pay
• Requires three arguments
• a condition, which Excel must be able to evaluate
  as true or false
• a value if true
• a value if false
• The value if true and value if false may contain
  additional (nested) IF functions for more complex
  decisions.

18
IF functions

• Enables decision making to be implemented within
  a worksheet.
• IF(condition,value-if-true, value-if-false)

Value returned for a true condition
Value returned for a true condition
Condition is either true or false
19
Operators that used with IF Function

• Operator Description
• Equal to
• ltgt not equal to
• lt less than
• gt Greater than
• lt less than or equal to
• gt Greater than or equal to

20
AND OR

• And return true if all its arguments are true,
  return false if any argument are false.
• OR returns true if any arguments are true,
  return false if all arguments are false

21
Summary

• Financial functions (PMT and FV)
• Statistical functions (MAX, MIN, AVERAGE, and
  COUNT)
• Decision making functions (IF )
• Isolate and clearly label initial assumptions