IRR Calculation in Excel

IRR (Internal Rate of Return) is the most widely used financial indicator while assessing return on an investment or a project. It is defined as the discount rate which makes the net present value of the cash flows from the investment equal to zero. To understand it in simple terms, if an investment of 100 yields cash inflows of 10 at the end of every year for next 3 years and at the end of 3rd year, the initial investment of 100 is also received, the IRR for this investment is 10%; because if all the cash inflows from the investment viz., 10 at the end of year1, another 10 at the of year2 and 110 at the end of year3 are all discounted to year0 (investment date) at the rate of 10%, the present value of this will be 100 i.e., the same as the initial investment, which means the Net Present Value (NPV) of the above cash flows is zero.

Now, imagine the yearly cash inflows in the above example are not uniform but say, they are 5 at the end of year1, 10 at the end of year2 and 115 (including original investment) at the end of year3; We can discount each of these cash flows at a particular discount rate and arrive at the present value; but what discount rate will make such present of these cash flows equal to the original investment of 100 (so that the NPV is zero); If it is to be done manually, we have to make several attempts on trial and error basis (or iteratively) to arrive at exact discount rate (or IRR) which makes the net present value of the cash flow equal to zero.

In excel spreadsheet, we can calculate such discount rate or IRR for any series of cash flows by using the built-in IRR function almost instantaneously. What will be the IRR in the second example above? Though the absolute values of the returns are the same as in the first example, IRR in the second case will be lower, because of lower amount received in the initial years i.e., the time value of money comes into picture here. The underlying principle of time value of money is " a dollar today is worth more than a dollar tomorrow"; The IRR calculation mentioned above is demonstrated in this excel spreadsheet.

If the cash flows are NOT yearly but at any other uniform intervals like monthly, quarterly, semi annual, etc, IRR function can still be used in Excel but the result needs to be annualized with appropriate factor to calculate effective annual IRR(See below); If the cash flows are at IRREGULAR intervals, Excel's XIRR function can be used to calculate the IRR.

IRR Calculation with monthly cash flows in Excel:

When calculating IRR with Excel's IRR function, Excel assumes that the cash flows are annual cash flows. Therefore, if we want to calculate IRR for monthly cash flows, we have to first calculate IRR for these cash flows using IRR function and then we need to annualize the result by using the factor [(1+IRR)^12]-1, where IRR is the IRR calculated for montly cash flows using Excel's IRR function. This spreadsheet demonstrates calculation of IRR for monthly cash flows in Excel. The same aproach can be used for calculating IRR for cash flows occurring at any other regular intervals like quarterly or semi annual - by using appropriate factor.