Excel Question: Formula for Time to Multiply Balance with Periodic Investments

[simonsez]

Does anyone know an Excel formula that it takes for an investment to multiply with equal periodic investments?

Example:  Say I started with $0 and was going to have $10,000 to invest each year @ 5% interest (10500 after year 1, 21025 after year 2, 32076 after year 3, etc.).  I want to know if there is a formula that can account for that subsequent 10k while knowing the time to get to, say, 50k, 100k, etc.  For 100k, I can see that at the end of year 8 there would be just over 96k so I know it has to be just a couple months into year 9 (like 8.2 or 8.3 something if I had to guess) but a formula spitting that out exactly would be lovely (as then I could quickly know durations for many different interest rates and other milestones).

After a cursory search where every combination of search terms I could think of only yielded Rule of 72 links, things related to the Rule of 72 seem to only take into account the initial investment.  Before I fill up spreadsheets with daily amounts to crudely brute force what the duration would be to certain milestones, I want to check this forum to see if I'm overlooking/ignorant-to a formula that accounts for additional investments.

Any ideas?  I don't care if the compounding is continuous, monthly, daily, etc. - whatever works will be close enough (plus I'm guessing if a formula exists, it would be easy to change the type of compounding anyway) as this is a thought exercise on durations to milestones and how they get easier once your nest egg has gotten going.  i.e. The duration from 400k to 500k is shorter than 0k to 100k when investing 10k per year @ 5%.  I just want to know by how much and what the durations are.

[MDM]

Perhaps NPER function - Office Support.

[dblaace]

One of these might help.

https://www.calculatorsoup.com/calculators/financial/index-time-value-of-money-calculators.php

https://www.calculatorsoup.com/calculators/financial/future-value-calculator.php#formulas

[simonsez]

Thanks! I think the NPER function works!

=nper(.05,-10000,0,100000) gives 8.31 meaning it takes 8.31 years starting from 0, investing 10k per year @ 5% to get to 100k. 

To go from 100k to 200k under the same conditions takes less than 6 years.  Nice to have the momentum of the nest egg do more and more of the heavy lifting once you get it rolling.

Cheers,

[MDM]

Thanks! I think the NPER function works!

=nper(.05,-10000,0,100000) gives 8.31 meaning it takes 8.31 years starting from 0, investing 10k per year @ 5% to get to 100k. 

To go from 100k to 200k under the same conditions takes less than 6 years.  Nice to have the momentum of the nest egg do more and more of the heavy lifting once you get it rolling.

Cheers,

Yes, that's it.

For more esoteric situations, there is some underlying math in http://forum.mrmoneymustache.com/ask-a-mustachian/fire-in-8-years/.

[simonsez]

Here's the table I made for anyone curious, please excuse the table formatting:

Number of Years to Save Additional 100k
   Avg Int Rate                  
Amt   3%      4%   5%    6%    7%     8%      9%
Starting at $0                     
100k   8.88   8.58   8.31   8.07   7.84   7.64   7.45
200k   7.02   6.41   5.90   5.47   5.10   4.78   4.50
300k   5.81   5.12   4.57   4.14   3.78   3.49   3.23
400k   4.96   4.26   3.74   3.33   3.01   2.75   2.53
500k   4.32   3.65   3.16   2.79   2.50   2.27   2.07
600k   3.83   3.19   2.74   2.40   2.14   1.93   1.76
700k   3.44   2.84   2.41   2.10   1.87   1.68   1.53
800k   3.13   2.55   2.16   1.87   1.66   1.49   1.35
900k   2.86   2.32   1.95   1.69   1.49   1.33   1.21
1m     2.64   2.13   1.78   1.54   1.35   1.21   1.09
1.1m   2.45   1.96   1.64   1.41   1.24   1.11   1.00
1.2m   2.28   1.82   1.52   1.30   1.14   1.02   0.92
1.3m   2.14   1.70   1.41   1.21   1.06   0.95   0.85
1.4m   2.01   1.59   1.32   1.13   0.99   0.88   0.79
1.5m   1.90   1.50   1.24   1.06   0.93   0.83   0.74

Thanks again @MDM, I will check out that link.