Eli Katsiri

1
Lecture 4
Eli Katsiri School of Computer Science
2
Lecture 3 Overview

• Mathematical statistical functions
• Round
• Trunc
• Mean
• Median
• Mode
• Variance
• Standard deviation

3
Lecture 4 overview

• PV
• FV
• PMT
• If statements

4
IF statement

• Logical test what are we asking? lets say our
  logical test is Is the value in Column A larger
  than the value in Column B?
• Value if true what do we want displayed if the
  answer to our question is true? In this
  example, lets say we want the answer to be yes
• Value if false what do we want displayed if the
  answer is false? In this example, lets say we
  want no.

5
How do we build an IF statement?

• In a formula, the arguments are separated by
  commas, so for this example, lets put our
  formula in cell C2 and this is what it would look
  like
• IF(A2gtB2,"yes","no")?
• This says, IF the value in A2 is greater than the
  value in B2, put yes in C2 and if its not
  greater than B2, put no in C2.

6
IF-THEN-ELSE
IF A2gtB2 THEN yes ELSE no
7
Text vs number vs space

• When you want text displayed in a cell, you must
  put the text in quotes in the formula. If instead
  of yes and no, you wanted to have numbers like 1
  and 2, the second two arguments would not require
  quotes and would look like this
• IF(A2gtB2,1,2)?
• You can put pretty much anything you like in the
  second two arguments. As shown above you can put
  text or a number. You can also tell Excel to
  leave the cell empty by using two quotes as your
  argument (). Lets say we want yes if its
  true, but if its false, we want the cell left
  empty. Then our formula would look like this
• IF(A2gtB2,"yes","")?

8
Absolute cell reference

• Or, we can put cell references in the second two
  arguments. For example, if we want the value if
  false to be something that is in another cell, we
  can just put that cell name in the third
  argument. Lets say, cell D2 contains a bonus
  amount, we could use this formula
• IF(A2gtB2,contact this salesperson,D2)?
• NOTE the dollar signs in the reference to D2.
  This is because we want this formula to ALWAYS
  refer to cell D2 no matter where we copy the
  formula to. The dollar signs make the cell
  reference absolute.

9
IF Formula

• You can also nest other formulas within an IF
  statement. Lets say if the number in cell A2 is
  not larger than the number in B2, we want to give
  this person 50 of whats in B2. Our formula
  would look like this
• IF(A2gtB2,"contact this salesperson",(B250))?
• IF A2gtB2
• THEN contact this salesperson
• ELSE (B250)?

10
Examples
11
Multiple conditions - AND
Data
Problem

• Lets suppose you want a formula in column E that
  will assign a job level based on two different
  criteria Salespeople who have been employed for
  more than 5 years AND have annual sales of more
  than 10,000 should be assigned a job level code
  of 2, and all others should have a job level code
  of 1. To include two criteria, when both criteria
  must be met, you must nest an AND function within
  your IF statement. Put this formula in cell E2
• IF((AND(D2gt5,C2gt10000)),2,1)?

12
AND

• IF((AND(D2gt5,C2gt10000)),2,1)?
• IF D2gt5 AND C2gt10000
• THEN 2
• ELSE 1

13
Multiple conditions -OR
Data
Problem

• Similarly, you can use a nested OR function if
  you want a job level code of 2 to be applied if
  an employee meets EITHER criterion, instead of
  both criteria. A nested OR function works the
  same way as an AND function, except it will
  return the 2, if ANY of the criteria are met,
  instead of requiring that ALL criteria are met.
• IF((OR(D2gt5,C2gt10000)),2,1)?
• In this case, everyone is assigned a job level 2
  except John, because he is the only one who did
  not meet either criterion, since he has not
  worked for the company for more than 5 years OR
  had annual sales greater than 10,000.

14
OR

• IF((OR(D2gt5,C2gt10000)),2,1)?
• IF (D2gt5) OR (C2gt10000)?
• THEN 2
• ELSE 1

15
Nested IF
Data
Problem

• Lets say we want to assign a job level 3 if the
  employee meets BOTH criteria above, a job level 2
  if the employee meets EITHER criteria above, and
  a job level 1 if the employee doesnt meet any of
  the criteria above.
• IF((AND(D2gt5,C2gt10000)),3,(IF((OR(D2gt5,C2gt10000))
  ,2,1)))?

16
Nested IF

• IF((AND(D2gt5,C2gt10000)),3,(IF((OR(D2gt5,C2gt10000))
  ,2,1)))?
• IF (D2gt5) AND (C2gt10000)?
• THEN 3
• ELSE IF (D2gt5) OR (C2gt10000)?
• THEN 2
• ELSE 1

