Microsoft Office Tutorials and References

In Depth Information

**Creating a Megaformula: A Simple Example**

Creating a Megaformula: A Simple Example

Creating a megaformula basically involves copying formula text and pasting it into another

formula. I start with a relatively simple example. Examine the spreadsheet shown in Figure 20-1. This

sheet uses formulas to calculate mortgage loan information.

Figure 20-1:
This spreadsheet uses multiple formulas to calculate mortgage loan information.

This workbook, named
total interest.xlsx
, is available on the companion

CD-ROM.

The Result Cells section of the worksheet uses information entered into the Input Cells section

and contains the formulas shown in Table 20-1.

Table 20-1:
Formulas Used to Calculate Total Interest

Cell

Formula

What It Does

C10

=C4*C5

Calculates the down payment amount

C11

=C4–C10

Calculates the loan amount

C12

=PMT(C7/12,C6,–C11)

Calculates the monthly payment

C13

=C12*C6

Calculates the total payments

=C13–C11

C14

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.