Using Microsoft Excel to Calculate 'The Rule of 72'

Microsoft Excel is an extraordinarily handy bookkeeping software program. But did you know Excel can also be used to perform the Rule of 72 calculation? For the uninitiated, this rule is a simple mathematical shortcut that helps investors estimate the number of years it would take to double their money, given a known interest rate, or a known compounding annual rate of return. For investors standing at a crossroads, who are faced with deciding between two or more investment options, the Rule of 72 can help them make more educated decisions that best align with their risk tolerance, time horizons, and long-term investment goals.

A Usage Example

Let us assume you wish to compare the approximate number of years it would take for five separate investments to double, with expected rates of return of 5%, 10%, 13%, 15%, and 20%. Using Microsoft Excel, this exercise may be achieved by taking the following iterative steps:

  1. Increase the widths of columns A, B, C, and D by right-clicking on each respective column.
  2. Left-click on Column Width and change the value to 35.
  3. Make the typeset bold for the titles by pressing the CTRL and B keys together.
  4. Enter "Expected Rate of Return" in cell A1, "Actual Number of Years" in cell B1, "Number of Years Using the Rule of 72" in cell C1, and "Difference" in cell D1.
  5. Enter "5" into cell A2, "10" into cell A3, "13" into cell A4, "15" into cell A5, and "20" into cell A6. The formula used to calculate the actual number of years it takes to double your investment is the natural log of two divided by the natural log of one plus the expected rate of return.

The values in cells A2 through A6 must be expressed in percentage terms to calculate the actual number of years it would take for the investments to double. Therefore, the values must be divided by 100. To accomplish this, take the following steps:

  1. In cell B2, enter "=(LN(2)/(LN(1+A2/100)))".
  2. Left click and hold on the bottom right corner of cell B2 and drag the cell down to cell B6.
  3. Now, use the rule of 72 to calculate the approximate number of years by entering "=72/A2" into cell C2, "=72/A3" into cell C3, "=72/A4" into cell C4, "=72/A5" into cell C5 and "=72/A6" into cell C6.