Microsoft KB Archive/816643

= XL: How to Create a Loan Amortization Schedule =

Article ID: 816643

Article Last Modified on 2/1/2007

-

APPLIES TO


 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



SUMMARY
The purpose of this article is to demonstrate how to create a basic loan amortization schedule in Microsoft Excel.



MORE INFORMATION
To create a basic loan amortization schedule, follow these steps.

Note The following steps are an example that contains the formulas that you have to have for a basic loan amortization schedule. If a cell reference is blank (for example, cell B1), do not type data in the cell.  In Excel, open a new workbook.  On Sheet 1, type the following data in the specified cells:  A1: Loan Amount:    B1:                    C1:                    D1: A2: Interest Rate:  B2:                    C2:                    D2: A3: # of Months:    B3:                    C3:                    D3: A4: Monthly Payment B4: =PMT(B2/12/100,B3,B1)  C4:                    D4: A5: Payment #       B5: Start Balance      C5: Int. for Month    D5: Payment  E5:End Balance A6: 1               B6: =B1                C6: =B6*($B$2/12)/100  D6: =$B$4    E6: =B6+C6+D6 A7: =A6+1           B7: =E6                C7: =B7*($B$2/12)/100  D7: =$B$4    E7: =B7+C7+D7 Note Most cells show #DIV/0 because cells B:1, B:2, and B:3 have no values typed in them. After you type values in the cells B:1, B:2, and B:3, the correct values for the cells appear.  Select column B, column C, column D, and column E. On the Format menu, click Cells. On the Number tab, click Accounting in the Category list, change the Decimal places box to 2, change the Symbol box to None, and then click OK. In cell B:1, type the loan amount that you want to amortize. If the loan amount is $181,900, type 181900 . In cell B:2, type the interest rate of the loan. For example, if the loan interest rate is 5.75%, type 5.75 .</li> In cell B:3, type the total number of payments of the loan. For example, for monthly payments over a 30-year loan duration, type 360 .</li> Select cells A:7 to E:7</li> With your mouse, click and hold the square dot on the lower-right corner of the selection, and then drag to extend the selection to the number of payments in cell B:4. For example, if you typed 360 for the total number of payments of the loan, extend the selection to row 365 of your worksheet.</li></ol>

<div class="references_section">

Microsoft provides additional templates to calculate loans balances and amortization. To obtain the additional templates, visit the Template Gallery at the following Microsoft Web site:

http://officeupdate.microsoft.com/templategallery/

For a Mortgage Loan Amortization Schedule, visit the following Microsoft Web site:

http://officeupdate.microsoft.com/TemplateGallery/templates/6/tp1396.asp?i=8&l=1121,1120,1026,923,1117,1443,1118,1115,1396,1339,1119,1123,928,932,1122,1124,1113,1114,1125,&RC=9&M=19&mh=20&qu=&ct=&cid=0.138.144

Keywords: kbhowto KB816643

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.