Calculating NPV and IRR in Excel: A Step-by-Step Tutorial

[@leavesproduction1043]

Thank you for your material , I have watched over dozens of KZbin channels regatrding finance and working in Excel, and your tutorials are the most easy to understand and to follow. No extra time spend , will stay with your channel. Thank you

[@eric_andrews]

Means a lot, thanks !

[@Oveea-i9e]

Excellent video, Eric! I'm a CPA and I love the way that you have explained an often convoluted concept so lucidly. Thanks! :)

[@eric_andrews]

Wow! So happy to hear that Oveeya! Thanks for the feedback.

[@In2uCon]

This was extremely helpful… I got stuck on a project and this video pulled me right out. Thanks for sharing!!

[@eric_andrews]

Glad it helped!

[@erfansharifi7969]

Perfect explanation ❤️❤️❤️

[@eric_andrews]

Glad you think so Erfan! Cheers!

[@Rafiqazizada]

Thank you for such a great video. Plz keep up the good work.

[@eric_andrews]

Appreciate the comment Rafi! I will keep trying to release more helpful stuff!! Cheers 👍

[@macsocalixte9475]

Thank you for the great insights you provide about calculating NPV & IRR. It is very useful!

[@eric_andrews]

You are very welcome !

[@youssramouhssine2521]

Great video i love the way you explain things ❤️ keep going 👍👏 I hope do a video for online business on how to record & track monthly or daily sales & expenses

[@eric_andrews]

Coming soon!

[@abdelrahmanalzubaidi1917]

Thank you for the great video and for the great explanation !

[@eric_andrews]

Hey Abdel - thanks for checking it out I'm glad it was insightful for you 👍👍👍

[@Rocinante2021]

Amazing explanation mate, quite in depth. thank you so so so much!

[@eric_andrews]

Glad you liked it!

[@_tacotempura]

Thanks for the videos, Eric! Do you think you could shed some additional light on how to come to the 'guess' percentage for IRR and the WACC? Thanks!

[@eric_andrews]

Sure, in general you want to use higher discount rates (WACCs) for companies that are riskier (higher risk of not executing plan). Also, you need to compare discount rates to the current "risk free rates" which are government bonds. In our case, risk free rates are basically zero, so people are willing to lower discount rates right now to value companies (check out my video on the discounted cash flow method) thus driving valuations higher. Generally a very risky early stage company would be like 20-30% WACC, a high growth but more stable company might be like 15-20%, and a very safe company might be 10% or even lower, just to give you a range. But if government bonds yield more, people demand higher returns from equities and raise discount rates, and if government bonds yield less, people require lower returns on equities and lower discount rates. Make sense?

[@_tacotempura]

@@eric_andrews makes sense! thanks again

[@eric_andrews]

@@_tacotempura no problem happy to help

[@eric_andrews]

@@_tacotempura oh ya for the guess percentage on IRR it's completely meaningless. No particular strategy just put in a random number and the function Will solve for the real one.

[@_tacotempura]

@@eric_andrews gotcha that makes sense yeah my interview tomorrow is for an fpa role w an EV company and they said it was gonna center around a cash flow case study using Excel I’ve got excel shortcuts down pretty much but was looking for material to review dcf/cash flow fundamentals and found your vids haha - they’ve been super helpful trying to make sure I can apply the fundamental concepts to industry specific metrics/line items that may show up

[@zeenatamber3038]

Hi...your vedio was amazing...kindly also make a vedio about calculation of discount rate(WACC)

[@eric_andrews]

Hey Zeenat, really appreciate it. I'll put it on my list of videos to make, thanks for the suggestion 🙏🙏🙏

[@lazaca]

Great video!!!! Thanks so much

[@eric_andrews]

Laisha - thanks for letting me know. Really glad it was helpful

[@prabhavdhanesha7036]

Quite helpful! Thanks!

[@almagallardo61]

Hello thank you for the video, it is really helpful! Can you please let me know which is the formula of IRR without excel? How can it be calculated? Thanks :)

[@ChillForge101]

Hi Eric, great video! One question: For the PE- backed Biz, are the cash flows in 2024-2029 all dividends for the private equity firm and then 2030 is the sale price they get? Curious why they would get cash flows during those years but the pre-rev startup and public tech company don't. Thanks!

[@eric_andrews]

Hey sure, the cash flows are available to the owners of the business to do anything they want. That could be for the portfolio company to make acquisitions, that could be for a "recap" where the fund takes out a large loan against the business to pay itself a lump sum and then the business pays the loan off over time, do share buybacks, for the business to build up cash in savings, or a dividend would be possible as well. But the main idea is just that it is cash that has a value....what they do with it is up to the owners.

[@KifleAmbaye]

Excellent!

[@businesstrash]

I am getting different value on manual vs formula!!

[@Barskada]

Pretty good, thanks!

[@keyspanmusic]

Super helpful video! Also if I understood correctly, this helped me to make sense of the different approaches which might typically be taken by a private equity investor (first example - lower risk, biz currently making solid cash flow, with opportunity to perhaps optimize the operations post purchase) vs. a venture capital investor (third example, biz is not yet stood up or early stage cash flow negative, unproven model, but the one of these that hits out of a VC portfolio of 20+ of them pays for all the rest of the misses many times over)!

