Microsoft Office Tutorials and References

In Depth Information

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

Of the four formulas in Table 4–2, the most difﬁ cult to understand is the PV function that will be assigned to

cell I3. The
PV function
returns the present value of an annuity. An
annuity
is a series of ﬁ xed payments (such as the

monthly payment in cell E4) made at the end of each of a ﬁ xed number of periods (months) at a ﬁ xed interest rate. You

can use the PV function to determine how much the borrower of the loan still owes at the end of each year.

The PV function can determine the ending balance after the ﬁ rst year (cell I3) by using a term equal to the

number of months for which the borrower still must make payments. For example, if the loan is for 18 years

(216 months), then the borrower still owes 204 payments after the ﬁ rst year (216 months – 12 months). The

number of payments outstanding can be determined from the formula 12 * (E3 – G3) or 12 * (18 – 1), which equals

204. Recall that column G contains integers that represent the years of the loan. After the second year, the number

of payments remaining is 192, and so on.

If you assign the PV function as shown in Table 4–2 to cell I3 and then copy it to the range I4:I20, the ending

balances for each year will display properly. If the loan is for less than 18 years, however, then the ending balances

displayed for the years beyond the time the loan is due are invalid. For example, if a loan is taken out for 5 years,

then the rows representing years 6 through 18 in the amortization schedule should be 0. The PV function, however,

will display negative numbers even though the loan already has been paid off.

To avoid this, the worksheet should include a formula that assigns the PV function to the range I3:I20 as long

as the corresponding year in column G is less than or equal to the number of years in cell E3. If the corresponding year

in column G is greater than the number of years in cell E3, then the ending balance for that year and the remaining

years should be 0. The following IF function causes the value of the PV function or 0 to display in cell I3 depending

on whether the corresponding value in column G is less than or equal to the number of years in cell E3. Recall that

the dollar signs within the cell references indicate the cell reference is absolute and, therefore, will not change as

you copy the function downward.

=IF(G3 <= $E$3, PV($E$2 /12, 12 * ($E$3 – G3), –$E$4), 0)

logical test

value if true

value if false

In the above formula, the logical test determines if the year in column G is less than or equal to the term of

the loan in cell E3. If the logical test is true, then the IF function assigns the PV function to the cell. If the logical

test is false, then the IF function assigns zero (0) to the cell.

The PV function in the IF function includes absolute cell references (cell references with dollar signs) to ensure

that the references to cells in column E do not change when the IF function later is copied down the column.

The following steps enter the four formulas shown in Table 4–2 into row 3. Row 3 represents year 1 of the loan.

1

•
Select cell H3 and

then enter
=c6
as

the beginning

balance of the loan.

Enter box

IF function

typed in active

cell appears in

formula bar

=c6

•
Select cell I3 and then

type
=if(g3 <=

$e$3, pv($e$2 /

12, 12 * ($e$3

– g3), –$e$4),

0)
as the entry

(Figure 4-36).

Excel automatically

assigns format of

cell C6 to cell H3

IF function

assigns PV

function or 0

Figure 4–36