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.
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:
- From the Options menu, choose Display and select Row & Column Headings. Click OK.
- Change Term (years) to 30 in cell D7. (No. of Payments will automatically update to 360 in cell D11.)
- 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).
- From the Data menu, choose Series. For Step Value, type 1; under Series In, select Columns; under Type, select Linear. Click OK.
- Select cells C33 through C374 (see step 3 for an easy method of doing this).
- 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.
- From the Formula menu, choose Define Name.
- Click "interest" and change E33 to E374.
- Click "beginning_balance" and change D33 to D374, and click OK.
- Select cells D33 through H374 and choose Fill Down from the Edit menu.
- From the Options menu, choose Display and deselect Row & Column Headings, if desired.