13 Nov
Microsoft Excel is by far the most widely used financial modeling tool, leveraged by millions of financial analysts across the world to build models and perform analysis. Knowing exactly which Excel function to use in each situation helps you work faster and build more robust and powerful models.
Here are 10 of the most useful Excel functions for financial modeling:
The INDEX MATCH functions look up and return the value of a cell in a selected array, using 1 or 2 reference values. INDEX MATCH is like VLOOKUP, but INDEX MATCH is more robust and flexible.
Example: You have a list of countries and their associated tax rates in an Excel worksheet. When building your model, you need to dynamically lookup the tax rate of a specific country and populate it in your model. Use INDEX MATCH to define the INDEX array (column of tax rates), the lookup value (country you want to find a tax rate for), and the MATCH lookup array (column containing the list of countries).
Tip: Convert your lookup range to a Table or remove row numbers from your formula (e.g. S:T instead of S2:T100) in order to automatically lookup data that is added outside of the current lookup range.
The SUMPRODUCT function multiplies two ranges or arrays together. It is called SUMPRODUCT because it sums up the product of each corresponding value between the two ranges or arrays.
Example: You have a list of products and their corresponding units and prices. You want to calculate the total revenue across all products. You could create another column that multiplies the units and price for each product and then separately sum up the values of those products, or you can use SUMPRODUCT to do it all in one function.
Tip: You can add criteria within SUMPRODUCT so only certain values within the selected ranges are included in the calculation. Using the example above, you can add criteria to only calculate revenue for products that belong to a certain product family.
PMT calculates the standard payment for a loan with fixed principal and interest payments, such as a standard mortgage. PPMT and IPMT calculate the specific principal and interest components of the total payment. Since the mix of principal and interest change over time, you must also specific the payment number when using PPMT and IPMT (e.g., 4th payment on the loan).
Example: You are calculating the monthly payment on a loan. Use PMT to calculate the payment based on the loan’s interest rate, number of payments, and amount (pv or present value).
Tip: Make sure to match your PMT inputs to the loan’s terms. For example, if a loan is monthly and the interest rate is annual, divide the interest rate by 12 to convert it to a monthly rate. Similarly, if payments are made at the beginning of each month instead of the end, set type = 1. PMT returns a negative value for a positive pv argument so add a negative if you want your payment to return as a positive value.
Also see PV
The NPV function determines the net present value of a series of cash flows using a discount rate. NPV differs from PV in that PV assumes the same future cash flow each period.
Example: You are attempting to determine the value of an investment or entity. After modeling the future cash flows, use NPV to determine the net present value of the investment or entity.
Tip: If the interval between your cash flows is not consistent, use XNPV to specify the dates of each cash flow.
Also see MIRR
The IRR function calculates the internal rate of return of a series of cash flows. IRR is essentially the compounding return on an investment. There is an eternal debate in finance over NPV vs. IRR, with NPV generally preferred. However, IRR provides an easy way of comparing an investment’s return to a benchmark or hurdle rate on a relative basis and therefore continues to be used in financial analysis.
MIRR is another version of IRR that accounts for borrowing and/or reinvestment rates that are different than the rate of the return of the cash flows being modeled. For example, if you can borrow at 5% and reinvest at 10% (e.g., your hurdle rate), MIRR will use those to get a more accurate return estimate.
Example: You have forecasted the cash flows for an investment. Use IRR to calculate the relative return on that investment.
Tip: As with XNPV, use XIRR if your cash flows do not fall on a consistent interval.
The SUMIF function and its many derivatives perform basic math functions on a range of cells based on selected criteria.
Example: You have a table of revenue by state and each state’s corresponding region and want to sum the revenue only from the states in a certain region. Use SUMIF to choose the range of cells containing region, the name of the region you want to sum, and the values to sum up.
Tip: Use SUMIFS, COUNITFS, AVERAGEIFS, MAXIFS, and MINIFS to include more than one criteria. Note that unlike SUMIF and its derivatives, the range of values is the first argument and not the last.
The IF function is one of the most widely used and straightforward functions in Excel. Though simple, the IF function provides the necessary if-then logic to return values based on any selected criteria. Nest IF functions within one another to add multiple layers of if-then logic.
Example: Your business incurs an extra cost when forecasted unit sales of a certain product exceed a set threshold. Use IF to add the extra cost to your forecast only in the months when you project unit sales of that product to exceed the threshold.
Tip: Nested IF functions can get unwieldy and hard to read. Separate each IF into its own line by using ALT + Enter before each IF.
Also see ISERROR
The IFERROR function lets you define what value returns if a function results in an error. It is useful for presentation purposes when you want to hide or replace errors.
Example: You are calculating gross profit margins across periods, products, and regions and some of your period, product, and region combinations do not contain revenue. Rather than return a #DIV/0! error, you use IFERROR to return “n/a”.
Tip: Use “” as your value_if_error argument to show a blank within the cell with the error function.
The EOMONTH (end of month) function returns the last day of a specified month. You can add or subtract months from within the function to make it easier to create a series of dates such as consecutive month ends.
Example: You want to create dynamic column headers for a monthly forecast. Starting with one date, use EOMONTH to return the last day of the next month (set the months argument = 1). For example, if the start of the model is Jan 31, 2021, EOMONTH in the next column returns Feb 29, 2021.
Tip: Use 3 in the months argument to create consecutive quarters.
The LEFT and RIGHT functions allow you separate out a certain number of characters from the left or right side of a string. LEFT, RIGHT, and their cousin MID are helpful when you need to clean up raw data to use in your analysis or model.
Example: You want to analyze data by state; however, the raw data includes a location field that includes state and other information in a string of text. Use LEFT to isolate the first 2 characters of the string to pull out the state abbreviation.
Tip: Use LEN to calculate the total number of characters in a string, or its length. This can be useful if the number of characters you want to isolate using LEFT, RIGHT, or MID depends on the length of the string.
RANDBETWEEN is personally one of my favorite Excel functions because it is useful for quickly creating mock data.
Example: You have created a financial model but don’t yet have any actual values to use in the model. Use RANDBETWEEN to generate random yet realistic values for the model. For example, set monthly revenue to a random value between 50 million and 60 million. Set cost of goods sold to a random value of 40% to 45% of revenue.
Tip: You must use integers in RANDBETWEEN. To use smaller numbers, such as percentages simply multiple by a larger number (e.g., 100) and then divide after the RANDBETWEEN function.
More about TackleBox
As former financial analysts responsible for building and presenting Excel data models, we created TackleBox to make it easier to present and track changes on your Excel files and analysis.
TackleBox provides automated linking from Excel to PowerPoint, live Excel and Power BI dashboards, Version Deep Diver to analyze Excel version history and track changes, and more. See how much time your team can save with TackleBox! Contact us to schedule a personalized demo and check out our pricing page to learn about our plans. Get started with a free trial today!
Interested in partnering with TackleBox? Check out our partner information page.
