Excel’s TVM functions and the sign convention
◈ 8 cardsrate, nper, pmt, pv, fv, type — the argument list five functions share, and why PV comes out negative.
Five functions, one argument list
Every time-value problem has five variables: a rate per period, a number of periods (nper), a level payment per period (pmt), a present value (pv) and a future value (fv). Exactly one of them is unknown. Excel gives you one function per unknown — PV, FV, PMT, NPER, RATE — and each takes the other four as arguments, plus a sixth, type, which is 0 for end-of-period payments (the default) and 1 for beginning-of-period.
The order is always the same family:
Square brackets mean optional, defaulting to 0. Notice that RATE is the odd one out — since the rate is the unknown, nper moves to the front.
Worked example — a deposit and a target
Dana deposits $1,000 today at 5 % a year for three years. In Excel: =FV(0.05,3,0,-1000), which returns 1,157.63. Check it by hand: $1{,}000 \times 1.05^3 = 1{,}157.625$.
Why the minus sign on the 1,000? Because Excel treats every argument as a cash flow from your point of view. The deposit leaves your pocket, so it is negative; the future value comes back to you, so Excel returns it positive. Enter the deposit as +1000 instead and the function returns −1,157.63: the same magnitude, the opposite sign.
Now the other direction. Dana wants $2,000 in five years and can earn 4 %. =PV(0.04,5,0,2000) returns −1,643.85 — negative, because to receive 2,000 later you must pay out 1,643.85 now. Hand check: $2{,}000 / 1.04^5 = 1{,}643.854$.
Predicting the sign
The rule that predicts every sign: the function returns the cash flow that balances the ones you entered. Enter a positive pv into =PMT(rate,nper,pv) — you received a loan — and the payment comes out negative, because you repay it. Enter a negative pv and a positive fv into =NPER(…) and you get a sensible number of periods; enter both positive and Excel cannot find a period count at which money paid to you grows into more money paid to you, so it returns #NUM!. RATE fails the same way.
On the paper the marker generally wants the magnitude, and you can force a positive display with =-PV(…). But a stray minus inside a chain of cells propagates into every later answer, so decide the sign deliberately every time rather than fixing it at the end.
The two habits that survive the exam
- Write the argument list in your head before you type: rate, nper, pmt, pv or fv, type.
- Decide which flows are out of your pocket and enter them negative — the answer's sign will then read as money coming back to you.