Loan Amortization Schedule in Excel with Graphs and Extra Payments

Managing a loan effectively requires understanding its amortization schedule. An amortization schedule is a table that details each periodic loan payment, showing the amount allocated to principal and interest. This article provides a comprehensive guide to creating a dynamic loan amortization schedule in Excel, complete with graphs to visualize the loan’s progress, and functionality to simulate the impact of extra payments.

Setting Up the Amortization Schedule

First, we need to create a basic table structure to hold the loan details and the amortization schedule itself. Here’s how to set up the initial structure:

Loan Input Section

Dedicate a section at the top of your Excel sheet for loan inputs. These inputs will drive the entire amortization schedule. Include the following:

• Loan Amount (Principal): Cell B2 (e.g., $100,000)
• Annual Interest Rate: Cell B3 (e.g., 5% or 0.05)
• Loan Term (in Years): Cell B4 (e.g., 30)
• Extra Payment (Optional): Cell B5 (e.g., 0 initially)
• Payments per Year: Cell B6 (e.g., 12 for monthly payments)

Amortization Schedule Header

Create a header row for your amortization schedule. This row will label each column of the table.

Start the header row in cell A8 (or any cell you prefer). Enter the column headers as shown above.

Formulas and Calculations

Now, we’ll input the formulas to calculate the values for each column in the amortization schedule.

Calculating the Periodic Payment (PMT Function)

Excel’s PMT function calculates the periodic payment required to repay a loan. In cell B8, we’ll use this function:

=PMT(B3/B6,B4*B6,-B2)

• B3/B6: Monthly interest rate (Annual Rate / Payments per Year)
• B4*B6: Total number of payments (Loan Term * Payments per Year)
• -B2: Loan amount (entered as a negative value since it’s an outflow)

This formula calculates the *required* payment based on the loan terms. We’ll use this as a basis but adjust it later to incorporate extra payments.

Populating the Amortization Schedule

Now, let’s fill in the formulas for the amortization schedule itself. We’ll start in row 9.

• Period (Column A): In A9, enter 1. In A10, enter =A9+1 and drag down.
• Beginning Balance (Column B): In B9, enter =$B$2 (the loan amount). In B10, enter =F9 (the ending balance from the previous period).
• Payment (Column C): This column will consider extra payments. In C9, enter: =MIN(B9, IF(B9>0, $B$8+$B$5, 0))
  • This ensures that the payment never exceeds the remaining balance.
  • It adds the extra payment ($B$5) to the calculated payment ($B$8).
  • The IF(B9>0,... ,0) part ensures the payment is 0 once the balance is zero.
• Interest (Column D): In D9, enter =B9*($B$3/$B$6) (Beginning Balance * Monthly Interest Rate).
• Principal (Column E): In E9, enter =C9-D9 (Payment – Interest).
• Ending Balance (Column F): In F9, enter =B9-E9 (Beginning Balance – Principal).

After entering these formulas in row 9, select cells A10 through F10. Now, drag the fill handle (the small square at the bottom-right corner of the selection) down to populate the schedule for the entire loan term (calculated as B4*B6). Make sure to drag down enough rows to cover all payments.

Adding Conditional Formatting

Conditional formatting can help highlight important aspects of the schedule. For instance, you can highlight the row where the loan is fully paid off.

1. Select the entire amortization schedule (excluding the header row).
2. Go to Home > Conditional Formatting > New Rule…
3. Choose “Use a formula to determine which cells to format.”
4. Enter the formula: =$F9=0 (assuming F9 is the first cell in the Ending Balance column).
5. Click “Format…” and choose a fill color or other formatting to indicate a paid-off loan.

Creating a Loan Amortization Graph

Visualizing the loan amortization process through a graph can provide valuable insights.

1. Select Data: Select the Period (Column A), Interest (Column D), and Principal (Column E) columns of your amortization schedule, including the header row. Hold down the Ctrl key (or Cmd key on a Mac) while selecting the non-contiguous columns.
2. Insert Chart: Go to Insert > Charts > Recommended Charts.
3. Choose Chart Type: Select a stacked column chart or a line chart. A stacked column chart clearly shows the proportion of each payment going towards principal and interest. A line chart shows the trends over time.
4. Customize Chart: Adjust the chart title, axis labels, and colors for better readability. Add a legend.

This graph will visually represent how much of each payment goes toward interest versus principal over the life of the loan. You’ll see that in the beginning, a larger portion goes to interest, and over time, a larger portion goes to principal.

Analyzing the Impact of Extra Payments

The beauty of this Excel model is its ability to analyze the impact of extra payments. By changing the value in cell B5 (Extra Payment), you can immediately see how it affects:

• Loan Payoff Time: The amortization schedule will dynamically adjust, showing fewer rows used as the loan is paid off faster.
• Total Interest Paid: You can add a formula to calculate the total interest paid. In a cell (e.g., B7), enter =SUM(D9:D1000) (adjust the D1000 to the end of your amortization schedule). This will sum the interest column and update automatically as you change the extra payment.

By experimenting with different extra payment amounts, you can determine the optimal strategy for paying off your loan faster and saving on interest.

Calculating Total Interest Paid and Total Principal Paid

To quickly see the totals, add these calculations:

• Total Interest Paid: In a cell like B12, use the formula =SUM(D9:INDEX(D:D, COUNT(A:A)+8)). This sums all the interest payments in column D. The `INDEX` and `COUNT` functions dynamically determine the last row with data, so you don’t need to manually adjust the range.
• Total Principal Paid: In a cell like B13, use the formula =SUM(E9:INDEX(E:E, COUNT(A:A)+8)). This sums all the principal payments in column E, similarly using dynamic ranges.

Protecting the Worksheet (Optional)

To prevent accidental changes to formulas, you can protect the worksheet.

1. Select all cells you want to remain editable (the input section: B2, B3, B4, B5, B6).
2. Right-click and choose “Format Cells…”
3. Go to the “Protection” tab and uncheck “Locked.”
4. Go to Review > Protect Sheet.
5. Enter a password (optional) and choose which elements users can interact with (e.g., selecting locked cells).

Conclusion

This dynamic loan amortization schedule in Excel, complete with graphs and extra payment options, provides a powerful tool for managing and understanding your loans. By experimenting with different scenarios, you can make informed decisions to optimize your repayment strategy and save money on interest.