Internal rate of return IRR Excel Function

Written by:Abraham A.
Location:Excel

Learn to find internal rate of return IRR with MS Excel function, download easy to use Excel template. Here we will show you how you can use Excel to find internal rate of return with the IRR formula in Excel.

What is IRR

IRR is a discounted cash flow technique used in financial analysis to evaluate capital investment proposals. IRR is referred to as investor's required rate of return, and at this rate the net present value of the cash flows is zero. We prefer to have an IRR that is higher than the company's WACC.

IRR Formula in Excel

MS Excel provides a built in formula to find internal rate of return, to use this formula you type in
=IRR(values,guess)
Here the values are a range of cells such as A1:B10 that carry the net cash flows, one thing to keep in mind is that at least one of these values must be a negative number and other may be positive, or mixed. The second argument the formula may expect is the guess but this one is optional and you need not provide it in which case Excel uses a default value of 10%. The guess comes in handy in cases where Excel is unable to find the IRR for the net cash flows you provided and when it reports #NUM error. This may happen as Excel only iterates through the program logic only 20 times and if in these 20 iterations it is unable to find IRR it reports an error. What you can do is provide a close guess for the IRR such as 1% if you think the IRR is close to 0%

IRR MS Excel Example

Los Angeles Police Department in California is planning an installation of Candy Bar Vending machine for its officers. The machine will cost $15,000 and is expected to generate sales in amounts of $3900, $4900, $5900, and $6900 each of the next four years. LAPD has a WACC of 12%. Compute the IRR

In Cell A1, Type the text WACC. In Cell B1 enter the value 12%.

In Cell A2, Type the text Time, In Cell B2 type 0, in cell C2 type 1, in cell D2 type 2, in cell E2 type 3 and in cell F2 type 4.

In Cell A3, Type the text Cash Flows, In Cell B3 type -15000, in cell C3 type 3900, in cell D3 type 4900, in cell E3 type 5900 and in cell F3 type 6900.

In Cell A4, Type the text IRR. In Cell B4 enter the following function

=IRR(B3:F3)

MS Excel IRR Template

Download this MS Excel Template to find Internal Rate of Return
A B C D E F
1 WACC 12%        
2 Time 0 1 2 3 4
3 Cash Flow -15000 3900 4900 5900 6900
4 IRR 14.76%        

Related Investment Analysis Excel Templates

Following is a list of related Excel templates that cover other 5 commonly used investment analysis methods
  1. MIRR in Excel
  2. NPV in Excel
  3. Profitability index in Excel
  4. Payback period in Excel
  5. Discounted payback period in Excel
Location: Excel

IRR Calculator

Developed by:Emma A.
Location:Financial Calculators
type in the authorization code in the box located below:

Results

IRR = 26.21%
Annualized IRR = 26.21%

Input Data

type in an initial guess for IRR:  %
type in net cash flows in the space below:

Select frequency of net cash flows:

Instructions

  1. Enter the series of cash flows in the text box where each of the cash flows is separated by a space. Ensure there is at least 1 positive and at least 1 negative cash flow
  2. Select the frequency of the cash flows that may be either daily, weekly, bi-weekly, monthly, quarterly, semi-annually or annually
  3. If the IRR calculator does not return a result, retry the calculations using a guess rate different than 10%
Location: Financial Calculators