Microsoft Office Tutorials and References

In Depth Information

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

2

•
Click the Enter box

in the formula bar

to insert the formula

(Figure 4–37).

What happens when

the Enter box is

clicked?

Excel evaluates the

IF function in cell I3

and displays the

result of the PV

function

(227326.7922)

because the value

in cell G3 (1) is less

than or equal to the

term of the loan in

cell E3 (18). With cell

I3 active, Excel also

displays the formula

in the formula bar.

If the borrower

wanted to pay off

the loan after one

year, the cost would

be $227,326.79.

Excel automatically

capitalizes all cell

references after

function is entered

cell I3 assigned

ending balance

(present value) of

loan after year 1

Figure 4–37

3

•
Select cell J3. Type

=h3 – i3
and then

press the
RIGHT

ARROW
key.

•
Type
=if(h3 > 0,

12 * $e$4 – j3,

0)
in cell K3 to

display the amount

paid on the prin-

cipal after 1 year

($7,673.21) in cell

J3, using the same

format as in cell H3

(Figure 4–38).

if beginning value

is greater than 0,

show interest paid

for year; else show 0

beginning balance

minus ending

balance, or H3 – I3

Excel automatically

assigns format of

cell H3 to cell J3

Figure 4–38