Financial & Statistical Functions

Excel Mastery Track · Green belt · ~7–9 hours · Microsoft 365 on Windows assumed

Two families of functions turn Excel from a calculator into an analyst's instrument, and both share the same seductive, dangerous property. They collapse an entire worksheet of reasoning into a single cell. One =PMT(...) compresses a full loan-amortisation schedule into one number. One =IRR(...) finds, by iteration, the discount rate a normal person could only reach by trial and error across a hundred rows. And =LINEST(...) runs a least-squares regression, a calculation that fills a statistics textbook chapter, then spills its answer in the time it takes to press Enter. This is real power. It is also where the most expensive errors in finance hide, because a single cell shows you its answer while silently keeping its assumptions to itself.

Every function here carries a buried assumption, and if you don't know it's there, it will hand you a plausible wrong number. Excel's NPV assumes the first cash flow you feed it happens one period from now. So an analyst who drops the initial outlay inside NPV discounts the whole project by an extra period and never notices. IRR assumes every interim cash flow is reinvested at the IRR itself, which for a 40% deal is a fantasy that MIRR exists to fix. XIRR needs actual dates, and sometimes a guess, to converge. On the statistical side, an R² of 0.99 tells you a line fits your eight points beautifully. It tells you nothing at all about whether footfall causes sales, whether eight points is enough to trust, or whether both series are just drifting upward with time. This is the most numeric material in the track, so the rule is strict: know the assumption inside every one-cell function, and verify every figure a second way before you trust it.

One version note, stated once: everything here is Microsoft 365. Back in the lookups lesson the modern upgrade was XLOOKUP. Here the modern members are FORECAST.LINEAR, which replaced the legacy FORECAST, plus XNPV and XIRR. The classics have existed since the 1990s and run everywhere. They are PV, FV, PMT, RATE, NPER, NPV, IRR, SLOPE, INTERCEPT, LINEST, CORREL and RSQ.

Learning objectives

By the end you can:

1. Write the five time-value functions (PV, FV, PMT, RATE, NPER) from memory, explain how they are five rearrangements of one equation, and get Excel's cash-flow sign convention right every time (money in positive, money out negative).
2. Build a loan or annuity from first principles and read back the total interest, using PMT and an amortisation view.
3. State precisely why Excel's NPV treats its first argument as occurring at the end of period 1, and therefore why the initial outlay must sit outside the NPV call, the module's signature trap.
4. Use XNPV and XIRR for cash flows on irregular real dates, and explain why they are structurally safer than NPV/IRR for anything dated.
5. Compute and distinguish IRR, XIRR, and MIRR, and articulate the reinvestment-rate assumption that separates them.
6. Run a single-variable regression three ways: SLOPE/INTERCEPT, FORECAST.LINEAR/TREND, and LINEST, and read every number LINEST returns, including R², standard errors, and the F-statistic.
7. Interpret CORREL and RSQ correctly, and recover the identity R² = CORREL² for a simple regression.
8. Diagnose the four ways a regression on financial data lies (reading R² as causation, trusting a tiny sample, ignoring non-stationarity, and extrapolating past the data) and state the guard against each.

Prerequisites & connections

Builds on. EX0.02 (Formulas I) for the reference and function-entry mechanics, and EX1.01 (Formulas II) for the array mindset that makes LINEST's spilled output and TREND's multi-cell result feel natural. EX1.02 (Lookups Mastery) supplies the tier-table habit you'll reuse when a discount rate or a tax rate has to be fetched instead of typed. The Table habit from EX0.01 matters more here than anywhere. A cash-flow stream, a dated distribution schedule and a regression's x/y columns should all be real Excel Tables (Ctrl+T). That way IRR, XIRR and LINEST grow automatically as you add rows and never point at a stale range.

Feeds forward. This is the numerical spine of the analyst track. The DCF valuation of M3.06 is NPV/XNPV applied to a forecast free-cash-flow stream. The IRR-vs-hurdle-rate logic of capital budgeting in M3.07 is IRR/MIRR with a decision rule bolted on. The sensitivity tables of EX2.02 wrap a two-variable Data Table around these same functions. On the statistical side, the beta of a stock (M4.02) is the SLOPE of its returns against the market. A cost-behaviour split into fixed and variable, from managerial accounting in M2.04, is the INTERCEPT and SLOPE of cost against volume. And every "revenue driver" model rests on the FORECAST.LINEAR/TREND machinery you meet here. The final subsection drills a kind of scepticism: R² isn't causation, and small n isn't evidence. It's the most transferable habit in the entire curriculum, and it comes back the moment you touch real market data.

Core teaching content

Common mistakes & how experts think differently

1. Putting the initial outlay inside NPV, the signature trap here. Excel's NPV discounts its first argument by one period. So =NPV(rate, -outlay, cf1, cf2, …) over-discounts the whole project by (1 + rate) and mis-times every flow. The result looks reasonable. It's just wrong by a consistent factor, and that's the most dangerous kind of wrong. The expert's reflex is automatic. The t = 0 outlay stays outside the NPV (=-outlay + NPV(rate, cf1…)), or the whole calculation uses XNPV with dates, whose first flow is correctly undiscounted. Whenever you inherit a DCF, check first whether the period-0 flow got swept into the NPV call.

