How to Calculate Net Present Value in Excel

In this video I’m going to show you how to calculate the NPV (or net present value) of a project in Excel. So I’ve got 20 different projects here with a series of cash flows. Cash outflows are in parentheses, while the cash inflows aren’t. There’s an initial cash outflow in period 0 and then a series of cash flows occurring at the end of each year for 5 years.
To calculate the NPV of a project, we’re going to use Excel’s NPV function. So type =NPV and then select the discount rate. I’m going to anchor the discount rate so I’ll be able to drag down the formula and calculate the NPV of each of the 20 projects. You can also type in the discount rate directly; in this case, you’d type in 0.09.
Next, highlight the future cash flows. Now you might be tempted to highlight all the project’s cash flows, but don’t do it. You only highlight the cash flows that occur after period 0.
So why isn’t the cash outflow that occurs today included in the values? It’s because the NPV function assumes cash flows occur at the end of each period. If the initial cash flow occurs today, then it doesn’t occur at the end of the period and thus shouldn’t be part of the NPV function.
But don’t worry, we will account for the cash flow in period 0 separately. After you close the parentheses, add the cash flow from period 0 and hit “Enter.” Thus, the cash outflow that occurs today is still part of the project’s NPV, it’s just not part of the values we enter for NPV function.
Now you can see the NPV for Project 1, which is $28,345. To see the NPV for the other 19 projects, just click the fill handle and drag and it down.
Now the NPV function assumes the cash flows occurred at the end of each period and that the cash flows were periodic. But what if the cash flows weren’t equally spaced out?
For example, what if we had a project with nonperiodic cash flows like this? We have one cash flow occurring on May 6, one occurring February 19, and so forth. We shouldn’t use the NPV function here because the cash flows don’t occur at regular intervals. Instead, we should use the XNPV function. Just type =XNPV, enter the discount rate, select the range of values, and select the dates, and you can see that the NPV of this project is $713. Note that I included the first cash flow in the XNPV function; this is different from the NPV function where we had to deal with the upfront cash flow separately.
-
Edspira is the creation of Michael McLaughlin, an award-winning professor who went from teenage homelessness to a PhD. Edspira’s mission is to make a high-quality business education freely available to the world.
-
SUBSCRIBE FOR A FREE 53-PAGE GUIDE TO THE FINANCIAL STATEMENTS, PLUS:
• A 23-PAGE GUIDE TO MANAGERIAL ACCOUNTING
• A 44-PAGE GUIDE TO U.S. TAXATION
• A 75-PAGE GUIDE TO FINANCIAL STATEMENT ANALYSIS
• MANY MORE FREE PDF GUIDES AND SPREADSHEETS
* eepurl.com/dIaa5z
-
SUPPORT EDSPIRA ON PATREON
* / prof_mclaughlin
-
GET CERTIFIED IN FINANCIAL STATEMENT ANALYSIS, IFRS 16, AND ASSET-LIABILITY MANAGEMENT
* edspira.thinki...
-
LISTEN TO THE SCHEME PODCAST
* Apple Podcasts: podcasts.apple...
* Spotify: open.spotify.c...
* Website: www.edspira.co...
-
GET TAX TIPS ON TIKTOK
* / prof_mclaughlin
-
ACCESS INDEX OF VIDEOS
* www.edspira.co...
-
CONNECT WITH EDSPIRA
* Facebook: / edspira
* Instagram: / edspiradotcom
* LinkedIn: / edspira
-
CONNECT WITH MICHAEL
* Twitter: / prof_mclaughlin
* LinkedIn: / prof-michael-mclaughlin
-
ABOUT EDSPIRA AND ITS CREATOR
* www.edspira.co...
* michaelmclaugh...

[@camilaromano2670]

Thanks very much for this vídeo! I Just have one doubt! If we are looking now at some projects that will begin only next year, so we have cash flow for today equals zero, we consider year 0 the first year of investment? In this case 2023?

[@Edspira]

Year 0 stands for today (right now). If the cash flow for today equals zero, then the cash flow for Year 0 would be zero. If the first cash flow doesn't occur until one year from now, that cash flow would be for Year 1. Thus, Year 1 is for cash flows that occur one year from now, Year 2 is for cash flows that occur two years from now, etc. (and Year 0 is for cash flows that occur today, not in the future).

[@boundsean]

Thank you so much ive been stumped on my homework for an hour and this cleared it right up. Thank you!

[@samuellonge3673]

Thank you so much for releasing content like this.

[@Edspira]

No problem, I'm glad you enjoyed the video!

[@marcusamor-smith1571]

Super easy Video. Thanks so much.

[@kelseakeys5426]

Thank you SO MUCH! This was so helpful!

[@carlosbourgeois8920]

how do i go about using npv if its not a discount rate and actually a cost of borrowing rate? unless that discount rate is the cost of borrowing rate.

[@ItsToast123]