Creating a Loan Amortization Table in Excel

Creating an amortization table is a crucial task when managing loans or mortgages. This detailed guide will walk you through the steps to create an amortization schedule in Excel, either manually or by utilizing predefined templates.

Method 1: Creating an Amortization Schedule Manually

Step 1: Open a New Spreadsheet in Excel

Launch Microsoft Excel and open a new spreadsheet. This will serve as the canvas for your amortization table.

Step 2: Create Labels in Column A

As you organize your data, start by labeling each column. Begin in column A with the following labels:

A1: Loan Amount
A2: Interest Rate
A3: Months
A4: Payments (Leave this cell blank for now)

Step 3: Enter Loan Information in Column B

Now, populate cells B1 to B3 with the relevant details of your loan.

Step 4: Calculate Your Payment

Use the PMT function to calculate your loan payment. For this, select cell B4 and enter the formula: ROUNDPMT(B2/12, B3, -B1, 0, 0). This formula divides the annual interest rate by 12 (to convert to a monthly rate) and uses the PMT function to determine the monthly payment. Press Enter to see the result.

Step 5: Create Column Headers in Row 7

Prepare your second chart area by adding labels in row 7:

A7: Period
B7: Beginning Balance
C7: Payment
D7: Principal
E7: Interest
F7: Cumulative Principal
G7: Cumulative Interest
H7: Ending Balance

Step 6: Populate the Period Column

Open the first payment date in cell A8. Drag this information down to cover cells A8 to A367 to ensure accurate payment dates are generated for each month of the loan.

Step 7: Fill Out the Other Entries in Column B8 through Column H8

Input the initial loan amount in B8. In C8, input B4 to reference the monthly payment. In E8, calculate the interest with the formula ROUNDB8*B2/12*2. In D8, subtract interest from the payment to get the principal: C8-E8. In H8, subtract the principal from the beginning balance: B8-D8.

Step 8: Continue the Schedule

Starting from Row 9, repeat the process of adding entries until Row 367 by using relative references. For instance, in B9 use H8.

Step 9: Automate and Replicate the Schedule

Select cells B9 through H9, and drag the crosshair down to Row 367 to populate the table automatically.

Method 2: Using an Excel Template

Step 1: Access the Excel Template

Microsoft provides a free downloadable template that simplifies the process of creating an amortization schedule. Access the template by navigating to Microsoft Create.

Step 2: Download the Template

Once you download, double-click on the file (likely called 'Amortization Schedule.xlsx'). This will open the template in Excel.

Step 3: Fill Out the Template

Enter your loan amount into the designated cell (labelled 'Loan Amount'). Input the annual interest rate in the appropriate cell ('Interest Rate'). Specify the loan duration in years in the relevant cell ('Loan Term'). Note the number of payments made per year ('Payment Frequency'). Add the loan start date into the designated cell ('Loan Start Date'). Enter any additional payment amounts, if applicable, in the 'Additional Payment' field. Input the name of the loan issuer in the 'Issuer' field (default 'Loan Issuer').

Step 4: Save Your Work

After completing the template, save your document. Go to File > Save As, choose a location, name the file, and select the Excel file type.

Conclusion

Creating an amortization table in Excel can be a complex process, but with a structured approach, as outlined in these methods, it can be simplified. Whether you choose to create it manually or use an automated template, ensuring accuracy and clarity in your financial planning is key. Use these guides to streamline your financial management and stay on top of your loan payments.