1. Getting the cash-flow sign convention wrong. PMT, PV, FV, RATE and NPER model flows on a signed timeline. In is positive, out is negative. Feeding pv and fv the same sign, or forgetting that PMT returns a negative for a loan, produces #NUM! or a reversed answer that nothing warns you about. The expert sets signs on purpose: "I received the loan, so pv is positive. I pay it back, so PMT is negative." And they never fight the minus sign Excel returns, because it's telling the truth about direction.

1. Mismatching rate and period units. A 9% annual rate on a monthly loan has to become 0.09/12 per month, with nper in months. Using the annual rate with monthly periods (or vice versa) is a common and large error. The expert converts rate and term to the same period in the same breath, every time, and states the period out loud.

1. Using IRR/NPV on irregularly dated flows. IRR and NPV assume equal spacing. Feed IRR a PE stream on real dates and it mis-times every distribution and reports a wrong return. The expert reaches for XIRR/XNPV the instant dates are involved. They also keep the dates in a proper date-formatted column so the functions read them as serial numbers and never as text.

1. Forgetting IRR's reinvestment assumption. IRR assumes interim cash is reinvested at the IRR itself, which flatters high-return projects. Whenever the IRR is high, the expert reports MIRR alongside it with a realistic reinvestment rate. To them a 40% IRR reads as "40% if I can redeploy at 40%," and that's usually false.

1. XIRR returning #NUM! and giving up. For awkward streams the solver needs a starting guess, and it needs at least one sign change to have a root at all. Before assuming the data is broken, the expert supplies a guess, such as 0.2, and checks that the flows actually change sign.

1. Reading R² (or CORREL) as causation. This is the deepest statistical error. A tight fit shows co-movement, and co-movement isn't a lever. The expert never says "x drives y" from a regression alone. They want a mechanism and they rule out confounders. They also stay alert to two series that merely trend together over time.

1. Trusting a regression on a handful of points, or extrapolating past the data. Small n manufactures a high R², and a linear fit is only a local approximation. The expert weighs the standard errors and the sample size, forecasts only inside the observed x-range, and flags anything beyond it as an assumption.

All eight mistakes come down to one habit. Every one-cell function hides an assumption: the period-1 timing of NPV, the reinvestment rate of IRR, the "co-movement only" meaning of R². The expert's first move is to name it before trusting the number the cell so confidently shows.

Worked examples

Worked example 1: India private-equity XIRR vs a naive IRR, and a home-loan PMT (India)

A Mumbai family office invests in a private company through a series of dated cash flows. It commits capital, adds a follow-on, receives two interim distributions and finally exits. You'll compute the true annualised return with XIRR and compare it with the misleading "naive" IRR that pretends the flows are annual. Separately, you'll price the monthly instalment on a ₹ home loan with PMT. Every figure below has been recomputed and verified.

Step 1, lay the dated stream out as a Table. Dates go in a real date column and amounts in ₹. Negative means cash out and positive means cash in:

     A             B               C
1    Date          Cash flow (₹)   Years from start
2    2019-04-01    -20000000       0.0000
3    2020-07-15    -5000000        1.2904
4    2021-11-20    +4000000        2.6411
5    2023-03-10    +6000000        3.9425
6    2024-09-30    +28000000       5.5041

The invested capital totals ₹2,50,00,000, which is the ₹2 crore initial investment plus the ₹50 lakh follow-on. The distributions total ₹3,80,00,000, a multiple on invested capital (MOIC) of 1.52× over about 5.5 years. MOIC tells you how many times your money came back. It says nothing about how fast, and that's the gap XIRR fills.

Step 2, the true, date-aware return with XIRR:

=XIRR(B2:B6, A2:A6)   →   9.3898%

Check it by confirming that the discounted stream nets to zero at that rate. =XNPV(0.093898, B2:B6, A2:A6) returns ₹0.00 to the rupee. That's the definition of the IRR: the rate at which the present value of everything in equals everything out.

Step 3, the naive IRR that ignores the dates. Strip the dates and pretend the five flows land at neat annual intervals (t = 0, 1, 2, 3, 4):

=IRR({-20000000, -5000000, 4000000, 6000000, 28000000})   →   13.0532%

Step 4, read the gap. The naive IRR of 13.05% overstates the true XIRR of 9.39% by 3.66 percentage points, and the timing explains it. The big ₹2.8 crore exit actually arrived at year 5.50. The naive calculation pretends it arrived at year 4.0. That's a year and a half early. Money that comes back sooner earns a higher implied return, so squashing the real 5.5-year timeline into a tidy 4-year one flatters the number. A rounding difference would be harmless. This is a structural lie, and a fund could use it, knowingly or not, to make a mediocre 9% deal look like a good 13% one. This rule has no exceptions: dated cash flows go through XIRR, never IRR.

