How to calculate IRR in Exel for business plan

Internal rate of return (IRR) is the discount rate at which the discounted net cash flow is equal to the discounted amount of investment.


where

  • INV – volume of investments made for a certain period.
  • CF – net cash flow.

CF  =  ICF – OCF

  • ICF – input cash flows during a certain period;
  • OCF – output cash flows covered during a certain period.

Determining the net present value, we considered the risk-free discount rate, but in this case it is possible to determine the rate of return for the current investment project.

In fact, this is the maximum possible rate at which an enterprise could take a loan. Therefore, if a the indicator is significantly higher than the average credit rate, it is necessary to consider all the pros and cons of choosing the sources of funding. This indicator shows the lower guaranteed level of the project’s profitability as well as the maximum rate of investment fee.

If you calculate the IRR for a business plan using the formula above, then you need to take into account the maximum and minimum discount rate. The calculation is quite complex and includes two values that are difficult to predict in unstable economic conditions.

The simplest calculation is done using Excel. Download an example of the business plan in Excel and you’ll see how to do it in the simplest way. The special formula of IRR allows determine the company’s average internal rate of return for five years. This indicator is the closest to the real one.

Download free Excel Template

Get templates from: