Excel: Creating an Amortization Schedule

Last reviewed: November 29, 1994
Article 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'

KBCategory: kbusage
KBSubcategory:
Additional words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.2 2.20
3.0 3.00 4.0 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 29, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.