Step 5, the home-loan PMT. The same family takes a ₹50,00,000 home loan at 9% nominal annual over 20 years, repaid monthly:

=PMT(0.09/12, 20*12, 5000000)   →   -44,986.30

The instalment is ₹44,986.30 per month. Excel returns it negative, because it's cash leaving the borrower. Over 240 months that's ₹1,07,96,711.47 paid in total, and ₹57,96,711.47 of it is interest, more than the amount borrowed. The two halves of this example are the two faces of time value. XIRR reads the rate out of a dated stream you already have. PMT reads the payment out of a rate and a principal you choose. It's the same equation with a different unknown.

Three real-world wrinkles lie outside the mechanics. XIRR uses an ACT/365 day count, so a leap year shifts the last few basis points. PE returns are usually reported both as a net-of-fees XIRR and as a MOIC, because neither is complete on its own. And the home-loan rate here is fixed for illustration. Most Indian home loans float with the repo rate, which would make PMT a moving target you'd re-run at each reset.

Worked example 2: US same-store sales vs footfall: a regression and its diagnostics (US)

A US retail chain suspects that weekly same-store sales are driven by store footfall. Over eight comparable weeks it records footfall in thousands of visitors and same-store sales in $ thousands. Fit the regression, read every diagnostic LINEST returns, and forecast sales at a footfall the model has seen. Then say plainly what the fit does and doesn't prove. All figures have been recomputed and verified.

Step 1, the data, laid out as a Table, with x as footfall in thousands and y as sales in $ thousands:

     A         B                    Week   Footfall(x)   Sales(y)
1    Week      Footfall  Sales      1       12            44
2    W1        12        44         2       15            54
3    W2        15        54         3       18            60
4    W3        18        60         4       20            68
5    W4        20        68         5       23            71
6    W5        23        71         6       27            84
7    W6        27        84         7       30            93
8    W7        30        93         8       34            100
9    W8        34        100

Step 2, slope, intercept, and a forecast, three equivalent ways:

=SLOPE(y, x)                    →   2.5524
=INTERCEPT(y, x)                →   14.6398
=FORECAST.LINEAR(25, y, x)      →   78.45      (sales at footfall = 25)
=INDEX(LINEST(y, x), 1, 1)      →   2.5524     (slope, same number)

The fitted line is sales = 14.64 + 2.5524 × footfall. Interpret the slope in domain units: each additional 1,000 visitors is associated with about $2,552 more in weekly same-store sales. The intercept, 14.64 or $14,640, is the line's value at zero footfall. It's mathematically necessary and economically meaningless, since a store with literally zero visitors is outside anything the data observed. Report it as a fitting constant and don't call it "base sales."

Step 3, the full LINEST diagnostic block. =LINEST(y, x, TRUE, TRUE) spills a 5×2 array in M365:

        2.5524       14.6398        slope,      intercept
        0.0896        2.1034        se(slope),  se(intercept)
        0.9927        1.7965        R²,         se(estimate)
      811.2022        6             F,          degrees of freedom
     2618.14         19.36          regression SS, residual SS

Read it as a report card. R² = 0.9927, so footfall explains 99.3% of the sales variation in this sample. Cross-check it with the correlation. =CORREL(x, y) = 0.9963, and 0.9963² = 0.9927, which confirms the identity R² = CORREL² for a single-predictor fit. The slope's standard error (0.0896) is tiny next to the slope itself (2.5524), so the slope is tightly estimated. At two standard errors it's roughly 2.55 ± 0.18, nowhere near zero. The standard error of the estimate (1.7965, in $ thousands) is the typical prediction miss, about $1,800. And the F-statistic of 811 on 6 degrees of freedom overwhelmingly rejects "no relationship."

Step 4, forecast inside the range. =FORECAST.LINEAR(30, y, x) = 91.21 ($91,210) at footfall 30. That's legitimate, because 30 lies inside the observed 12 to 34 range. =FORECAST.LINEAR(120, y, x) = 320.9 would be extrapolation four times past the data. The linear form is a local approximation, and a real sales curve almost certainly bends, because a store saturates. Report the in-range forecast as a result and the out-of-range one as a flagged assumption.

Step 5, read it sceptically. The fit is beautiful, and a beautiful fit is the moment to get suspicious. Three cautions travel with this result. First, causation is unproven. Footfall and sales may both be driven by a third thing: a promotion week, a holiday, good weather. So the slope means "associated with" and doesn't mean "causes." Second, eight points is a thin sample. An R² of 0.99 on n = 8 is encouraging but not decisive, and a couple of lucky weeks could inflate it. For precision, trust the standard errors over the R². Third, suppose footfall and sales were both merely trending up week over week. Then part of that R² would be the spurious shared trend instead of a real footfall-to-sales link, and regressing the week-on-week changes would test that. The regression has sharpened a hypothesis worth investigating. It hasn't proved a business law, and telling those two apart is what the green belt is for.

