How to Calculate the Return Measure in QuickBooks 2013 - dummies

How to Calculate the Return Measure in QuickBooks 2013

By Stephen L. Nelson

There are two basic ways that you can calculate a return by using Microsoft Excel. You cannot do this calculation by using QuickBooks 2013 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; 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:

Combining All Cash Flows
Year Net Cash Flows Liquidation Cash Flow The Real Deal
1 –1,000 0 –1,000
2 –400 0 –400
3 218 0 218
4 855 0 855
5 1,511 0 1,511
6 2,186 0 2,186
7 2,882 0 2,882
8 3,598 0 3,598
9 4,336 0 4,336
10 5,096 0 5,096
11 5,879 0 5,879
12 6,685 0 6,685
13 7,516 0 7,516
14 8,371 0 8,371
15 9,252 0 9,252
16 10,160 0 10,160
17 11,095 0 11,095
18 12,058 0 12,058
19 13,050 0 13,050
20 14,072 300,000 314,072

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 (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, 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 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,822.37. In other words, this investment exceeds a benchmark rate of return by $9, 822.37. 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,823.37 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 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.