YouTube2Text

NPV() in Excel Explained | Capital Budgeting — Transcript

by srccan Academy · 2,208 words · 360 segments · language en · Watch on YouTube

Full transcript

  1. 0:00Question one. A project needs 10 lakh
  2. 0:02rupees today and returns 2 lakh 50,000,
  3. 0:053 lakh, 3 lakh 50,000, 4 lakh, and 4
  4. 0:09lakh 50,000 rupees over the next 5
  5. 0:11years. At a discount rate of 10% a year,
  6. 0:14is the project worth it? Take a second
  7. 0:16and think. What do you expect the answer
  8. 0:18to be? Pause here if you like and try it
  9. 0:20yourself.
  10. 0:22Right. Let us solve it together in
  11. 0:23Excel.
  12. 0:27First, the function. We use NPV = NPV
  13. 0:32(rate,
  14. 0:33values) and then we add the initial
  15. 0:35investment and then we add the initial
  16. 0:36investment separately.
  17. 0:38Rate is the discount rate. Values are
  18. 0:40the future cash flows, years 1 to 5. And
  19. 0:43the initial investment is not inside the
  20. 0:45NPV range because it happens today at
  21. 0:48time zero and it is not discounted at
  22. 0:50all.
  23. 0:51On every sheet, the inputs live in the
  24. 0:53same cells. Initial investment in B1,
  25. 0:56discount rate in B2, and the cash flows
  26. 0:59for years 1 to 5 in B3 to B7.
  27. 1:02So, the live formula just points at
  28. 1:04those cells. = NPV(B2, B3 to B7) + B1.
  29. 1:12B2 is the rate. B3 to B7 are the future
  30. 1:15cash flows and B1 is the investment,
  31. 1:18negative, added on its own. Let us open
  32. 1:21the project one sheet and set it up just
  33. 1:23like you would. Initial investment in
  34. 1:25B1, -10 lakh rupees, negative because it
  35. 1:29goes out today.
  36. 1:30Discount rate in B2, 10% a year. Cash
  37. 1:34flow year one in B3, 2 lakh 50,000
  38. 1:37rupees.
  39. 1:38Year two in B4, 3 lakh rupees.
  40. 1:41Year three in B5, 3 lakh 50,000 rupees.
  41. 1:45Year four in B6, 4 lakh rupees.
  42. 1:49Year five in B7, 4 lakh 50,000 rupees.
  43. 1:53Good. The inputs are ready, all in B1 to
  44. 1:56B7. Now I click on cell B8 and type the
  45. 1:59formula. =NPV
  46. 2:02B2 B3 to B7 close bracket + B1.
  47. 2:07B2 is the discount rate. B3 to B7 are
  48. 2:11the five future cash flows and B1 is the
  49. 2:14investment added separately.
  50. 2:16Now watch what happens when I press
  51. 2:18enter. And there it is. 2 lakh 90,786
  52. 2:23rupees and 77 paisa.
  53. 2:25The net present value is positive, so
  54. 2:27accept the project. And look at row
  55. 2:29nine. The undiscounted total is 17 lakh
  56. 2:3250,000 rupees. 7 lakh 50,000 more than
  57. 2:36the 10 lakh invested.
  58. 2:38Discounting shrinks that surplus to 2
  59. 2:40lakh 90,786
  60. 2:42rupees and 77 paisa, but is still
  61. 2:45positive.
  62. 2:46That's the whole point of NPV. It tells
  63. 2:49you what is left after time has had its
  64. 2:50say.
  65. 2:52That is NPV. It answers one question.
  66. 2:56After discounting every future rupee
  67. 2:58back to today, is anything left over?
  68. 3:01The rule is simple.
  69. 3:02NPV above zero, accept. NPV below zero,
  70. 3:06reject.
  71. 3:08Project one gives 2 lakh 90,786
  72. 3:11rupees and 77 paisa. So we accept. And
  73. 3:15here is the trap to avoid. Never put the
  74. 3:17initial investment inside the NPV range.
  75. 3:20Excel discounts the first value by one
  76. 3:22year. So a time zero investment gets
  77. 3:25discounted, too. And every cash flow
  78. 3:27lands a year late.
  79. 3:29For project one, that mistake gives 2
  80. 3:30lakh 64,351
  81. 3:33rupees and 61 paisa. 26,435
  82. 3:38rupees and 16 paisa too low.
  83. 3:40Keep B1 outside and add it separately.
  84. 3:43Same project. 10 lakh rupees today. The
  85. 3:46same five cash flows. Only the discount
  86. 3:49rate changes.
  87. 3:50At 0% with no discounting all, NPV is 7
  88. 3:54lakh 50,000 rupees, the full
  89. 3:56undiscounted surplus.
  90. 3:58At 5%, 4 lakh 94,215
  91. 4:02rupees and 1 pies.
  92. 4:05At 10% our rate, 2 lakh 90,786
  93. 4:10rupees and 77 pies.
  94. 4:13At 15%, 1 lakh 26,795
  95. 4:17rupees and 92 pies.
  96. 4:20And at 20%, minus 7,040 rupees and 90
  97. 4:24pies.
  98. 4:25Already negative.
  99. 4:26So, somewhere between 15 and 20% NPV
  100. 4:30crosses zero.
  101. 4:31The higher the rate, the less the future
  102. 4:33is worth today. Now, let us make a chart
  103. 4:35of that so you can see the fall.
  104. 4:38First, select the rates and their NPV
  105. 4:40values. Then, click the insert tab, then
  106. 4:42charts.
  107. 4:44Now, choose line because this is a
  108. 4:45journey across rates and a line shows
  109. 4:48the fall.
  110. 4:49Excel inserts the chart and now we
  111. 4:51replace the placeholder title with a
  112. 4:52proper one and add a label on every
  113. 4:55point.
  114. 4:56There it is, a line falling from left to
  115. 4:58right. Look at the shape of it.
  116. 5:00The net present value falls steadily as
  117. 5:02the discount rate rises, from 7 lakh
  118. 5:0550,000 rupees at 0% down through 2 lakh
  119. 5:0890,786
  120. 5:11rupees and 77 pies at 10% in gold to
  121. 5:15minus 7,040 rupees and 90 pies at 20%.
  122. 5:20The line never rises because a higher
  123. 5:22rate can only shrink what the future is
  124. 5:24worth today.
  125. 5:25That is the whole story of the discount
  126. 5:27rate in one picture. Let us try this one
  127. 5:29together.
  128. 5:30Question two.
  129. 5:31A project needs 5 lakh rupees today and
  130. 5:34returns 1 lakh, 1 lakh 20,000, 1 lakh
  131. 5:3740,000, 1 lakh 60,000, and 1 lakh 80,000
  132. 5:41rupees over 5 years.
  133. 5:43At 12% a year, is it worth it? Even
  134. 5:46though it takes in more than it costs?
  135. 5:48Take a second, have a guess,
  136. 5:49[clears throat]
  137. 5:50then watch the solution.
  138. 5:54Same function, new numbers. Equals NPV,
  139. 5:58rate, values, plus the initial
  140. 6:00investment separately. The layout is the
  141. 6:03same on every sheet. Initial investment
  142. 6:05in B1, discount rate in B2, and the cash
  143. 6:09flows for years 1 to 5 in B3 to B7.
  144. 6:12So, the live formula is equals NPV, B2,
  145. 6:17B3 to B7, close bracket, plus B1. Which
  146. 6:21here, B2 is 12% a year. B3 to B7 are the
  147. 6:25five cash flows, and B1 is minus five
  148. 6:28lakh rupees added separately. On the
  149. 6:31project two sheet, I set up the example
  150. 6:33in the same cells. Initial investment in
  151. 6:35B1, minus five lakh rupees. Discount
  152. 6:39rate in B2, 12% a year. Cash flow year
  153. 6:42one in B3, one lakh rupees.
  154. 6:45Year two in B4, one lakh 20,000 rupees.
  155. 6:49Year three in B5, one lakh 40,000
  156. 6:52rupees.
  157. 6:53Year four in B6, one lakh 60,000 rupees.
  158. 6:58Year five in B7, one lakh 80,000 rupees.
  159. 7:02The inputs are ready. Now, I click on
  160. 7:04cell B8 and type the formula. Equals
  161. 7:07NPV, B2, B3 to B7, close bracket, plus
  162. 7:11B1. B2 is the discount rate. B3 to B7
  163. 7:16are the five future cash flows, and B1
  164. 7:18is the investment added separately.
  165. 7:21Press enter and watch. And there it is,
  166. 7:24minus 11,582
  167. 7:26rupees and six paisa. The net present
  168. 7:29value is negative, so reject the
  169. 7:31project, even though seven lakh rupees
  170. 7:33comes in against five lakh going out.
  171. 7:36Look at row nine. The undiscounted total
  172. 7:38is seven lakh rupees, a surplus of two
  173. 7:41lakh on paper,
  174. 7:42but at 12% a year, those future rupees
  175. 7:45are not worth enough today.
  176. 7:47The project looks profitable, and it
  177. 7:49still fails.
  178. 7:50That is exactly why we discount.
  179. 7:54Question three, and this time two
  180. 7:55projects compete. Both need eight lakh
  181. 7:58rupees at the same discount rate of 10%
  182. 8:00a year.
  183. 8:01Project A returns a flat two lakh rupees
  184. 8:03a year for five years.
  185. 8:05What is its NPV?
  186. 8:07Take a second. Have a guess. Then watch
  187. 8:09the solution.
  188. 8:14Same function, new numbers. Equals NPV,
  189. 8:18rate, values, plus the initial
  190. 8:20investment separately.
  191. 8:21The layout is the same on every sheet.
  192. 8:23Initial investment in B1, discount rate
  193. 8:26in B2, and the cash flows for years 1 to
  194. 8:285 in B3 to B7.
  195. 8:31The live formula is equals NPV, B2, B3
  196. 8:35to B7, close bracket, plus B1.
  197. 8:38When here, B2 is 10% a year. B3 to B7
  198. 8:42are two lakh rupees each year, and B1 is
  199. 8:45minus eight lakh rupees added
  200. 8:47separately. On the project A sheet, I
  201. 8:49set up the example in the same cells.
  202. 8:51Initial investment in B1, minus eight
  203. 8:53lakh rupees.
  204. 8:55Discount rate in B2, 10% a year.
  205. 8:58Cash flows in B3 to B7, two lakh rupees
  206. 9:01each year, five years in a row.
  207. 9:03The inputs are ready. Now, I click on
  208. 9:06cell B8 and type the formula. Equals
  209. 9:08NPV, B2, B3 to B7, close bracket, plus
  210. 9:13B1.
  211. 9:14B2 is the discount rate. B3 to B7 are
  212. 9:17the five future cash flows, and B1 is
  213. 9:20the investment added separately. Press
  214. 9:22enter and watch. And there it is, minus
  215. 9:2541,842
  216. 9:27rupees and 65 paise.
  217. 9:30Project A on its own would be rejected.
  218. 9:32A flat two lakh a year for five years is
  219. 9:34not enough at 10% to repay eight lakh
  220. 9:37today.
  221. 9:38But hold that thought. Project B is
  222. 9:40coming with the same investment and the
  223. 9:42same rate.
  224. 9:44Now, project B. Same eight lakh rupees,
  225. 9:47same 10% a year, but its cash flows
  226. 9:50grow. One lakh 50,000, two lakh, two
  227. 9:53lakh 50,000, three lakh, and three lakh
  228. 9:5650,000 rupees.
  229. 9:58What is its NPV? And which project wins?
  230. 10:02Take a second. Have a guess. Then watch
  231. 10:04the solution.
  232. 10:08Same function, new numbers. Equals NPV,
  233. 10:12rate, values, plus the initial
  234. 10:14investment separately.
  235. 10:16The layout is the same on every sheet.
  236. 10:18Initial investment in B1, discount rate
  237. 10:20in B2, and the cash flows for years 1 to
  238. 10:235 in B3 to B7.
  239. 10:25The live formula is equals NPV, B2, B3
  240. 10:30to B7, close bracket, plus B1.
  241. 10:34Here, B2 is 10% a year. B3 to B7 are the
  242. 10:38growing cash flows, and B1 is minus
  243. 10:40eight lakh rupees added separately.
  244. 10:43On the project B sheet, I set up the
  245. 10:45example in the same cells. Initial
  246. 10:47investment in B1, minus eight lakh
  247. 10:50rupees.
  248. 10:51Discount rate in B2, 10% a year.
  249. 10:54Cash flow year one in B3, one lakh
  250. 10:5750,000 rupees.
  251. 10:59Year two in B4, two lakh rupees.
  252. 11:02Year three in B5, two lakh 50,000
  253. 11:05rupees.
  254. 11:06Year four in B6, three lakh rupees.
  255. 11:10Year five in B7, three lakh 50,000
  256. 11:12rupees.
  257. 11:14The inputs are ready. Now, I click on
  258. 11:16cell B8 and type the formula. Equals
  259. 11:19NPV, B2, B3 to B7, close bracket, plus
  260. 11:23B1.
  261. 11:25B2 is the discount rate. B3 to B7 are
  262. 11:28the five future cash flows, and B1 is
  263. 11:30the investment added separately.
  264. 11:33Press enter and watch. And there it is.
  265. 11:35141,000,
  266. 11:3711,708
  267. 11:39rupees and nine paisa. Project B is
  268. 11:42positive. Project A is negative. Project
  269. 11:45B wins by 141,550
  270. 11:49rupees and 74 paisa of NPV.
  271. 11:52When projects compete, choose the higher
  272. 11:54NPV, not the higher undiscounted total.
  273. 11:58So, the verdict, project B. Same
  274. 12:01investment, same rate, different NPV.
  275. 12:05Project A, minus 41,842
  276. 12:08rupees and 65 paisa.
  277. 12:10Project B, plus one lakh, 11,708
  278. 12:15rupees and nine paisa.
  279. 12:17Project B wins by one lakh, 53,550
  280. 12:21rupees and 74 paisa.
  281. 12:23And notice, project B also takes in more
  282. 12:26in total. 12 lakh, 50,000 rupees against
  283. 12:29project A's 10 lakh. But the timing
  284. 12:32matters, too.
  285. 12:33Project B's later cash flows are bigger,
  286. 12:35and at 10% that is enough to win. So,
  287. 12:39the takeaway. Net present value equals
  288. 12:42NPV. B2, B3 to B7, close bracket, plus
  289. 12:47B1.
  290. 12:48Discount rate, future cash flows, plus
  291. 12:51the initial investment added separately.
  292. 12:53NPV discounts only the future cash
  293. 12:56flows.
  294. 12:57The initial investment at time zero is
  295. 12:58never inside the NPV range.
  296. 13:01NPV above zero, accept. NPV below zero,
  297. 13:05reject.
  298. 13:07A project can take in more than it costs
  299. 13:09and still be rejected. Project two takes
  300. 13:11in seven lakh rupees against five lakh,
  301. 13:14but at 12% its NPV is minus 11,582
  302. 13:19rupees and six paisa.
  303. 13:21When projects compete, choose the higher
  304. 13:23NPV.
  305. 13:24Project B, one lakh, 11,000 708 rupees
  306. 13:29and 9 paisa, beats project A, minus
  307. 13:3241,842
  308. 13:35rupees and 65 paisa.
  309. 13:37And remember the anchor.
  310. 13:3910 lakh rupees today, five cash flows,
  311. 13:4210% a year.
  312. 13:44NPV 2 lakh, 90,786
  313. 13:48rupees and 77 paisa. Accept. Your turn.
  314. 13:52Try it yourself.
  315. 13:53A project needs 6 lakh rupees today and
  316. 13:56returns 1 lakh 50,000, 1 lakh 80,000, 2
  317. 14:00lakh 10,000, 2 lakh 40,000, and 2 lakh
  318. 14:0470,000 rupees over 5 years.
  319. 14:07At 12% a year, what is its NPV? And
  320. 14:10would you accept it?
  321. 14:11Pause here, work it out in Excel, and
  322. 14:14then watch the solution.
  323. 14:19Same function, your numbers. Equals NPV,
  324. 14:23rate, values, plus the initial
  325. 14:25investment separately.
  326. 14:27The layout is the same on every sheet.
  327. 14:29Initial investment in B1, discount rate
  328. 14:31in B2, and the cash flows for years 1 to
  329. 14:345 in B3 to B7. Just the live formula is
  330. 14:37equals NPV, B2, B3 to B7, close bracket,
  331. 14:42plus B1.
  332. 14:44If B2 is 12% a year, B3 to B7 are your
  333. 14:47five cash flows, and B1 is minus 6 lakh
  334. 14:51rupees, added separately. On the your
  335. 14:53turn sheet, I set up the example in the
  336. 14:55same cells. Initial investment in B1,
  337. 14:58minus 6 lakh rupees.
  338. 15:00Discount rate in B2, 12% a year.
  339. 15:03Cash flow year one in B3, 1 lakh 50,000
  340. 15:07rupees.
  341. 15:08Year two in B4, 1 lakh 80,000 rupees.
  342. 15:12Year three in B5, 2 lakh 10,000 rupees.
  343. 15:17Year four in B6, 2 lakh 40,000 rupees.
  344. 15:21Year five in B7, 2 lakh 70,000 rupees.
  345. 15:25The inputs are ready. Now, I click on
  346. 15:28cell B8 and type the formula, equals NPV
  347. 15:31B2 B3 to B7 close bracket plus B1.
  348. 15:36B2 is the discount rate. B3 to B7 are
  349. 15:39the five future cash flows and B1 is the
  350. 15:42investment added separately.
  351. 15:44Press enter and watch. And there it is,
  352. 15:471 lakh 32,626
  353. 15:50rupees and 91 paisa.
  354. 15:53The net present value is positive, so
  355. 15:55accept the project. And the undiscounted
  356. 15:57total is 10 lakh 50,000 rupees.
  357. 16:004 lakh 50,000 more than the 6 lakh
  358. 16:02invested, shrinking to 1 lakh 32,000
  359. 16:06after discounting, but still positive.
  360. 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.