YouTube2Text

IRR() in Excel Explained | Capital Budgeting — Transcript

by srccan Academy · 2,382 words · 335 segments · language en · Watch on YouTube

Full transcript

  1. 0:00Question one. A project needs 10 lock
  2. 0:02rupees today and returns two lock 50,000
  3. 0:06three lock three lock 50,000 four lock
  4. 0:09and four lock 50,000 rupees over the
  5. 0:11next 5 years. The cost of capital is 12%
  6. 0:15a year. What is the project's internal
  7. 0:17rate of return and should we accept it?
  8. 0:20Take a second and think. What do you
  9. 0:22expect the answer to be? Pause here if
  10. 0:24you like and try it yourself. Right? Let
  11. 0:27us solve it together in Excel.
  12. 0:32First the function we use irr equals irr
  13. 0:36open bracket values comma guess close
  14. 0:39bracket values are the whole cash flow
  15. 0:42series including the initial investment
  16. 0:44at time zero that is the big difference
  17. 0:46from NPV where the investment stayed
  18. 0:49outside the range guess is optional a
  19. 0:52starting point for Excel's search 10% if
  20. 0:54you leave it out on every sheet the
  21. 0:57inputs live in the same cells
  22. 0:59initial investment in B1, cash flows for
  23. 1:01years 1 to 5 and B2 to B6, the IRR
  24. 1:05answer in B7, the cost of capital in B8,
  25. 1:09and two cross checks in B9 and B10. The
  26. 1:12live formula just points at those cells
  27. 1:15equals irrational
  28. 1:17to B6 close bracket. B1 to B6 are the
  29. 1:21whole series investment included. Let us
  30. 1:24open the project one sheet and set it up
  31. 1:26just like you would. Initial investment
  32. 1:28in B1 minus 10 lock rupees negative
  33. 1:31because it goes out today. Cash flow
  34. 1:34year 1 and B2 lock 50,000 rupees. Year 2
  35. 1:38and B3 lock rupees. Year three and B4
  36. 1:43three lock 50,000 rupees. Year four and
  37. 1:46B5 four lock rupees. Year five and B6
  38. 1:51four lock 50,000 rupees. Cost of capital
  39. 1:54in B8 12% a year. Good. The inputs are
  40. 1:58ready. All in B1 to B8. Now I click on
  41. 2:02cell B7 and type the formula equals IRR
  42. 2:06B1 to B6 close bracket. B1 to B6 are the
  43. 2:10whole cash flow series investment
  44. 2:12included. Now watch what happens when I
  45. 2:14press enter. And there it is 19.71% a
  46. 2:18year. The internal rate of return is
  47. 2:2019.71%
  48. 2:22and the cost of capital is only 12%. So
  49. 2:25accept the project and look at rows 9
  50. 2:27and 10 at 12% the NPV is two lock 21,044
  51. 2:33rupees and 85 PY positive exactly as it
  52. 2:36should be when IRR beats the cost of
  53. 2:38capital and at the IRRa itself the NPV
  54. 2:41is zero to the PYSE that is the
  55. 2:44definition checked live irr is the
  56. 2:47discount rate at which NPV equals zero
  57. 2:51that is IRR it answers one Question at
  58. 2:54what discount rate does this project
  59. 2:56exactly break even in present value
  60. 2:58terms? The rule is simple. IRR above the
  61. 3:02cost of capital accept. IRR below the
  62. 3:05cost of capital reject. Project 1 gives
  63. 3:0919.71%
  64. 3:10against a cost of capital of 12%. So we
  65. 3:13accept. And remember the two things that
  66. 3:16make IRR different from NPV. First, the
  67. 3:19IRR range includes the initial
  68. 3:21investment B1 to B6 the whole series
  69. 3:25because IRR needs the complete story
  70. 3:27outflow and inflows together. Second,
  71. 3:31Excel finds irr by trial and error which
  72. 3:34is why the guess argument exists. If
  73. 3:36Excel ever struggles, give it a starting
  74. 3:38guess near where you expect the answer.
  75. 3:41Same project 10 lock rupees today the
  76. 3:43same five cash flows only the discount
  77. 3:46rate changes at 0% with no discounting
  78. 3:49at all NPV is 7 lock 50,000 rupees the
  79. 3:53full undiscounted surplus at 5% four
  80. 3:57lock 94,215
  81. 4:00rupees and one pisa at 10% two lock
  82. 4:0490,786
  83. 4:06rupees and 77 pisa at 15% One lock
  84. 4:1126,795
  85. 4:13rupees and 92 pisa at 20% -7,040
  86. 4:19rupees and 90 pisa already negative and
  87. 4:22at 25% minus one lock 17,54
  88. 4:27rupees. So somewhere between 15 and 20%
  89. 4:31NPV crosses zero that crossing point is
  90. 4:34the IRR 19.71%.
  91. 4:38Now let us make a chart of that so you
  92. 4:40can see the crossing. First select the
  93. 4:42rates and their NPV values. Then click
  94. 4:45the insert tab then charts. Now choose
  95. 4:48line because this is a journey across
  96. 4:50rates and a line shows the fall and the
  97. 4:52crossing. Excel inserts the chart. And
  98. 4:55now we replace the placeholder title
  99. 4:57with a proper one and add a label on
  100. 4:59every point. There it is. A line falling
  101. 5:02from left to right crossing zero. Look
  102. 5:05at the shape of it. The net present
  103. 5:07value falls steadily as the discount
  104. 5:09rate rises from 7 lock 50,000 rupees at
  105. 5:120% down through two lock 90,786
  106. 5:16rupees and 77 pies at 10% to minus one
  107. 5:20lock 17,54
  108. 5:23rupees at 25%.
  109. 5:25And the gold point is where the line
  110. 5:27crosses zero 19.71%
  111. 5:31NPV exactly zero. That gold point is the
  112. 5:35IRRa. Every rate below it gives a
  113. 5:37positive NPV. Every rate above it gives
  114. 5:40a negative 1. Let us try this one
  115. 5:42together. Question two. A project needs
  116. 5:45five lock rupees today and returns one
  117. 5:48lock, one lock 20,000, one lock 40,000,
  118. 5:52one lock 60,000 and one lock 80,000
  119. 5:55rupees over 5 years. The cost of capital
  120. 5:58is 12% a year. What is its irr and is it
  121. 6:02worth it even though it takes in more
  122. 6:04than it costs? Take a second, have a
  123. 6:07guess, then watch the solution.
  124. 6:12Same function, new numbers equals irr,
  125. 6:17guess close bracket. The layout is the
  126. 6:20same on every sheet. Initial investment
  127. 6:22in B1, cash flows for years 1 to 5 and
  128. 6:26B2 to B6. The irr answer in B7 the cost
  129. 6:30of capital in B8 just the live formula
  130. 6:33is equals IRR B1 to B6 close bracket
  131. 6:38here B1 is minus 5 lock rupees B2 to B6
  132. 6:42are the five cash flows and B8 is 12% a
  133. 6:46year on the project 2 sheet I set up the
  134. 6:48example in the same cells initial
  135. 6:50investment in B1 minus 5 lock rupees
  136. 6:54cash flow year 1 in B2 one lock rupees
  137. 6:58year two in B3 one lock 20,000 rupees
  138. 7:02year three in B4 one lock 40,000 rupees
  139. 7:06year four in B5 one lock 60,000 rupees
  140. 7:11year five in B6 one lock 80,000 rupees
  141. 7:15cost of capital in B8 12% a year the
  142. 7:19inputs are ready now I click on cell B7
  143. 7:22and type the formula equals IR B1 to B6
  144. 7:27close bracket. B1 to B6 are the whole
  145. 7:30cash flow series investment included.
  146. 7:33Press enter and watch. And there it is
  147. 7:3711.15%
  148. 7:38a year. The internal rate of return is
  149. 7:4011.15%
  150. 7:42below the cost of capital of 12%. So
  151. 7:45reject the project even though 7 lock
  152. 7:47rupees comes in against five lock going
  153. 7:49out. Look at rows 9 and 10. At 12% the
  154. 7:53NPV is minus 11,582
  155. 7:57rupees and six pay negative exactly as
  156. 8:00it should be when IRRa falls short of
  157. 8:02the cost of capital and at the IRRa
  158. 8:05itself the NPV is zero. The project
  159. 8:08looks profitable and it still fails.
  160. 8:11That is exactly why we compare IRRa with
  161. 8:13the cost of capital not with zero.
  162. 8:17Question three and this time two
  163. 8:19projects compete both need eight lock
  164. 8:22rupees at the same cost of capital of
  165. 8:2410% a year project A returns four lock
  166. 8:28three lock two lock one lock and 50,000
  167. 8:31rupees over 5 years frontloaded most of
  168. 8:34the money early what is its irr
  169. 8:38have a guess then watch the solution
  170. 8:45same function new numbers equals irr
  171. 8:49values, comma, guess close bracket. The
  172. 8:53layout is the same on every sheet.
  173. 8:55Initial investment in B1, cash flows for
  174. 8:58years 1 to 5 and B2 to B6, the IRR
  175. 9:02answer in B7, the cost of capital in B8.
  176. 9:05And the live formula is equals IRR B1 to
  177. 9:09B6 close bracket. Here B1 is minus 8
  178. 9:13lock rupees. B2 to B6 are project A's
  179. 9:17five cash flows and B8 is 10% a year. On
  180. 9:20the project A sheet, I set up the
  181. 9:22example in the same cells. Initial
  182. 9:24investment in B1 minus 8 lock rupees.
  183. 9:28Cash flow year 1 in B2 four lock rupees.
  184. 9:31Year 2 in B3 lock rupees. Year three in
  185. 9:35B4 two lock rupees. Year four in B5 one
  186. 9:40lock rupees. Year five in B6 50,000
  187. 9:43rupees cost of capital in B8 10% a year
  188. 9:48the inputs are ready now I click on cell
  189. 9:51B7 and type the formula equals IR B1 to
  190. 9:55B6 close bracket B1 to B6 are the whole
  191. 9:59cash flow series investment included
  192. 10:02press enter and watch and there it is
  193. 10:0514.14% a year project A's IRRa is 14.14%
  194. 10:10% above the cost of capital of 10%. So
  195. 10:14on its own it would be accepted. Its NPV
  196. 10:16at 10% is 61,180
  197. 10:20rupees and 62 pesa. Positive consistent
  198. 10:23but hold that thought. Project B is
  199. 10:26coming with the same investment and the
  200. 10:28same cost of capital.
  201. 10:30Now project B same eight lock rupees
  202. 10:34same 10% a year but its cash flows are
  203. 10:36backloaded. Two lock, two lock, two
  204. 10:40lock, two lock and four lock rupees in
  205. 10:43the final year. What is its irr and
  206. 10:46which project wins? Take a second, have
  207. 10:49a guess, then watch the solution.
  208. 10:54Same function, new numbers equals irr,
  209. 10:59guess close bracket. The layout is the
  210. 11:02same on every sheet. initial investment
  211. 11:04in B1, cash flows for years 1 to 5 and
  212. 11:07B2 to B6, the IRR answer in B7, the cost
  213. 11:11of capital in B8. And so the live
  214. 11:14formula is equals IRR B1 to B6 close
  215. 11:18bracket. Here B1 is minus 8 lock rupees.
  216. 11:22B2 to B6 are project B's five cash flows
  217. 11:25and B8 is 10% a year. On the project B
  218. 11:29sheet, I set up the example in the same
  219. 11:31cells. Initial investment in B1 minus 8
  220. 11:34lock rupees. Cash flow year 1 and B2
  221. 11:38lock rupees. Year two in B3 two lock
  222. 11:41rupees. Year three in B4 two lock
  223. 11:44rupees. Year four in B5 two lock rupees.
  224. 11:49Year five in B6 four lock rupees. Cost
  225. 11:53of capital in B8 10% a year. The inputs
  226. 11:56are ready. Now I click on cell B7 and
  227. 12:00type the formula equals irr B1 to B6
  228. 12:04close bracket B1 to B6 are the whole
  229. 12:07cash flow series investment included.
  230. 12:10Press enter and watch. And there it is
  231. 12:1313.55% a year. Project B's IRRa is
  232. 12:1713.55%.
  233. 12:19Also above the cost of capital so on its
  234. 12:22own it would be accepted too. Its NPV at
  235. 12:2410% is 82,341
  236. 12:27rupees and 62 pa. Now look at the two
  237. 12:30together. IRR says project A wins 14.14
  238. 12:35against 13.55.
  239. 12:37But NPV says project B wins 82,341
  240. 12:42rupees and 62 PASY against 61,180
  241. 12:46rupees and 62 pa. The two rules
  242. 12:49disagree.
  243. 12:51So the verdict project B when two
  244. 12:54projects are mutually exclusive and the
  245. 12:56rules disagree choose by NPV not by irr
  246. 13:0561,180
  247. 13:07rupees and 62 pesa project B IRRa 13.55%
  248. 13:14NPV 82,341
  249. 13:17rupees and 62 pesa project B wins by
  250. 13:2121,161
  251. 13:23rupees of NPV. Why? Because irrra ranks
  252. 13:27percentages and NPV ranks rupees. A
  253. 13:30higher percentage on the same investment
  254. 13:32does not always mean more wealth added
  255. 13:34and wealth added is what the owners
  256. 13:36actually keep. Two honest limits before
  257. 13:38you trust irrra too far. First, multiple
  258. 13:42irr. If the cash flows change sign more
  259. 13:45than once, money out, money in, then
  260. 13:47money out again, there can be more than
  261. 13:49one rate that makes NPV zero. Take a
  262. 13:52simple case. Invest 100 rupees, take in
  263. 13:56250, then pay out 150. NPV is zero at
  264. 14:01both 0% and 50%. So there are two IRRs
  265. 14:05and Excel will only find one of them
  266. 14:07depending on your guess. Second, the
  267. 14:10reinvestment assumption. IRR silently
  268. 14:13assumes every intermediate cash flow is
  269. 14:15reinvested at the IRR itself at 19.71%
  270. 14:20for project one which is a heroic
  271. 14:22assumption. NPV assumes reinvestment at
  272. 14:25the cost of capital which is far more
  273. 14:27realistic. Neither limit means you
  274. 14:29abandon IRR. Just never let it overrule
  275. 14:32NPV when projects compete. So the
  276. 14:35takeaway internal rate of return equals
  277. 14:38irr B1 to B6 close bracket the whole
  278. 14:43cash flow series initial investment
  279. 14:45included unlike NPV guess is optional
  280. 14:4910% by default irr is the discount rate
  281. 14:53at which NPV equals zero row 10 proves
  282. 14:56it on every sheet irr above the cost of
  283. 14:59capital except IRR below the cost of
  284. 15:03capital reject. Act project 1 19.71%
  285. 15:07against 12% accept. Project 2 11.15%
  286. 15:13against 12% reject. Even though seven
  287. 15:16lock comes in against five lock when
  288. 15:19projects compete and the rules disagree.
  289. 15:21Choose by NPV. Project B 82,341
  290. 15:27rupees and 62 pesa beats project A
  291. 15:3061,180
  292. 15:32rupees and 62 pay even though project
  293. 15:36A's IRR is higher. And remember the
  294. 15:38limits non-conventional cash flows can
  295. 15:41give multiple IRRs and irr assumes
  296. 15:44reinvestment at the IRRa itself. Your
  297. 15:47turn. Try it yourself. A project needs
  298. 15:50five lock rupees today and returns one
  299. 15:52lock 20,000, one lock 40,000, one lock
  300. 15:5660,000, one lock 80,000 and two lock
  301. 16:00rupees over 5 years. The cost of capital
  302. 16:03is 12% a year. What is its IRRa and
  303. 16:07would you accept it? Pause here, work it
  304. 16:09out in Excel and then watch the
  305. 16:11solution.
  306. 16:16Same function your numbers equals irr
  307. 16:20values comma guess close bracket the
  308. 16:24layout is the same on every sheet
  309. 16:26initial investment in B1 cash flows for
  310. 16:29years 1 to 5 in B2 to B6 the IRR answer
  311. 16:33in B7 the cost of capital in B8 the live
  312. 16:36formula is equals IRR B1 to B6 close
  313. 16:41bracket here B1 1 is minus 5 lock
  314. 16:45rupees. B2 to B6 are your five cash
  315. 16:48flows and B8 is 12% a year. On the your
  316. 16:51turn sheet, I set up the example in the
  317. 16:53same cells. Initial investment in B1
  318. 16:56minus 5 lock rupees. Cash flow year 1 in
  319. 16:59B2 one lock 20,000 rupees. Year two in
  320. 17:03B3 one lock 40,000 rupees. Year three in
  321. 17:07B4 one lock 60,000 rupees. Year four in
  322. 17:11B5 one lock 80,000 rupees year five and
  323. 17:14B6 two lock rupees cost of capital in B8
  324. 17:1912% a year the inputs are ready now I
  325. 17:22click on cell B7 and type the formula
  326. 17:25equals IRR B1 to B6 close bracket B1 to
  327. 17:30B6 are the whole cash flow series
  328. 17:33investment included press enter and
  329. 17:36watch and there it is 16.36% a Here the
  330. 17:40internal rate of return is 16.36%
  331. 17:43above the cost of capital of 12%. So
  332. 17:46accept the project and the cross check
  333. 17:48agrees. At 12% the NPV is 60,513
  334. 17:53rupees and 46 pes positive exactly as it
  335. 17:57should be. Did you get the same

About this transcript

This page contains the full transcript of IRR() in Excel Explained | Capital Budgeting by srccan Academy, generated from the public captions YouTube serves with the video. The transcript has 2,382 words across 335 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.