Calculating NPV and IRR in Excel: A Step-by-Step Tutorial
HTML-код
- Опубликовано: 7 сен 2024
- We walk through the process of how calculating net present value & IRR to analyze three example investments. Also included is a downloadable template of the model.
✅ Download the Excel template: bit.ly/npv_irr...
🚀 If you want to master the finance skills & frameworks to successfully scale technology startups, secure your spot in my "Finance for Startups" program, today: www.ericandrew...
Communities:
💼 Linkedin: / eric-andrews-1624b656
☑️ Twitter/X: / eric__andrews
🌟 Instagram: / ericandrews_startups
🔥 Discord: bit.ly/discord...
💻 Newsletter: bit.ly/joineri...
🎥 Clips: / @ericandrewsclips7818
Related Finance Videos:
🚩 Top 10 Startup Metrics Used by VCs: • Startup Metrics & KPIs...
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
In this video, we analyze three very different potential investments to calculate the net present value of the investment as well as the internal rate of return. We then use the NPV and IRR to discuss whether or not we think this is an attractive investment for each of our example companies.
Conceptually, we the relationship between net present value & IRR and how to calculate them to understand the risk-return profile of an investment. The video also includes a discussion about the discount rate or WACC (weighted average cost of capital) and what it means.
Also included is a link (in the "resources & links" section) where you can download the NPV & IRR Excel template for free.
The net present value in IRR frameworks enables us to analyze the riskiness of investments and compare that to the potential reward that we think is possible.
The future is always going to be uncertain, but in certain industries and with certain business models the future is more or less uncertain. The concept of uncertainty is the same thing as the concept of risk. In our model risk is reflected in the discount rate or WACC.
In the free market system, you need to take higher risks if you want to earn higher returns. So you need to calculate metrics like net present value and internal rate of return so that you can make sure that the risks are not higher than the potential returns.
NPV and IRR calculations are some of the most common calculations in venture capital, private equity, investment banking, etc, because they are extremely useful in analyzing opportunities.
I hope that you can now feel confident with the IRR & NPV formulas in Excel. If you have questions (I'm sure you do) - please leave comments below and I'll try to help. Cheers!
► Subscribe for more finance videos: bit.ly/EricAnd...
#netpresentvalue #npv #irr #investing
Thank you for your material , I have watched over dozens of RUclips 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
Means a lot, thanks !
Hi...your vedio was amazing...kindly also make a vedio about calculation of discount rate(WACC)
Hey Zeenat, really appreciate it. I'll put it on my list of videos to make, thanks for the suggestion 🙏🙏🙏
Perfect explanation ❤️❤️❤️
Glad you think so Erfan! Cheers!
This was extremely helpful… I got stuck on a project and this video pulled me right out. Thanks for sharing!!
Glad it helped!
Thank you for the great insights you provide about calculating NPV & IRR. It is very useful!
You are very welcome !
Excellent video, Eric! I'm a CPA and I love the way that you have explained an often convoluted concept so lucidly. Thanks! :)
Wow! So happy to hear that Oveeya! Thanks for the feedback.
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
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 🙏🙏
Excellent!
Amazing explanation mate, quite in depth. thank you so so so much!
Glad you liked it!
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
Coming soon!
Thank you for the great video and for the great explanation !
Hey Abdel - thanks for checking it out I'm glad it was insightful for you 👍👍👍
Quite helpful! Thanks!
Great video!!!! Thanks so much
Laisha - thanks for letting me know. Really glad it was helpful
Thank you for such a great video. Plz keep up the good work.
Appreciate the comment Rafi! I will keep trying to release more helpful stuff!! Cheers 👍
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)!
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!
@@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.
Pretty good, thanks!
I would like to calculate IRR for monthly flows, and verify the same with Monthly flow NPPV
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 :)
Awesome video. By the way, wondering how I can approach WACC and IRR(guessing rate) depending on business type.
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!!
@@eric_andrews Hey Eric, great video, it was very informative. I second your suggestion on making a dedicated WACC video :)
@@eric_andrews WACC is calculated using cost of equity and cost of debt isnt it?
@@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 😃
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!
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?
@@eric_andrews makes sense! thanks again
@@_tacotempura no problem happy to help
@@_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.
@@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
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
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?
@@eric_andrews Yeah that helps :) Thanks!
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!
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.
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.
@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.
Can I download the subtitle?
Hi Eric, how you get the number of Cashflow 2024 - 2030?
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: ruclips.net/video/xlXDZyZ9azk/видео.html
what if the cash flow is perpetuity?
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.
You didnt subtrac the Initial Outlay from NPV calculation. Excel doesnt do that for you.
NPV(%,cashflows)+(Cashout flow)?
As I know, Can you please assist further
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?
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
☑️☑️☑️☑️☑️☑️☑️☑️