Financial & Statistical Functions

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; EX1.01 (Formulas II) for the array mindset that makes LINEST's spilled output and TREND's multi-cell result feel natural rather than alien. EX1.02 (Lookups Mastery) supplies the tier-table discipline you will reuse when a discount rate or a tax rate must be fetched rather than 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), so that 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 (M3.07) is IRR/MIRR with a decision rule bolted on; the sensitivity tables of EX2.02 wrap a two-variable Data Table around exactly these functions. On the statistical side, the beta of a stock (M4.02) is SLOPE of returns against the market; a cost-behaviour split into fixed and variable (managerial accounting, M2.04) is INTERCEPT and SLOPE of cost against volume; and every "revenue driver" model rests on the FORECAST.LINEAR/TREND machinery you meet here. The scepticism the final subsection drills (R² is not causation, small n is not evidence) is the single most transferable habit in the entire curriculum, and it reappears the moment you touch real market data.

Time value in one cell: PV, FV, PMT, RATE, NPER

A rupee today is worth more than a rupee next year, because today's rupee can be invested to become 1 + r rupees. Compound that over n periods and a single amount today (PV) grows to a future amount (FV) by FV = PV × (1 + r)^n, and almost all of finance is this one relationship plus a stream of equal payments (PMT) layered on top. The five time-value functions are five ways to solve one equation for its five unknowns, not five separate ideas:

`` PV × (1 + r)^n + PMT × [((1 + r)^n − 1) / r] + FV = 0 ``

Given any four of {PV, FV, PMT, r (rate), n (nper)}, Excel solves for the fifth:

• PV(rate, nper, pmt, [fv], [type]), today's value of a future amount and/or a payment stream.
• FV(rate, nper, pmt, [pv], [type]), what a stream and/or lump sum grows to.
• PMT(rate, nper, pv, [fv], [type]), the level payment that ties pv to fv.
• RATE(nper, pmt, pv, [fv], [type], [guess]), the per-period rate implied by the other four (solved iteratively).
• NPER(rate, pmt, pv, [fv], [type]): how many periods it takes.

One rule trips everyone: the cash-flow sign convention. Excel models these as your cash flows on a timeline. Money that comes to you is positive; money that leaves you is negative. A loan you receive is a positive pv (cash in now); the repayments are therefore negative, and PMT returns a negative number to say "this leaves your pocket." If you fed pv and fv with the same sign, Excel returns a #NUM! or a nonsensical answer. The signs must express the direction of each flow. type is 0 for end-of-period payments (an ordinary annuity, the default and the usual case for loans) and 1 for beginning-of-period (an annuity-due, e.g. rent paid in advance).