The Orange Mane -  a Denver Broncos Fan Community  

Go Back   The Orange Mane - a Denver Broncos Fan Community > Orange Mane Discussion > Orange Mane Central Discussion
Register FAQ Members List Calendar Chat Room Mark Forums Read



Reply
 
Thread Tools Display Modes
Old 08-04-2013, 09:24 PM   #1
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
Eat greedy

Join Date: Apr 2002
Location: Boulder... Vail when it snoooows
Posts: 16,642

Adopt-a-Bronco:
Lamin Barrow
Default Economists/finance guys... or any math/computer science/excel whizes

Anyone know why Excel wants a "guess" when trying to compute the Internal Rate of Return in corporate financial cashflow statements?

I'm getting a -50% IRR, and by intuition I know thats wrong. (Initial cash investment of $4mil, with an NPV of -$108,869.)

I'd post the cash flow myself, but unfortunately it's confidential.
Mogulseeker is offline   Reply With Quote
Sponsored Links
Old 08-04-2013, 09:26 PM   #2
Eldorado
Formerly orange&blue
 
Eldorado's Avatar
 

Join Date: Sep 2007
Posts: 2,288

Adopt-a-Bronco:
Pot Roast
Default

You did something wrong.
Eldorado is offline   Reply With Quote
Old 08-04-2013, 09:29 PM   #3
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
Eat greedy

Join Date: Apr 2002
Location: Boulder... Vail when it snoooows
Posts: 16,642

Adopt-a-Bronco:
Lamin Barrow
Default

Quote:
Originally Posted by Eldorado View Post
You did something wrong.
The IRR function always requires a guess... because you can come to several different conclusions when you're putting in different variables - capital gains tax, inflation, discount rates....

I'm getting -50 percent when it should be -4.2 percent.

The function is IRR(cell a:cell z, guess).... I try to plug ing "4%" as a guess, and it returns -50%.

I know I'm doing something wrong.
Mogulseeker is offline   Reply With Quote
Old 08-04-2013, 09:32 PM   #4
schaaf
Guest
 

Posts: n/a
Default

Sounds like your math is wrong....
  Reply With Quote
Old 08-04-2013, 09:27 PM   #5
DenverBound
Thanks for the memories
 
DenverBound's Avatar
 
This one's for Pat!

Join Date: Oct 2009
Posts: 998
Default

You really think this is the forum for this? We get it. You're smart.

Repeat after me...

You is smart. You is kind. You is beautiful.
DenverBound is offline   Reply With Quote
Old 08-04-2013, 09:33 PM   #6
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
Eat greedy

Join Date: Apr 2002
Location: Boulder... Vail when it snoooows
Posts: 16,642

Adopt-a-Bronco:
Lamin Barrow
Default

Quote:
Originally Posted by DenverBound View Post
You really think this is the forum for this? We get it. You're smart.

Repeat after me...

You is smart. You is kind. You is beautiful.
If I were smart I wouldn't post something like this... there are plenty of people here of whom I would trust their advice on this - BroncoSteven, Kaylore, GarciaBronco - all smarter people than me.
Mogulseeker is offline   Reply With Quote
Old 08-05-2013, 06:36 PM   #7
ak1971
uhhhh
 
ak1971's Avatar
 

Join Date: Oct 2003
Posts: 5,761
Default

Quote:
Originally Posted by Swedish Extrovert View Post
If I were smart I wouldn't post something like this... there are plenty of people here of whom I would trust their advice on this - BroncoSteven, Kaylore, GarciaBronco - all smarter people than me.
You do know that Kaylore works in a cell phone kiosk in the Aurora Mall, BroncoSteven likes to hum showtunes with his farts..
ak1971 is offline   Reply With Quote
Old 08-05-2013, 08:32 PM   #8
broncosteven
Kranz Dictum
 
broncosteven's Avatar
 
Where is Amazon!

Join Date: Jun 2005
Location: Tranquility Base
Posts: 35,687

Adopt-a-Bronco:
CJ Anderson!
Default

Quote:
Originally Posted by ak1971 View Post
You do know that Kaylore works in a cell phone kiosk in the Aurora Mall, BroncoSteven likes to hum showtunes with his farts..
I almost got this down without the humming part!

http://www.youtube.com/watch?feature...DjQ1-T3z8&t=45
broncosteven is offline   Reply With Quote
Old 08-05-2013, 09:04 PM   #9
ak1971
uhhhh
 
ak1971's Avatar
 

Join Date: Oct 2003
Posts: 5,761
Default

Quote:
Originally Posted by broncosteven View Post
I almost got this down without the humming part!

http://www.youtube.com/watch?feature...DjQ1-T3z8&t=45
I was thinking more along these lines


