7.3 Financial Functions

NPV Versus XNPV, IRR Versus XIRR

CFI's Advanced Excel Formulas article calls XNPV the number-one finance formula because it discounts each cash flow by its actual calendar date. Excel NPV does not. Mixing them up is one of the highest-leverage errors on an FMVA modeling case: you can accept a project that period NPV says is worthless, or reject one that dated discounting says creates value.

What Excel NPV Actually Assumes

=NPV(rate, value1, [value2], ...)

Excel treats value1 as the end of period 1, value2 as the end of period 2, and so on. There is no time-0 slot inside the function. The initial outlay belongs outside:

=t0 + NPV(rate, t1:tn)

If you drop t0 into the NPV range, Excel discounts it as if it occurred one full period from today. If you omit t0 entirely, you have valued only the inflows.

Horizon Foods is evaluating a cold-storage expansion. WACC is 10%. Three dated cash flows:

A candidate who treats the two inflows as year-end periods and adds t0 outside writes:

=−120000 + NPV(0.10, 50000, 90000)

= −120,000 + 50,000 / 1.10 + 90,000 / 1.21

= −120,000 + 45,454.55 + 74,380.17 = −$165.28

Period NPV says the project destroys a sliver of value. That arithmetic would be correct only if the $50,000 arrived a full year after the capex and the $90,000 arrived two full years after. They do not. The first inflow is about six months later.

Worked XNPV with Three Dated Cash Flows

XNPV takes every cash flow, including t0, plus a matching date for each:

=XNPV(discount_rate, cash_flows, dates)

=XNPV(0.10, D2:D4, C2:C4) where C2:C4 are the three dates and D2:D4 are −120,000, 50,000, 90,000.

The identity Excel implements is:

XNPV = Σ CF_i / (1 + r)^((date_i − date_1) / 365)

Day counts from 15 Jan 2026 (2026 is not a leap year):

• 15 Jul 2026 is 181 days (16 + 28 + 31 + 30 + 31 + 30 + 15).
• 15 Jan 2027 is 365 days.

Exponents: 181/365 = 0.495890; 365/365 = 1.

(1.10)^0.495890: ln(1.10) = 0.095310, × 0.495890 = 0.047263, e^0.047263 = 1.04840.

PV of $50,000 = 50,000 / 1.04840 = $47,691.72

PV of $90,000 = 90,000 / 1.10 = $81,818.18

XNPV = −120,000 + 47,691.72 + 81,818.18 = $9,509.90 (about $9,510)

Dated discounting says the project creates about $9,510 of value. Period NPV said −$165. The first inflow is discounted for half a year, not a full year, so its present value is $47,692 rather than $45,455. That gap, plus a one-year rather than two-year wait on the second inflow, flips the decision. Accept if XNPV > 0. Here XNPV is positive, so Horizon accepts at a 10% WACC.

Include t0 in the XNPV ranges. Exclude t0 from the NPV function and add it outside. Those two sentences are the entire Excel timing skill.

IRR Versus XIRR

IRR(values) is the rate that sets period NPV to zero, with equal spacing implied. XIRR(values, dates) is the rate that sets XNPV to zero, with the same day-count engine as XNPV.

=XIRR(D2:D4, C2:C4)

You do not need to iterate XIRR by hand on exam day. You do need the sign: XNPV at 10% is +$9,510, so XIRR is above 10%. If the hurdle is WACC of 10%, both the XNPV rule (accept if > 0) and the XIRR rule (accept if XIRR > hurdle) agree. They can disagree with a nonconventional cash-flow strip (multiple IRRs); XNPV at the hurdle is then the decision that still makes economic sense.

=IRR(D2:D4) on these three cells is the wrong function. The gaps are six months and then six months, not two equal years, and t0 is in the range. Use XIRR whenever dates are not a regular period grid.

PMT, IPMT, NPER, RATE, EFFECT, and Depreciation

CFI lists PMT and IPMT with the advanced finance formulas. They are the Excel form of the ordinary-annuity identity from Chapter 2, with the same sign convention: money that leaves the firm is negative; money that enters is positive.

PMT and IPMT on a Term Loan

=PMT(rate, nper, pv, [fv], [type])

Horizon draws a $250,000 equipment loan at 8% annual interest, 4 year-end payments, fully amortizing (fv = 0, type = 0).

=PMT(0.08, 4, -250000)

Annuity factor = [1 − (1.08)^(−4)] / 0.08. (1.08)^4 = 1.360489, so (1.08)^(−4) = 0.735030. Factor = 0.264970 / 0.08 = 3.312125. Payment = 250,000 / 3.312125 = $75,480.04.

IPMT returns the interest piece of one period; PPMT returns the principal piece. They add to PMT.

=IPMT(0.08, 1, 4, -250000) = 250,000 × 0.08 = $20,000.00 in year 1.

Principal year 1 = 75,480.04 − 20,000.00 = $55,480.04. Closing balance = 250,000 − 55,480.04 = $194,519.96.

Year 2 interest = 194,519.96 × 0.08 = $15,561.60 = IPMT(0.08, 2, 4, -250000). Interest falls as the balance falls. A model that expenses PMT as interest overstates the tax shield in later years and understates principal repayment on the cash flow statement. Split the payment: IPMT to the income statement, PPMT to financing cash flow and the debt balance.

NPER and RATE

=NPER(rate, pmt, pv, [fv], [type]) and =RATE(nper, pmt, pv, [fv], [type]) invert the same identity. How many year-end $75,480.04 payments clear $250,000 at 8%? =NPER(0.08, -75480.04, 250000) returns 4. What rate turns four $75,480.04 payments into a $250,000 present value? =RATE(4, -75480.04, 250000) returns 8%.

Sign errors are the usual #NUM!. If pv, pmt, and fv are all positive, Excel has no sign change to iterate. Match the TVM convention: loan proceeds positive, payments negative, or the reverse — but not all the same sign.

EFFECT Versus NOMINAL

EFFECT converts a nominal annual rate that compounds m times per year into an effective annual rate. NOMINAL goes the other way.

=EFFECT(nominal_rate, npery)

=NOMINAL(effective_rate, npery)

=EFFECT(0.12, 12) = (1 + 0.12/12)^12 − 1 = (1.01)^12 − 1 = 12.68%

=EFFECT(0.09, 4) = (1.0225)^4 − 1 = 9.308%

=NOMINAL(0.126825, 12) returns 12%. Discount annual project cash flows at the effective rate that matches their calendar, not at the brochure APR. A 12% APR monthly credit line next to a 12% WACC is not the same hurdle; the credit line's EAR is 12.68%.

SLN, DB, and Why Models Use a Custom Schedule

SLN is straight-line depreciation:

=SLN(cost, salvage, life)

A line is $80,000, salvage $8,000, life 6 years. SLN = (80,000 − 8,000) / 6 = $12,000 per year.

DB is declining-balance with a rate Excel derives from cost, salvage, and life:

=DB(cost, salvage, life, period, [month])

The rate is 1 − (salvage/cost)^(1/life) = 1 − (8,000/80,000)^(1/6) ≈ 31.87%. Year 1 DB ≈ 80,000 × 0.3187 = $25,496. Year 2 applies that rate to the remaining book. Depreciation is front-loaded. DDB is double-declining; SYD is sum-of-years-digits. Know that they exist. Do not build a three-statement forecast out of them.

Live CFI models use a custom depreciation schedule: opening gross PP&E, plus capex by vintage, minus depreciation (often a percent of opening gross or of a remaining useful-life ladder), equals closing gross, with accumulated depreciation as a contra. That schedule can encode half-year conventions, tax versus book lives, and a different useful life for this year's capex than for the legacy book. SLN($80,000, $8,000, 6) is a one-asset check. It cannot see next year's $40,000 conveyor add-on, a mid-year in-service date, or a tax method that is not straight-line.

If an FMVA case gives an explicit depreciation table, use the table. If it gives one asset, SLN is the fast check. If it gives a capex forecast and a useful life, build the schedule — that is Operational Modeling, not a nested DB call across 10 forecast years.

Function Map and Exam Traps

Traps: putting t0 inside NPV; leaving t0 out of XNPV; using IRR on irregular dates; treating PMT as interest; averaging 12% APR with a 12% WACC; dropping SLN on a multi-vintage capex strip. XNPV at the hurdle is the decision rule that survives all of those.