Excel Functions

Question 1

How do I get the present value of an annuity

Answer 1

=pv(discount rate, n periods, periodic payment)

Question 2

How do I get the future value of an annuity

Answer 2

=fv(interest rate, n periods, periodic payment)

Question 3

How do I find the periodic payments on a loan

Answer 3

=pmt(interest rate, n periods, pv of loan)

Question 4

How do I get the periodic interest payments on a loan

Answer 4

=ipmt(interest rate, period, n periods, pv of loan)

Question 5

How do I get the principle payments on a loan

Answer 5

=ppmt(interest rate, period, n periods, pv of loan)

Question 6

How do I get the IRR of an investment

Answer 6

=irr(values, [guess]) or =xirr(values, dates, [guess])

Question 7

How do I get the net present value of future cashflows

Answer 7

=npv(discount rate, values) or xnpv(discount rate, values, dates)

Question 8

How do I calculate the yield on a bond

Answer 8

=yield(…)

Question 9

How do I count the nonempty cells in a range

Answer 9

=counta(range)

Question 10

How do I count cells that meet certain criteria

Answer 10

=countif(range, criteria) or = countifs(range, criterias)

Question 11

How do I sum the values in cells that meet certain criteria in a range

Answer 11

=sumif(range, criteria) or =sumifs(range, criterias)

Question 12

How do I manage errors

Answer 12

=iferror(value, value for error)

Question 13

How do I use XLOOKUP

Answer 13

=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

Question 14

How do I use INDEX

Answer 14

=index(array, row number, column number) returns the value in the row and column specified for the specified range

Question 15

How do I use MATCH

Answer 15

=Match(search value, array, match type) returns the relative position of a cell in a specified array that contains the search value

Question 16

How do I use index and match together for lookups

Answer 16

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

Question 17

How do I use EDATE

Answer 17

=EDATE(start date, number of months before or after)

Question 18

How do I use EOMONTH

Answer 18

=EOMONTH(start date, number of months before or after)

Question 19

How do I get the current date

Answer 19

Today()

Question 20

How can I find the amount of time between two dates

Answer 20

=YEARFRAC(start date, end date) returns the decimal of years between two dates

Question 21

How can I find the number of days between two dates

Answer 21

=DAYS(start date, end date)

Question 22

How can I combine strings in different cells

Answer 22

=CONCAT(cells) or use &

Question 23

What does the trim function do

Answer 23

=TRIM(cells) removes excess spaces

Question 24

How can I replace text in cells

Answer 24

=REPLACE(cell, start char, number of chars, new text)

Question 25

How can I substitute text in cells

Answer 25

=SUBSTITUTE(cell, old text, new text)

Question 26

How can I get just some parts of a string in a cell

Answer 26

use the left, mid, or right functions with the number of characters you want

Question 27

What does goal seek do and how do I use it

Answer 27

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

Question 28

How can I perform sensitivity analysis in excel

Answer 28

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

Question 29

How do I use LAMBDA functions

Answer 29

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