Fluency means having the formula in your head and under your fingers. All of what follows works identically in Excel and Google Sheets. Two conventions come first:
- The sign convention. Spreadsheet financial functions treat cash paid out as negative and cash received as positive, and each function solves the equation
PV + Σpayments + FV = 0in time-value terms. So=PMT(...)on a positive loan PV returns a negative EMI, because you pay it. Fighting the convention causes more spreadsheet bugs than any formula error. Pick a direction and stay consistent. I'd model from the borrower's or investor's pocket. - Rate/period match, again. Feed monthly problems monthly rates and month counts:
rate = 8.5%/12,nper = 240.
What do the main Excel financial functions do?
| Task | Formula | Notes |
|---|---|---|
| FV of a lump sum / stream | =FV(rate, nper, pmt, [pv], [type]) | type 0 = ordinary (default), 1 = due |
| PV of a lump sum / stream | =PV(rate, nper, pmt, [fv], [type]) | annuity + terminal lump in one call |
| EMI / payment | =PMT(rate, nper, pv, [fv], [type]) | =PMT(8.5%/12, 240, 8000000) → −69,425.86 |
| Interest part of period n | =IPMT(rate, per, nper, pv) | amortization split without a schedule |
| Principal part of period n | =PPMT(rate, per, nper, pv) | IPMT + PPMT = PMT, always |
| Cumulative interest, periods a–b | =CUMIPMT(rate, nper, pv, a, b, 0) | "interest paid in year 1" in one cell |
| Solve for the rate | =RATE(nper, pmt, pv, [fv]) | returns the periodic rate, so annualize yourself; this is how you expose a flat-rate loan |
| Solve for periods | =NPER(rate, pmt, pv, [fv]) | prepayment/term questions |
| EAR from APR | =EFFECT(nominal_rate, npery) | =EFFECT(42%, 12) → 51.1% |
| APR from EAR | =NOMINAL(effect_rate, npery) | inverse |
| NPV | =NPV(rate, CF1:CFn) + CF0 | see the trap below |
| IRR | =IRR(values, [guess]) | try several guesses when signs change twice |
| MIRR | =MIRR(values, finance_rate, reinvest_rate) | cures reinvestment fantasy and multiple roots |
| Dated NPV / IRR | =XNPV(rate, values, dates) / =XIRR(values, dates, [guess]) | actual/365 convention; first date = t0 |
Advertisement
What are the Excel NPV and IRR traps?
The Excel NPV trap: the most common spreadsheet bug in finance. =NPV() discounts every cell handed to it by one period, including the first. It assumes the range starts at t = 1. If your range includes the t = 0 outflow, Excel discounts your initial investment as if it happened a year from now and overstates NPV without a word. Here's the correct form, every time:
=CF0_cell + NPV(rate, CF1:CFn)
Test yourself right now: −250, 90, 110, 130 at 11% must return 15.41. If your sheet says 13.88, you've just met the bug, which discounted everything one extra period. XNPV, by contrast, treats the first dated cash flow as t = 0, so the two functions disagree by design. This bug has turned up in real fairness opinions and real bid models. Auditors look for it, and from this week, so do you.
Verify the multiple-IRR behavior yourself. This drill makes Trap 1 permanent. Enter −100, 230, −132. =IRR(range) returns 10.0%. =IRR(range, 0.25) returns 20.0%. Same cells, two answers, no warning. Then =MIRR(range, 12%, 12%) gives you one answer, every time.
Which spreadsheets should you build to practise?
Build these five sheets from blank this week. The mini-project and the mastery check assume you have them:
- Converter: APR to EAR and back at any frequency, using EFFECT/NOMINAL plus the manual
(1+APR/m)^m − 1to prove they match. - Amortizer: loan inputs (P, rate, years) that feed an EMI via PMT, a full month-by-month schedule via IPMT/PPMT with a running balance, a CUMIPMT year-1 check, and a prepayment cell that recomputes remaining term via NPER.
- NPV/IRR lab: a cash-flow column with NPV (correct convention), IRR at several guesses, MIRR, and a one-way data table plotting NPV against r from 0% to 30%. That's the NPV profile, where you'll see the double crossing.
- Fisher sheet: nominal/real/π triangle solver plus the four-row consistency demonstration from the real-versus-nominal section, rebuilt with your own numbers.
- Dated-flows sheet: an XNPV/XIRR ledger for irregular dates. Model a SIP with real dates and confirm the XIRR against the annuity answer.
Keep your hands on the keyboard while you build. Navigate with Ctrl+arrows and Ctrl+Shift+arrows instead of the mouse. Lock references with F4. Name your rate cells (rate_m, pi_in) instead of pointing at "$B$3". You build speed for the timed exam now, while you practise.