Excel: Creating an Amortization Schedule

ID: Q30653

SUMMARY

An amortization table can be created if more information is desired than is provided by Excel's built-in financial functions.

MORE INFORMATION

The following is an example containing the necessary formulas for loan amortization:


A1: Loan Amount:    B1:       $50,000     C1:         D1:
A2: Interest Rate:  B2:            10%    C2:         D2:
A3: # of Months:    B3:            48     C3:         D3:
A4: Monthly Payment B4: =PMT(B2/12,B3,B1) C4:         D4:
A5: Start Balance   B5: Int. for Month    C5: Payment D5:End Balance
A6: =B1             B6: =A6*($B$2/12)     C6: =$B$4   D6: =A6+B6+C6
A7: =D6             B7: =A7*($B$2/12)     C7: =$B$4   D7: =A7+B7+C7
A8: =D7             B8: =A8*($B$2/12)     C8: =$B$4   D8: =A8+B8+C8

Fill down cells A7:D7 for as many rows as there are periods in the loan. This procedure will create an amortization table that shows the principal balance at the start of each period, the interest paid each period, the payment, and the ending balance for each period.

If payments different from the normal monthly payment are made for some months, the actual payment amount can be entered for that month. The following interest amounts and balances will be adjusted automatically. Another column can be added to show the principal paid for the month.

The following formula will automatically show the interest paid each month on a loan:


   ='Payment'-'Int.for Month'

Additional query words:


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 11, 1999