IRR in Excel

How to calculate IRR (the Internal Rate of Return) in Excel? And once you've calculated it, how to interpret IRR? #IRR is used in #investmentanalysis and #capitalbudgeting. IRR is the discount rate at which NPV becomes 0.
Philip de Vroe (The Finance Storyteller) aims to make strategy, finance and leadership enjoyable and easier to understand. Learn the business and accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better stock market investment decisions. Philip delivers finance training in various formats: KZbin videos, classroom sessions, webinars, and business simulations. Connect with me through Linked In!

[@TheFinanceStoryteller]

Enjoyed this video? Then subscribe to the channel right now, and watch the explanation of how the NPV and IRR concepts work next: kzbin.info/www/bejne/fKiYXqqZmLucha8

[@jordanhehe]

This short video is more informative than my 3 hours online class last week. Thank you very much Sir 🙏

[@TheFinanceStoryteller]

Happy to help! More related videos on various concepts used in capital budgeting in this playlist: kzbin.info/www/bejne/hF7Pf2iuhLd8rcU

[@SpaghettiWithMeatSauce]

Excellent video. Straight to business. Clear and succinct. You summed up in a 2 min video what my overly wordy Corporate Finance textbook was trying to convey in lord knows how many pages.

[@TheFinanceStoryteller]

Yeah, let's get it done, and we're off to the beach! 😉 More related concepts in this playlist: kzbin.info/aero/PLKbmcnUUQMlkkCqQs7M_b6ktTDLITcRoG

[@markwesley8940]

Your videos are so helpful for someone like me, taking a self directed course with no lectures.

[@TheFinanceStoryteller]

Happy to hear that, Mark! I like the idea of a self directed course, going with the flow... In every video on my channel, there are the cards in the top right suggesting related videos, as well as the endscreens that propose the next video to watch.

[@alexandervalladares2501]

Very simple straight to the point explanation... Thank you

[@TheFinanceStoryteller]

Happy to help! 🙂

[@jericho3294]

Thank you so mucn, you explained it so clearly and it was easy to follow. Just sat through a 2 hour lecture and this explained it better than that. You should have more subs, absolute legend

[@TheFinanceStoryteller]

You're very welcome, Jericho! Please share the link to the video with your fellow students, and let's ace that upcoming test. ;-) Here's a useful playlist with related concepts like NPV, WACC, etc.: kzbin.info/www/bejne/hF7Pf2iuhLd8rcU

[@abidisu1679]

God bless you for this upload

[@TheFinanceStoryteller]

You're welcome!!! :-)

[@Adrian-tw8un]

@@TheFinanceStoryteller thank you 👍👍

[@pavaninisha6119]

Just subscribed to this gem of a channel! You made the explanation so simple and easy to understand, thank you so much and i hope you dont stop making videos because they are so easy to digest with your way of teaching.

[@TheFinanceStoryteller]

Thank you for the kind words, Pavani Nisha! Yes, I do plan to continue making new videos, and enjoy the process a lot. Hope to have a new one ready in the next few days.

[@StarvrosMitchell]

This was very helpful and created to be easily understood.

[@TheFinanceStoryteller]

Very happy to hear that! Related videos on NPV and WACC in this playlist: kzbin.info/www/bejne/hF7Pf2iuhLd8rcU&pp=gAQBiAQB

[@KindagoodAtguitar]

Thank you so much for this video it taught me more than my teacher has all year!

[@TheFinanceStoryteller]

You're welcome! Thank you for the kind words to me, and sorry to hear that your teacher couldn't get this concept across to you. Here's the link to my playlist on capital budgeting (NPV IRR WACC profitability index etc.), there might be more in there that could of use to you: kzbin.info/www/bejne/hF7Pf2iuhLd8rcU

[@johncollins8511]

Dude, you should be teaching my finance class. Your way better than my crappy teacher.

[@TheFinanceStoryteller]

