Author Topic: deleted  (Read 5561 times)

henders

  • Guest
deleted
« on: November 24, 2015, 03:26:59 AM »
deleted
« Last Edit: June 26, 2017, 09:24:47 PM by henders »

arebelspy

  • Administrator
  • Senior Mustachian
  • *****
  • Posts: 28444
  • Age: -997
  • Location: Seattle, WA
Re: calculate percentage return in Excel
« Reply #1 on: November 24, 2015, 03:32:47 AM »
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.
I am a former teacher who accumulated a bunch of real estate, retired at 29, spent some time traveling the world full time and am now settled with three kids.
If you want to know more about me, this Business Insider profile tells the story pretty well.
I (rarely) blog at AdventuringAlong.com. Check out the Now page to see what I'm up to currently.

P0IS0N

  • 5 O'Clock Shadow
  • *
  • Posts: 23
  • Age: 39
  • Location: Kuwait
Re: calculate percentage return in Excel
« Reply #2 on: November 24, 2015, 03:42:19 AM »
I got 8.07 on moneychimp.

arebelspy

  • Administrator
  • Senior Mustachian
  • *****
  • Posts: 28444
  • Age: -997
  • Location: Seattle, WA
Re: calculate percentage return in Excel
« Reply #3 on: November 24, 2015, 04:19:50 AM »

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.  :)
I am a former teacher who accumulated a bunch of real estate, retired at 29, spent some time traveling the world full time and am now settled with three kids.
If you want to know more about me, this Business Insider profile tells the story pretty well.
I (rarely) blog at AdventuringAlong.com. Check out the Now page to see what I'm up to currently.

Aphalite

  • Bristles
  • ***
  • Posts: 425
Re: calculate percentage return in Excel
« Reply #4 on: November 24, 2015, 06:42:50 AM »
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

  • Administrator
  • Senior Mustachian
  • *****
  • Posts: 28444
  • Age: -997
  • Location: Seattle, WA
Re: calculate percentage return in Excel
« Reply #5 on: November 24, 2015, 06:46:31 AM »
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.

I am a former teacher who accumulated a bunch of real estate, retired at 29, spent some time traveling the world full time and am now settled with three kids.
If you want to know more about me, this Business Insider profile tells the story pretty well.
I (rarely) blog at AdventuringAlong.com. Check out the Now page to see what I'm up to currently.

Tjat

  • Pencil Stache
  • ****
  • Posts: 570
Re: calculate percentage return in Excel
« Reply #6 on: November 24, 2015, 07:50:34 AM »
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

  • Administrator
  • Senior Mustachian
  • *****
  • Posts: 28444
  • Age: -997
  • Location: Seattle, WA
Re: calculate percentage return in Excel
« Reply #7 on: November 24, 2015, 09:57:24 AM »
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.
I am a former teacher who accumulated a bunch of real estate, retired at 29, spent some time traveling the world full time and am now settled with three kids.
If you want to know more about me, this Business Insider profile tells the story pretty well.
I (rarely) blog at AdventuringAlong.com. Check out the Now page to see what I'm up to currently.

beltim

  • Magnum Stache
  • ******
  • Posts: 2957
Re: calculate percentage return in Excel
« Reply #8 on: November 24, 2015, 10:50:55 AM »
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

  • Administrator
  • Senior Mustachian
  • *****
  • Posts: 28444
  • Age: -997
  • Location: Seattle, WA
Re: calculate percentage return in Excel
« Reply #9 on: December 08, 2015, 02:42:37 AM »

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. :)
I am a former teacher who accumulated a bunch of real estate, retired at 29, spent some time traveling the world full time and am now settled with three kids.
If you want to know more about me, this Business Insider profile tells the story pretty well.
I (rarely) blog at AdventuringAlong.com. Check out the Now page to see what I'm up to currently.

Financial.Velociraptor

  • Handlebar Stache
  • *****
  • Posts: 2148
  • Age: 51
  • Location: Houston TX
  • Devour your prey raptors!
    • Living Universe Foundation
Re: calculate percentage return in Excel
« Reply #10 on: December 08, 2015, 08:56:40 AM »
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

  • Administrator
  • Senior Mustachian
  • *****
  • Posts: 28444
  • Age: -997
  • Location: Seattle, WA
Re: calculate percentage return in Excel
« Reply #11 on: December 08, 2015, 09:26:46 AM »

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?
I am a former teacher who accumulated a bunch of real estate, retired at 29, spent some time traveling the world full time and am now settled with three kids.
If you want to know more about me, this Business Insider profile tells the story pretty well.
I (rarely) blog at AdventuringAlong.com. Check out the Now page to see what I'm up to currently.

Financial.Velociraptor

  • Handlebar Stache
  • *****
  • Posts: 2148
  • Age: 51
  • Location: Houston TX
  • Devour your prey raptors!
    • Living Universe Foundation
Re: calculate percentage return in Excel
« Reply #12 on: December 08, 2015, 03:54:39 PM »

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

  • Pencil Stache
  • ****
  • Posts: 585
  • Age: 43
  • Location: Houston, TX
Re: calculate percentage return in Excel
« Reply #13 on: December 09, 2015, 10:24:38 AM »
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

  • Pencil Stache
  • ****
  • Posts: 585
  • Age: 43
  • Location: Houston, TX
Re: calculate percentage return in Excel
« Reply #14 on: December 09, 2015, 10:30:53 AM »
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

  • Pencil Stache
  • ****
  • Posts: 585
  • Age: 43
  • Location: Houston, TX
Re: calculate percentage return in Excel
« Reply #15 on: December 09, 2015, 10:34:31 AM »
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.

 

Wow, a phone plan for fifteen bucks!