Earn your certification as a Financial Modeling & Valuation Analyst (FMVA)™. Register today!

Excel Functions

Excel functions list

Below are over 200 Excel functions to help you become a world-class financial analyst.

 

Date and Time

DATE Create a valid date from year, month, and day
year
month
day
DATEDIF Get days, months, or years between two dates
start_date
end_date
unit
DATEVALUE Convert a date in text format to a valid date
date_text
DAY Get the day as a number (1-31) from a date
date
DAYS Get days between dates
end_date
start_date
DAYS360 Get days between 2 dates in a 360-day year
start_date
end_date
method
EDATE Get the same date in future or past months
start_date
months
EOMONTH Get the last day of the month in future or past months
start_date
months
HOUR Get the hour as a number (0-23) from a time
serial_number
ISOWEEKNUM Get ISO week number for a given date
date
MINUTE Get the minute as a number (0-59) from a time
serial_number
MONTH Get the month as a number (1-12) from a date
date
NETWORKDAYS Get the number of working days between two dates
start_date
end_date
holidays
NETWORKDAYS.INTL Get work days between two dates
start_date
end_date
weekend
holidays
NOW Get the current date and time
-
SECOND Get the second as a number (0-59) from a time
serial_number
TIME Create a time with hours, minutes, and seconds
hour
minute
second
TIMEVALUE Get a valid time from a text string
time_text
TODAY Get the current date
-
WEEKDAY Get the day of the week as a number
serial_number
return_type
WEEKNUM Get the week number for a given date
serial_number
return_type
WORKDAY Get a date n working days in the future or past
start_date
days
holidays
WORKDAY.INTL Get date n working days in future or past
start_date
days
weekend
holidays
YEAR Get the year from a date
date
YEARFRAC Get the fraction of a year between two dates
start_date
end_date
basis

 

Financial

ACCRINT Get the accrued interest for a security that pays interest on a periodic basis
issue
first
interest
settlement
rate
par
frequency
basis
calc_method
ACCRINTM Get the accrued interest for a security that pays interest on maturity
issue
settlement
rate
par
basis
CUMIPMT Get the cumulative interest paid on a loan
rate
nper
pv
start_period
end_period
type
CUMPRINC Get the cumulative principal paid on a loan
rate
nper
pv
start_period
end_period
type
DB Calculate the depreciation of an asset
cost
salvage
life
period
month
DISC Calculate the rate of discount for a bond
settlement
maturity
pr
redemption
basis
DOLLARDE Converts a dollar value in fractional notation into a dollar value expressed in decimal notation
fractional_dollar
fraction
DOLLARFR Convert a dollar value in decimal notation into a dollar value in fractional notation
decimal_dollar
fraction
DURATION Get the duration of a security
settlement
maturity
coupon
yield
frequency
basis
EFFECT Get the annual interest rate and the number of compounding periods per year
nominal_rate
npery
FV Get the future value of an investment
rate
nper
pmt
pv
type
FVSCHEDULE Get the future value of an investment with a variable interest rate
principal
schedule
INTRATE Get the interest rate for a security
settlement
maturity
investment
redemption
basis
IPMT Get principal for given period
rate
per
nper
pv
fv
type
IRR Get the Internal Rate of Return for a given cash flow
values
guess
ISPMT Get the interest paid during a specific period of Investment
rate
per
nper
pv
MDURATION Get the duration of a security using the Modified Macauley method
settlement
maturity
coupon
yld
frequency
basis
MIRR Get he modified internal rate of return for given periodic cash flows
values
finance_rate
reinvest
rate
NOMINAL Get the nominal annual interest rate
effect_rate
npery
NPER Get number of periods for loan or investment
rate
pmt
pv
fv
type
NPV Get the Net Present Value (NPV) for periodic cash flows
rate
value1
value2
PDURATION Get the time or specific number of periods required for an investment to reach a particular value
rate
pv
fv
PMT Get the periodic payment for a loan
rate
nper
pv
fv
type
PPMT Get the payment on the principal for an investment or a loan
rate
per
pv
fv
type
PRICE Get the price of a bond per $100 face value
settlement
maturity
rate
yld
redemption
frequency
basis
PRICEDISC Get the price of a bond per $100 face value of a discounted security
settlement
maturity
discount
redemption
basis
PRICEMAT Get the price of a bond that pays interest at maturity per $100 face value
settlement
maturity
issue
rate
yld
basis
PV Get the present value of an investment
rate
nper
pmt
fv
type
RATE Get the interest rate per period of an annuity
nper
pmt
pv
fv
type
guess
RECEIVED Get the amount received at maturity for a fully invested security
settlement
maturity
investment
discount
basis
SLN Get the straight-line depreciation of an asset
cost
salvage
life
SYD Get the sum-of-years' digits depreciation of an asset
cost
salvage
life
per
TBILLPRICE Get the fair market value of a Treasury bill
settlement
maturity
discount
TBILLEQ Get the bond-equivalent yield of a Treasury bill
settlement
matirity
discount
TBILLYIELD Get the yield on a Treasury bill
settlement
maturity
pr
VDB Get the depreciation of an asset using the Double Declining Balance method
cost
salvage
life
start_period
end_period
factor
no_switch
XIRR Get the Internal Rate of Return (IRR) for a series of cash flows that may not be periodic
values
dates
guess
XNPV Get the Net Present Value (NPV) for a series of cash flows that may not be periodic
rate
values
dates
YIELD Get the yield on a security
settlement
maturity
rate
pr
redemption
frequency
basis
YIELDDISC Get the annual yield for a discounted security
settlement
maturity
pr
redemption
basis
YIELDMAT Get the annual yield on a security
settlement
maturity
issue
rate
pr
basis

 

