For business users of Microsoft Excel Free guides and templates

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, 2005-2014
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)

Rule of 72 in ExcelI 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:

Chart of the Rule of 72=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:   =C5-D5 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 spot-on.

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?

Free Excel Dashboards

Charley's SwipeFile charts