The formal version of all three cautions comes with sampling error and the t-distribution in the quantitative methods track. There you divide the slope by its standard error to get t = 2.5524 / 0.089616 = 28.48. You compare that against the critical t at 6 degrees of freedom, which is 2.4469 for a two-tailed test at 95%. Then you read a p-value of about 0.00000012 instead of eyeballing "nowhere near zero". The proper interval is 2.5524 ± 2.4469 × 0.089616 = (2.3331, 2.7717). It's wider than the two-standard-error band quoted in step 3, and it widens quickly as the sample shrinks. Notice too that t² = 28.48² = 811.20, the same number as the F above. With one predictor the two tests are the same test.

Practice set

Work each problem before you read its solution. Assume every cash-flow stream and every x/y pair is a real Table where that makes sense. Rates are nominal annual unless stated, so convert to the period the problem uses. Each figure has been recomputed and verified.

P1 (guided). Loan PMT and total interest. You borrow ₹8,00,000 for a car at 10% nominal annual over 5 years, repaid monthly. Write the PMT formula, give the monthly instalment, and compute the total interest paid over the life of the loan.

Solution. =PMT(0.10/12, 5*12, 800000) = −₹16,997.64 per month. It's negative because it leaves your pocket, and you can display it with =-PMT(...). Over 60 months you pay 16,997.64 × 60 = ₹10,19,858.15, so total interest = 10,19,858.15 − 8,00,000 = ₹2,19,858.15. Watch the units. The 10% annual rate becomes 0.10/12 per month and the 5-year term becomes 60 months. Mixing an annual rate with monthly periods is the classic error here.

P2 (guided). The NPV off-by-one. A project costs ₹2,50,000 today and returns ₹80,000, ₹90,000, ₹1,00,000 and ₹70,000 at the end of years 1 to 4. At a 11% discount rate, give the correct NPV. Then show what the common mistake =NPV(11%, -250000, 80000, 90000, 100000, 70000) returns instead, and explain how the two are related.

Solution. Correct: =-250000 + NPV(0.11, 80000, 90000, 100000, 70000) = ₹14,348.40, so accept the project, since the NPV is positive. The mistake puts the outlay inside NPV and gives ₹12,926.48. The wrong figure equals the correct one divided by 1.11 (14,348.40 / 1.11 = 12,926.48), because dropping the t = 0 flow into NPV over-discounts the entire project by one period. To fix it, keep the initial outlay outside the NPV, or use XNPV with dates.

P3 (guided). XIRR vs naive IRR. A venture is funded ₹10,00,000 on 10 Jan 2022, and returns ₹3,00,000 on 5 Sep 2022, ₹4,00,000 on 20 Jun 2023, and ₹5,00,000 on 15 Feb 2024. Compute the date-aware XIRR, and the naive IRR that treats the four flows as annual. Which is correct and why?

Solution. =XIRR({-1000000,300000,400000,500000}, {dates}) = 12.95%, the true annualised return on the real dates. The naive =IRR({-1000000,300000,400000,500000}) = 8.90%, because it treats the flows as one year apart. XIRR is correct because the distributions are irregularly spaced. Most of them arrive sooner than a tidy annual schedule would assume, and that's why the true 12.95% beats the naive 8.90% here. Whenever cash flows carry real dates, XIRR is the only right tool, and IRR mis-times every flow.

P4. FV of a SIP. You invest ₹20,000 at the end of every month into a fund returning 9% nominal annual, for 15 years. What is the maturity value?

Solution. =FV(0.09/12, 15*12, -20000, 0) = ₹75,68,115.38. The payments are negative because they're cash you put in, and the result is positive because it's cash that comes back. You contributed ₹20,000 × 180 = ₹36,00,000, so compounding added roughly ₹39.7 lakh. That's the engine of long-horizon investing at work. Getting the sign right (−20000) and the period right (0.09/12, 180 months) is most of the job.

P5. IRR then MIRR. A project has flows ₹−2,00,000 (t = 0), then +₹70,000, +₹80,000, +₹90,000, +₹60,000 at t = 1–4. Give the IRR, then the MIRR with a 12% finance rate and a 9% reinvestment rate, and say in one line why they differ.

Solution. =IRR({-200000,70000,80000,90000,60000}) = 18.64%. =MIRR({...}, 0.12, 0.09) = 14.50%. They differ because IRR assumes interim cash is reinvested at 18.64%, the IRR itself, while MIRR assumes a realistic 9% reinvestment. So MIRR comes out lower and more believable. Quote MIRR whenever the IRR is high enough that reinvesting at it is implausible.

P6. XNPV at a stated hurdle. Using the dated venture stream from P3, is the deal value-positive at a 12% hurdle rate? Compute XNPV.

Solution. =XNPV(0.12, {-1000000,300000,400000,500000}, {dates}) = +₹12,525.31. The XNPV is positive at 12%, so the deal clears a 12% hurdle. That agrees with its XIRR of 12.95% being above 12%, because NPV is positive when, and only when, the internal rate exceeds the hurdle. This is the standard cross-check. The sign of XNPV at your hurdle and the comparison of XIRR to that hurdle must always agree.

