How to Calculate Internal Rate of Return (IRR) with Excel

Internal rate of return (IRR) that is also known as the discounted cash flow rate of return (DCFROR), is commonly used to evaluate the profitability of an investment. For folks who like to sign up for an investment plan, endowment plan and wealth accumulation plan to enhance their wealth management and diversification certainly would like to know the internal rate of return. Normally, the IRR is not mentioned in the plan, but can be easily calculated with Microsoft Office Excel application. With Microsoft Excel, you can calculate IRR yourself to avoid any misleading cases due to dishonest financial planner and agent.

For example, an investment plan requires you to invest $6,000 annually for 10 years, and $1,000 cash is returned as rebate from year 2 to year 10, followed by $2,500 yearly cash rebate from year 11 – 19. For final year (20) which is when the plan is terminated, you can get back another one lump sum of $80,000. So, what’s the internal rate of return?

Formula to use in Excel is IRR(values,guess).

Note: guess is a number that you guess is close to the result of IRR; Excel will use the default value of 0.1 (10%) if omitted. It’s normally no need to enter a guess value for the IRR calculation.

Just as what you see in the photo above, key in the amount into same column such as column A (negative is money out while positive is money in), from row 1 – 20 (depends how long period of the plan), then select IRR function and select A1 to A20.

Formula =IRR(A1:A20) = 0.053 = 5.3%

The real effective interest rate of the plan A is 5.3% per year!