How to Calculate the Return Measure in QuickBooks 2014
There are two basic ways that you can calculate a return by using Microsoft Excel. You cannot do this calculation by using QuickBooks alone. To calculate a rate of return with Microsoft Excel, you first enter the cash flows produced by the investment.
Even if you’ve never used Excel before, you may be able to construct this worksheet; all you have to do is start Excel (the same way you start any other Windows program). Then you enter the cash flows shown for the building investment.
Actually, you enter only the values shown in cells B2, B3, B4, B5, and so on, through cell B22. These values are the cash flow numbers calculated and summarized here:
|Year||Net Cash Flows||Liquidation Cash Flow||The Real Deal|
By the way, in case you’re 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 (IRR) 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, for example, you click cell G4 and then type the following:
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 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 it.
Another common rate of return measure is something called a net present value, which essentially specifies the dollar amount by which 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 can’t see it — it’s 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 in Figure 3-1, 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.
All this may sound a bit tricky, but essentially, this is what’s 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 investment’s cash flows.
An important thing to know about pre-tax cash flows and returns versus after-tax cash flows and returns: Make sure that you’re using apples-to-apples comparisons. It’s often fine to work with pre-tax cash flows; just make sure that you’re comparing pre-tax cash flows with other pre-tax cash flows. You don’t want to compare pre-tax returns with after-tax returns. That’s an apples-to-oranges comparison. Predictably, it doesn’t work.