Microsoft Office Tutorials and References
In Depth Information
The Amortization Table
FIGURE 32.1 The Monthly Payment of the Loan
Using the Payment Function to Calculate
the Monthly Mortgage Payment
pv is the present value of the loan; in this case, $600,000.
fv is defined as
. . . the future value, or a cash balance due after the last pay-
ment is made.
This is what we call a balloon payment, it is a lump sum
payment made at the end of a long-term balloon loan. We do not use it here.
type indicates when payments are due and is either the number 0 or 1. When type
is omitted, it is assumed to be 0, which means that the payments are made at
the end of the period. When the type is 1, it is assumed that the payments are
due at the beginning of the period, as is the case in some mortgage situations.
The Amortization Table
The amortization table will have a number of components. The table headers will be
Month, Payment Date, Principal, Interest, and Balance. You need to set up the table
for the various calculations as shown in Figure 32.2.
Since the table will be set up to calculate the complete duration of the loan, it will
need to show 300 months. You have to enter the values 1 to 300 on the left side of the
table (the first column). To automatically enter the values 1 to 300 without effort,
follow this procedure:
Type the number 1 in the first cell E2 and hit Enter.
n
Reselect the E2 cell and under the Home ribbon, use Fill Series.
n
In the Fill Series menu, select Columns, Linear, and then type 300 for the stop
value. See Figure 32.3.
n
This just created the desired outcome. The result is shown in Figure 32.4. The
series of numbers from 1 to 300 was filled in in column E.
Search JabSto ::




Custom Search