IRR Formulae Problems

  roger 09:22 25 Jul 03

I do not know if this is the right room but any help from anyone will be much appreciated.

I have prepared an Excel spreadsheet where the interest and income periods are quarterly (they could be monthly)

The excel formulae is: IRR(values,guess)where value is a range. However this is for a one year period. If I block each group of four quarters together as a year and, assuming a four year period for the plan, I get a completely different result to that which I get using a different formulae which is:

(1*(1+IRR(range,guess))^periods pa)-1

where I leave the columns as quarters and insert "4" in "periods pa"

However this second formulae gives the same result to that given by the Excel one where I blocked the four quarterly periods into one period pa.

So the formulae is right but seems to be more flexible. The question is - which is the right answer - to block into yearly periods or to leave in quarterly ones?

The reason I am going to this trouble is the difference is not insignificant.

In my case blocking into yearly periods gives an IRR of 57.18% and leaving as quarters an IRR of 42.84%

OR - am I doing something WRONG!!!!!!!!!!!!

Please help

Many thanks


  recap 12:20 25 Jul 03

One for VoG I think.

  VoG II 12:27 25 Jul 03

Sorry, I don't use the financial functions in Excel.

However click here

  roger 15:34 25 Jul 03

Thanks VoG and cezar for your comments. Unfortunately I still have to resolve the point. Does anyone else have any thoughts.


  Alan2 16:49 25 Jul 03

A note of caution.

It's not a good idea to use powerful tools such as those found in Excel unless you are confident about their application.

You should test functions on simple models for which you can calculate - by hand - the expected result.

I know this from my early engineering experiences when I had to know the ball-park results for very complex calculations (done by slide rule, Facit or pocket calculator) - I continue to apply the principle.


  roger 17:05 25 Jul 03


Thanks for the word of caution. Point well made and I will bear it clearly in mind!


  skeletal 22:18 25 Jul 03

I have not used this function before, but, like your equation it is, or is the equivalent of, a power series of some sort. These will often give different results with only small changes in parameters.

The best explanation I can give for my waffle is building society interest paid monthly: if they paid 1% per month (I wish!), you may think you get 12% per year, WRONG, its12.6%. 2% per month is not 24% its 26.8% (because you get interest on the interest, or in your case, cash flow on the cash flow).

You have to be very careful to understand the exact meaning of each parameter. I agree with Alan, try to do a manual calc in the way you want the numbers to work.

Sorry if you know this and its another problem entirely!


  jazzypop 00:41 26 Jul 03

Further info, references and worked examples -

click here

click here

click here

click here

click here

  roger 11:57 26 Jul 03

Thanks to both Skeletal and jazzypop for the input. I will go away and use the grey matter now!


This thread is now locked and can not be replied to.

What is ransomware and how do I protect my PC from WannaCry?

Disney layout supervisor Rob Dressel on the challenges of visualising Moana

Siri vs Google Assistant