How to Calculate the Rate of Return on Capital

By Stephen L. Nelson from QuickBooks 2011 All-in-One For Dummies

Capital budgeting boils down to the idea that you should look at capital investments (machinery, vehicles, real estate, entire businesses, yard art, and so on) just as you look at the CDs (certificates of deposits) that a bank offers.

You want to earn the highest return possible on your money. Therefore, you want a CD that pays a high interest rate.

You can also look at the interest rates earned on capital investments. However, interest rates don't typically go by that name in capital investing. For some strange reason, the interest rate that a capital investment earns is called a return on investment, or a rate of return. But it's the same thing.

Calculating a rate of return on a capital expenditure requires three steps:

Calculate the investment amount.

The first step in calculating a return is estimating the amount that you need to invest. This amount is similar to the check you write to a bank in order to buy a CD.
Estimate the net cash flows paid by the investment.

Sit down and think carefully about any additional revenues and any additional costs that the investment produces.
Use either a financial calculator, such as one of those fancy Hewlett-Packard calculators, or a spreadsheet program, such as Microsoft Excel, to calculate the rate of return measure.

To calculate a rate of return with Microsoft Excel, you first enter the cash flows produced by the investment. Then you enter the cash flows. After you provide the cash flow values of the investment, you tell Excel the rate of return that you want calculated.

To calculate an internal rate of return, you enter an internal rate of return function formula into a worksheet cell. In the case of this worksheet, you click cell G4 and then type the following:

=IRR(B2:B22,.1)

If you've never seen an Excel function before, this probably looks like Greek. But all this function does is tell Excel to calculate the internal rate of return for the cash flows stored in the range, or block of cells, that goes from cell B2 to cell B22. The .1 is the internal rate of return used to calculate the return in this example.

To calculate the net present value by using Excel, you use another function. In the case of this worksheet, you click cell G6 and type the following formula:

=NPV(0.1,B3:B22)+B2

This formula looks at the cash flows for years 1 through 20, discounts these cash flows by using a 10 percent rate of return, and then compares these discounted cash flows with the initial investment amount, which is the value stored in cell B2.

The net present value formula looks at the cash flows stored in the worksheet and calculates the present value amount by which these cash flows exceed a 10 percent rate of return.