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&Time Math&Trig Statistical Lookup&Reference. Database Text Logical Engineering All.

lemuel
Download Presentation

Excel Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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