Essential Excel Functions for Building Financial Models

Part of Financial Modeling in Excel For Dummies Cheat Sheet

Today well over 400 functions are available in Excel, and Microsoft keeps adding more with each new version of the software. Many of these functions aren’t relevant for use in finance, and most Excel users only use a very small percentage of the available functions. If you’re using Excel for the purpose of financial modeling, you need a firm grasp on the most commonly used functions, at the very least.

Although there are many, many more that you’ll find useful when building models, here’s a list of the most basic functions that you can’t be without.

Function What It Does
SUM Adds up, or sums together, a range of cells.
MIN Calculates the minimum value of a range of cells.
MAX Calculates the maximum value of a range of cells.
AVERAGE Calculates the average value of a range of cells.
ROUND Rounds a single number to the nearest specified value, usually to a whole number.
ROUNDUP Rounds up a single number to the nearest specified value, usually to a whole number.
ROUNDDOWN Rounds down a single number to the nearest specified value, usually to a whole number.
IF Returns a specified value only if a single condition has been met.
IFS Returns a specified value if complex conditions have been met.
COUNTIF Counts the number of values in a range that meet a certain single criterion.
COUNTIFS Counts the number of values in a range that meet multiple criteria.
SUMIF Sums the values in a range that meet a certain single criterion.
SUMIFS Sums the values in a range that meet multiple criteria.
VLOOKUP Looks up a range and returns the first corresponding value in a vertical table that matches exactly the specified input.
HLOOKUP Looks up a range and returns the first corresponding value in a horizontal table that matches exactly the specified input. An error is returned if it cannot find the exact match.
INDEX Works like the coordinates of a map and returns a single value based on the column and row numbers you input into the function fields.
MATCH Returns the position of a value in a column or a row. Modelers often combine MATCH with the INDEX function to create a lookup function, which is far more robust and flexible and uses less memory than the VLOOKUP or HLOOKUP.
PMT Calculates the total annual payment of a loan.
IPMT Calculates the interest component of a loan.
PPMT Calculates the principal component of a loan.
NPV Takes into account the time value of money by giving the net present value of future cash flows in today’s dollars, based on the investment amount and discount rate.

There’s a lot more to being a good financial modeler than simply knowing lots of Excel functions. A skilled modeler can select which function is best to use in which situation. Usually, you can find several different ways to achieve the same result, but the best option is always the function or solution that is the simplest, clearest, and easiest for others to understand.