Microsoft Office Tutorials and References

In Depth Information

**Principal—PPMT Function**

in the local menu. See Figure 32.6 for the procedure and Figure 32.7 for the results.

The payment dates will appear as every ninth day of the 300 months in the table. You

are now ready to calculate the actual entries in the amortization table.

To fill the table, you have to calculate the Principal, the Interest, and the

remaining Balance for each one of the 300 months or periods. The
Principal
is

the part of the monthly payment that is actually repaying back the loan. The Prin-

cipal is subtracted from the
Balance
every month to create the next month

s Balance.

The
Interest
is monthly interest (0.5 percent) on the Balance of the loan for the

current month. Adding the monthly Interest and the monthly Principal will equal

the payment amount for a single month. This is how it is calculated.

’

Principal—PPMT Function

The PPMT function will calculate the monthly principal repayment for each of the

300 months. Principal reimbursements are very low at the beginning of the loan,

since the interest on the remaining principal balance is high. The principal repayment

portion will be much higher toward the end of the loan since the interest amounts will

be smaller on a lower remaining principal balance.

This is how to calculate the PPMT. It has all the components of the PMT,

the Payments function that we calculated above, with one additional input, the

period numbers:

PPMT

ð

rate, per, nper, pv, fv, type

Þ

Where:

rate
is the interest rate per period.

per
specifies the period and must be in the range 1 to
nper.

FIGURE 32.7
The Completed List of Monthly Dates