ak1971 is offline   Reply With Quote
Old 08-05-2013, 09:40 PM   #10
Kaylore
Because I am better
 
Kaylore's Avatar
 
Taysom Hill for Heisman!

Join Date: Aug 2004
Location: Ceti Alpha V
Posts: 46,481

Adopt-a-Bronco:
Pat Bowlen
Default

Quote:
Originally Posted by broncosteven View Post
I almost got this down without the humming part!

http://www.youtube.com/watch?feature...DjQ1-T3z8&t=45
Our band is going to spread like wildflowers!
Kaylore is offline   Reply With Quote
Old 08-05-2013, 10:05 PM   #11
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
Eat greedy

Join Date: Apr 2002
Location: Boulder... Vail when it snoooows
Posts: 16,642

Adopt-a-Bronco:
Lamin Barrow
Default

Quote:
Originally Posted by ak1971 View Post
You do know that Kaylore works in a cell phone kiosk in the Aurora Mall, BroncoSteven likes to hum showtunes with his farts..
I find that hard to believe considering Kaylore went to BYU.

I currently make a living building, selling and installing batteries for cars/power tools/security systems, etc. That's why I'm trying to learn this stuff.
Mogulseeker is offline   Reply With Quote
Old 08-05-2013, 10:07 PM   #12
SoCalBronco
Nixonite
 
SoCalBronco's Avatar
 

Join Date: Aug 2001
Location: Arcadia, CA
Posts: 36,214

Adopt-a-Bronco:
D.J. Williams
Default

Quote:
Originally Posted by Swedish Extrovert View Post
I find that hard to believe considering Kaylore went to BYU.

I currently make a living building, selling and installing batteries for cars/power tools/security systems, etc. That's why I'm trying to learn this stuff.
You should have taken the actuary job with Berkshire Hathaway.
__________________
SoCalBronco is offline   Reply With Quote
Old 08-05-2013, 10:20 PM   #13
ak1971
uhhhh
 
ak1971's Avatar
 

Join Date: Oct 2003
Posts: 5,761
Default

Quote:
Originally Posted by Swedish Extrovert View Post
I find that hard to believe considering Kaylore went to BYU.

.
Yes..then I have to run him and all his drunkard friends from huffing paint and passing out on my lawn.
ak1971 is offline   Reply With Quote
Old 08-04-2013, 09:33 PM   #14
LonghornBronco
Ring of Famer
 
Hook'em Horns

Join Date: Jul 2006
Location: Austin
Posts: 1,421

Adopt-a-Bronco:
Rahim Moore
Default

Initial investment should be negative,no?
LonghornBronco is offline   Reply With Quote
Old 08-04-2013, 09:33 PM   #15
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
Eat greedy

Join Date: Apr 2002
Location: Boulder... Vail when it snoooows
Posts: 16,642

Adopt-a-Bronco:
Lamin Barrow
Default

Quote:
Originally Posted by LonghornBronco View Post
Initial investment should be negative,no?
That's correct, initial cash flow in year 0 of -$4,000,000

The only thing that would be different if the initial cost was wrong would be the sign.

Last edited by Mogulseeker; 08-04-2013 at 09:39 PM..
Mogulseeker is offline   Reply With Quote
Old 08-04-2013, 09:49 PM   #16
Jekyll15Hyde
Anti Frown Cannon & McD..
 
Jekyll15Hyde's Avatar
 

Join Date: Sep 2008
Posts: 1,816
Default

Its an optional argument to the formula. Its there from old versions I believe as excel uses an iterative technique and without a starting "guess" the computations could take a long time for a finite answer (ie 22.321321324% vs 22.32%). However, I think this was really an issue many years back as today's CPUs don't break a sweat to quickly come up with a result.
Jekyll15Hyde is offline   Reply With Quote
Old 08-04-2013, 09:52 PM   #17
Jekyll15Hyde
Anti Frown Cannon & McD..
 
Jekyll15Hyde's Avatar
 

Join Date: Sep 2008
Posts: 1,816
Default

Oh - IRR is the discount rate where NPV = 0.

So a negative NPV should have a negative IRR.
Jekyll15Hyde is offline   Reply With Quote
Old 08-04-2013, 09:54 PM   #18
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
Eat greedy

Join Date: Apr 2002
Location: Boulder... Vail when it snoooows
Posts: 16,642

Adopt-a-Bronco:
Lamin Barrow
Default

Quote:
Originally Posted by Jekyll15Hyde View Post
Oh - IRR is the discount rate where NPV = 0.

So a negative NPV should have a negative IRR.
So if I'm calculating NPV of a group of cells, do I calculate the IRR of the same group of cells?
Mogulseeker is offline   Reply With Quote
Old 08-04-2013, 10:03 PM   #19
Jekyll15Hyde
Anti Frown Cannon & McD..
 
