There are two basic ways that you can calculate a return by using Microsoft Excel. You cannot do this calculation by using QuickBooks 2012 alone. To calculate a rate of return with Microsoft Excel, you first enter the cash flows produced by the investment.

In case you are new to Excel, all you do to enter one of these values is click the box (technically called a cell ) and then type the value. For example, to enter the initial investment required to buy the building $65,000 you click the B2 cell and then type 65000 .

After you type this number, press Enter. You enter each of the other net cash flow values in the same manner in order to make the rate of return calculations.

After you provide the cash flow values of the investment, you tell Excel the rate of return that you want calculated. In Cell G4, for example, an internal rate of return is calculated.

An internal rate of return is the interest rate that the investment delivers. For example, a CD that pays an 11 percent interest rate pays an 11 percent internal rate of return. To calculate an internal rate of return, you enter an internal rate of return function formula into a worksheet cell. In the case of the worksheet shown, you click cell G4 and then type the following:

If youve 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 an initial guess about the IRR you provide that value so that Excel has a starting point for calculating the return. The office building cash flows, it turns out, produce an internal rate of return equal to 11 percent. This means that, essentially, the office building delivers an 11 percent interest rate annually on the amounts invested in the office building.

Another common rate of return measure is something called a net present value, which essentially specifies by what dollar amount the rate of return on a business exceeds a benchmark rate of return.

For example, the worksheet shows the net present value equal to $9,821.71. In other words, this investment exceeds a benchmark rate of return by $9,821.71. You cant see it its buried in the formula but the benchmark rate of return equals 10 percent. So this rate of return essentially is $9,821.71 better than a 10 percent rate of return.

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

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.

This all may sound a bit tricky, but essentially, this is whats going on: 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.

The discount rate equals the rate of return that you expect on your capital investments. The discount rate is the rate at which you can reinvest any money you get from the capital investments cash flows.

An important thing to know about pre-tax cash flows and returns versus after-tax cash flows and returns: Make sure that youre using apples-to-apples comparisons. Its often fine to work with pre-tax cash flows; just make sure that youre comparing pre-tax cash flows with other pre-tax cash flows. You dont want to compare pre-tax returns with after-tax returns. Thats an apples-to-oranges comparison. Predictably, it doesnt work.

Add a Comment Print Share