Excel Functions Flashcards
How do I get the present value of an annuity
=pv(discount rate, n periods, periodic payment)
How do I get the future value of an annuity
=fv(interest rate, n periods, periodic payment)
How do I find the periodic payments on a loan
=pmt(interest rate, n periods, pv of loan)
How do I get the periodic interest payments on a loan
=ipmt(interest rate, period, n periods, pv of loan)
How do I get the principle payments on a loan
=ppmt(interest rate, period, n periods, pv of loan)
How do I get the IRR of an investment
=irr(values, [guess]) or =xirr(values, dates, [guess])
How do I get the net present value of future cashflows
=npv(discount rate, values) or xnpv(discount rate, values, dates)
How do I calculate the yield on a bond
=yield(…)
How do I count the nonempty cells in a range
=counta(range)
How do I count cells that meet certain criteria
=countif(range, criteria) or = countifs(range, criterias)
How do I sum the values in cells that meet certain criteria in a range
=sumif(range, criteria) or =sumifs(range, criterias)
How do I manage errors
=iferror(value, value for error)
How do I use XLOOKUP
=xlookup(search value, search range, return range) it looks for the search value in the search range and returns the value in the corresponding cell in the return range
How do I use INDEX
=index(array, row number, column number) returns the value in the row and column specified for the specified range
How do I use MATCH
=Match(search value, array, match type) returns the relative position of a cell in a specified array that contains the search value
How do I use index and match together for lookups
use index then for the first argument give the array where the desired value will be and for the second and third argument use match to get the necessary row and column numbers in that array
How do I use EDATE
=EDATE(start date, number of months before or after)
How do I use EOMONTH
=EOMONTH(start date, number of months before or after)
How do I get the current date
Today()
How can I find the amount of time between two dates
=YEARFRAC(start date, end date) returns the decimal of years between two dates
How can I find the number of days between two dates
=DAYS(start date, end date)
How can I combine strings in different cells
=CONCAT(cells) or use &
What does the trim function do
=TRIM(cells) removes excess spaces
How can I replace text in cells
=REPLACE(cell, start char, number of chars, new text)
How can I substitute text in cells
=SUBSTITUTE(cell, old text, new text)
How can I get just some parts of a string in a cell
use the left, mid, or right functions with the number of characters you want
What does goal seek do and how do I use it
goal seek back calculates to fill in a cell based on some target set. enter in some cell B that is dependent on some cell A and a value you want for cell B then it will fill in the necessary value for cell A
How can I perform sensitivity analysis in excel
use data tables in what if analysis. setup with the base case value in the top left by linking that cell to the one in the model with the actual formula in it and then in the row under and column to the right put in possible values for the variables your output is based on. highlight the array and go to what if analysis, data table and select the appropriate cells and excel will fill in the potential cases
How do I use LAMBDA functions
used to make your own function in excel. initially you use lambda and pass in parameters then the formula you want to turn into a function. once it is working add it to the name manager and call it something easy to use and remember and now you can use this as a normal excel function