Engineering

CONVERT Convert measurement units
number
from_unit
to_unit

 

Information

CELL Get information about a cell
info_type
reference
ERROR.TYPE Test for a specific error value
error_val
INFO Get information about current environment
type_text
ISBLANK Test if a cell is blank
value
ISERR Test for any error but #N/A
value
ISERROR Test for any error
value
ISEVEN Test if a value is even
value
ISFORMULA Test if a cell contains a formula
reference
ISLOGICAL Test if a value is logical
value
ISNA Test for the #N/A error
value
ISNONTEXT Test for a non-text value
value
ISNUMBER Test for numeric value
value
ISODD Test if a value is odd
value
ISREF Test for a reference
value
ISTEXT Test for a text value
value
N Convert a value to a number
value
NA Create an #N/A error
-
TYPE Get the type of value in a cell
value

 

Logical

AND Test multiple conditions with AND
logical1
logical2
...
FALSE Generate the logical value FALSE
-
IF Test for a specific condition
logical_test
value_if_true
value_if_false
IFERROR Trap and handle errors
value
value_if_error
IFNA Returns a specified alternate value if a formula results in an #N/A error
value
value_if_na
IFS Test multiple conditions, return first true
test1
value1
test2
value2
...
NOT Reverse arguments or results
logical
OR Test multiple conditions with OR
logical1
logical2
...
SWITCH Evaluates a given expression and returns a result corresponding to the first matching value
expression
value1
result1
default
value2
result2
...
TRUE Generate the logical value TRUE
-
XOR Perform exclusive OR
logical1
logical2
...

 

Lookup and Reference

ADDRESS Create a cell address from a given row and column
row_num
col_num
abs_num
a1
sheet
AREAS Get the number of areas in a reference
reference
CHOOSE Get a value from a list based on position
index_num
value1
value2
COLUMN Get the column number of a reference
-
reference
COLUMNS Get the number of columns in an array or reference
array
FORMULATEXT Get the formula in a cell
reference
GETPIVOTDATA Get data from a pivot table in a formula
data_field
pivot_table
field1
item1
...
HLOOKUP Look up a value in a table by matching on the first row
value
table
row_index
range_lookup
HYPERLINK Create a clickable link
link_locaation
friendly_name
INDEX Get a value in a list or table based on location
array
row_num
col_num
area_num
INDIRECT Create a reference from text
ref_text
a1
LOOKUP Look up a value in a one-column range
lookup_value
lookup_vector
result_vector
MATCH Get the position of an item in an array
lookup_value
lookup_array
match_type
OFFSET Create a reference offset from given starting point
reference
rows
cols
height
width
ROW Get the row number of a reference
-
reference
ROWS Get the number of rows in an array or reference
array
RTD Get real-time data from a COM automation server
progID
server
topic1
topic2
...
TRANSPOSE Flip the orientation of a range of cells
array
VLOOKUP Lookup a value in a table by matching on the first column
value
table
col_index
range_lookup

 

Math

ABS Find the absolute value of a number
number
ARABIC Convert a Roman numeral to an Arabic numerals
roman_text
BASE Converts a number into the supplied base
number
radix
min_length
CEILING Round a number up to the nearest specified multiple
number
multiple
CEILING.MATH Round a number to the nearest integer or multiple of significance
number
significance
mode
CEILING.PRECISE Round up a number to the nearest integer or multiple of significance
number
significance
COMBIN Calculate the total number of combinations
number
number_chosen
COMBINA Calculate the total number of combinations along with repetitions
number
number_chosen
DEGREES Convert radians to degrees
angle
EVEN Round a number up to the next even integer
number
ISO.CEILING Get a number rounded up to the nearest integer or multiple of significance
number
signficance
MOD Get the remainder from division
number
divisor
MROUND Round a number to the nearest specified multiple
number
multiple
ODD Round a number up to the next odd integer
number
PI Get the value of π
-
RAND Get a random number between 0 and 1
-
RANDBETWEEN Get a random integer between two values
bottom
top
ROUND Round a number to a given number of digits
number
num_digits
ROUNDDOWN Round a number down to a given number of digits
number
num_digits
ROUNDUP Round a number up to a given number of digits
number
num_digits
SQRT Find the positive square root of a number
number
SUBTOTAL Get a subtotal in a list or database
function_num
ref1
ref2
...
SUM Add numbers together
number1
number2
number3
...
SUMIF Sum numbers in a range that meet supplied criteria
range
criteria
sum_range
SUMIFS Sum cells that match multiple criteria
sum_range
range1
criteria1
range2
criteria2
...
SUMPRODUCT Multiply, then sum arrays
array1
array2
...
TRUNC Truncate a number to a given precision
number
num_digits

 

