How to Calculate IRR in Excel?In Excel, the IRR function is a financial function that returns the internal rate of return (IRR) for a series of cash flows at regular intervals. What is IRR?The internal rate of return (IRR) is the discount rate that makes the net present value equal to zero. When the IRR has only one value, this criterion becomes more interesting when comparing different investments' profitability. In Excel, IRR has the following calculation formula: The IRR function uses the following arguments: 1. Values (required argument): This is an array of values representing the series of cash flows. Cash flows include investment and net income values. Values can be a reference to a range of cells containing values.
2. Guess (optional argument): This is a number guessed by the user that is close to the expected internal rate of return (as there can be two solutionsfor the internal rate of return).
IRR Function in ExcelThe problem with using math to calculate the internal rate of return is that the necessary calculations are complicated and timeconsuming. A mathbased solution involves calculating the net present value (NPV) for each cash flow amount using various guessed interest rates on a trialanderror basis. Then, those NPVs are added together. This process is repeated using multiple interest rates until you find the exact interest rate that produces NPV amounts that sum to zero. The interest rate that produces a zerosum NPV has then declared the internal rate of return. To simplify this process, Excel offers three functions for calculating the internal rate of return. Each of these represents a better option than using the mathbased formulas approach. These Excel functions are:
How to Calculate IRRTo understand how to use the IRR function in excel, follows the following steps: Assuming you have been running a business for three months and now you want to figure out the rate of return for your cash flow. Step 1: Type the initial investment into B2 cell. Since it is an outgoing payment, it needs to be a negative number. Type the subsequent cash flows into the cells under or to the right of the initial investment. This money has been coming in through profit, so we enter these as positive numbers. Here is the created report in which we expect to profit at the end of the months. The discount rate equals 10%. This is the rate of return of the best alternative investment. Step 2: In Excel uses the NPV function to calculate the present value of a series of cash flows and subtracts the initial investment. A positive net present value indicates that the project's rate of return exceeds the discount rate. In other words, it's better to invest your money in the project than to put your money in a savings account at an interest rate of 10%. Step 3: The IRR function below calculates the internal rate of return of the project. Step 4: The internal rate of return is the discount rate that makes the net present value equal to zero. You need to replace the discount rate of 10% in cell E4 with 15%. A net present value of 0 indicates that the project generates a return rate equal to the discount rate. In other words, investing your money in a project or putting your money in a highyield saving account at an interest rate of 15% yields an equal return. Present ValuesLet's suppose another project requires an initial investment of Rs.100. We expect a profit of Rs.25 at the end of the second month, Rs.50 at the end of the third month, and Rs.152.09 at the end of the fourth month as given below: Step 1: Now, use the NPV function to calculate the present value of a series of cash flows of this project. Step 2: The IRR function calculates the internal rate of return of this project. Step 3: Again, the internal rate of return is the discount rate that makes the net present value equal to zero. You need to replace the discount rate of 15% in cell E4 with 39%. A net present value of 0 indicates that the project generates a return rate equal to the discount rate. In other words, you are investing your money in this project or putting your money in a highyield saving account at an interest rate of 39% yield an equal return. Step 4: First, we calculate the present value (Pv) of each cash flow. Next, we sum these values as given below. Instead of investing $100 in this project, you could also put Rs.17.95 in a savings account for 1st month, Rs.25.77 in a saving account for 2nd month, and Rs.56.28 in a saving account for 3rd month, at an interest rate equal to the IRR (39%). IRR RuleThe IRR rule says that you can accept the project if the IRR is greater than the required return rate. IRR values are frequently used to compare investments. In the below example, we take A and B two different projects. Step 1: The IRR function calculates the internal rate of return of project A. If the required rate of return equals 15%, you should accept this project because the IRR of this project equals 32%. Step 2: The IRR function calculates the internal rate of return of project B. In general, a higher IRR indicates a better investment. Therefore, project A is a better investment than project B.
Next TopicHow to Count Characters in Excel
