Excel: Amortization Schedule in Sample Documents, LOAN3

SUMMARY

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

MORE INFORMATION

The "LOAN3" 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 "interest" and change E33 to E374.

9. Click "beginning_balance" 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.