17
Nested IF (2)?
Data
Problem

• You can also nest multiple IFs if you want
  different results based on different values in
  the same cell. For this example, lets say all of
  the employees in the East district will be
  assigned a job level 4, the West ones will be job
  level 3, the North ones will be job level 2, and
  the ones in the South will be job level 1. Our
  formula would look like this
• IF((B2"East"),4,IF((B2"West"),3,IF((B2"North")
  ,2,IF((B2"South"),1,""))))?

18
Nested IF (2)?

• IF((B2"East"),4,IF((B2"West"),3,IF((B2"North")
  ,2,IF((B2"South"),1,""))))?
• IF (B2"East")
• THEN 4
• ELSE IF (B2"West")
• THEN 3
• ELSE IF (B2North)?
• THEN 2
• ELSE IF (B2South)?
• THEN 1
• ELSE

19
Functions for Personal Financial Decisions

• When we borrow money to buy a car or a house, we
  always wonder whether were getting a good deal.
• When we save for retirement, were curious how
  large a nest egg well have when we retire.
• In our daily work and personal life, financial
  questions similar to these questions often arise.
  Knowing how to use the PV, FV, PMT, functions in
  Excel makes answering these types of questions
  easy.

20
Present Value (PV)?

• Should I pay 11,000 today for a copier or 3,000
  a year for 5 years?
• Your company provides a service in December 2005
  and agrees to be paid in three instalments of
  100 each. The payments will occur at the end of
  each of the years 2006-08. The time value of
  money tells us that a portion of the three 100
  payments represents interest your company will
  earn because it has agreed to wait for its money.
  It may be that only 250 of the total 300 in
  payments can be considered service revenue earned
  in 2005 the remaining 50 is interest revenue
  earned over the three years 2006-08. How do you
  determine what amount is actual service revenue
  and what amount is implicit interest revenue? By
  using a present value calculation, you can remove
  the implicit interest so that the amount of
  service revenue can be determined.

21
PV

• The PV function returns the value in todays
  pounds of a series of future payments, assuming
  periodic, constant payments and a constant
  interest rate.
• assuming that each periods interest rate is the
  same.

22
Visualisation of the problem
3000
3000
3000
3000
3000
1 year
1 year
1 year
1 year
1 year
23
PV of 3000 in 1 year

• What is the present value (pv) of 3000 in a
  year?
• Lets assume pvX.
• If I had kept X and invested it with a interest
  rate of 12 then I would have 3000 in the end of
  the year.
• X0.12X3000
• 1.12X3000
• X3000/1.12 2678.571

24
PV of 3000 in 2 years

• What is the present value (pv) of 3000 in a
  year?
• Lets assume pvX.
• If I had kept X and invested it with a interest
  rate of 12 then I would have 3000 in the end of
  the second year.

25
PV of 3000 in 5 years

• What is the present value (pv) of 3000 in a
  year?
• Lets assume pvX.
• If I had kept X and invested it with a interest
  rate of 12 then I would have 3000 in the end of
  the second year.

26
PV total
PV year 1 PV year 2 PV year 3 PV year 4 PV year 5
27
Future Value (FV)?

• If I invest 2,000 a year for 40 years toward my
  retirement and earn 8 percent a year on my
  investments, how much will I have when I retire?
• In this situation, we want to know the value of
  an annuity in future dollars (40years from now)
  and not in todays dollars. This is a job for the
  FV or futurevalue function. The future value
  function calculates the future value of an
  investment assuming periodic, constant payments
  with a constant interest rate.

28
FV of 2000 in 1 year

• If I were to keep 2000 and invest them at a 8
  interest rate, how much will it be worth in a
  year?
• 1.082000 2160

29
FV of 2000 in 2 years

• If I were to keep 2000 and invest them at a 8
  interest rate, how much will it be worth in two
  years?
• (1.08(1.082000))

30
FV of 2000 in 40 years

• If I were to keep 2000 and invest them at a 8
  interest rate, how much will it be worth in two
  years?
• (1.08(1.082000))

31
Total FV
FV year 1 FV year 2 PV year 40 total
32
Syntax

• PV(rate,per,pmt,pv,type)?
• FV(rate,per,pmt,fv,type)?

33
PMT

• I am borrowing 10,000 on a 10-month loan with an
  annual interest rateof 8 percent. What will my
  monthly payments be?

34
Debt

• P initial capital borrowed 10000
• r0.08/120.006667
• z1r1.006667
• apayment
• Debt(1)FV(P)-a Pz-a
• Debt(2)(Pz-a)z a
• Debt(10)

35
(No Transcript)