### How to Calculate Internal Rate of Return (IRR) in Excel

Last week, I covered how to calculate discounted cash flow. In this post, I’ll build off that worksheet and show you how you can calculate the internal rate of return (IRR) in Excel. IRR tells you the return that you’re making on an investment or project, and at what discount rate the net present value of all the cash flows will be zero. In these scenarios, there’s typically an outlay of cash, usually at the beginning.

In my previous example, I only looked at cash flows coming in. This time, I’ll look at a scenario where you pay money out at the beginning and generate cash flow in future periods. A common example is paying to upgrade a piece of equipment and then generating cost savings from it for x number of years. Knowing the IRR can tell you if you’re making enough of a return off of the investment and whether you should move forward with it. Using IRR can also be helpful when you’re comparing multiple options to see which one is the best one.

This step is about the same as when setting up the discounted cash flow template. You’ll need to enter the different years, the cash you expect to come in or out, and then calculate back what the present value is today.

Here’s what the file looks like setting in a scenario where you pay \$100,000 upfront and then generate \$10,000 in cash flow for 25 years. At a 5% discount rate, in this example the present value of all that cash flow is a positive \$40,939.45:

## Calculating the IRR

The problem here is the discount rate can be difficult to determine, and that can have a significant impact on your overall returns. And so rather than worry about what your discount rate should be, you only need to determine the IRR — which is to say at what point would your present value be worth \$0? If you need a higher return than the IRR the project would be a no-go but if you’re okay with anything up to and including the IRR, then the project or investment would be passable. What it comes down to is the lower the IRR is, the worse the investment is

There are a couple of different ways to calculate IRR in Excel. One way is through a formula called XIRR. It only has two required arguments — dates and cash flow. This is why in this example I entered dates for my cash flows rather than just numbering the years. This makes it easier for me to use the XIRR formula. In my spreadsheet, I enter the following formula:

=XIRR(D6:D31,C6:C31)

Column D contains my cash flow and column C contains the dates. Doing this, Excel tells me the IRR is 9.687% for this specific project. But if I work backwards and calculate the net present value, it doesn’t get me right to 0:

It certainly gets close to 0 and it’s probably close enough that it can help you make a decision about your investment. However, there’s another way to calculate IRR and that’s using Excel’s What-If Analysis. On the Data tab, there’s a drop-down for this option in the Forecast section:

Depending on which version of Excel you’re using, it may show a bit differently, but what you’re ultimately looking for is Goal Seek.

Goal Seek is an accelerated way of doing trial-and-error. Excel’s doing it for you much quicker than you could ever do it by yourself. For IRR, it’s the best solution.

Here’s how it works. You’ll need to enter the cell that you want to get to a certain value, what value that is, and which cell Excel should be changing values in. In my spreadsheet, E2 is where my net present value formula is, and I want that to equal 0. In cell B2 is my discount rate, which is what I want Excel to be changing. Here are what my inputs look like:

Then, once I click on OK, Excel goes to work. After a few seconds you should see Excel show you that the target value and the current value are a match (e.g. they’re both 0), meaning it’s done its job successfully:

Now, if I look at my template, I see a different discount rate and my total present value is netting out to 0:

As you can see, this is much more accurate than Excel’s XIRR function. You can repeat these steps and make this table for other projects that you can assess side-by-side.

If you’d like to test this out, try downloading the discounted cash flow spreadsheet from my last post and then just using Goal Seek or the XIRR function to determine your IRR. You can remove unnecessary columns from the sheet and then duplicate the table, and then you’ve got a template where you can assess multiple investments against one another.

If you liked this post on how to calculate IRR in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

### Use Goal Seek to Avoid Trial and Error

Excel’s what-if-analysis options aren’t the greatest, but one option which is very useful and can save time is Goal Seek. What Goal Seek effectively does is it can do trial and error for you in seconds, and be much more precise than doing it manually.

In many examples you could use algebra to get to your answer but if it’s a one-time calculation perhaps it’s not worth the trouble of going that route. For example, suppose I had an investment of \$1,234 and wanted to know what average return would be needed for it to grow to \$10,000 after 10 years. This can certainly be done with algebra, but Goal Seek can also do it. Below, I have entered my inputs in yellow.
My ending balance is equal to the following formula : =C4*(1+C5)^C6
C4 = Investment
C5 = Required Rate of Return
C6 = Years
Next, I will select Goal Seek from the Data tab under What-If Analysis

The next screen prompts me to enter which cell I want to set to which value, and which cell I want changed to accomplish this (the variable). In my example I use C7 for the set cell box (this is my desired ending balance), the value is 10,000, and by changing cell I enter my required rate of return (which is C5).
After clicking OK it fills in the required rate of return as 0.23273147. It also sets the value in C5 to this amount as well. If I hit OK on the next screen the value in C5 will keep this value, if I cancel, it will revert to what was there before – nothing.
This was a simple example but there are also more complex ones it can be used for. Another example could be an amortization table. The table below has a principal amount owing of 10,000, an interest rate of 5%, a payment of 550 per month. This is what it looks like:

So it will take roughly 19 payments to for this amount to be paid off under the terms described. But let’s say I wanted to know precisely what interest rate would have to be applied to make the ending balance at payment number 20 equal 0. I don’t want an extra payment for the balance to be included at the end, just so that it perfectly matches 0 after payment 20.

To do this, I go back to Goal Seek. For the first box (set cell) which is what I want my result to be – I select the ending balance at payment 20 – currently it is (579.34). This is cell F27 on my spreadsheet. The second box is what result I want, which in this case is 0. Lastly, my variable, or the cell I want changed to make this work – that is my interest rate, which is in cell C4 – currently it is at 5%. These are what my inputs look like:

Once I hit OK, I get the following:
You’ll notice now my target value matches my current value and the Goal Seek was successful. It has changed my interest rate to 11% – not exactly 11%, 11.1048262490731%. A very precise percentage that has allowed my table to reach 0.
Goal Seek can be used in a variety of different circumstances as you can see. As long as you have a formula like in the above two cases that the change cell (e.g. variable) directly effects, then it should work. I could have selected the payment as my change cell rather than the interest rate and my result would be as follows:
It would indicate a rounded payment of 522.16 (exact amount of 522.162995552194) would be needed to pay off after 20 payments with the interest rate staying at 5%.
The one limitation of Goal Seek is that you can only use one variable. But beyond that it does it very well and can help save you a lot of time.