Payoff time, true rates and savings targets

Find how long a card balance takes to clear with NPER, the real rate behind a payment plan with RATE, and plan a savings goal with PV and FV. Intermediate · 6 min · NPER, RATE, PV, FV, Cash-flow signs

In the loans lesson, PMT worked out a payment. Excel's money functions are really one equation seen from different sides: give it any four of rate, number of payments, payment, present value and future value, and a function finds the fifth.

NPER: how many payments
RATE: the interest rate per period
PV: what a future amount is worth today
FV: what money grows to by the end

The sign rule still applies: money that comes to you is positive, money that leaves you is negative.

> If you see #NUM!, a sign is usually wrong. With every amount pointing the same way, no rate or term can make the numbers balance.

Steps

How long until the card is clear? In B5, enter =ROUNDUP(NPER(B3/12, -B4, B2), 0). NPER takes the monthly rate, the payment and the balance. The payment leaves your account, so it goes in as negative; the balance is money the card lent you, so it stays positive. ROUNDUP turns a part month into a whole one, because the last, smaller payment still takes a month.
The car dealer only mentions the payment: $389 a month for 36 months on $12,000. In B11, use RATE(nper, pmt, pv) to find the rate the loan really charges. RATE works per payment period, which here is a month, so multiply its answer by 12 to get a yearly rate.
Now the savings goal in column E: E2 in E4 years, earning E3 a year. In E7, work out the single lump sum you would have to invest today to reach the target with no more deposits. Use PV(rate, nper, pmt, fv) with monthly periods, 0 for the payment and the target as the future value. Put a minus in front of PV, as you did with PMT, so the amount shows as positive.
You don't have $16,540 to spare, but you do have E5 saved and can add E6 a month. In E8, use FV to see what that grows to by the deadline. This time FV needs its 4th argument, the starting balance. The savings you already have and the monthly deposits both go into the account, so both are negative.

Remember

NPER, RATE, PV, FV and PMT each solve the same equation for a different unknown.
Keep units consistent: a monthly rate (÷12) with a count of months.
Money in and money out need opposite signs. #NUM! usually means they don't.
RATE returns the rate per period, so ×12 turns a monthly rate into a yearly one.

Previous: Repairing numbers and names · Next: Build a loan amortization schedule