Solving for n and r: NPER, RATE and the Rule of 72

◈ 9 cards

How long, and at what rate — =NPER and =RATE need pv and fv of opposite signs, return periods and a periodic rate, and the Rule of 72 checks the doubling time.

The two unknowns that are not amounts

So far the unknown has been a dollar amount. Two other questions are just as common on the paper: how long will it take, and at what rate. Excel has a function for each — =NPER(rate, pmt, pv, fv, type) and =RATE(nper, pmt, pv, fv, type, guess) — and both carry a rule the amount functions do not: pv and fv must have opposite signs. You pay out the present value and receive the future value; enter them with the same sign and Excel is asked when money paid to you grows into more money paid to you, finds no answer, and returns #NUM!.

Worked example — how long to double?

Anika has $10,000 and wants $20,000. At 6 % compounded annually, how many years?

=NPER(0.06,0,-10000,20000) → 11.8957 years. By algebra, $n = ln (2) / ln (1.06) = 0.693147/0.058269$ . The Rule of 72 gives the estimate first: $72/6 = 12$ years. Close enough to catch a keying error, never a substitute for the function on an answer-only paper.

At 9 %, the rule says $72/9 = 8$ years exactly; =NPER(0.09,0,-1,2) gives 8.0432. Notice the amounts do not matter for a doubling — any pv against 2 × pv gives the same n — so −1 and 2 will do.

Worked example — what rate?

A private placement will turn $8,000 into $12,000 in 7 years. What annual return is that?

=RATE(7,0,-8000,12000) → 0.059634, or 5.9634 % a year. By algebra, $(12, 000/8, 000)^{1/7} - 1 = 1. 5^{0.142857} - 1$ . Note RATE puts nper first — it is the one function in the family whose argument order differs (Lesson 1.2).

Periods and periodic rates

Both functions answer in the units you fed them. =RATE(24,0,-1000,1500) — twenty-four monthly periods — returns 0.017038 per month; the annual (nominal) rate is 12 × that = 0.2045, and the effective rate is 1.017038¹² − 1 = 0.2247. Which one the paper wants depends on the wording; a monthly figure reported as an annual one is marked wrong. Likewise =NPER with a monthly rate returns months — divide by 12 for years.

The sign trap, deliberately

=NPER(0.05,0,1000,2000) → #NUM!, because 1,000 and 2,000 share a sign. Fix it: =NPER(0.05,0,-1000,2000) = 14.2067 years (Rule of 72: 14.4). The error is not a rounding difference you might miss; it is a refusal, and it is the most common reason a NPER or RATE cell shows an error on the paper.