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
 
https://soundcloud.com/mogulseeker

Join Date: Apr 2002
Location: Vail
Posts: 16,358

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,261

Adopt-a-Bronco:
Ayers
Default

You did something wrong.
Eldorado is offline   Reply With Quote
Old 08-04-2013, 09:27 PM   #3
DenverBound
Thanks for the memories
 
DenverBound's Avatar
 
This one's for Pat!

Join Date: Oct 2009
Posts: 932
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:29 PM   #4
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
https://soundcloud.com/mogulseeker

Join Date: Apr 2002
Location: Vail
Posts: 16,358

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   #5
schaaf
Guest
 

Posts: n/a
Default

Sounds like your math is wrong....
  Reply With Quote
Old 08-04-2013, 09:33 PM   #6
LonghornBronco
Ring of Famer
 
Hook'em Horns

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

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   #7
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
https://soundcloud.com/mogulseeker

Join Date: Apr 2002
Location: Vail
Posts: 16,358

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-04-2013, 09:33 PM   #8
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
https://soundcloud.com/mogulseeker

Join Date: Apr 2002
Location: Vail
Posts: 16,358

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   #9
Jekyll15Hyde
Anti Frown Cannon & McD..
 
Jekyll15Hyde's Avatar
 

Join Date: Sep 2008
Posts: 1,766
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   #10
Jekyll15Hyde
Anti Frown Cannon & McD..
 
Jekyll15Hyde's Avatar
 

Join Date: Sep 2008
Posts: 1,766
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   #11
baja
Pat Bowlen
 
baja's Avatar
 
The best owner ever

Join Date: Apr 2001
Location: in the present moment
Posts: 58,889

Adopt-a-Bronco:
Chase Vaughn
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, 09:54 PM   #12
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
https://soundcloud.com/mogulseeker

Join Date: Apr 2002
Location: Vail
Posts: 16,358

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:00 PM   #13
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:03 PM   #14
Jekyll15Hyde
Anti Frown Cannon & McD..
 
Jekyll15Hyde's Avatar
 

Join Date: Sep 2008
Posts: 1,766
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   #15
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
https://soundcloud.com/mogulseeker

Join Date: Apr 2002
Location: Vail
Posts: 16,358

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:10 PM   #16
Mogulseeker
Formerly mightysmurf
 
Mogulseeker's Avatar
 
https://soundcloud.com/mogulseeker

Join Date: Apr 2002
Location: Vail
Posts: 16,358

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-04-2013, 10:13 PM   #17
Jekyll15Hyde
Anti Frown Cannon & McD..
 
Jekyll15Hyde's Avatar
 

Join Date: Sep 2008
Posts: 1,766
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, 11:07 PM   #18
ZONA
Ring of Famer
 
ZONA's Avatar
 

Join Date: Jan 2006
Location: Phoenix, AZ
Posts: 9,916

Adopt-a-Bronco:
Chris Harris
Default

The answer is always E=MC2
ZONA is offline   Reply With Quote
Old 08-05-2013, 03:42 AM   #19
GreatBronco16
!!!TEAM!!!
 
GreatBronco16's Avatar
 

Join Date: Dec 2003
Location: Bama Baby
Posts: 6,386

Adopt-a-Bronco:
The Defense
Default

18. You can't go wrong with 18.
GreatBronco16 is offline   Reply With Quote
Old 08-05-2013, 04:09 AM   #20
gyldenlove
Ring of Famer
 
gyldenlove's Avatar
 

Join Date: Mar 2006
Location: Nęstved, DK
Posts: 10,921

Adopt-a-Bronco:
Spencer Larsen
Default

IRR uses Newtons method to calculate the rate, it is simple but fast. That is why you need a guess. Newtons method is very susceptible to falling in local minima if the initial guess is bad
gyldenlove is offline   Reply With Quote
Old 08-05-2013, 07:00 AM   #21
Rohirrim
Partisan
 
Rohirrim's Avatar
 

Join Date: Jan 2003
Location: Twixt Hell & Highwater
Posts: 53,993

Adopt-a-Bronco:
Malik Jackson
Default

Rohirrim is offline   Reply With Quote
Old 08-05-2013, 07:22 AM   #22
DenverBound
Thanks for the memories
 
DenverBound's Avatar
 
This one's for Pat!

Join Date: Oct 2009
Posts: 932
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
Old 08-05-2013, 07:35 AM   #23
SeedReaver
Seasoned Veteran
 

Join Date: Aug 2009
Location: Fort Collins, CO
Posts: 425
Default

Quote:
Originally Posted by gyldenlove View Post
IRR uses Newtons method to calculate the rate, it is simple but fast. That is why you need a guess. Newtons method is very susceptible to falling in local minima if the initial guess is bad
You must be a PhD if you know about optimization techniques (though Newton's method is quite simple).

Here is why you need a guess, MightySmurf. Iterative optimization techniques often use gradient based techniques that "find the best direction" to move in to optimize some function. Look at the following picture:



Say you are trying to minimize that wavey function. Many of the techniques Excel uses tries to "greedily" minimize, i.e., find the closest "hill" and move down it. If your initial guess is at point "A," many techniques will stop at the bottom of the valley (where the pink ball is). If you're initial guess is at point "C," you will most likely end up in the valley between "B" and "C." Of course, if you are trying to minimize that function, the valley where the pink ball is located is better than the valley between "B" and "C."

I know nothing about the IRR function or whatever, but
SeedReaver is offline   Reply With Quote
Old 08-05-2013, 07:36 AM   #24
TheReverend
www.PatrickTurley.org
 
TheReverend's Avatar
 
Not. Too. Shabby.

Join Date: Apr 2005
Location: Jacksonville, FL
Posts: 36,614

Adopt-a-Bronco:
Mike Shanahan
Default

1. Why are you using Excel for this?

2. Why does a multimillion dollar company not already have the template completed?
TheReverend is offline   Reply With Quote
Old 08-05-2013, 07:37 AM   #25
Kaylore
Shall we begin?
 
Kaylore's Avatar
 
Pete Carroll is a douche

Join Date: Aug 2004
Location: Ceti Alpha V
Posts: 45,564

Adopt-a-Bronco:
Pat Bowlen
Default

I'm really good at Mass Effect 3.
Kaylore is offline   Reply With Quote
Reply

Thread Tools
Display Modes



Forum Jump


All times are GMT -7. The time now is 05:52 PM.


Denver Broncos