How can I calculate the hurdle rate in Excel?
Hurdle rate, or desired rate of return, is the lowest rate of return on an investment or project that would make it an acceptable risk for the investor. Multiple functions in Excel can be used to complete this fundamental analysis of a project or investment to make budgeting easier for an experienced and beginning investor. This simple method to evaluate an investment is a key input for many companies in their capital budgeting process. The most relevant are Internal Rate of Return, or IRR; Irregular Rate of Return, or XIRR; and Modified Internal Rate of Return, or MIRR.
The IRR function requires an estimate of the future cash flows to be received at routine intervals such as weekly or monthly. The XIRR function requires dates of expected cash flows to be entered. The MIRR function uses the same cash flows in routine intervals, but is more complex than the IRR and also factors in the cost of investment and interest received from reinvesting the cash flows. This function can take into account interest rate sensitivity and amount of invested capital.
Excel uses the inputs provided for each of these functions as well as an estimate for the hurdle rate, called a "guess" in Excel. The default value of the guess in Excel is 10%, but the user can enter a different valuation into the formula. Excel conducts an iterative calculation that continuously updates the guess value until an acceptable hurdle rate is found. If Excel finds multiple acceptable values, only the first is returned. However, if the function finds no acceptable value, an error is returned. A change to the guess value can change the result and may be appropriate if there are multiple possible rates of return or if the answer returned is not as expected.