Rules of Thumb
Excel and the Rule of 72
The Rule of 72 is a guesstimate of how long it will take an
investment at a specific interest rate to double in value. But
how accurate is this rule? This Excel analysis provides the
answer.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

This week, I learned that my son—who's an avid amateur
investor—had never heard of the Rule of 72.
The rule of 72, I texted him, says that if you divide 72 by
the anual interest rate that you earn on an investment, you'll
learn approximately how long it will take for your investment to
double in value.
For example, if you divide 72 by 6, you learn that it will
take about 12 years to double an investment that earns 6%,
compounded annually
And by the same logic, if you divide 72 by an annual
inflation rate, you'll learn about how many years it will take
for a currency to lose half its value.
For example, in 1980 the US dollar experienced an average
inflation rate of nearly 14%. If that rate had continued, the
dollar would have lost half its value in about 5 years.
But as I was texting my son about the rule, I realized that I
hadn't confirmed the limits of its accuracy for years. I knew it
worked well for interest rates between about 6% and 8%. But
what about rates that are higher and lower than that? What if I
was giving my son bad advice?
So, of course, I quickly turned to Excel, and created the following
two figures.
In the table below, column C uses the Rule of 72 to find
about how long it takes an investment to double. For example,
here's the formula for the cell shown:
C5: =72/(100*B5)
I
used two different formulas to calculate column D.
First, because I remembered that Excel doesn't have a function that
calculates the number I needed, I created my own formula.
In general, the formula is...
Inv * (1+i)^n = 2 * Inv
...which means that a dollar invested at some interest rate
will grow to twice the investment in n number of years. So, to
solve for n, I first took the logarithm of both sides giving me
this formula:
=LOG10(Inv) + LOG10(1+i) * n= LOG10(2 * Inv)
Solving for n gave me the formula:
n = (LOG10(2 * Inv)  LOG10(Inv)) / LOG10(1+i)
Because I was using an investment of $1 growing to $2, and
because LOG10(1) = 0, that gave me the simplified formula...
D2: =LOG10(2)/LOG10(1+B5)
...and that gave me the results shown above.
But then, I checked Excel's financial functions, and
discovered that Excel 2013 introduced the PDURATION function,
which does exactly what my formula did. The function has this syntax:
=PDURATION(rate, pv, fv)
And so, I changed the formula in cell D2 to:
D2: =PDURATION(B5,1,2)
When you look at column E, which has formulas like this...
E5: =C5D5
...you can see that with the exception of a 1% interest rate,
the Rule of 72 gives a result that's within one year for all
other interest rates I tried. And above a 2% rate, it's nearly
spoton.
And the chart of columns C and D show an even more impressive
match between the Excel formula and a rule of thumb that we can
calculate in our head.
In short, the Rule of 72 is amazingly accurate.
Who'd a thunk it?
