Download Presentation
## Excel Functions

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**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**Financial**Date&Time Math&Trig Statistical Lookup&Reference Database Text Logical Engineering All Nine Categories Prof. Leighton**Function Syntax**• Begin with an = sign • Function’s NAME • No space • Open parenthesis • Argument1, argument2 • Close parenthesis Prof. Leighton**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**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**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**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**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**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**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**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**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**Buying a Car**BMW M5 Prof. Leighton**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**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**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**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**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**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**Lookup Table for Grades: Associate a Numeric Grade with a**Letter Grade C starts at 70 and goes to 75.99 Prof. Leighton**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**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**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**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**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