P7. Regression slope, intercept, forecast. A US firm's monthly marketing spend ($ thousands) and revenue ($ thousands) over six months are: spend 10, 14, 18, 22, 26, 30; revenue 62, 74, 90, 101, 118, 129. Fit the line, report R², and forecast revenue at a spend of 24.

Solution. =SLOPE(rev, spend) = 3.4143 and =INTERCEPT(rev, spend) = 27.3810, so revenue ≈ 27.38 + 3.4143 × spend. =RSQ(rev, spend) = 0.9972. =FORECAST.LINEAR(24, rev, spend) = $109.32 thousand, and since 24 is inside the observed 10 to 30 range, the forecast is legitimate. One caution on interpretation. A slope of 3.41 says each extra $1,000 of spend is associated with $3,414 more revenue in this sample of six. That's far too few points, and too likely confounded by season, to treat as a guaranteed return on ad spend.

P8. US mortgage PMT. Price the monthly payment on a $350,000 US mortgage at 6.5% nominal annual over 30 years.

Solution. =PMT(0.065/12, 30*12, 350000) = −$2,212.24 per month. Over 360 payments that's $796,406 in total, and about $446,406 of it is interest. It's the long-tenure arithmetic again, now in dollars. Same function, same sign convention, different currency. Nothing about the mechanics changes.

P9. Solve for the rate, then the term. (a) A ₹5,00,000 loan is repaid with ₹5,000 monthly for 120 months. What nominal annual rate is implied? (b) If you save ₹5,000 monthly into an 8% nominal account, how many months does it take to reach ₹5,00,000?

Solution. (a) =RATE(120, -5000, 500000) = 0.3114% per month, and multiplying by 12 gives 3.7370% nominal annual. (b) =NPER(0.08/12, -5000, 0, 500000) = 76.88 months. Round up to 77, because you can't make a fractional final deposit. RATE and NPER solve the same time-value equation for the two unknowns the closed-form functions can't isolate, and both may need a guess on awkward inputs.

P10. CORREL and what it does not mean. Two series are x = 2, 4, 6, 8, 10 and y = 5, 4, 7, 6, 9. Compute CORREL, and state in one line what a value of this size does and does not license you to conclude.

Solution. =CORREL(x, y) = 0.8220, a fairly strong positive linear co-movement. It licenses "these two rise together in this sample." It does not license "x causes y," or any claim about points outside the observed range, or much confidence at all when you only have five points. Correlation describes co-movement. It never proves a mechanism.

P11 (timed: 8 min). End-to-end mini-DCF. A project needs ₹5,00,000 today and is forecast to return ₹1,70,000, ₹1,90,000, ₹2,10,000, ₹1,70,000 at the end of years 1–4. Your cost of capital is 12%. Compute the NPV (correctly), the IRR, and the MIRR (finance 12%, reinvest 9%), and give the accept/reject decision.

Solution. NPV: =-500000 + NPV(0.12, 170000, 190000, 210000, 170000) = +₹60,764.48. The four inflows discount to ₹1,51,785.71 + ₹1,51,466.84 + ₹1,49,473.85 + ₹1,08,038.07 = ₹5,60,764.48, less the ₹5,00,000 outlay. IRR: =IRR({-500000,170000,190000,210000,170000}) = 17.59%, comfortably above the 12% hurdle. MIRR (12%, 9%) comes to 14.01%. That's still above 12% but below the IRR, as you'd expect once interim cash is reinvested at a realistic 9% instead of at 17.59%. Decision: accept. The NPV is positive. Both the IRR and the more conservative MIRR clear the hurdle. Keeping the outlay outside NPV is the one place this problem can go wrong.

P12 (timed: 6 min). Choose the right tool and name its assumption. For each task, name the single best function and state the assumption you must respect. (a) The annualised return on a stream of dated fund distributions. (b) The level monthly payment on a fixed loan. (c) The fraction of a stock's return variance explained by the market. (d) The return on a project when you can only reinvest interim cash at 7%. (e) Forecasting next quarter's sales from four years of quarterly data that is trending upward.

Solution. (a) XIRR, which assumes you feed it real dates in a date-formatted column. Text dates won't do. (b) PMT, which assumes rate and term are in the same period, so convert annual to monthly, and follows the sign convention, so the result is negative. (c) RSQ (= CORREL²), which treats R² as explained variance and co-movement and never as causation. (d) MIRR with a 7% reinvest rate, which assumes you set a realistic reinvestment rate instead of accepting IRR's "reinvest at the IRR" fantasy. (e) FORECAST.LINEAR/TREND, used with caution. It assumes you've checked for a spurious shared trend by regressing the changes instead of the levels, and that you're forecasting only just beyond the observed range. The skill underneath all five is naming the buried assumption before you trust the cell.

Applied mini-project