Statistical

AVERAGE Get the average of a group of numbers
number1
number2
...
AVERAGEA Get the average of a group of numbers and text
value1
value2
...
AVERAGEIF Get the average of numbers that meet criteria
range
criteria
average-criteria
AVERAGEIFS Average cells that match multiple criteria
avg_rng
range1
criteria1
range2
criteria2
...
BINOM.DIST Get the binomial distribution probability for the number of successes from the trials
number_s
trials
probability_s
cumulative
BINOM.DIST.RANGE Get the binomial distribution probability for a given number of successes
trials
probability_s
number_s
number_s2
CHISQ.DIST Get the chi-square distribution
x
deg_freedom
cumulative
CHISQ.DIST.RT Get the right-tailed probability of a chi-square distribution
x
deg_freedom
CHISQ.INV Get the inverse of the left-tailed probability of the chi-square distribution
probability
deg_freedom
CHISQ.INV.RT Get the inverse of the right-tailed probability of a chi-square distribution
probabilty
deg_freedom
CHISQ.TEST Get the chi-square distribution of two provided datasets
actual_range
expected_range
CONFIDENCE Get the confidence interval for a population mean
alpha
standard_dev
size
CONFIDENCE.NORM Get the confidence interval for a population mean
alpha
standard_dev
size
CONFIDENCE.T Get the confidence value for the confidence interval of a population mean
alpha
standard_dev
size
CORREL Calculate the correlation coefficient between two variables
array1
array2
COUNT Count numbers
value1
value2
...
COUNTA Count the number of non-blank cells
value1
value2
...
COUNTBLANK Count cells that are blank
range
COUNTIF Count cells that match criteria
range
criteria
COUNTIFS Count cells that match multiple criteria
range1
criteria1
range2
criteria2
...
COVAR Get the covariance of two sets of values
array1
array2
COVARIANCE.P Get the covariance of two given set of values
array1
array2
COVARIANCE.S Get the sample covariance for two sets of values
array1
array2
CRITBINOM Get the smallest value that is greater than or equal to the criterion value in a Cumulative Binomial Distribution
trials
probability_s
trials
DEVSQ Get the sum of the squared deviations from the sample mean
number1
number2
F.DIST Calculate the probability density function
x
deg_freedom1
deg_freedom2
cumulative
F.DIST.RT Calculate the (right-tailed) F Probability Distribution
x
deg_freedom1
deg_freedom2
F.INV Calculate the inverse of the Cumulative F Distribution for a supplied probability
x
deg_freedom1
deg_freedom2
F.INV.RT Calculate the inverse of the (right-tailed) F probability distribution
probability
deg_freedom1
deg_freedom2
FORECAST Get the future value using existing values
x
known_y’s
known_x’s
FORECAST.LINEAR Get the future value using linear progression
x
known_y’s
known_x’s
FREQUENCY Get the frequency of values in a data set
data_array
bins_array
F.TEST Get the result of an F-test for two given arrays or ranges
array1
array2
GAUSS Get the probability that a member of a standard normal population will be between the mean and a specified number of standard deviations
z
GROWTH Get the predicted exponential growth using existing data
known_y’s
known_x’s
new_x’s
const
LARGE Get the nth largest value
array
n
LOGNORM.DIST Get the cumulative log-normal distribution function at a given value of x
x
mean
standard_dev
cumulative
LOGNORM.INV Get the inverse of the lognormal cumulative lognormal distribution function at a given value of x
probability
mean
standard_dev
MAX Get the largest value
number1
number2
...
MAXA Get the largest value
value1
value2
...
MAXIFS Get maximum value with criteria
max_range
range1
criteria1
range2
criteria2
...
MEDIAN Get the median of a group of numbers
number1
number2
...
MIN Get the smallest value
number1
number2
...
MINA Get the smallest value
value1
value2
...
MINIFS Get minimum value with criteria
min_range
range1
criteria1
range2
criteria2
...
MODE Get the mode of a group of numbers
number1
number2
...
MODE.MULT Get a vertical array of statistical modes within a list of given numbers
number1
number2
MODE.SNGL Get the most frequently occurring number in a numeric data set
number1
number2
NEGBINOM.DIST Get the negative binomial distribution probability
number_f
number_s
probabilty_s
cumulative
NORMDIST Get the normal distribution for a stated mean and standard distribution
x
mean
standard_dev
cumulative
NORM.DIST Get the probability that variable x falls below or at a specified value
x
mean
standard_dev
cumulative
NORMINV Get the inverse of a normal cumulative distribution
probability
mean
standard_dev
NORM.INV Get the inverse of a normal cumulative distribution
probability
mean
standard_dev
NORM.S.DIST Get the standard normal distribution function for a given value
z
cumulative
NORM.S.INV Get the inverse of the normal cumulative distribution for a given probability value
probability
PEARSON Get the Pearson Product-Moment Correlation Coefficient for two sets of values
array1
array2
PERCENTILE Get the k-th percentile of the values in a range
array
k
PERCENTILERANK Get the rank of a value in a data set
array
x
significance
PERMUT Get the total number of permutations
number
number_chosen
PERMUTATIONA Get the total number of permutations, with repetitions
number
number_chosen
PHI Get the value of the density function for a standard normal distribution
x
POISSON.DIST Get the Poisson probability mass function
x
mean
cumulative
PROB Get he probability that is associated with a given range
x_range
prob_range
lower_limit
upper_limit
QUARTILE Get the quartile in a data set
array
quart
QUARTILE.EXC Get the quartile of a given data set based on percentile values from 0.1 exclusive
array
quart
QUARTILE.INC Get the quartile of a given data set based on percentile values from 0.1 inclusive
array
quart
RANK Rank a number against a range of numbers
number
array
order
SLOPE Get the slope of a linear regression line
known_y's
Known_x’s
SMALL Get the nth smallest value
array
k
STANDARDIZE Get a normalized value (z-score) based on the mean and standard deviation
x
mean
standard_dev
STDEV Get the standard deviation in a sample
number1
number2
...
STDEVA Get the standard deviation in a sample
number1
number2
...
STDEVPA Get the standard deviation of an entire population given as arguments
value1
value2
STDEV.P Get the standard deviation in a population
number1
number2
...
STDEV.S Get the standard deviation in a sample
number1
number2
...
T.DIST Get the student's t-distribution
x
deg_freedom
tails
T.DIST.RT Get the right-tailed student's t-distribution
x
deg_freedom
T.INV Get the left-tailed student's t-distribution
probability
deg_freedom
T.INV.2T Get the two-tailed Student's T-Distribution
probability
deg_freedom
T.TEST Get the probability associated with a student’s t-test
array1
array2
tails
type
TREND Get the linear trend line to the arrays of known_y’s and known_x’s
known_y’s
known_x’s
new_x’s
const
VARA Get the sample variance of a set of values
number1
number2
...
VAR.P Get the variance of a set of values (population)
number1
number2
...
VAR.S Get the variance of a sample
number1
number2
...
WEIBULL.DIST Get the Weibull distribution for a supplied set of parameters
x
alpha
beta
cumulative
Z.TEST Get the one-tailed P-value of a Z-test
array
x
sigma

 

