written by: N Nayab
• edited by: Ginny Edwards
• updated: 11/26/2010
Internal Rate of Return (IRR) or Discounted Cash Flow Rate of Return measures the expected rate of returns on an investment or project based on internal factors. It finds widespread application to compare and rank the attractiveness of different projects. Read on for some IRR examples.
slide 1 of 3
Example 1: Manual Method
Internal Rate of Return (IRR) ranks amongst the most popular method of evaluating and comparing capital projects. IRR reveals the projects expected rate of return, all other factors such as external environment factors and risks remaining equal.
Assume a new bottling plant costs $25,000, including installation cost, operating at 100 percent capacity for five years, with uniform operating costs and profits:
cost of raw materials: $750
overheads such as rent, salary, energy costs and others: $1000
costs to maintain the machinery: $250
expected sales revenue per annum: $7500
effective tax rate: 10 percent
The IRR of the project can help in determining the attractiveness of the project relative to the cost of capital.
The first step is to determine the cash flow of the project every year
Annual cash inflow: $7500
Less: cash outflows: ($750+$1000+$250) = $2000
Gross Profit = $7500-$2000 = $5000
Less: Tax @10 percent of gross profit = $500
Net annual Cash Flow =$5000-$500=$4500
The second step is to determine the present value of future cash flows
The formula to determine future cash flows= (Cash Flow)*(1 + Interest Rate)^number of years
Assuming the cost of capital is 5 percent for this project.
Present Value for first year = 4500*(1+5 percent) = 4500*1.05 = 4725
The cash flow can vary every year or period of calculation, but for the sake of convenience, assume that the cash flow remains same for the entire period of the machinery’s lifecycle, that is 5 years.
Present value for the second year = 4500*(1+5 percent)^2 = 4500*1.1025 = 4961.25
Present value for the third year = 4500*(1+5 percent)^3 = 4500*1.1576 = 5209.20
Present value for the fourth year = 4500*(1+5 percent)^4 = 4500*1.2150 = 5467.5
Present value for the fifth year = 4500*(1+5 percent)^5 = 4500*1.2763 = 5743.35
Net present value of the project = -25,000 (initial investment) + 4725 + 4961.25 + 5209.20 + 5467.5 + 5743.35 = 1106.30
At 5 percent, the net present value is greater than the investment, meaning that IRR is less than 5 percent, and if the project delivers a return of 5 percent, it becomes profitable.
In a similar manner, IRR ranks two projects of similar nature.
Calculating IRR manually is a tedious job, and requires determining the net present value at varying cost of capital or interest rates. The manual approach is best limited to determining whether a particular interest rate or cost of capital makes the project worthwhile or not.
values is the range of cells that represent the series of cash flows.
guess is an estimated internal rate of return. This is optional, and the default value is 0.1 or 10 percent. This estimated IRR or guess does not in any way impact the calculation of IRR, and the IRR remains the same irrespective of the guess given. MS Excel uses an iterative technique for calculating IRR, and starts with the guess. If the IRR function cannot find a result that works after 20 tries, it delivers a #NUM! error value.
Assume another internal rate of return example of two projects with the following annual cash flows
The formula to calculate IRR for Project A is =IRR(A1:A5)
The formula to calculate IRR for Project B is IRR(B1:B5)
Project A makes a profit of $1450 in five years and Project B makes a profit of $2500 in the same period. The IRR of Project A is 4 percent whereas the IRR of Project B is 3 percent. This means that in spite of Project B making more profits, Project A remains more attractive for investors.
slide 3 of 3
TechOnTheNet.com. "MS Excel: IRR Function." http://www.techonthenet.com/excel/formulas/irr.php. Retrieved 22 November 2010.
Baker, Samuel, L. “The Internal Rate of Return.” University of South Carolina. http://hadm.sph.sc.edu/courses/econ/irr/irr.html. Retrieved 10 November 2010