Thanks for the compliment, John! I have over 200 videos on this channel, I hope some of them can help you. Here is the link to the ones on capital budgeting like NPV IRR WACC payback kzbin.info/www/bejne/hF7Pf2iuhLd8rcU

[@adityakejriwal1376]

thanks a lot man... really helpful may God bless you ✌🏽❤️💯

[@TheFinanceStoryteller]

You're welcome! 🙂

[@Fishman573]

This was fantastic excellent work and excellent clarity

[@TheFinanceStoryteller]

Great to hear that! Thanks for the kind words. Here is the link to related videos in the series (NPV, IRR, WACC, hurdle rate, profitability index): kzbin.info/www/bejne/hF7Pf2iuhLd8rcU

[@ravingagunawardana178]

Concise!!!! Nice Work!

[@TheFinanceStoryteller]

Glad it was helpful!

[@ginotarabotto]

Very nicely explained!

[@TheFinanceStoryteller]

Glad it was helpful!

[@omnirealtyassoc]

Great information! Thank you!

[@TheFinanceStoryteller]

Glad it was helpful!

[@AnkushSharma-zv5hv]

thank you so much

[@TheFinanceStoryteller]

You're welcome!

[@cbdbotanicaltherapy]

GREAT content! Do you have multi-family commercial real estate analysis spreadsheets available?

[@TheFinanceStoryteller]

Thank you for the kind words. No, I am not an expert in commercial real estate, so cannot help you there unfortunately.

[@skies4436]

Is there a standard for IRR? Or it is automatically a good investment if IRR is higher than discount rate?

[@TheFinanceStoryteller]

There is no "absolute" standard, it varies by company. A company would want the IRR to be larger than WACC. See my videos "IRR vs WACC" and "WACC vs hurdle rate".

[@elhussienehab4061]

well done

[@TheFinanceStoryteller]

Thank you!

[@johnballo2402]

I have a project with real data info... Invested amount: $1600. i (WACC) = 15%=0.15 The company is expected to generate: Year 1 = $21.600 benefits of $18,782.6 Year 2 = $29,880 benefits of $29,880 Year 3 = $44,280 benefits of $29,114.8 NPV = $68,891 As I calculated the IRR with 15% of WACC it gave me more than 100% it's like thousands of percentage... so can the IRR be considered 100% and does it exists when IRR is more than 100%??? Because the company can really generate this money each year... please sr I need your answer... thanks

[@TheFinanceStoryteller]

Hello John! Yes, an IRR can be more than 100%, if the investment base (amount) is small, and the expected returns are huge, like in your example. I did not replicate your calculation, as I understand the potential of the project already when I see $1600 investment (are you sure of this number?) versus benefits in tens of thousands per year! The payback time of this project is not even one month (which is extremely fast).

[@johnballo2402]

@@TheFinanceStoryteller yes sr I'm sure of the number because if a company can make $83 per day with wacc = 0.15 I think is possible to generate 22.000 on the second year...

[@abdikadirjama6537]

thanks genuis

[@TheFinanceStoryteller]

You're welcome! 🙂

[@dream7507]

The ans come different if we take the numbers vertically.

[@TheFinanceStoryteller]

Check your formulas very carefully, it should come out to the same number.

[@Jan-sd6nc]

Thanks for your helpful video. :) I have a question about using the Excel IRR formula for project valuation. I have financial statements for several years (e.g. from year 1 to 3). If I now want to calculate the IRR of the capital providers, wouldn't I have to determine the initial capital at time 0 and then adjust the Free CF of year 1 by the investments financed by the provided capital? If I would simply apply the IRR formula to the Free CF on the results of the years 1 to 3, I would shift otherwise the income of year 1 wrongly to the time 0 (thus as present value) or? To illustrate my question with an example. My project is financed for 100 E (to purchase equipment) and subsequently has an Operating CF of 50 E for each of 3 years each. The CF statement subsequently shows me a Free CF of -50 after year 1 and then +50 E for years 2 and 3. If I apply my IRR formula to this, I would have an IRR of 62%. But this would be wrong, my return would actually be 23%, because I have the income only after one year, but the investment right at the beginning? I hope my question is formulated understandably :)

