IRR() in Excel Explained | Capital Budgeting — Transcript
Full transcript
- 0:00Question one. A project needs 10 lock
- 0:02rupees today and returns two lock 50,000
- 0:06three lock three lock 50,000 four lock
- 0:09and four lock 50,000 rupees over the
- 0:11next 5 years. The cost of capital is 12%
- 0:15a year. What is the project's internal
- 0:17rate of return and should we accept it?
- 0:20Take a second and think. What do you
- 0:22expect the answer to be? Pause here if
- 0:24you like and try it yourself. Right? Let
- 0:27us solve it together in Excel.
- 0:32First the function we use irr equals irr
- 0:36open bracket values comma guess close
- 0:39bracket values are the whole cash flow
- 0:42series including the initial investment
- 0:44at time zero that is the big difference
- 0:46from NPV where the investment stayed
- 0:49outside the range guess is optional a
- 0:52starting point for Excel's search 10% if
- 0:54you leave it out on every sheet the
- 0:57inputs live in the same cells
- 0:59initial investment in B1, cash flows for
- 1:01years 1 to 5 and B2 to B6, the IRR
- 1:05answer in B7, the cost of capital in B8,
- 1:09and two cross checks in B9 and B10. The
- 1:12live formula just points at those cells
- 1:15equals irrational
- 1:17to B6 close bracket. B1 to B6 are the
- 1:21whole series investment included. Let us
- 1:24open the project one sheet and set it up
- 1:26just like you would. Initial investment
- 1:28in B1 minus 10 lock rupees negative
- 1:31because it goes out today. Cash flow
- 1:34year 1 and B2 lock 50,000 rupees. Year 2
- 1:38and B3 lock rupees. Year three and B4
- 1:43three lock 50,000 rupees. Year four and
- 1:46B5 four lock rupees. Year five and B6
- 1:51four lock 50,000 rupees. Cost of capital
- 1:54in B8 12% a year. Good. The inputs are
- 1:58ready. All in B1 to B8. Now I click on
- 2:02cell B7 and type the formula equals IRR
- 2:06B1 to B6 close bracket. B1 to B6 are the
- 2:10whole cash flow series investment
- 2:12included. Now watch what happens when I
- 2:14press enter. And there it is 19.71% a
- 2:18year. The internal rate of return is
- 2:2019.71%
- 2:22and the cost of capital is only 12%. So
- 2:25accept the project and look at rows 9
- 2:27and 10 at 12% the NPV is two lock 21,044
- 2:33rupees and 85 PY positive exactly as it
- 2:36should be when IRR beats the cost of
- 2:38capital and at the IRRa itself the NPV
- 2:41is zero to the PYSE that is the
- 2:44definition checked live irr is the
- 2:47discount rate at which NPV equals zero
- 2:51that is IRR it answers one Question at
- 2:54what discount rate does this project
- 2:56exactly break even in present value
- 2:58terms? The rule is simple. IRR above the
- 3:02cost of capital accept. IRR below the
- 3:05cost of capital reject. Project 1 gives
- 3:0919.71%
- 3:10against a cost of capital of 12%. So we
- 3:13accept. And remember the two things that
- 3:16make IRR different from NPV. First, the
- 3:19IRR range includes the initial
- 3:21investment B1 to B6 the whole series
- 3:25because IRR needs the complete story
- 3:27outflow and inflows together. Second,
- 3:31Excel finds irr by trial and error which
- 3:34is why the guess argument exists. If
- 3:36Excel ever struggles, give it a starting
- 3:38guess near where you expect the answer.
- 3:41Same project 10 lock rupees today the
- 3:43same five cash flows only the discount
- 3:46rate changes at 0% with no discounting
- 3:49at all NPV is 7 lock 50,000 rupees the
- 3:53full undiscounted surplus at 5% four
- 3:57lock 94,215
- 4:00rupees and one pisa at 10% two lock
- 4:0490,786
- 4:06rupees and 77 pisa at 15% One lock
- 4:1126,795
- 4:13rupees and 92 pisa at 20% -7,040
- 4:19rupees and 90 pisa already negative and
- 4:22at 25% minus one lock 17,54
- 4:27rupees. So somewhere between 15 and 20%
- 4:31NPV crosses zero that crossing point is
- 4:34the IRR 19.71%.
- 4:38Now let us make a chart of that so you
- 4:40can see the crossing. First select the
- 4:42rates and their NPV values. Then click
- 4:45the insert tab then charts. Now choose
- 4:48line because this is a journey across
- 4:50rates and a line shows the fall and the
- 4:52crossing. Excel inserts the chart. And
- 4:55now we replace the placeholder title
- 4:57with a proper one and add a label on
- 4:59every point. There it is. A line falling
- 5:02from left to right crossing zero. Look
- 5:05at the shape of it. The net present
- 5:07value falls steadily as the discount
- 5:09rate rises from 7 lock 50,000 rupees at
- 5:120% down through two lock 90,786
- 5:16rupees and 77 pies at 10% to minus one
- 5:20lock 17,54
- 5:23rupees at 25%.
- 5:25And the gold point is where the line
- 5:27crosses zero 19.71%
- 5:31NPV exactly zero. That gold point is the
- 5:35IRRa. Every rate below it gives a
- 5:37positive NPV. Every rate above it gives
- 5:40a negative 1. Let us try this one
- 5:42together. Question two. A project needs
- 5:45five lock rupees today and returns one
- 5:48lock, one lock 20,000, one lock 40,000,
- 5:52one lock 60,000 and one lock 80,000
- 5:55rupees over 5 years. The cost of capital
- 5:58is 12% a year. What is its irr and is it
- 6:02worth it even though it takes in more
- 6:04than it costs? Take a second, have a
- 6:07guess, then watch the solution.
- 6:12Same function, new numbers equals irr,
- 6:17guess close bracket. The layout is the
- 6:20same on every sheet. Initial investment
- 6:22in B1, cash flows for years 1 to 5 and
- 6:26B2 to B6. The irr answer in B7 the cost
- 6:30of capital in B8 just the live formula
- 6:33is equals IRR B1 to B6 close bracket
- 6:38here B1 is minus 5 lock rupees B2 to B6
- 6:42are the five cash flows and B8 is 12% a
- 6:46year on the project 2 sheet I set up the
- 6:48example in the same cells initial
- 6:50investment in B1 minus 5 lock rupees
- 6:54cash flow year 1 in B2 one lock rupees
- 6:58year two in B3 one lock 20,000 rupees
- 7:02year three in B4 one lock 40,000 rupees
- 7:06year four in B5 one lock 60,000 rupees
- 7:11year five in B6 one lock 80,000 rupees
- 7:15cost of capital in B8 12% a year the
- 7:19inputs are ready now I click on cell B7
- 7:22and type the formula equals IR B1 to B6
- 7:27close bracket. B1 to B6 are the whole
- 7:30cash flow series investment included.
- 7:33Press enter and watch. And there it is
- 7:3711.15%
- 7:38a year. The internal rate of return is
- 7:4011.15%
- 7:42below the cost of capital of 12%. So
- 7:45reject the project even though 7 lock
- 7:47rupees comes in against five lock going
- 7:49out. Look at rows 9 and 10. At 12% the
- 7:53NPV is minus 11,582
- 7:57rupees and six pay negative exactly as
- 8:00it should be when IRRa falls short of
- 8:02the cost of capital and at the IRRa
- 8:05itself the NPV is zero. The project
- 8:08looks profitable and it still fails.
- 8:11That is exactly why we compare IRRa with
- 8:13the cost of capital not with zero.
- 8:17Question three and this time two
- 8:19projects compete both need eight lock
- 8:22rupees at the same cost of capital of
- 8:2410% a year project A returns four lock
- 8:28three lock two lock one lock and 50,000
- 8:31rupees over 5 years frontloaded most of
- 8:34the money early what is its irr
- 8:38have a guess then watch the solution
- 8:45same function new numbers equals irr
- 8:49values, comma, guess close bracket. The
- 8:53layout is the same on every sheet.
- 8:55Initial investment in B1, cash flows for
- 8:58years 1 to 5 and B2 to B6, the IRR
- 9:02answer in B7, the cost of capital in B8.
- 9:05And the live formula is equals IRR B1 to
- 9:09B6 close bracket. Here B1 is minus 8
- 9:13lock rupees. B2 to B6 are project A's
- 9:17five cash flows and B8 is 10% a year. On
- 9:20the project A sheet, I set up the
- 9:22example in the same cells. Initial
- 9:24investment in B1 minus 8 lock rupees.
- 9:28Cash flow year 1 in B2 four lock rupees.
- 9:31Year 2 in B3 lock rupees. Year three in
- 9:35B4 two lock rupees. Year four in B5 one
- 9:40lock rupees. Year five in B6 50,000
- 9:43rupees cost of capital in B8 10% a year
- 9:48the inputs are ready now I click on cell
- 9:51B7 and type the formula equals IR B1 to
- 9:55B6 close bracket B1 to B6 are the whole
- 9:59cash flow series investment included
- 10:02press enter and watch and there it is
- 10:0514.14% a year project A's IRRa is 14.14%
- 10:10% above the cost of capital of 10%. So
- 10:14on its own it would be accepted. Its NPV
- 10:16at 10% is 61,180
- 10:20rupees and 62 pesa. Positive consistent
- 10:23but hold that thought. Project B is
- 10:26coming with the same investment and the
- 10:28same cost of capital.
- 10:30Now project B same eight lock rupees
- 10:34same 10% a year but its cash flows are
- 10:36backloaded. Two lock, two lock, two
- 10:40lock, two lock and four lock rupees in
- 10:43the final year. What is its irr and
- 10:46which project wins? Take a second, have
- 10:49a guess, then watch the solution.
- 10:54Same function, new numbers equals irr,
- 10:59guess close bracket. The layout is the
- 11:02same on every sheet. initial investment
- 11:04in B1, cash flows for years 1 to 5 and
- 11:07B2 to B6, the IRR answer in B7, the cost
- 11:11of capital in B8. And so the live
- 11:14formula is equals IRR B1 to B6 close
- 11:18bracket. Here B1 is minus 8 lock rupees.
- 11:22B2 to B6 are project B's five cash flows
- 11:25and B8 is 10% a year. On the project B
- 11:29sheet, I set up the example in the same
- 11:31cells. Initial investment in B1 minus 8
- 11:34lock rupees. Cash flow year 1 and B2
- 11:38lock rupees. Year two in B3 two lock
- 11:41rupees. Year three in B4 two lock
- 11:44rupees. Year four in B5 two lock rupees.
- 11:49Year five in B6 four lock rupees. Cost
- 11:53of capital in B8 10% a year. The inputs
- 11:56are ready. Now I click on cell B7 and
- 12:00type the formula equals irr B1 to B6
- 12:04close bracket B1 to B6 are the whole
- 12:07cash flow series investment included.
- 12:10Press enter and watch. And there it is
- 12:1313.55% a year. Project B's IRRa is
- 12:1713.55%.
- 12:19Also above the cost of capital so on its
- 12:22own it would be accepted too. Its NPV at
- 12:2410% is 82,341
- 12:27rupees and 62 pa. Now look at the two
- 12:30together. IRR says project A wins 14.14
- 12:35against 13.55.
- 12:37But NPV says project B wins 82,341
- 12:42rupees and 62 PASY against 61,180
- 12:46rupees and 62 pa. The two rules
- 12:49disagree.
- 12:51So the verdict project B when two
- 12:54projects are mutually exclusive and the
- 12:56rules disagree choose by NPV not by irr
- 13:0561,180
- 13:07rupees and 62 pesa project B IRRa 13.55%
- 13:14NPV 82,341
- 13:17rupees and 62 pesa project B wins by
- 13:2121,161
- 13:23rupees of NPV. Why? Because irrra ranks
- 13:27percentages and NPV ranks rupees. A
- 13:30higher percentage on the same investment
- 13:32does not always mean more wealth added
- 13:34and wealth added is what the owners
- 13:36actually keep. Two honest limits before
- 13:38you trust irrra too far. First, multiple
- 13:42irr. If the cash flows change sign more
- 13:45than once, money out, money in, then
- 13:47money out again, there can be more than
- 13:49one rate that makes NPV zero. Take a
- 13:52simple case. Invest 100 rupees, take in
- 13:56250, then pay out 150. NPV is zero at
- 14:01both 0% and 50%. So there are two IRRs
- 14:05and Excel will only find one of them
- 14:07depending on your guess. Second, the
- 14:10reinvestment assumption. IRR silently
- 14:13assumes every intermediate cash flow is
- 14:15reinvested at the IRR itself at 19.71%
- 14:20for project one which is a heroic
- 14:22assumption. NPV assumes reinvestment at
- 14:25the cost of capital which is far more
- 14:27realistic. Neither limit means you
- 14:29abandon IRR. Just never let it overrule
- 14:32NPV when projects compete. So the
- 14:35takeaway internal rate of return equals
- 14:38irr B1 to B6 close bracket the whole
- 14:43cash flow series initial investment
- 14:45included unlike NPV guess is optional
- 14:4910% by default irr is the discount rate
- 14:53at which NPV equals zero row 10 proves
- 14:56it on every sheet irr above the cost of
- 14:59capital except IRR below the cost of
- 15:03capital reject. Act project 1 19.71%
- 15:07against 12% accept. Project 2 11.15%
- 15:13against 12% reject. Even though seven
- 15:16lock comes in against five lock when
- 15:19projects compete and the rules disagree.
- 15:21Choose by NPV. Project B 82,341
- 15:27rupees and 62 pesa beats project A
- 15:3061,180
- 15:32rupees and 62 pay even though project
- 15:36A's IRR is higher. And remember the
- 15:38limits non-conventional cash flows can
- 15:41give multiple IRRs and irr assumes
- 15:44reinvestment at the IRRa itself. Your
- 15:47turn. Try it yourself. A project needs
- 15:50five lock rupees today and returns one
- 15:52lock 20,000, one lock 40,000, one lock
- 15:5660,000, one lock 80,000 and two lock
- 16:00rupees over 5 years. The cost of capital
- 16:03is 12% a year. What is its IRRa and
- 16:07would you accept it? Pause here, work it
- 16:09out in Excel and then watch the
- 16:11solution.
- 16:16Same function your numbers equals irr
- 16:20values comma guess close bracket the
- 16:24layout is the same on every sheet
- 16:26initial investment in B1 cash flows for
- 16:29years 1 to 5 in B2 to B6 the IRR answer
- 16:33in B7 the cost of capital in B8 the live
- 16:36formula is equals IRR B1 to B6 close
- 16:41bracket here B1 1 is minus 5 lock
- 16:45rupees. B2 to B6 are your five cash
- 16:48flows and B8 is 12% a year. On the your
- 16:51turn sheet, I set up the example in the
- 16:53same cells. Initial investment in B1
- 16:56minus 5 lock rupees. Cash flow year 1 in
- 16:59B2 one lock 20,000 rupees. Year two in
- 17:03B3 one lock 40,000 rupees. Year three in
- 17:07B4 one lock 60,000 rupees. Year four in
- 17:11B5 one lock 80,000 rupees year five and
- 17:14B6 two lock rupees cost of capital in B8
- 17:1912% a year the inputs are ready now I
- 17:22click on cell B7 and type the formula
- 17:25equals IRR B1 to B6 close bracket B1 to
- 17:30B6 are the whole cash flow series
- 17:33investment included press enter and
- 17:36watch and there it is 16.36% a Here the
- 17:40internal rate of return is 16.36%
- 17:43above the cost of capital of 12%. So
- 17:46accept the project and the cross check
- 17:48agrees. At 12% the NPV is 60,513
- 17:53rupees and 46 pes positive exactly as it
- 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.