Excel Functions Flashcards
(30 cards)
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)
