The Orange Mane -  a Denver Broncos Fan Community

The Orange Mane - a Denver Broncos Fan Community (http://www.orangemane.com/BB/index.php)
-   Orange Mane Central Discussion (http://www.orangemane.com/BB/forumdisplay.php?f=6)
-   -   Economists/finance guys... or any math/computer science/excel whizes (http://www.orangemane.com/BB/showthread.php?t=111629)

Mogulseeker 08-04-2013 09:24 PM

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.

Eldorado 08-04-2013 09:26 PM

You did something wrong.

DenverBound 08-04-2013 09:27 PM

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.

Mogulseeker 08-04-2013 09:29 PM

Quote:

Originally Posted by Eldorado (Post 3887719)
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.

08-04-2013 09:32 PM

Sounds like your math is wrong.... :)

LonghornBronco 08-04-2013 09:33 PM

Initial investment should be negative,no?

Mogulseeker 08-04-2013 09:33 PM

Quote:

Originally Posted by DenverBound (Post 3887720)
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 08-04-2013 09:33 PM

Quote:

Originally Posted by LonghornBronco (Post 3887726)
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.

Jekyll15Hyde 08-04-2013 09:49 PM

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 08-04-2013 09:52 PM

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

So a negative NPV should have a negative IRR.

baja 08-04-2013 09:54 PM

If you make a negative investment it means you have a lot of Gummy Bears.

Mogulseeker 08-04-2013 09:54 PM

Quote:

Originally Posted by Jekyll15Hyde (Post 3887732)
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?

Broncos4tw 08-04-2013 10:00 PM

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

Jekyll15Hyde 08-04-2013 10:03 PM

Quote:

Originally Posted by Swedish Extrovert (Post 3887735)
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.

Mogulseeker 08-04-2013 10:08 PM

Quote:

Originally Posted by Jekyll15Hyde (Post 3887737)
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 08-04-2013 10:10 PM

Quote:

Originally Posted by Broncos4tw (Post 3887736)
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.

Jekyll15Hyde 08-04-2013 10:13 PM

Quote:

Originally Posted by Swedish Extrovert (Post 3887739)
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.

ZONA 08-04-2013 11:07 PM

The answer is always E=MC2

GreatBronco16 08-05-2013 03:42 AM

18. You can't go wrong with 18.:thumbs:

gyldenlove 08-05-2013 04:09 AM

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

Rohirrim 08-05-2013 07:00 AM

http://myskylanterns.files.wordpress...pg?w=245&h=300

DenverBound 08-05-2013 07:22 AM

Quote:

Originally Posted by Swedish Extrovert (Post 3887740)
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.

SeedReaver 08-05-2013 07:35 AM

Quote:

Originally Posted by gyldenlove (Post 3887766)
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:

http://upload.wikimedia.org/wikipedi...pe-cartoon.png

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
http://24.media.tumblr.com/33102f3a4...xzspo1_400.jpg

TheReverend 08-05-2013 07:36 AM

1. Why are you using Excel for this?

2. Why does a multimillion dollar company not already have the template completed?

Kaylore 08-05-2013 07:37 AM

I'm really good at Mass Effect 3.


All times are GMT -7. The time now is 04:51 AM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.