Microsoft Office Tutorials and References

In Depth Information

**To Enter the Formulas in the Amortization Schedule**

Excel extends

series to cell G20

Auto Fill

Options

button

Figure 4–35

The next step is to enter the four formulas that form the basis of the amortization schedule in row 3. Later,

these formulas will be copied through row 20. The formulas are summarized in Table 4–2.

Table 4–2 Formulas for the Amortization Schedule

Tab Stop

Tab Marker

Result of Pressing Tab Key

Example

H3

Beginning Balance

=C6

The beginning balance (the balance at the end of a

year) is the initial loan amount in cell C6.

I3

Ending Balance

=IF(G3 <= $E$3, PV($E$2 /12, 12 *

($E$3 – G3), –$E$4), 0)

The ending balance (the balance at the end of a year)

is equal to the present value of the payments paid over

the remaining life of the loan.

J3

Paid On Principal

=H3 – I3

The amount paid on the principal at the end of the

year is equal to the beginning balance (cell H3) less the

ending balance (cell I3).

K3

Interest Paid

=IF(H3 > 0, 12 * $E$4 – J3, 0)

The interest paid during the year is equal to 12 times

the monthly payment (cell E4) less the amount paid on

the principal (cell J3).