FV, PV and NPER: Time Value Functions in Excel

FV, PV and NPER are time value of money functions. FV returns a future value, PV returns a present value, and NPER returns the number of periods. They share the same set of arguments built around a periodic rate, a payment, and lump sums. Getting the sign convention and the periodic rate right is the key to correct answers.

Syntax and the sign convention

The signatures are =FV(rate, nper, pmt, [pv], [type]), =PV(rate, nper, pmt, [fv], [type]) and =NPER(rate, pmt, pv, [fv], [type]). The rate is the interest rate per period, nper is the number of periods, and pmt is the payment made each period.

Excel follows a cash flow sign convention. Money you pay out is negative and money you receive is positive. If you deposit savings, the payment and present value are negative, and the future value returns positive. Mixing signs is the most common reason these functions return a confusing or negative result. The optional type is 0 for payments at period end and 1 for the beginning.

Worked example: future value of monthly savings

You save 200 per month for 5 years at 6 percent annual interest, compounded monthly, starting from a zero balance. Because compounding is monthly, divide the annual rate by 12 and multiply the years by 12.

1. Set the periodic rate to 6%/12, which is 0.5 percent per month.
2. Set the number of periods to 5*12, which is 60 months.
3. Enter the deposit as a negative payment because it is cash out: =FV(6%/12, 5*12, -200, 0).
4. Excel returns about 13954, the positive balance you would hold after 60 deposits.

The negative payment returns a positive future value of roughly 13954 after 60 months.

How analysts use time value functions

These functions anchor any model that involves financing, savings, or discounting a stream of equal cash flows.

• Use PV to find the lump sum equivalent of a fixed annuity or lease stream.
• Use FV to project a savings or sinking fund balance at a target date.
• Use NPER to solve how many periods it takes to pay off a balance at a set payment.
• Always convert an annual rate to the compounding period with rate divided by 12 for monthly or 4 for quarterly.

Common pitfalls

The two biggest errors are sign and period mismatch. If you enter the payment as positive when it is cash out, the result flips sign and looks wrong. If you use an annual rate with a monthly payment count, the math is internally inconsistent and the answer is far off.

NPER can also surprise you. It returns a fractional number of periods, so 23.4 means the balance is cleared partway through the twenty fourth period, not at a clean boundary. And if the rate, payment, and lump sums imply the balance never reaches the target, NPER returns a #NUM! error, which usually signals a sign problem in the inputs.