Excel for finance interviews: the modeling test questions that come up
8 questions on the functions every analyst test uses, with computed answers, the traps graders look for, and a graded exercise for each.
Finance interviews test Excel differently from a generic skills test. Beyond knowing the functions, you are judged on whether your numbers are right under time pressure and whether someone else could follow your workbook. Depending on the firm, that ranges from a few formula questions in an online assessment to a timed case where you build a small projection, returns analysis or model from a case pack.
The questions below cover the functions that appear most in those tests. If you need the general skills first (lookups, SUMIFS, pivot tables), start with the Excel interview test practice page.
On this page
8 practice questions with answers
Work each one in Excel before opening the answer, and check your sign conventions as you go: they cause more wrong answers than the functions themselves.
1. NPV of a project, without the classic off-by-one
A project costs 10,000 today (year 0) and returns the cash flows below in years 1 to 4. The discount rate is 8%. What is the NPV?
| A | B | |
|---|---|---|
| 1 | Year | Cash flow |
| 2 | 0 | -10,000 |
| 3 | 1 | 3,000 |
| 4 | 2 | 4,000 |
| 5 | 3 | 5,000 |
| 6 | 4 | 2,000 |
Show answer
Result: 1,646.35
Excel's NPV assumes the first value in the range arrives one period from now. The year-0 outflow is already in today's money, so it stays out of NPV and gets added on separately. Putting B2:B6 inside NPV discounts the initial investment by a year and overstates the answer. This is the most common NPV mistake in finance tests.
Practice this in a graded grid →NPV/IRR guide
2. IRR of the same project
Using the same cash flows, what is the project's internal rate of return?
Show answer
Result: 15.32%
IRR does include the year-0 flow, unlike NPV, because it solves for the rate where the NPV of all flows is zero. A quick sanity check: NPV at 8% was positive, so the IRR has to be above 8%.
Practice this in a graded grid →NPV/IRR guide
3. Returns on irregular dates: XIRR
An investment's cash flows land on the dates below. What is the annualized return?
| A | B | |
|---|---|---|
| 1 | Date | Cash flow |
| 2 | 15 Jan 2026 | -50,000 |
| 3 | 30 Jun 2026 | 12,000 |
| 4 | 01 Mar 2027 | 18,000 |
| 5 | 31 Dec 2027 | 30,000 |
Show answer
Result: 14.09%
IRR assumes equal periods. When cash flows come on real calendar dates, as in private equity, project finance or any deal model, use XIRR, which annualizes on a 365-day basis. You need at least one negative and one positive flow, and the first date should be the initial investment.
Practice this in a graded grid →XNPV/XIRR guide
4. Loan payment and total interest
A 20-year, $300,000 loan at 4.5% annual interest is repaid monthly. Find the monthly payment, then the total interest paid over the life of the loan.
Show answer
Result: payment 1,897.95; total interest 155,507.55
Convert both the rate and the term to months. Enter the principal as negative so the payment comes out positive (cash-flow sign convention). Total interest is simply all payments minus the principal; an interviewer may ask for it to see whether you understand what the payment contains.
Practice this in a graded grid →PMT guide
5. Future value of a monthly savings plan
An employee saves $500 at the end of every month for 10 years at 5% annual interest, compounded monthly. What is the balance at the end?
Show answer
Result: 77,641.14
Same sign logic as PMT: money paid in is negative, the balance you receive back is positive. If deposits are made at the start of each month, add a fifth argument of 1 (the type argument), which makes the result slightly larger.
Practice this in a graded grid →FV/PV guide
6. Compound annual growth rate (CAGR)
Revenue (in millions) grew from 120 in 2022 to 185 in 2026. What is the CAGR?
| A | B | |
|---|---|---|
| 1 | Year | Revenue |
| 2 | 2022 | 120.00 |
| 3 | 2023 | 138.00 |
| 4 | 2024 | 151.00 |
| 5 | 2025 | 170.00 |
| 6 | 2026 | 185.00 |
Show answer
Result: 11.43%
Four years of growth between five data points, so the exponent is 1/4, not 1/5. Counting years instead of intervals is a very common error. =RRI(4,B2,B6) gives the same answer in one function, and =RATE(4,0,-B2,B6) works too.
Practice this in a graded grid →RATE/NPER guide
7. Weighted portfolio return with SUMPRODUCT
Each asset class has a portfolio weight and an expected return. What is the portfolio's expected return?
| A | B | C | |
|---|---|---|---|
| 1 | Asset | Weight | Return |
| 2 | Equities | 55% | 8.2% |
| 3 | Bonds | 30% | 4.1% |
| 4 | Real estate | 10% | 6.3% |
| 5 | Cash | 5% | 2.5% |
Show answer
Result: 6.50%
SUMPRODUCT multiplies each pair and adds the results in one step, with no helper column. It's the standard way to do weighted averages in finance models. If the weights don't sum to 100%, divide by SUM(B2:B5).
Practice this in a graded grid →SUMPRODUCT guide
8. Nominal vs effective interest rate
A savings account advertises 6% nominal annual interest, compounded monthly. What is the effective annual rate?
Show answer
Result: 6.168%
The effective rate is what you actually earn over a year once compounding is included. Comparing two offers with different compounding frequencies only makes sense on an effective basis. NOMINAL converts the other way.
Practice this in a graded grid →SLN/EFFECT/NOMINAL guide
Modeling conventions graders look for
In a case or modeling test, the reviewer reads your workbook as well as checking the final number. These habits are what make it readable, and they are standard across banks and funds:
- Inputs separate from calculations. Assumptions (growth rate, discount rate, tax rate) live in their own clearly labeled cells, often in blue font, and formulas refer to them. A number typed inside a formula, like
=B5*1.05, is a hard-code and is the first thing a reviewer flags. - Fixed references done right. Lock assumption cells with
$or give them a name, so a formula can be filled across all years without breaking. - One formula per row. The same formula filled across every period. A row where year 3 is calculated differently from year 2 looks like an error even when it isn't.
- Consistent signs. Decide whether costs are negative or positive and stick to it everywhere, especially in NPV and IRR inputs.
- Checks. A balance check or a total that should tie to another total, shown in its own row. It tells the reviewer you verify your own work.
- No IFERROR blankets. Hiding errors in a model hides mistakes; reviewers distrust a sheet with no visible errors and no checks.
Working fast
Modeling tests are timed, and the mouse is slow. The candidates who finish are usually the ones who navigate, select ranges and fill formulas from the keyboard. Learn the core Excel keyboard shortcuts (moving to the edge of a data range, selecting to the end, filling right and down, toggling $ with F4) and practice them until they are automatic. LearnExcel's shortcut drills grade each keystroke on Windows or Mac.
FAQ
What Excel skills do investment banking and finance interviews test?
Typically lookups (XLOOKUP or INDEX/MATCH), conditional sums, NPV, IRR and XIRR, loan and growth calculations, and building a small projection with clean, consistent formulas. Speed with keyboard shortcuts matters in timed tests.
Do I need to know VBA or macros?
Rarely for analyst-level tests. Formulas, clean structure and speed matter far more. VBA comes up mainly in roles that explicitly mention automation.
Should I use XLOOKUP or INDEX/MATCH in a finance test?
Either is accepted if the firm's Excel version supports XLOOKUP. INDEX/MATCH works everywhere, so knowing it avoids surprises on older setups.
Why does my NPV not match the expected answer?
Usually because the year-0 cash flow was included inside the NPV function. Excel's NPV discounts the first value by one period, so add the initial investment outside the function.
Practice finance formulas in a real grid
Graded exercises for NPV, IRR, XIRR, PMT, FV and more, with instant feedback. Free to start, no signup required.
Start with NPV →