1 / 29

# Excel Functions

Excel Functions. A Function. Performs a predefined operation A function Accepts one or more arguments as input Performs the indicated calculation Returns another value as output. Financial Date&amp;Time Math&amp;Trig Statistical Lookup&amp;Reference. Database Text Logical Engineering All.

## Excel Functions

E N D

### Presentation Transcript

1. Excel Functions

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 Prof. Leighton

3. Financial Date&Time Math&Trig Statistical Lookup&Reference Database Text Logical Engineering All Nine Categories Prof. Leighton

4. Function Syntax • Begin with an = sign • Function’s NAME • No space • Open parenthesis • Argument1, argument2 • Close parenthesis Prof. Leighton

5. Statistical Functions • =Average(number1,number2,..) • Excel sums the values in the range and then divides by the number of non-blank cells in the range • =Stdev(number1,number2,..) • =Max(number1,number2,..) • =Median(number1,number2,..) • =Pearson(number1,number2,..) • Pearson correlation coefficient Prof. Leighton

6. Counting Functions • =Count • Counts the number of cells that contains numbers • =Counta • Counts the number of cells that are not empty • Both numeric and text entries are included • =Countblank(range) • Counts the number of empty cells • =Countif(range,criteria) • Counts the number of cells within a range that meets the condition Prof. Leighton

7. 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 • The PV Function • Value now of an entire stream of future benefits or costs Prof. Leighton

8. 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 Prof. Leighton

9. 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 Prof. Leighton

10. Annuity Functions - PV • The following functions apply to annuities: • PMT CUMIPMT • PPMT IPMT • CUMPRINC FVSCHEDULE • PV FV • RATE • 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 4*12 for nper. • Annual payments on the same loan, use 12% for rate and 4 for nper Prof. Leighton

11. Prof. Leighton

12. The IF Function • Decision rule for investing is if PVB>PVC, then invest, otherwise (else) do not invest • IF function specifies the if-then-else logic required to calculate information based on one or more conditions Prof. Leighton

13. The IF Function Syntax • IF(logical test, value if true, value if false) • Logical test is any value or expression that Excel evaluates as true or false • Value if true - Excel uses this expression if the logical test is true • Value if false - Excel uses this expression if the logical test is false Prof. Leighton

14. Should Our Student Invest? • Logical test is PVB>PBC • E53>F53 • Value if true - Invest • Value if false - Do not Invest • IF(E53>F53),Invest,Do not invest) Prof. Leighton

15. Prof. Leighton

16. Buying a Car BMW M5 Prof. Leighton

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 loan=0 • Type, when payments are due Prof. Leighton

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 4*12 for nper Prof. Leighton

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,5*12,-15000) • Place a negative sign before the function • =-PMT(.08/12,5*12, 15000) Prof. Leighton

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 Prof. Leighton

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 Prof. Leighton

22. Retiring a Millionaire • PMT(.10/12, 40*12, 0, 1000000) =-\$158.13 • Multiply the returned payment by nper to calculate how much is paid over the duration of the annuity • \$158.13*480 = \$75,900 • Note, the pv = 0 • fv = 1000000 Prof. Leighton

23. Prof. Leighton

24. Lookup Table for Grades: Associate a Numeric Grade with a Letter Grade C starts at 70 and goes to 75.99 Prof. Leighton

25. VLOOKUP Function • Look-up value is a value or a cell reference of the item for which you need information • It is the search item • Appears in the first column of the information table • Table-array is the range reference of the lookup table Prof. Leighton

26. VLOOKUP Function • Column index numberis the column number in the information table from which the information should be found and returned to your cell • Range-lookupis set equal to true (the default) and finds the closest match; to find an exact match, set = false Prof. Leighton

27. Lookup Table for Grades Student receives an 83 for the semester Table Array is A2:C11 Look-up value is the 83 Column index number 1 2 3 Prof. Leighton

28. Rules for VLOOKUP Function • The values in the look_up table must be in ascending order. • If Range-lookupis set equal to true (the default) the function returns the value from the table or the largest value less than or equal to the search item • Student receives an 85 (search item) • 83 is the largest value less <= 85 • Returns a B Prof. Leighton

29. Rules for VLOOKUP Function • If Range-lookupis set equal to false, the search item must be found or #N/A is returned • The range of the information table should be absolute reference Prof. Leighton

More Related