Microsoft KB Archive/50451

{| = Excel: Amortization Schedule in Sample Documents, LOAN3 =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q50451

SUMMARY
To create a complete amortization schedule in Microsoft Excel that displays principal and interest payments, use the &quot;LOAN3&quot; sample on the Help & Examples Disk in the &quot;Financial Analysis&quot; folder within the &quot;Sampler Files&quot; folder.

MORE INFORMATION
The &quot;LOAN3&quot; sample is an example of a standard home mortgage amortization schedule for the first 18 periods (months).

To extend the schedule to display the full standard 30-year schedule (360 payments), follow these instructions:


 * 1) From the Options menu, choose Display and select Row & Column Headings. Click OK.
 * 2) Change Term (years) to 30 in cell D7. (No. of Payments will automatically update to 360 in cell D11.)
 * 3) Select cells B33 through B374 (an easy way to do this is to choose Goto from the Formula menu, type B33:B374 in the Reference box, and click OK).
 * 4) From the Data menu, choose Series. For Step Value, type 1; under Series In, select Columns; under Type, select Linear. Click OK.
 * 5) Select cells C33 through C374 (see step 3 for an easy method of doing this).
 * 6) From the Data menu, choose Series. For Step Value, type 1; under Series In, select Columns; under Type, select Date; under Date Unit, select Month. Click OK.
 * 7) From the Formula menu, choose Define Name.
 * 8) Click &quot;interest&quot; and change E33 to E374.
 * 9) Click &quot;beginning_balance&quot; and change D33 to D374, and click OK.
 * 10) Select cells D33 through H374 and choose Fill Down from the Edit menu.
 * 11) From the Options menu, choose Display and deselect Row & Column Headings, if desired.
 * }