- 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
IRR is the rate at which the net present value of the investment becomes zero.
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.
Image Credit: flickr.com/Kevinzhengli
- slide 2 of 3
Example 2: Using MS-Excel
Choosing the best alternative by IRR and calculating the IRR percentage is best done using the built in formula in MS-EXCEL
Irr (values, guess)
- 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
- Project A: -9000 (Initial Investment), -2000 (loss),1200, 4750, 6500
- Project B: -27000 (Initial Investment), 6250, 6500, 8250, 8500
Type in these figures column wise in MS Excel.
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
References
- 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