• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 17. Building Loan Formulas > Building a Fixed-Rate Amortization Schedul...

Building a Fixed-Rate Amortization Schedule

The simplest amortization schedule is just a straightforward application of three of the payment functions you've seen so far: PMT(), PPMT(), and IPMT(). Figure 17.6 shows the result, which has the following features:

  • The values for the five main arguments of the payment functions are stored in the range B2:B6.

  • The amortization schedule is shown in A9:G24. Column A contains the period, and subsequent columns calculate the payment (column B), principal component (C), interest component (D), cumulative principal (E), and cumulative interest (F). The Remaining Principal column shows the original principle amount (B4) minus the cumulative principal for each period.

  • The cumulative principal and interest values are calculated by adding the running totals of the principal and interest components. You need to do this because the CUMPRINC() and CUMIPMT() functions don't work with balloon payments. If you never use balloon payments, you can convert the worksheet to use these functions.

  • This schedule uses a yearly time frame, so no adjustments are applied to the rate and nper arguments.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint