Financial Modeling in 2026: The Excel Formulas, AI Tools, and Skills Every Investment Banker Should Know

Financial modeling has changed considerably over the past few years. Investment banks now have access to AI-powered research platforms, automated financial data extraction, and increasingly sophisticated Excel tools. Tasks that once required hours of manual research can be completed much faster.

Yet, if you're interviewing for an investment banking analyst position in 2026, you're still expected to understand the fundamentals of accounting, valuation, and financial modeling. You should know how to build a discounted cash flow (DCF) model, calculate enterprise value, explain the relationship between the three financial statements, and understand how leverage affects an investment's returns.

Once you're on the job, the expectations evolve. You'll have access to your firm's existing models, financial databases, research tools, and potentially AI platforms such as Rogo. The challenge is learning how to use these resources efficiently while maintaining the accuracy and judgment expected of an investment banker.

This guide covers both sides of financial modeling in 2026, beginning with the skills you need to pass technical interviews and moving into the Excel formulas, shortcuts, valuation calculations, and AI practices you'll use throughout your career.

1. Financial modeling in 2026: what has actually changed?

Imagine two analysts working on the same acquisition opportunity. The first spends several hours searching through annual reports, copying financial information into Excel, and reviewing comparable companies. The second uses approved financial research tools to locate the relevant filings, retrieve comparable-company data, and organize the information before building the model.

Both analysts still need to understand the company's financial statements, select appropriate valuation assumptions, and review their calculations. The second analyst simply has more time available for analyzing the transaction. This is where financial modeling is heading in 2026.

AI tools are becoming increasingly useful for financial research, document analysis, and repetitive workflows. However, financial models still require assumptions, accounting knowledge, and careful validation.

What you need for investment banking interviews

Before joining a bank, focus on understanding:

• The income statement, balance sheet, and cash flow statement
• How the three financial statements connect
• Enterprise value versus equity value
• EBITDA, free cash flow, and working capital
• Discounted cash flow valuation
• Comparable company and precedent transaction analysis
• Accretion and dilution in M&A transactions
• Leveraged buyout modeling and investment returns
• Excel formulas, references, and shortcuts

During a technical interview, you might be asked to walk through a DCF, explain how depreciation affects the three financial statements, or calculate an implied enterprise value using an EBITDA multiple. You should be able to answer without relying on AI.

What changes once you're working at a bank?

Once you're employed, your workflow will depend on your firm's technology policies, data subscriptions, and internal processes. You might use:

• Excel: building and updating financial models
• Capital IQ, FactSet, or Bloomberg: financial information, market data, and comparable-company research
• Rogo: AI-assisted financial research, document analysis, and institutional workflows, where approved
• ChatGPT: learning Excel formulas, troubleshooting generic spreadsheet errors, and understanding financial concepts, subject to firm policy
• Internal model templates: maintaining consistency with your firm's financial modeling standards

Rogo, for example, offers AI-assisted financial workflows that integrate institutional data and internal knowledge, and its platform emphasizes source traceability and enterprise controls. The practical lesson is that you should understand what every tool is doing, know where the underlying data came from, and independently check material calculations.

2. How to use ChatGPT for financial modeling without creating unnecessary risk

ChatGPT can be useful when you're learning Excel or trying to remember a formula. Suppose you need to calculate revenue growth between two years. You remember the calculation but can't immediately recall the best Excel syntax. You can ask:

You can then enter the formula into Excel, check the result, and apply percentage formatting. This is a practical use of AI because you're requesting a generic formula.

What should you ask ChatGPT?

• “Explain how XLOOKUP works in Excel.”
• “Write an Excel formula to calculate CAGR over five years.”
• “How do I calculate an IRR using dated cash flows?”
• “Explain the difference between INDEX MATCH and VLOOKUP.”
• “Why does my generic DCF model produce a circular reference?”
• “What formula calculates an EBITDA exit multiple?”
• “Explain how a debt sweep works in an LBO model.”

For ordinary public ChatGPT use, a conservative practice is to limit requests to formulas, Excel mechanics, and generic financial concepts. Avoid entering confidential client information, nonpublic financial statements, deal terms, proprietary assumptions, or internal spreadsheets into an unapproved AI service. Even a formula question can expose sensitive information if it includes real client data.

Your firm's policies determine which AI systems are permitted and what information can be entered into them. If your bank provides an approved enterprise AI platform, follow its specific permissions, data-handling requirements, and review procedures.

3. Essential Excel formulas for investment banking

Financial models can contain thousands of cells, but many calculations rely on a relatively small group of Excel functions. Learning these functions makes it easier to build three-statement models, DCF valuations, comparable-company analyses, and LBO models. The examples below use simplified cell references for educational purposes. For syntax and more examples, see our Excel functions guide.

SUM: adding financial data

SUM adds the values in a specified range. Investment bankers use it to calculate total operating expenses, capital expenditures, revenue across business segments, and other financial totals. If cells B5 through B12 contain eight expense categories, SUM calculates the combined expense.

AVERAGE: calculating average financial performance

AVERAGE calculates the arithmetic mean of a range. It can be used to calculate historical margins, revenue growth rates, or average valuation multiples. For comparable-company valuation, analysts often use MEDIAN as well, because it can reduce the influence of unusually high or low observations.

MEDIAN: analyzing valuation multiples

Suppose you're comparing eight publicly traded companies and have calculated their EV/EBITDA multiples. MEDIAN returns the middle value of the dataset, which is useful when one company trades at a significantly higher multiple than the rest. However, selecting appropriate comparable companies remains essential: a median calculated from an unsuitable peer group can still produce an unreliable valuation.

IF: creating conditional calculations

IF returns one result when a condition is true and another when it is false. In financial modeling, it can help determine whether a company has positive earnings, whether debt exceeds a threshold, or whether a financial covenant has been breached:

This formula compares an actual financial metric against a minimum covenant requirement.

IFERROR: managing formula errors

IFERROR replaces an Excel error with a specified result. This can be useful when calculating valuation multiples for companies with zero or unavailable earnings: if EBITDA is zero, EV/EBITDA cannot be meaningfully calculated, and displaying “N/M” communicates that the multiple is not meaningful. Use IFERROR carefully. Applying it broadly can conceal broken links, missing inputs, or incorrect calculations.

XLOOKUP: finding financial information

XLOOKUP searches for a specified value and returns the corresponding information from another range. In a comparable-company analysis, you can use it to retrieve each company's revenue, EBITDA, enterprise value, or industry classification from a separate dataset. For modern Excel users, XLOOKUP is generally more flexible than VLOOKUP.

INDEX and MATCH: flexible financial data lookups

INDEX returns a value from a specified range; MATCH identifies the position of the lookup value. Together, they retrieve information from financial datasets without relying on fixed column positions, which is especially useful in legacy models or Excel environments where XLOOKUP is unavailable.

SUMIFS: summing data based on multiple conditions

SUMIFS adds values that meet multiple criteria, for example the total transaction value of technology acquisitions completed during 2026. In this example, the year column is assumed to contain the text “2026”. If the column contains actual Excel dates, use date-based criteria instead.

COUNTIFS: counting transactions

COUNTIFS counts the number of records meeting specified criteria, such as completed healthcare transactions in an M&A dataset.

EOMONTH: building financial forecast dates

EOMONTH returns the last day of the month a specified number of months before or after a given date. It's particularly useful when building monthly forecasts, debt schedules, and working capital models. If B5 contains December 31, 2026, the formula returns January 31, 2027.

4. Financial modeling formulas you must understand

Knowing Excel functions is only part of the process. Investment banking analysts must also understand the financial calculations those functions support.

Revenue growth

If revenue increases from $100 million to $120 million, revenue growth is 20%.

Compound annual growth rate (CAGR)

This example assumes four annual intervals between the beginning and ending values. CAGR measures the annualized growth rate over a period.

Gross profit and gross margin

EBITDA and EBITDA margin

EBITDA is commonly used to compare operating performance and calculate enterprise valuation multiples. It is not the same as cash flow.

Net working capital

For an operating financial model, a common definition is:

This typically excludes cash, interest-bearing debt, and other financing items. The included accounts depend on the company's business model and the purpose of the analysis.

Change in net working capital

An increase in operating net working capital generally represents a use of cash.

Unlevered free cash flow

This formula is central to a discounted cash flow valuation. It calculates cash flow available to all capital providers before financing cash flows. See UFCF in our glossary.

5. DCF valuation: the core formulas

A discounted cash flow model estimates the present value of a company's projected unlevered free cash flows and terminal value. The process usually begins with a forecast of revenue, operating expenses, taxes, capital expenditures, and working capital. The resulting cash flows are discounted using the weighted average cost of capital.

Weighted average cost of capital (WACC)

Where E is the market value of equity, D the market value of debt, V = E + D, Re the cost of equity, Rd the pre-tax cost of debt, and T the marginal tax rate.

Here, B5 and B7 represent the equity and debt weights.

Cost of equity using CAPM

A country risk premium or other appropriate adjustments can be incorporated when justified.

Present value of forecast cash flows

Where B5 is projected free cash flow, B10 is WACC, and B4 is the discount period. The example assumes end-of-period discounting.

Terminal value

The perpetual growth rate must be lower than WACC for this formula to produce a conventional positive terminal value.

Enterprise value, equity value, and share price

This assumes B5:B9 already contain discounted forecast cash flows and B10 contains the discounted terminal value.

The exact bridge depends on the company's capital structure, including preferred securities, minority interests, and other relevant adjustments.

Here, B5 represents equity value and B6 represents diluted shares outstanding.

6. Comparable company and M&A valuation formulas

Comparable-company analysis examines how the market values businesses with similar operating and financial characteristics. Precedent transaction analysis examines prices paid in relevant acquisitions. Both approaches frequently use enterprise value multiples.

Implied enterprise value

For example, a business generating $10 million of EBITDA at an 8.0x multiple has an implied enterprise value of $80 million. The selected multiple should reflect comparable companies, market conditions, growth, profitability, size, and risk.

Transaction premium

Investment bankers use transaction premiums to evaluate acquisition offers involving publicly traded companies.

7. LBO modeling: formulas every private equity analyst should know

A leveraged buyout model examines how an investor can acquire a company using a combination of debt and equity. It forecasts operating performance, debt repayment, and equity proceeds at exit, and it's particularly useful for understanding the relationship between leverage, cash generation, entry valuation, and investment returns.

Sponsor equity contribution

A complete sources and uses schedule also accounts for transaction fees, refinancing, minimum cash requirements, and other transaction-specific items.

Annual interest expense

Using average debt balances can introduce circular references when debt repayment depends on cash flow after interest. Models address this through an appropriate debt schedule design and controlled calculation methodology.

Adjustments may be necessary for transaction fees, preferred securities, minority interests, or other claims.

Internal rate of return (IRR)

For equally spaced annual cash flows:

The first cash flow is usually the negative initial investment, followed by distributions and exit proceeds. For irregularly dated cash flows, XIRR uses the actual dates:

Annualized return from MOIC

For example, a 2.5x MOIC over five years produces an annualized return of approximately 20.1%. This shortcut is unsuitable when there are interim cash distributions or additional investments; in those situations, use IRR or XIRR. More in IRR vs. MOIC.

8. Building sensitivity analysis in Excel

A valuation is heavily influenced by assumptions. Changing WACC, terminal growth, EBITDA margins, or exit multiples can materially change the result, and sensitivity analysis helps bankers understand these relationships. For a DCF, a common sensitivity table varies WACC and terminal growth. For an LBO, a common table compares entry or exit multiples against operating performance or leverage assumptions, depending on the investment question.

Excel's two-variable Data Table feature calculates model outputs across combinations of assumptions. To build one:

1. Create a functioning model with clearly identified input cells.
2. Set up a grid of alternative assumptions.
3. Link the corner of the grid to the model's output.
4. Select the grid and open Data → What-If Analysis → Data Table.
5. Assign the appropriate row and column input cells.
6. Review the resulting valuation or return calculations.

9. Excel shortcuts investment banking analysts should practice

Speed matters when working with large financial models, and bankers often navigate and edit spreadsheets primarily through the keyboard. These shortcuts are commonly used in Windows Excel:

Learning shortcuts is particularly valuable when building forecast schedules, linking worksheets, or reviewing formulas across multiple periods.

10. Financial modeling best practices used in investment banking

A model needs to be understandable to another analyst, associate, or managing director who reviews it. Many modeling errors originate from inconsistent assumptions, broken links, and hardcoded values, and a well-structured model makes these problems easier to identify.

Use consistent cell colors

• Blue font for hardcoded numerical inputs
• Black font for formulas referencing the same worksheet
• Green font for links to other worksheets

These conventions vary by firm, so follow your team's template.

Separate assumptions from calculations

Revenue growth, tax rates, WACC, and valuation multiples should be identifiable inputs. If assumptions are embedded inside complex formulas, reviewing and updating the model becomes more difficult.

Avoid unnecessary hardcoding

If revenue is expected to grow by 10%, don't type next year's revenue manually. Link the forecast to the prior year's revenue and a growth assumption, so the model updates automatically when assumptions change:

Build financial statement checks

The result should equal zero, subject to an explicitly defined rounding tolerance. You should also check cash flow reconciliation, debt balances, and transaction sources and uses.

Validate the source data

When using market data from Capital IQ, FactSet, Bloomberg, Rogo, or another approved provider, confirm the financial period, currency, units, and definitions. Fiscal-year EBITDA and trailing-twelve-month EBITDA are different measures, and comparing one company's FY EBITDA with another's TTM EBITDA without normalization can distort a comparable-company analysis.

Keep your model reviewable

Avoid unnecessary complexity. Use consistent time periods, clearly labeled assumptions, separate operating and financing schedules, and checks that identify broken calculations. Document important assumptions and maintain version control.

11. Common financial modeling mistakes in 2026

AI-assisted tools can make analysts faster, but they can also accelerate the spread of incorrect information. Consider an analyst retrieving financial data for a comparable-company analysis. An automated tool returns revenue and EBITDA figures for several companies, and the analyst imports them into Excel and calculates valuation multiples. One company's figures are in thousands, while the others are in millions. The formulas are mathematically correct, but the comparison is wrong.

This is why analysts should review the source data before relying on a calculation. Other common mistakes include mixing enterprise and equity values, using inconsistent financial periods, double-counting working capital changes, applying the wrong tax rate, and using incorrect debt balances.

12. How to practice financial modeling for investment banking interviews in 2026

Begin with accounting and valuation fundamentals. Practice explaining how a $10 increase in depreciation affects the income statement, balance sheet, and cash flow statement. Then move into enterprise value, equity value, valuation multiples, and DCF calculations.

Once you're comfortable with these concepts, build a three-statement model in Excel: start with historical financial information, forecast the income statement, develop supporting balance sheet schedules, and connect the cash flow statement. After the three statements balance, use the model to build a DCF. Then practice a simplified LBO using entry EBITDA, purchase multiples, leverage, debt repayment, and exit assumptions. Our free templates give you a worked example of each.

Throughout this process, practice using Excel without relying on the mouse for every action, and be able to explain your model's assumptions and results aloud. If an interviewer asks why a company's valuation increased when WACC decreased, you should understand how discount rates affect the present value of future cash flows.

When preparing for a timed modeling test, work without AI unless the test instructions explicitly permit it.

13. The future of financial modeling: what analysts should focus on next

Financial modeling in 2026 combines established financial principles with a growing set of research and automation tools. Investment banks are adopting platforms that help analysts retrieve information, analyze documents, and prepare financial materials more efficiently; Rogo is one example of this shift. At the same time, Excel remains central to valuation, transaction, and investment analysis.

For aspiring investment bankers, the most useful preparation is to develop three capabilities together:

1. Fundamentals: learn accounting and valuation well enough to explain them independently.
2. Excel proficiency: build, review, and troubleshoot financial models efficiently.
3. Responsible AI use: use approved AI and research tools with attention to source verification, confidentiality, and calculation accuracy.

These skills remain valuable across investment banking, private equity, corporate development, and financial analysis.

Final thoughts

Financial modeling proficiency develops through repeated practice. You begin by understanding financial statements, progress into forecasting and valuation, and eventually learn to analyze more complex transactions. AI tools can help you learn formulas and reduce time spent on repetitive work, but your understanding of the business, its financial performance, and the assumptions behind the model remains essential.

If you're preparing for investment banking interviews in 2026, start with the fundamentals and build complete models from scratch. By the time you're working on live transactions, you'll be better equipped to use the tools available to you and evaluate the work they produce.