Microsoft Office Tutorials and References

In Depth Information

C14
=C13-C11

Calculates the total interest

Suppose you're really interested in the total interest paid (cell C14). You could, of course, simply hide the rows

that contain the extraneous information. However, it's also possible to create a single formula that does the work

of several intermediary formulas.

This example is for illustration only. The CUMIPMT function provides a more direct way

to calculate total interest on a loan.

The formula that calculates total interest depends on the formulas in cells C11 and C13 (which are the direct

precedent cells). In addition, the formula in cell C13 depends on the formula in cell C12. And cell C12, in turn,

depends on cell C11. Therefore, calculating the total interest in this example uses five formulas. The steps that

follow describe how to create a single formula to calculate total interest so that you can eliminate the four inter-

mediate formulas.

C14 contains the following formula:

=C13-C11

The steps that follow describe how to convert this formula into a megaformula:

1.
Substitute the formula contained in cell C13 for the reference to cell C13.

Before doing this, add parentheses around the formula in C13. (Without the parentheses, the calculations

occur in the wrong order.) Now the formula in C14 is

=(C12*C6)-C11

2.
Substitute the formula contained in cell C12 for the reference to cell C12. Now the formula in C14 is

=(PMT(C7/12,C6,-C11)*C6)-C11

3.
Substitute the formula contained in cell C11 for the two references to cell C11. Before copying the formula,

you need to insert parentheses around it. Now the formula in C14 is

=(PMT(C7/12,C6,-(C4-C10))*C6)-(C4-C10)

4.
Substitute the formula contained in cell C10 for the two references to cell C10. Before copying the formula,

insert parentheses around it. Now the formula in C14 is

=(PMT(C7/12,C6,-(C4-(C4*C5)))*C6)-(C4-(C4*C5))

At this point, the formula contains references only to input cells. The formulas in range C10:C13 are not refer-

enced, so you can delete them. The single megaformula now does the work previously performed by the inter-

mediary formulas.

Unless you're a world-class Excel formula wizard, it's quite unlikely that you could arrive at that formula

without first creating intermediate formulas.