[@TheFinanceStoryteller]

Hello there! In working with NPV and IRR, you usually make a few assumptions in other to simplify the modeling. Investments are "immediate" at t=0 (i.e. all the money is spent in one go), and benefits materialize "in one go" as a lump sum at the end of each period of twelve months (at t=1, t=2, t=3). So for your example I would use 4 separate years. FCF of -100 in year 0, then 3 years of FCF of 50. In other words, I agree that 23% would be the more representative return here.

[@Jan-sd6nc]

@@TheFinanceStoryteller Thanks for your quick feedback. I really apreciate that you always answer to my questions (was'nt the first time ;)). Your channel is really helpful. :)

[@TheFinanceStoryteller]

@@Jan-sd6nc Happy to help! Please spread the word to friends and colleagues.

[@jackeyzhu3702]

Instead of selecting the range of values (cash flows), how do just just type in the cash flow in the the irr function?

[@TheFinanceStoryteller]

Interesting suggestion, Jackey! I tried to type the values directly into the formula, but that does not seem to work. The IRR function wants you to put the data in a range of cells, and then refer to those cells in the formula.

[@jackeyzhu3702]

@@TheFinanceStoryteller I have tested it. The curly bracket will work. It will treat the inputs like a list like normal brackets in python.

[@TheFinanceStoryteller]

Cool! Thanks for letting me know!

[@PhilaFlyers100]

Youre an absolute beauty

[@TheFinanceStoryteller]

Thank you for watching and commenting!

[@jonny421]

What decision needs to be made if the IRR is equal to the discout factor

[@TheFinanceStoryteller]

Then you need to review all your assumptions as well as the project scope, and see if you can make it work after all (i.e. get the IRR to exceed the discount factor): kzbin.info/www/bejne/Z4u3eqqjo5Z2o5I

[@blakepugh8680]

why dont they use the present value to determine irr

[@TheFinanceStoryteller]

In IRR calculations, the main question is: what is the discount rate that makes the NPV equal to zero? The process is: 1) Determine nominal (undiscounted) cash flows 2) Set NPV at 0 3) Calculate Internal Rate of Return (IRR) through trial and error, or Excel Have a look at this video on the IRR concept for a better understanding: kzbin.info/www/bejne/l4SbiXuQa7OAabc&pp=gAQBiAQB

[@jaikarsingh3870]

HELLO, I MA FACING NUM ERROR WHEN I TRY TO CALCULATE THE IRR

[@TheFinanceStoryteller]

Are all the values in the cells that you refer to in the formula numbers? If you refer to text, it cannot calculate...

[@aquataylorr]

Hi how did you know to discount at 20%

[@TheFinanceStoryteller]

Hi! I just made that up to illustrate how the calculation works. More on time value of money in this video kzbin.info/www/bejne/nZzTXmqvnculm8k and specifically on the discount rate using WACC kzbin.info/www/bejne/Z4CQdZWqi9qYg9k

[@aquataylorr]

Hi how do I work out the higher discount

[@TheFinanceStoryteller]

Hello there! Besides the videos that I already linked to, here's one on WACC vs hurdle rate that should give you some context kzbin.info/www/bejne/bnbcd3-ahLmofrc

[@aquataylorr]

@@TheFinanceStoryteller thanks so much great help

[@TheFinanceStoryteller]

@@aquataylorr Please subscribe to the channel, and spread the word! :-)

[@abefinance109]

IRR formula gives me an error not sure how to fix

[@TheFinanceStoryteller]

Hi! Two things that jump to mind: 1) Do all the cells that you include in the calculation contain numerical data? 2) Do you have at least one cell that has an investment (cash out)?