deleted

[henders]

deleted

[arebelspy]

On my phone, so can't do the excel formula, but just google time value of money excel for it.

This site I use for quick calcs when I don't want to use Excel:
www.zenwealth.com/BusinessFinanceOnline/TVM/TVMCalculator.html

I get an 8.72% ROR required to hit 1.5MM with 100k now, 40k annually added for 15 years.

[P0IS0N]

I got 8.07 on moneychimp.

[arebelspy]

I got 8.07 on moneychimp.

IDK what that is, but this is a simple math problem, no like an opinion thing.

8.7245% is the correct answer, not the 6% from the OP's podcast, and not the 8.07% from moneychimp.  :)

[Aphalite]

Excel formula is:

=RATE(15,-40000,-100000,1500000)

you can also do positive 40k and 100k and negative 1.5m, as long as the signs are opposite - Excel assumes you're taking the $1.5m out immediately

Reb, the formula chimp gave is annuity due - so 40k was added at beginning of the period

OP, formula for that in excel is:

=RATE(15,-40000,-100000,1500000,1)

[arebelspy]

Why would you add 40k at the beginning of the period?  Then OP would have said he had 140k, not 100k.

I stand by my answer as the correct one.

[Tjat]

Arebelspy is correct if you assume that the 40K annual payment is made at the end of the year. However, a more realistic scenario is if the savings is made on the last day of every month. It'd be set up like the following

FV   -1,500,000
PV   100,000
PMT   3,333.33
NPER   180 (15*12)

The Rate formula would then return a monthly required rate of return, which when multiplied will 12 will give you the annual amount. This number is 8.104%

[arebelspy]

Good point, that's probably a more realistic assumption. 8.104% is probably the "best" answer.

I think most projections from experts are lower than that for the next 15 years (but what do they know?), so you may need to: save more per year OR work longer OR FIRE on less.

[beltim]

I hate to tell you all this, but investment returns are path-dependent, i.e. the rate of return that you need depends on when you get the returns.  The answers given so far assume a constant rate of return.  But consider the following two situations:

Situation 1:  The market does nothing for 15 years then jumps 114.28%. 
The OP will have accumulated 100k + 15* 40k = 700k
700k increases 114.28% to $1.5 million.  OP has reached his goal!

Situation 2: The market jumps 114.28% tomorrow!  Then, unfortunately, it does nothing for the next 15 years.
The OP's original 100k has grown to 228.6k.  The next 15 years of contributions add 600k.  OP ends up with 828.6k, far short of his goal.  It's worth noting that both Situations 1 and 2 have the same formulaic rate of return over the 15 year time period.

Obviously these are extreme examples, but they're just to demonstrate my larger point: there are an infinite number of solutions to OP's question if we consider the real world.  This path-dependency can work for investors, by the way: if dividends are reinvested when the market is down, they will add more to your portfolio after the market has gone up than if the market monotonically increased at a constant annual rate.

[arebelspy]

Hi all

Not sure what I'm doing wrong, but if I plugin the following in Excel, I get 0.67%

=RATE(180,-3333.33,-100000,1500000,1)

That's correct, but since you changed the nunbers to monthly (the periods and contributions), the rate is monthly. Multiply that cell by 12 to get the annual rate. :)

[Financial.Velociraptor]

For the true nurds -

Note the formula for (simple) CAGR is: (currentValue / originalValue)^(1 / ((currentDate - originalDate)/365)) - 1

I built an MS Excel spreadsheet to do this before but it was pretty messy with lots cells doing the various components of the formula.

[arebelspy]

For the true nurds -

Note the formula for (simple) CAGR is: (currentValue / originalValue)^(1 / ((currentDate - originalDate)/365)) - 1

I built an MS Excel spreadsheet to do this before but it was pretty messy with lots cells doing the various components of the formula.

How is that relevant to the OP's question?

[Financial.Velociraptor]

For the true nurds -

Note the formula for (simple) CAGR is: (currentValue / originalValue)^(1 / ((currentDate - originalDate)/365)) - 1

I built an MS Excel spreadsheet to do this before but it was pretty messy with lots cells doing the various components of the formula.

You got me.  I committed a thread foul.  Minus five yards on me.

How is that relevant to the OP's question?

[sirdoug007]

I got 8.07 on moneychimp.

Which moneychimp calculator did you use?  I don't see one that does this exact calculation.

http://www.moneychimp.com/calculator/

[sirdoug007]

I think I see now.  I get the 8.07% result with this calculator

http://www.moneychimp.com/calculator/compound_interest_calculator.htm

You manually change the interest rate until you get $1.5 million.

Inputs are:

Current Principal = $100,000
Annual Addition = $ 40,000
Years to Grow = 15
Interest Rate = 8.07
Compound interest 1 time annually
Make additions at start of each compounding period

So this calculator is making a $40,000 addition on January 1 each year.

If you set it for end of period you get 8.73%.

[sirdoug007]

And none of these numbers really matter.

You well get some return on your investments over the next 15 years and I guarantee it will not be 8.07 or 8.73%!  It may be higher, or could be lower.

There is no way to "tune" your investments to guarantee 8%+ returns either.  Keep saving as much as you can and keep your spending down (maybe that will even lower the $1.5 million needed) and you will get there.  Predicting when is impossible unfortunately.