Build an investment-decision and driver-analysis workbook that puts both families of functions to work on a single business case. A company is deciding whether to open a new outlet, and it also wants to know what drives its existing outlets' sales. Nothing is typed twice, and every figure is a formula another figure can trace back to.

Sheet 1: Capital decision (money-math).

1. Lay out the new outlet's cash flows in a labelled Table with a period-number row above the amounts, so the t = 0 outlay is visibly in its own column. The fit-out cost today is a large negative, followed by five years of forecast net cash inflows.
2. Compute the NPV correctly at a stated cost of capital, with the outlay outside the NPV and the future flows inside. Put the wrong "outlay-inside-NPV" version in a clearly labelled "common error (do not use)" cell so you can see the gap.
3. Compute the IRR and the MIRR with a realistic reinvestment rate, and write the accept/reject decision as a formula-driven IF against the hurdle.
4. Build a second copy of the same flows on real calendar dates, putting each draw and return on the day it actually occurs. Then compute XNPV and XIRR on them. Note how much the answer moves once the true timing is respected.
5. Add a loan sheet. If the fit-out is debt-funded, compute the monthly PMT and show total interest over the term.

Sheet 2: Driver analysis (statistics).

1. Collect at least 10 rows of driver and sales data for existing outlets. The driver can be footfall, local population, marketing spend or something similar, set against monthly sales.
2. Fit the regression with SLOPE, INTERCEPT and the full LINEST block. Then label every cell of the LINEST output: slope, intercept, their standard errors, R², standard error of estimate, F, df, the two sums of squares.
3. Reconcile. Confirm in a check cell that RSQ equals CORREL², and confirm that the trendline equation on a scatter chart matches your SLOPE/INTERCEPT cells digit for digit.
4. Produce a FORECAST.LINEAR or TREND prediction for a new driver value inside the observed range. Add a second, clearly flagged prediction outside it, with a one-line note on why the second one is an assumption and can't count as a result.
5. Write a three-sentence honesty box. Say what the R² does and doesn't prove, whether the sample is large enough to bet on, and whether a shared time trend could be inflating the fit.

Rubric (self-score; 0 = missing/wrong, 1 = partial, 2 = correct and defended):

Pass: ≥ 8/10, with no zero on any criterion. Keep the workbook. Its DCF spine and its driver regression become templates you'll paste into every valuation, and into every "what moves this number?" question, for the rest of the track.

Lab: open the Financial & statistical functions workbook lab in the Practice ▸ Excel tab and pass its checks.

Reading & resources

Function references (reach for these first):

• Microsoft Learn, the official pages for PMT, PV, FV, RATE, NPER, NPV, XNPV, IRR, XIRR, MIRR, SLOPE, INTERCEPT, FORECAST.LINEAR, TREND, LINEST, CORREL, RSQ (search "PMT function Microsoft support" etc.). They give the authoritative argument order, the sign convention, and the note that NPV's first value is discounted one period. [Free] [Beginner]
• ExcelJet (exceljet.net), concise, example-driven entries for every function here, plus its "NPV function" and "XIRR function" write-ups that call out the period-1 timing directly. [Free] [Beginner]

Deeper on the finance:

