NPV() in Excel Explained | Capital Budgeting — Transcript
Full transcript
- 0:00Question one. A project needs 10 lakh
- 0:02rupees today and returns 2 lakh 50,000,
- 0:053 lakh, 3 lakh 50,000, 4 lakh, and 4
- 0:09lakh 50,000 rupees over the next 5
- 0:11years. At a discount rate of 10% a year,
- 0:14is the project worth it? Take a second
- 0:16and think. What do you expect the answer
- 0:18to be? Pause here if you like and try it
- 0:20yourself.
- 0:22Right. Let us solve it together in
- 0:23Excel.
- 0:27First, the function. We use NPV = NPV
- 0:32(rate,
- 0:33values) and then we add the initial
- 0:35investment and then we add the initial
- 0:36investment separately.
- 0:38Rate is the discount rate. Values are
- 0:40the future cash flows, years 1 to 5. And
- 0:43the initial investment is not inside the
- 0:45NPV range because it happens today at
- 0:48time zero and it is not discounted at
- 0:50all.
- 0:51On every sheet, the inputs live in the
- 0:53same cells. Initial investment in B1,
- 0:56discount rate in B2, and the cash flows
- 0:59for years 1 to 5 in B3 to B7.
- 1:02So, the live formula just points at
- 1:04those cells. = NPV(B2, B3 to B7) + B1.
- 1:12B2 is the rate. B3 to B7 are the future
- 1:15cash flows and B1 is the investment,
- 1:18negative, added on its own. Let us open
- 1:21the project one sheet and set it up just
- 1:23like you would. Initial investment in
- 1:25B1, -10 lakh rupees, negative because it
- 1:29goes out today.
- 1:30Discount rate in B2, 10% a year. Cash
- 1:34flow year one in B3, 2 lakh 50,000
- 1:37rupees.
- 1:38Year two in B4, 3 lakh rupees.
- 1:41Year three in B5, 3 lakh 50,000 rupees.
- 1:45Year four in B6, 4 lakh rupees.
- 1:49Year five in B7, 4 lakh 50,000 rupees.
- 1:53Good. The inputs are ready, all in B1 to
- 1:56B7. Now I click on cell B8 and type the
- 1:59formula. =NPV
- 2:02B2 B3 to B7 close bracket + B1.
- 2:07B2 is the discount rate. B3 to B7 are
- 2:11the five future cash flows and B1 is the
- 2:14investment added separately.
- 2:16Now watch what happens when I press
- 2:18enter. And there it is. 2 lakh 90,786
- 2:23rupees and 77 paisa.
- 2:25The net present value is positive, so
- 2:27accept the project. And look at row
- 2:29nine. The undiscounted total is 17 lakh
- 2:3250,000 rupees. 7 lakh 50,000 more than
- 2:36the 10 lakh invested.
- 2:38Discounting shrinks that surplus to 2
- 2:40lakh 90,786
- 2:42rupees and 77 paisa, but is still
- 2:45positive.
- 2:46That's the whole point of NPV. It tells
- 2:49you what is left after time has had its
- 2:50say.
- 2:52That is NPV. It answers one question.
- 2:56After discounting every future rupee
- 2:58back to today, is anything left over?
- 3:01The rule is simple.
- 3:02NPV above zero, accept. NPV below zero,
- 3:06reject.
- 3:08Project one gives 2 lakh 90,786
- 3:11rupees and 77 paisa. So we accept. And
- 3:15here is the trap to avoid. Never put the
- 3:17initial investment inside the NPV range.
- 3:20Excel discounts the first value by one
- 3:22year. So a time zero investment gets
- 3:25discounted, too. And every cash flow
- 3:27lands a year late.
- 3:29For project one, that mistake gives 2
- 3:30lakh 64,351
- 3:33rupees and 61 paisa. 26,435
- 3:38rupees and 16 paisa too low.
- 3:40Keep B1 outside and add it separately.
- 3:43Same project. 10 lakh rupees today. The
- 3:46same five cash flows. Only the discount
- 3:49rate changes.
- 3:50At 0% with no discounting all, NPV is 7
- 3:54lakh 50,000 rupees, the full
- 3:56undiscounted surplus.
- 3:58At 5%, 4 lakh 94,215
- 4:02rupees and 1 pies.
- 4:05At 10% our rate, 2 lakh 90,786
- 4:10rupees and 77 pies.
- 4:13At 15%, 1 lakh 26,795
- 4:17rupees and 92 pies.
- 4:20And at 20%, minus 7,040 rupees and 90
- 4:24pies.
- 4:25Already negative.
- 4:26So, somewhere between 15 and 20% NPV
- 4:30crosses zero.
- 4:31The higher the rate, the less the future
- 4:33is worth today. Now, let us make a chart
- 4:35of that so you can see the fall.
- 4:38First, select the rates and their NPV
- 4:40values. Then, click the insert tab, then
- 4:42charts.
- 4:44Now, choose line because this is a
- 4:45journey across rates and a line shows
- 4:48the fall.
- 4:49Excel inserts the chart and now we
- 4:51replace the placeholder title with a
- 4:52proper one and add a label on every
- 4:55point.
- 4:56There it is, a line falling from left to
- 4:58right. Look at the shape of it.
- 5:00The net present value falls steadily as
- 5:02the discount rate rises, from 7 lakh
- 5:0550,000 rupees at 0% down through 2 lakh
- 5:0890,786
- 5:11rupees and 77 pies at 10% in gold to
- 5:15minus 7,040 rupees and 90 pies at 20%.
- 5:20The line never rises because a higher
- 5:22rate can only shrink what the future is
- 5:24worth today.
- 5:25That is the whole story of the discount
- 5:27rate in one picture. Let us try this one
- 5:29together.
- 5:30Question two.
- 5:31A project needs 5 lakh rupees today and
- 5:34returns 1 lakh, 1 lakh 20,000, 1 lakh
- 5:3740,000, 1 lakh 60,000, and 1 lakh 80,000
- 5:41rupees over 5 years.
- 5:43At 12% a year, is it worth it? Even
- 5:46though it takes in more than it costs?
- 5:48Take a second, have a guess,
- 5:49[clears throat]
- 5:50then watch the solution.
- 5:54Same function, new numbers. Equals NPV,
- 5:58rate, values, plus the initial
- 6:00investment separately. The layout is the
- 6:03same on every sheet. Initial investment
- 6:05in B1, discount rate in B2, and the cash
- 6:09flows for years 1 to 5 in B3 to B7.
- 6:12So, the live formula is equals NPV, B2,
- 6:17B3 to B7, close bracket, plus B1. Which
- 6:21here, B2 is 12% a year. B3 to B7 are the
- 6:25five cash flows, and B1 is minus five
- 6:28lakh rupees added separately. On the
- 6:31project two sheet, I set up the example
- 6:33in the same cells. Initial investment in
- 6:35B1, minus five lakh rupees. Discount
- 6:39rate in B2, 12% a year. Cash flow year
- 6:42one in B3, one lakh rupees.
- 6:45Year two in B4, one lakh 20,000 rupees.
- 6:49Year three in B5, one lakh 40,000
- 6:52rupees.
- 6:53Year four in B6, one lakh 60,000 rupees.
- 6:58Year five in B7, one lakh 80,000 rupees.
- 7:02The inputs are ready. Now, I click on
- 7:04cell B8 and type the formula. Equals
- 7:07NPV, B2, B3 to B7, close bracket, plus
- 7:11B1. B2 is the discount rate. B3 to B7
- 7:16are the five future cash flows, and B1
- 7:18is the investment added separately.
- 7:21Press enter and watch. And there it is,
- 7:24minus 11,582
- 7:26rupees and six paisa. The net present
- 7:29value is negative, so reject the
- 7:31project, even though seven lakh rupees
- 7:33comes in against five lakh going out.
- 7:36Look at row nine. The undiscounted total
- 7:38is seven lakh rupees, a surplus of two
- 7:41lakh on paper,
- 7:42but at 12% a year, those future rupees
- 7:45are not worth enough today.
- 7:47The project looks profitable, and it
- 7:49still fails.
- 7:50That is exactly why we discount.
- 7:54Question three, and this time two
- 7:55projects compete. Both need eight lakh
- 7:58rupees at the same discount rate of 10%
- 8:00a year.
- 8:01Project A returns a flat two lakh rupees
- 8:03a year for five years.
- 8:05What is its NPV?
- 8:07Take a second. Have a guess. Then watch
- 8:09the solution.
- 8:14Same function, new numbers. Equals NPV,
- 8:18rate, values, plus the initial
- 8:20investment separately.
- 8:21The layout is the same on every sheet.
- 8:23Initial investment in B1, discount rate
- 8:26in B2, and the cash flows for years 1 to
- 8:285 in B3 to B7.
- 8:31The live formula is equals NPV, B2, B3
- 8:35to B7, close bracket, plus B1.
- 8:38When here, B2 is 10% a year. B3 to B7
- 8:42are two lakh rupees each year, and B1 is
- 8:45minus eight lakh rupees added
- 8:47separately. On the project A sheet, I
- 8:49set up the example in the same cells.
- 8:51Initial investment in B1, minus eight
- 8:53lakh rupees.
- 8:55Discount rate in B2, 10% a year.
- 8:58Cash flows in B3 to B7, two lakh rupees
- 9:01each year, five years in a row.
- 9:03The inputs are ready. Now, I click on
- 9:06cell B8 and type the formula. Equals
- 9:08NPV, B2, B3 to B7, close bracket, plus
- 9:13B1.
- 9:14B2 is the discount rate. B3 to B7 are
- 9:17the five future cash flows, and B1 is
- 9:20the investment added separately. Press
- 9:22enter and watch. And there it is, minus
- 9:2541,842
- 9:27rupees and 65 paise.
- 9:30Project A on its own would be rejected.
- 9:32A flat two lakh a year for five years is
- 9:34not enough at 10% to repay eight lakh
- 9:37today.
- 9:38But hold that thought. Project B is
- 9:40coming with the same investment and the
- 9:42same rate.
- 9:44Now, project B. Same eight lakh rupees,
- 9:47same 10% a year, but its cash flows
- 9:50grow. One lakh 50,000, two lakh, two
- 9:53lakh 50,000, three lakh, and three lakh
- 9:5650,000 rupees.
- 9:58What is its NPV? And which project wins?
- 10:02Take a second. Have a guess. Then watch
- 10:04the solution.
- 10:08Same function, new numbers. Equals NPV,
- 10:12rate, values, plus the initial
- 10:14investment separately.
- 10:16The layout is the same on every sheet.
- 10:18Initial investment in B1, discount rate
- 10:20in B2, and the cash flows for years 1 to
- 10:235 in B3 to B7.
- 10:25The live formula is equals NPV, B2, B3
- 10:30to B7, close bracket, plus B1.
- 10:34Here, B2 is 10% a year. B3 to B7 are the
- 10:38growing cash flows, and B1 is minus
- 10:40eight lakh rupees added separately.
- 10:43On the project B sheet, I set up the
- 10:45example in the same cells. Initial
- 10:47investment in B1, minus eight lakh
- 10:50rupees.
- 10:51Discount rate in B2, 10% a year.
- 10:54Cash flow year one in B3, one lakh
- 10:5750,000 rupees.
- 10:59Year two in B4, two lakh rupees.
- 11:02Year three in B5, two lakh 50,000
- 11:05rupees.
- 11:06Year four in B6, three lakh rupees.
- 11:10Year five in B7, three lakh 50,000
- 11:12rupees.
- 11:14The inputs are ready. Now, I click on
- 11:16cell B8 and type the formula. Equals
- 11:19NPV, B2, B3 to B7, close bracket, plus
- 11:23B1.
- 11:25B2 is the discount rate. B3 to B7 are
- 11:28the five future cash flows, and B1 is
- 11:30the investment added separately.
- 11:33Press enter and watch. And there it is.
- 11:35141,000,
- 11:3711,708
- 11:39rupees and nine paisa. Project B is
- 11:42positive. Project A is negative. Project
- 11:45B wins by 141,550
- 11:49rupees and 74 paisa of NPV.
- 11:52When projects compete, choose the higher
- 11:54NPV, not the higher undiscounted total.
- 11:58So, the verdict, project B. Same
- 12:01investment, same rate, different NPV.
- 12:05Project A, minus 41,842
- 12:08rupees and 65 paisa.
- 12:10Project B, plus one lakh, 11,708
- 12:15rupees and nine paisa.
- 12:17Project B wins by one lakh, 53,550
- 12:21rupees and 74 paisa.
- 12:23And notice, project B also takes in more
- 12:26in total. 12 lakh, 50,000 rupees against
- 12:29project A's 10 lakh. But the timing
- 12:32matters, too.
- 12:33Project B's later cash flows are bigger,
- 12:35and at 10% that is enough to win. So,
- 12:39the takeaway. Net present value equals
- 12:42NPV. B2, B3 to B7, close bracket, plus
- 12:47B1.
- 12:48Discount rate, future cash flows, plus
- 12:51the initial investment added separately.
- 12:53NPV discounts only the future cash
- 12:56flows.
- 12:57The initial investment at time zero is
- 12:58never inside the NPV range.
- 13:01NPV above zero, accept. NPV below zero,
- 13:05reject.
- 13:07A project can take in more than it costs
- 13:09and still be rejected. Project two takes
- 13:11in seven lakh rupees against five lakh,
- 13:14but at 12% its NPV is minus 11,582
- 13:19rupees and six paisa.
- 13:21When projects compete, choose the higher
- 13:23NPV.
- 13:24Project B, one lakh, 11,000 708 rupees
- 13:29and 9 paisa, beats project A, minus
- 13:3241,842
- 13:35rupees and 65 paisa.
- 13:37And remember the anchor.
- 13:3910 lakh rupees today, five cash flows,
- 13:4210% a year.
- 13:44NPV 2 lakh, 90,786
- 13:48rupees and 77 paisa. Accept. Your turn.
- 13:52Try it yourself.
- 13:53A project needs 6 lakh rupees today and
- 13:56returns 1 lakh 50,000, 1 lakh 80,000, 2
- 14:00lakh 10,000, 2 lakh 40,000, and 2 lakh
- 14:0470,000 rupees over 5 years.
- 14:07At 12% a year, what is its NPV? And
- 14:10would you accept it?
- 14:11Pause here, work it out in Excel, and
- 14:14then watch the solution.
- 14:19Same function, your numbers. Equals NPV,
- 14:23rate, values, plus the initial
- 14:25investment separately.
- 14:27The layout is the same on every sheet.
- 14:29Initial investment in B1, discount rate
- 14:31in B2, and the cash flows for years 1 to
- 14:345 in B3 to B7. Just the live formula is
- 14:37equals NPV, B2, B3 to B7, close bracket,
- 14:42plus B1.
- 14:44If B2 is 12% a year, B3 to B7 are your
- 14:47five cash flows, and B1 is minus 6 lakh
- 14:51rupees, added separately. On the your
- 14:53turn sheet, I set up the example in the
- 14:55same cells. Initial investment in B1,
- 14:58minus 6 lakh rupees.
- 15:00Discount rate in B2, 12% a year.
- 15:03Cash flow year one in B3, 1 lakh 50,000
- 15:07rupees.
- 15:08Year two in B4, 1 lakh 80,000 rupees.
- 15:12Year three in B5, 2 lakh 10,000 rupees.
- 15:17Year four in B6, 2 lakh 40,000 rupees.
- 15:21Year five in B7, 2 lakh 70,000 rupees.
- 15:25The inputs are ready. Now, I click on
- 15:28cell B8 and type the formula, equals NPV
- 15:31B2 B3 to B7 close bracket plus B1.
- 15:36B2 is the discount rate. B3 to B7 are
- 15:39the five future cash flows and B1 is the
- 15:42investment added separately.
- 15:44Press enter and watch. And there it is,
- 15:471 lakh 32,626
- 15:50rupees and 91 paisa.
- 15:53The net present value is positive, so
- 15:55accept the project. And the undiscounted
- 15:57total is 10 lakh 50,000 rupees.
- 16:004 lakh 50,000 more than the 6 lakh
- 16:02invested, shrinking to 1 lakh 32,000
- 16:06after discounting, but still positive.
- 16:08Did you get the same?
About this transcript
This page contains the full transcript of NPV() in Excel Explained | Capital Budgeting by srccan Academy, generated from the public captions YouTube serves with the video. The transcript has 2,208 words across 360 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.
What you can do with it
Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to choose a caption track available on the video.
Free YouTube transcript tool
YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, caption language selection, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.