Text

CHAR Get a character from a number
number
CLEAN Strip non-printable characters from text
text
CODE Get the code for a character
text
CONCAT Join text values without delimiter
text1
text2
...
CONCATENATE Join text together
text1
text2
text3
...
DOLLARDE Converts a dollar value in fractional notation into a dollar value in decimal notation
fractional_dollar
fraction
EXACT Compare two text strings
text1
text2
FIND Get the location of text in a string
find_text
within_text
start_num
FIXED Rounds and converts a supplied number to text
number
decimal
no_commas
LEFT Extract text from the left of a string
text
num_chars
LEN Get the length of text
text
LOWER Convert text to lower case
text
MID Extract text from inside a string
text
start_num
num_chars
NUMBERVALUE Converts a text string into a number in a locale-independent manner
text
decimal_separator
group_separator
PROPER Capitalize the first letter in each word
text
REPLACE Replace text based on location
old_text
start_num
num_chars
new_text
REPT Repeat text as specified
text
number_times
RIGHT Extract text from the right of a string
text
num_chars
SEARCH Get the location of text in a string
find_text
within_text
start_num
SUBSTITUTE Replace text based on content
text
old_text
new_text
instance
T Tests if the value provided is a text or not
value
TEXT Convert a number to text in a number format
value
format_text
TEXTJOIN Join text values with a delimiter
delimiter
ignore_empty
text1
text2
...
TRIM Remove extra spaces from text
text
UNICHAR Get the Unicode character for a given number
number
UNICODE Get the code point for a given character
text
UPPER Convert text to upper case
text
VALUE Convert text to a number
text