How to Calculate Hurdle Rate in Excel

Save

Calculate a firm’s hurdle rate by working out its weighted average cost of capital, which is the average required rate of return on its debt and equity capital. Use Microsoft Excel spreadsheet software to perform the calculations quickly and work out the hurdle rate for single or even multiple firms.

Things You'll Need

  • Annual report
Step 1

Identify the amounts of a company’s total long-term liabilities and total stockholders’ equity, listed on its balance sheet, which you can find in its annual report. For example, assume a company has $500,000 in long-term liabilities and $750,000 in stockholders’ equity.

Step 2

Find the company’s tax rate and the interest rate it pays on its long-term debt, listed in its annual report. In this example, assume a tax rate of 35 percent and an interest rate on long-term debt of 6 percent.

Step 3

Visit any financial website that provides stock information. Find the company’s beta, listed in the company’s stock quote section. Find the yield on three-month Treasury bills, listed in the website’s bonds section. In this example, assume a beta of 1.1 and a 3 percent yield on three-month Treasury bills.

Step 4

Estimate the expected market return, which is the percentage rate of return you expect the overall stock market to generate over the next year. In this example, assume you expect the market to return 10 percent.

Step 5

Click in cell A1 of a blank Excel worksheet. Type the three-month Treasury bill yield, the expected market return, beta, the amount of long-term liabilities, the amount of stockholders’ equity, the corporate tax rate and the interest rate on debt in cells A1 through A7, respectively. Press enter after typing in each cell. In this example, you would type 0.03, 0.1, 1.1, $500,000, $750,000, 0.35 and 0.06 in cells A1 through A7, respectively.

Step 6

Click in cell B1. Type the capital asset pricing model formula, “=A1+(A3*(A2-A1)),” and press Enter. This calculates the required rate of return on the company’s equity. In this example, Excel uses the values in cells A1 through A3 to arrive at 0.107 in cell B1.

Step 7

Click in cell B2. Type the WACC formula, “=(B1(A5/(A4+A5)))+(A7(1-A6)*(A4/(A4+A5))),” and press Enter. This calculates the firm’s hurdle rate. Continuing with the example, Excel uses the required rate of return on equity from cell B1 and the values in cells A4 through A7 to arrive at 0.08 in cell B2, which is an 8 percent hurdle rate.

Tips & Warnings

  • Input different values in cells A1 through A7, and Excel automatically recalculates cells B1 and B2.

References

Promoted By Zergnet

Comments

Related Searches

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!