Search Not Just Numbers

Loading...

Tuesday, 2 December 2014

Excel Tip: Calculating the internal rate of return for a project

Last week I introduced the PMT function for calculating payments on a loan.

This is one of the many functions under the Financial heading in Excel. This week I want to introduce you to another. One that allows you to calculate the internal rate of return (IRR) for a series of cash flows.

The IRR is a method often used to compare alternative projects or investments. and is stated as a percentage return, within a specified period.

As with the PMT function, it is worth starting by stating the assumptions used in the calculation. The IRR function requires the cash flows to be at regular intervals and the return percentage it calculates is  for the length of that interval. So, for example, if the cash flows fed to the function are annual cash flows, then the IRR returned will be a percentage per annum.

Also, the cash flows involved must include both positive and negative cash flows. If you think about this is self-evident, as any return percentage would be infinite if there was no outlay and all cash flows were positive!

Typically this will involve an initial outflow (or investment) followed by a series of projected inflows.

For example, a business investment opportunity might be presented as follows:

Initial outlay  £20,000
Year 1 positive cash flow  £500
Year 2 positive cash flow  £5,000
Year 3 positive cash flow  £8.200
Year 4 positive cash flow  £9,000
Year 5 positive cash flow  £9,000

We might want to compare it to another project and a useful piece of informationin doing this would be to calculate the IRR for each project.

So, how do we do it.

The IRR function syntax is as follows:

=IRR(values,[guess])

Most of the time you won't need the optional [guess] argument, but I will come to that in a minute.

values is the range of cells that show the cash flows in chronological order.

So, for our example we could enter the cash flows in cells B2 to B7 as follows:

B2  -20000
B3   500
B4   5000
B5   8200
B6   9000
B7   9000

Our function would then be:

=IRR(B2:B7)

which returns an IRR of 14% (rounded to zero decimal places). This means that over this 5 year period, the project has an internal rate of return of 14% per annum.

The [guess] argument is there because Excel uses an iterative process to calculate IRR and will give up if it has not arrived at it after 20 iterations. To do this it must start with an estimate. If the [guess] argument is omitted, an estimate of 10% (or 0.1) is used. For most projects this will work with no problems, however with cash flows that fluctuate wildly and/or have an unusually high or low return, it may not get there in the first 20 iterations, if it starts at 10%.

If this happens it will return a #NUM! error. You can then add a guess to the function so that it starts closer to the true answer and therefore can get there within the 20 iterations.

Next week we will look at calculating Net Present Value, which is the flipside of IRR.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

8 comments:

  1. Louis Vuitton HandbagIf Replica Louis Vuitton is the a lot of louis vuitton replica accepted best of fashionable woman,added arch brands continuously acquaintance huge demand.Also,Good-Handbag Chanel replica handbag,The Chanel replica backpack was aboriginal alien in February 1955- appropriately the name 2.55.Coco Chanel advised the backpack for her own use so some of the appearance acquaint us a little bit about her affairs as the aboriginal fashionista! Also,to amuse the tastes of chanel replica anniversary woman,We accumulation added than 20 high-quality Replicas Backpack and acclaimed cast Replicas Handbag,including replica of accoutrements by Chanel,Fendi,Balenciaga,Hermes,Gucci,And abounding added brands and models are on the Good-Handbag.Which allowance can be as admirable as Chanel replica handbags? You will acquaintance a abundant bulk of gucci replica achievement by alms Chanel replicas that are calmly accessible at affordable prices.However,Chanel replicas have to be accurate and of top quality.This is not an affair nowadays and a lot of Swiss replica watches accumulate time as accurately as the absolute stuff.The capital advantage of application these Swiss replica watches is that one can acquirement added than one of them and yet do not bother.Try to acquirement added than one aboriginal Swiss watch and you will see the cavity it makes in your wallet.For breitling replica the amount of one aboriginal Swiss watch,you can acquirement a amount of Swiss replica watches.The manufacturers of these Swiss replica watches consistently accumulate their banal up to date.As son as a new or adapted adaptation of a acclaimed Swiss watch is alien in the market,the artisans accomplishment the Swiss replica watches will accompany out a replica of that in a few weeks.

    ReplyDelete
  2. Didier Suberbielle has replica shoes worked for five years as Marketing Director and then replica handbags Commercial Director, on further developing Moet Chandon, the world's leader in champagne. He now takes lv replica up the presidency of Pommery with a cartier replica very good knowledge of consumers' needs and of the international market.

    ReplyDelete
  3. You are very detailed instructions, thanks

    ReplyDelete
  4. Looking for a design and style wholesale football shirts that affords lower servicing and care, nevertheless can look stylish and casual? Be in on the most cheap soccer jerseys uk current seems to be to attempt with our prime 5 black braided hairstyles that you can twist and braid your way to replica Gucci a stylish, straightforward search.Maybe the quickest way to give by yourself a makeover without having obtaining to spend an arm and a leg for an up to date wardrobe would be to Louis Vuitton replica handbags try out on a new hairstyle.

    With a quick go to to your stylist, you can very easily update your appear.Review the vendor's return policy! You should figure out if you can return the handbag to the vendor or be refunded if the item is not as described or replica watches for men counterfeit.Know the old adage "if it is too extremely great to be accurate, it most likely is" and "you get what you pay out for" is correct. You potentially can be assured that Fendi Spy Bag lovely this auction online, we commence to $ 249.00, one with the vibrant yellow replica handbags Uk tag is not the real thing. Nobody gives an Fendi bags Spy Bag for $ 249.00.

    ReplyDelete
  5. The second thing is, be sure and send an Omege Replica Watches age-send prior to putting in an order need to if the desired watch seems the same as the Replica Watches within the pictures. Convey to the company you will straight away contact your credit-based card business you get stolen if you achieve what that is different from this timpiece within the pictures. Also, you need to observe how expert the company was in answering and adjusting your age-send. Whenever they tend not to react to your Cartier Replica Watches age-send in the future, then certainly not have business dealings with this company. Forever use a credit card with 100Pct deception security. It's also possible to choose to cover your watch by money shipping in case you are made it possible for to see you buy the http://www.eworldwire.com/help.php car and then make a decision whether or not youare going to pay or perhaps not. Under no circumstances purchase IWC Replica below wholesale that won't offer you a money back guarantee.

    ReplyDelete
  6. The second thing is, be sure and send an Replica Watches UK age-send prior to putting in an order need to if the desired watch seems the same as the Replica Watches within the pictures. Convey to the company you will straight away contact your credit-based card business you get stolen if you achieve what that is different from this timpiece within the Replica Watches UK pictures. Also, you need to observe how expert the company was in answering and adjusting your age-send. Whenever they tend not to react to your age-send in the future, then certainly not have business dealings with this company. Forever use a credit card with 100Pct deception security. It's also possible to choose to cover your watch by money shipping in case you are made it possible for to see you buy the Replica Watches car and then make a decision whether or not youare going to pay or perhaps not. Under no circumstances purchase Replica Watches uk below wholesale that won't offer you a money back guarantee.

    ReplyDelete