• CFI (corporatefinanceinstitute.com), its NPV, IRR, and XIRR explainers connect the Excel functions to the underlying DCF and capital-budgeting theory you meet on the analyst track. [Free] [Intermediate]
• Any introductory corporate-finance text (Brealey/Myers, or Damodaran's free online material at pages.stern.nyu.edu/~adamodar) for the reinvestment-rate critique of IRR and why MIRR exists. [Free/Paid] [Intermediate]

Deeper on the statistics:

• Microsoft's LINEST documentation, which lays out the full five-row stats array cell by cell, the single best reference for reading a regression's diagnostics. [Free] [Intermediate]
• Any reputable "correlation is not causation" and "spurious correlations" resource (Tyler Vigen's spurious-correlations gallery is the memorable one) to inoculate yourself before you over-trust an R². [Free] [Beginner]

Bridge forward: the DCF and IRR machinery here is applied end-to-end in the valuation modules (M3.06–M3.07), and the regression tools are the foundation of beta estimation (M4.02) and cost-behaviour analysis (M2.04). Skim the intro of EX2.02 (Sensitivity & Data Tables) next. It wraps a two-variable Data Table around the same NPV/IRR you just built.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand, that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

AI-Augment this skill

{
  "skill": "IRR/XIRR/RATE as iterative root-finds rather than single guaranteed answers, STDEV.S vs STDEV.P feeding a Sharpe ratio, and day-count/YEARFRAC basis conventions across bond markets",
  "use": "Excel's IRR and RATE report one number and hide the search that produced it. Use a coding assistant to write the search Excel does not show you: scan a cash-flow polynomial for every real root instead of the one the default guess finds, or sweep a range of RATE guesses to see which converge inside the documented 20-iteration cap and which do not. Use a document-reading assistant to pull the day-count basis actually named in a bond's term sheet or exchange circular before setting YEARFRAC's basis argument — never guess it or accept an unsourced claim about 'the usual convention'.",
  "tools": ["Coding assistants — Claude, ChatGPT, or a notebook's inline assistant for the root-scan and iteration-path code below", "Chat assistants — for explaining a #NUM! error and drafting alternate guesses to test yourself, never to assert the answer", "Document/PDF AI — for extracting the stated day-count basis from a term sheet, offer document, or exchange circular"],
  "prompt": "Here is a cash-flow series: <PASTE>. Write Python that finds every real IRR root, not just one. Separately, sweep RATE guesses from 1% to 90% in 5-point steps against this loan's PV/PMT/FV/NPER and report which guesses converge within 20 Newton iterations and which diverge. Do not tell me which root or which guess is 'correct' — show me the full set so I can judge it against the cash-flow story myself.",
  "verify": "Re-run every returned root through the NPV or RATE equation by hand at that rate and confirm the residual is within a hundredth of a percent of zero — a root the assistant invents from pattern-matching rather than solving fails this check immediately. For a day-count basis, verify the assistant's claim against the actual term sheet or a named regulatory source (FIMMDA for Indian G-Secs, the instrument's own offer document otherwise) — never accept 'bonds usually use X' as a citation.",
  "diy": "The gate is unaided: you compute IRR and RATE by hand-iteration or a financial calculator, state from memory that a cash-flow series with two sign changes can have more than one real root, and name which YEARFRAC basis a plain Indian G-Sec and a plain US Treasury note each use — with no tool in the room."
}

Modern Data Analysis

By hand first. You already compute PMT, NPV, IRR, XIRR, RATE, SLOPE, and RSQ by hand and know the sign convention, the NPV off-by-one, and IRR's reinvestment assumption. Excel returns one number per call and nothing about how it got there.

Today's workflow. IRR and RATE are both iterative solvers with an undisclosed seed (guess, default 10% for both). Reconstruct that iteration in code and two hidden behaviours become visible. A cash-flow series with more than one sign change can have more than one mathematically valid IRR, and Excel reports whichever one its seed converges to, with no flag that a second root exists. And RATE can fail outright. With the documented default guess and 20-iteration cap, an ordinary-looking loan can return #NUM! while a different starting guess on the identical cash flows converges cleanly.

Tools & sources (IN + US). Use numpy for exact polynomial roots and for a from-scratch Newton-Raphson reconstruction of Excel's documented IRR/RATE iteration. That reconstruction follows the algorithm Microsoft's own support pages describe: a starting guess, a 0.00001%-accuracy target and 20-try cap for IRR, and a tighter 0.0000001 target for RATE. It makes no claim of bit-for-bit parity with Excel's undisclosed internal solver. India: FIMMDA's "Government Securities Market in India: A Primer," states that dated G-Secs price on a 30/360 basis (YEARFRAC basis 4, European 30/360) while Treasury Bills price on Actual/365. US: Corporate Finance Institute's day-count reference, already listed under Reading & resources above, states that Treasury notes/bonds use Actual/Actual, corporate bonds use 30/360 and money-market instruments use Actual/360.

# recomputed in-session — R3
import numpy as np
cf = [-1600, 10000, -10000]
def npv(r, flows):
    return sum(c / (1+r)**t for t, c in enumerate(flows))
# NPV(r)=0, substituting x=1/(1+r), gives 25x^2 - 25x + 4 = 0
roots_x = np.roots([25, -25, 4])
roots_r = sorted(1/roots_x - 1)
print(roots_r)   # [0.25, 4.0] -> IRR = 25.00% and IRR = 400.00%, both exact zeros of NPV

Verify. Both roots solve NPV(r)=0 to six decimal places. npv(0.25, cf) and npv(4.00, cf) each return 0.000000, matching the algebraic solution of 25x² − 25x + 4 = 0. A from-scratch Newton reconstruction started from Excel's documented default guess (10%) converges to the 25% root in 5 steps. Started from 300%, it converges to the 400% root in 5 steps. Nothing differs in the cash flows, yet the two answers are different and equally valid, and the seed alone picks between them.

Quantitative lens

One real loan defeats the documented default guess. Take a five-year facility, with PV = ₹50,000 received today, PMT = −₹30,000 paid at the end of each of five years and FV = −₹50,000 as a final balloon. Solved by Newton-Raphson from every guess between 20% and 80% in 10-point steps, this loan has one consistent answer, RATE = 60.00%. The equation's residual at that rate is 0.00000000 to eight places. But start the same reconstruction from Excel's own documented default guess of 10% and it doesn't converge. It diverges outward (10.0% → 10,873% → 8,681% → 6,927% → …) and blows through the 20-iteration cap without settling. Guesses of 1% and 5% fail a different way. They step to a rate below −100%, where (1+r) is no longer a valid base and the iteration has left its domain. This loan is a constructed cash-flow shape chosen to demonstrate the mechanism, and nobody is claiming this exact structure is common. The mechanism itself is Microsoft's documented behaviour. RATE "can have zero or more solutions," and convergence depends on how close guess is to the true root. Being "between 0 and 1" doesn't decide it.

The gap between STDEV.S and STDEV.P is a fixed, provable overstatement. Noise has nothing to do with it. Take a twelve-observation illustrative monthly excess-return series with a mean of 1.0583%. STDEV.S (divisor n−1 = 11) gives 1.9119%, and STDEV.P (divisor n = 12) gives 1.8305%. It's smaller because dividing the same sum of squared deviations by a smaller denominator can only shrink the result or leave it unchanged. Fed into a Sharpe ratio, that shrinkage moves the wrong way. Sharpe via STDEV.S is 0.5535, and via STDEV.P it's 0.5782, an overstatement of 4.45%. That figure isn't specific to this data. The ratio of the two Sharpe values always equals √(n/(n−1)), whatever the returns are. So a twelve-month track record inflates its own Sharpe by 4.45% every time STDEV.P is used on a sample instead of STDEV.S, and the effect grows as the track record shortens.

A basis you don't set is a basis Excel chose for you, and it moves real money. YEARFRAC's omitted-argument default is basis 0 (US 30/360, NASD), a US corporate-bond convention, applied without comment to whatever you hand it. Take a ₹10,00,000 face, a 7.5% coupon and a clean six-month accrual window, 15 March to 15 September 2024, which is 184 actual days. Five bases give five different accrued-interest figures. US 30/360 and the FIMMDA-recommended G-Sec basis (European 30/360) both give ₹37,500.00 in this particular window. US Treasury Act/Act and Indian T-Bill Act/365 both give ₹37,808.22, and US money-market Act/360 gives the largest, ₹38,333.33. The spread of ₹833.33, 2.22% of the 30/360 figure comes from the basis argument alone, on identical face value, coupon and dates. That the two 30/360 variants coincide here is itself informative. They only diverge at month-end edge cases (a 31st, or a 30th colliding with a 31st). So the real India/US split isn't that "the arithmetic always disagrees". Which basis applies is an instrument-specific, market-specific fact. G-Secs use 30/360 per FIMMDA, Indian T-Bills Act/365, US Treasuries Act/Act, US corporates 30/360 and US money-market instruments Act/360. Pricing one instrument on another instrument's convention is a computable misstatement of at least this size, and nothing will flag it.

Limits: the Newton reconstruction matches Microsoft's documented starting guess, accuracy target and iteration cap, but it isn't Excel's actual internal solver. Treat "diverges/converges" as a faithful demonstration of the documented mechanism. It's no promise that Excel's own binary fails on this exact loan to the decimal place. The loan and the return series are teaching specimens chosen to make each mechanism land cleanly, and they make no claim about typical market instruments. The Act/Act figure above uses simple actual-days/365 within one non-leap window instead of the full ISDA Act/Act leap-day-splitting algorithm. That's adequate to make the point in a window with no 29 February, and it's no substitute for the full standard on a window that has one.

Where this goes next: galaxy cross-links

• G1 · QM1.01 Probability, Distributions, Sampling & Estimation, where the n vs n−1 divisor is the sample-vs-population estimator distinction formalised; the √(n/(n−1)) Sharpe inflation is a direct application.
• G1 · FI1.01 Bond Pricing & Yield Measures, the day-count basis feeds directly into every yield and accrued-interest figure that module builds; get the basis right here and the rest is arithmetic.
• G1 · QM1.03 Time-Series, Monte Carlo & Intro ML for Finance, root-finding and iterative solvers generalise from a single RATE call to the constrained engines and convergence diagnostics that module builds at scale.

Teach it back & journal

Feynman prompt. Explain two things, in everyday language only, to a friend who has a home loan but has never opened Excel. First, why the bank's "EMI" is a single number that ties together how much they borrowed, the interest rate and the number of months, and that Excel's PMT is just the machine that computes it. Second, and harder, explain the "off-by-one" idea. When you value a project, why is the money you spend today counted at full value while money that arrives next year is worth a little less? And why does an analyst who accidentally tells the spreadsheet that today's spending happens "next year" make the whole project look worse than it is? You may use the words present value, discount rate and net present value only in your last two sentences. Attach each to the moment in the story it describes.

Journal prompt. Find one real financial decision in your own life or work. It might be a loan you carry or an investment you're weighing. It could also be a "does this spending drive that result?" question at work. Model it with the tools above. If it's a loan or an investment, build the PMT or the FV and read back the total interest or the compounding gain, and notice what surprised you. If it's a "does x drive y?" question, gather whatever handful of data points you really have and run SLOPE, INTERCEPT and RSQ. Then comes the real exercise. Write down all the reasons you should not yet trust the result: how few points you have, what third factor might explain the co-movement, whether both numbers are just drifting up with time. Finally, write the single sentence you want to remember for good about why every one-cell function here hides an assumption. Naming that assumption before you trust the number is the difference between an analyst and a person who owns a spreadsheet.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.