Internal Rate of Return

Internal Rate of Return - IRR

Internal Rate of Return (IRR) is defined as the discount rate that makes the Net Present Value zero. If the IRR is greater than the opportunity cost of capital then the investment is feasible. The greater the value the IRR, the more feasible it is.

It is however important to note that the IRR which makes the NPV zero is possible only if all the cash flow earned are invested at the IRR rate immediately. In real life circumstances, this is considered very ideal and not easily achievable.

The spreadsheet in Rate of Return.xls illustrates how to calculate the IRR of an Investment with NPV zero. Basically it involves in using the Goal Seek capabilities of Microsoft Excel. Basically Goal Seek will iterate through a set of values to determine the IRR value that will make NPV zero. The spreadsheet has already been setup for you to easily do this. First fill in the Cash Outflow and Cash Inflow in column B and C. Use Goal Seek to set Cell C4 to “Value 0” by “Changing cell C5”. The field marked with “**” means that the value of the field will be calculated by Goal Seek.

It is possible that the NPV and IRR will give conflicting results. If there are two investments, A and B, NPV may suggest selecting investment A while IRR may suggest selecting investment B. In this scenario, we should tend towards using the NPV to select the investment. The reason is NPV represents the actual amount an investment will make which increases actual wealth by the biggest amount. The IRR formula also assumes we are able to reinvest the earnings at the IRR rate which is an ideal scenario.

Next :
Money Weighted Rate of Return

Back to Free Investment and Financial Calculator main page.

Back to Excel Add-Ins and Templates main page.