Jekyll15Hyde's Avatar
 

Join Date: Sep 2008
Posts: 1,816
Default

Quote:
Originally Posted by Swedish Extrovert View Post
So if I'm calculating NPV of a group of cells, do I calculate the IRR of the same group of cells?
Not the way I do it...
IRR grabs all the cells including the initial investment as a negative number.

On NPV, I take the year 0 initial investment and add it the NPV of year 1 through year N, with a specified discount rate.

So I have have CF of 4 years in A1 through D1 (A1 is investment), my formulas are:
IRR
=IRR(A1: D1)
NPV
=A1+NPV(Rate,B1: D1)

Hope I am explaining clearly. Dog ass tired tonight.
Jekyll15Hyde is offline   Reply With Quote
Old 08-04-2013, 10:08 PM   #20
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
Eat greedy

Join Date: Apr 2002
Location: Boulder... Vail when it snoooows
Posts: 16,642

Adopt-a-Bronco:
Lamin Barrow
Default

Quote:
Originally Posted by Jekyll15Hyde View Post
Not the way I do it...
IRR grabs all the cells including the initial investment as a negative number.

On NPV, I take the year 0 initial investment and add it the NPV of year 1 through year N, with a specified discount rate.

So I have have CF of 4 years in A1 through D1 (A1 is investment), my formulas are:
IRR
=IRR(A1: D1)
NPV
=A1+NPV(Rate,B1: D1)

Hope I am explaining clearly. Dog ass tired tonight.
No, that's crystal... I got the NPV function right, and I need to include the first investment in IRR.

Now... why it still asks for a 'guess' is still dumb in my opinion.
Mogulseeker is offline   Reply With Quote
Old 08-04-2013, 10:13 PM   #21
Jekyll15Hyde
Anti Frown Cannon & McD..
 
Jekyll15Hyde's Avatar
 

Join Date: Sep 2008
Posts: 1,816
Default

Quote:
Originally Posted by Swedish Extrovert View Post
No, that's crystal... I got the NPV function right, and I need to include the first investment in IRR.

Now... why it still asks for a 'guess' is still dumb in my opinion.
You should see square brackets around the Guess argument. That means its optional. I never use it. I remember a finance prof telling me it was unnecessary.

Feel free to PM me for more help. Will have a look in the AM.

Last edited by Jekyll15Hyde; 08-04-2013 at 10:18 PM..
Jekyll15Hyde is offline   Reply With Quote
Old 08-04-2013, 09:54 PM   #22
baja
Happy camper
 
baja's Avatar
 
Sweet

Join Date: Apr 2001
Location: in the present moment
Posts: 60,023

Adopt-a-Bronco:
Ware
Default

If you make a negative investment it means you have a lot of Gummy Bears.
baja is offline   Reply With Quote
Old 08-04-2013, 10:00 PM   #23
Broncos4tw
Ring of Famer
 

Join Date: Sep 2005
Posts: 1,445
Default

If you are calculating that sort of money for a client (or your employer), do you really honestly think that a football forum is the place you'll get reliable answers?

I'd highly recommend contacting MS instead. I'm a computer professional.. and I'd not even try to answer your question, due to the fact it's actual money for a company. I'd recommend making sure you have the right answer. Not an inebriated answer from us fools. : p
Broncos4tw is offline   Reply With Quote
Old 08-04-2013, 10:10 PM   #24
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
Eat greedy

Join Date: Apr 2002
Location: Boulder... Vail when it snoooows
Posts: 16,642

Adopt-a-Bronco:
Lamin Barrow
Default

Quote:
Originally Posted by Broncos4tw View Post
If you are calculating that sort of money for a client (or your employer), do you really honestly think that a football forum is the place you'll get reliable answers?

I'd highly recommend contacting MS instead. I'm a computer professional.. and I'd not even try to answer your question, due to the fact it's actual money for a company. I'd recommend making sure you have the right answer. Not an inebriated answer from us fools. : p
You're absolutely right. I'm just kicking the tires on a project. I already know what the data is from computing it freehand, I'm just trying to get excel to show the same.
Mogulseeker is offline   Reply With Quote
Old 08-05-2013, 07:22 AM   #25
DenverBound
Thanks for the memories
 
DenverBound's Avatar
 
This one's for Pat!

Join Date: Oct 2009
Posts: 998
Default

Quote:
Originally Posted by Swedish Extrovert View Post
You're absolutely right. I'm just kicking the tires on a project. I already know what the data is from computing it freehand, I'm just trying to get excel to show the same.
No you're not. You're just patting your self in the back. It's kind of pathetic.
DenverBound is offline   Reply With Quote
Reply

Thread Tools
Display Modes



Forum Jump


All times are GMT -7. The time now is 03:38 AM.


Denver Broncos