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)
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.
• Select cell H3 and
then enter =c6 as
balance of the loan.
typed in active
cell appears in
• Select cell I3 and then
type =if(g3 <=
$e$3, pv($e$2 /
12, 12 * ($e$3
– g3), –$e$4),
0) as the entry
assigns format of
cell C6 to cell H3
function or 0