Microsoft Office Tutorials and References
In Depth Information
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
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.
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
rate, per, nper, pv, fv, type
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