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.
Search JabSto ::

Custom Search