[@eric_andrews]

Exactly! Some investors prefer to invest in the very high-risk high-return opportunities (venture capital) and some investors prefer to invest in the low-risk low-return opportunities (more private equity). They might even end up making similar returns but the process is very very different. Great insights Gabe!

[@dillianwhyte443]

@@eric_andrews I'm having trouble with my IRR. When I estimate by adjusting the discount rate manually I get 5.670%. But when I use the IRR command it comes up with 7% which would make the NPV -596. which is almost identical to the NPV i achieved with the discount rate 4.8%. So I don't know what's wrong. I thought it was supposed to reduce the NPV to 0( which happens with 5.670%) but 7% goes to -596.

[@seshadripadmanabhan9645]

I would like to calculate IRR for monthly flows, and verify the same with Monthly flow NPPV

[@bocobox]

Awesome video. By the way, wondering how I can approach WACC and IRR(guessing rate) depending on business type.

[@eric_andrews]

Hey thanks bocobox!! Yes I'm realizing that I should probably make a video just dedicated to calculating WACC. It has a very specific formula and can range anywhere from 3% to even 60%. I appreciate the feedback. In general though for most established companies you could probably use somewhere between 8 - 20%, but ya, it's worth another video. Thanks!!

[@alexkang5583]

@@eric_andrews Hey Eric, great video, it was very informative. I second your suggestion on making a dedicated WACC video :)

[@XxFORESHOTxX]

@@eric_andrews WACC is calculated using cost of equity and cost of debt isnt it?

[@sparkofjoy3191]

@@eric_andrews I would love it if you could make a detailed video on WACC. But not only with some random given numbers to input into the WACC formula but how to actually get those number we then need to use in the formula! Hope you make it one day, thanks 😃

[@SalmanWarisKhan]

Hi Eric can you please upload more videos about the topics used in the excel regarding start ups. Thank you so much love your videos

[@eric_andrews]

Absolutely! I'm actually just launching a new startup-focused video this coming Tuesday. Make sure you're subscribed with notifications *on* so you don't miss it 🙏🙏

[@hhhh-be5tv]

Great video! I'd really appreciate if you could explain this to me? Why is it that sometimes the NPV includes the actual so (3,000,000) but sometimes like in this case it is not included in the NPV

[@eric_andrews]

Hi! The NPV doesn't use the purchase price (because that's what we are trying to calculate 😀), so we don't need the $3MM for the NPV. The IRR uses the purchase price to calculate potential returns. The reason we look at the $3MM when we are calculating the NPV is because we are trying to figure out if the price is higher or lower than the real value (NPV) of the company. Does that help?

[@hhhh-be5tv]

@@eric_andrews Yeah that helps :) Thanks!

[@ahmadabdullahakbar6482]

Hi Eric, how you get the number of Cashflow 2024 - 2030?

[@eric_andrews]

You would need to build your own financial model to project the numbers. If you've never done that before, I recommend you start here: kzbin.info/www/bejne/rp27dY2vj56UsM0

[@SherryLiu-fd3nn]

Can I download the subtitle?

[@sorcererstone3303]

Just discovered this very clear & explained walk through to calculate NPV & IRR. Have a newbie question. In your video, the manual calculated NPV and the excel built in NPV formula both came out to be the same. I was told there is something wrong in Microsoft interpretation of NPV. For example: propertymetrics.com/blog/how-not-to-use-npv-in-excel/ Is this an old problem in previous Excel releases and newer versions of Excel do not have this issue? I am a bit surprise your manual calculated NPV and the built in NPV result are the same. But I understand your calculation method.

[@eric_andrews]

@Sorcerer Stone yes, great question. There can be an issue under the following circumstances. When you have specific dates, say July of a certain year to March of a year 5 years out, NPV is limited and can't calculate that unique time period, in that case you can use another function called XNPV which can use the specific dates to get the exact NPV. But in my case I was just using regular annual data so the NPV formula worked fine (as well as the manual one). Hope that helps.

[@umakemeill7]

what if the cash flow is perpetuity?

[@amanavasthi7172]

NPV(%,cashflows)+(Cashout flow)? As I know, Can you please assist further

[@eric_andrews]

You can use that formula if you have a proposed price. The cash outflow would be the proposed price you want to pay. In many situations though, you are trying to figure out what price you are going to propose so all you have is the npv formula by itself. Make sense?

[@ronfredericks6254]

I am with AMAN on this one. From my studies I have come to accept that NPV = PV-Initial investment. Where PV = FV/(1+WACC)^year

[@TheSQUISHY090]

You didnt subtrac the Initial Outlay from NPV calculation. Excel doesnt do that for you.

[@Visinle]

Sir please tell how to calculate irr on graduation education income and expenses in excel sheet . This is the question :- calculating Internal rate of return on your graduation and also, write the assumptions surrounding your inflows and outflows. Just giving an example again: your first year of graduation would be the first year and your expected life expectancy would be the last year. You can make the frequency of cashflows monthly, quarterly,yearly or semi annually, after giving proper assumption behind choosing a particular frequency. Then, with every cashflow (income and expenses) explicitly mention the assumption and reasoning behind the amount.

[@mostlikely...]

☑️☑️☑️☑️☑️☑️☑️☑️