How do I calculate how long it takes an investment to double (AKA 'The Rule of 72') in Excel?

تنظیم شده در تاریخ: ۱۳۹۷/۰۴/۲۵
A:

You can calculate the approximate amount of years it would take an investment to double, given the annual expected rate of return. To do so, you would need to implement the rule of 72 in Microsoft Excel.

The rule of 72 states that to determine the approximate amount of time it takes to double your investment at a given rate of return, you simply divide the rate of return by 72. For example, assume you invest $10 at an interest rate of 15%. It would take 4.8 years (72/15) to double your money to $20.

Assume you want to compare the actual number of years with the approximate number of years it would take for five different investments to double. Assume the five investments have expected rates of return of 5%, 10%, 13%, 15% and 20%. In Microsoft Excel, increase the widths of columns A, B, C and D by right-clicking on each respective column and left-clicking on Column Width and change the value to 35.

Make the typeset bold for the titles by pressing the CTRL and B keys together. Now, 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.

Enter "5" into cell A2, "10" into cell A3, "13" into cell A4, "15" into cell A5 and "20" into cell A6. The formula to calculate the actual number of years it takes to double your investment is the natural log of 2 divided by the natural log of 1 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. In cell B2, enter "=(LN(2)/(LN(1+A2/100)))". Now, left click and hold on the bottom right corner of cell B2 and drag the cell down to cell B6.

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.

To calculate the difference between the actual number of years and the approximate number of years calculated using the rule of 72, enter "=ABS(C2-B2)" into cell D2. Next, select cell D2, left-click and hold on the bottom right corner of the cell and drag the cell down to cell D6. The resulting values are the absolute values of the differences between the actual number of years and the approximate number of years.

The rule of 72 is a good approximation of the amount of years it takes to double your investment because the approximations are within 0.2 year of the actual number of years.


آیا این مقاله برای شما مفید بود؟
مقالات مربتط