Loan Amortization Schedule Excel Template

Excel provides the built-in financial function =PMT(rate, nper, pv, [fv], [type]) to determine the constant periodic annuity payment required to fully extinguish a loan balance. In corporate debt modeling, three critical rules govern its implementation:

First, the rate argument must represent the periodic interest rate, computed by dividing the annual coupon by annual payments: C4/C6 (5.75% / 12 = 0.479167% per month). Second, the nper argument represents total payment periods across the life of the loan: C5*C6 (15 years * 12 = 180 months). Third, the present value pv must be entered as a negative value -C3 (or the formula prefixed with a minus sign) so that the resulting debt service displays as a positive cash disbursement rather than a negative cash flow.

Select cell E3 and input the formula: =PMT(C4/C6, C5*C6, -C3). This yields an exact monthly debt service requirement of $3,736.85.

In financial ledgers, raw unformatted numbers look unpolished and obscure decimal variance. Corporate lending standards require the Accounting number format, which anchors currency symbols ($) flush against the left cell border, aligns decimal points vertically down the entire column, and displays zero balances cleanly as hyphens (-).

Highlight the entire schedule range D9:H22 along with summary cells C3 and E3:E5. Press Ctrl + 1 to open the Format Cells dialog box.

Under the Category menu on the left, select Accounting. Confirm that Decimal places is set to 2 and the Symbol dropdown is configured to $ ($). Click OK to apply. Notice how this immediately formats all monetary values into clean, institutional figures like $ 450,000.00 and $ 3,736.85.

Each fixed payment of $3,736.85 must be partitioned into its two constituent accounting components: interest expense (tax-deductible operational cost) and principal reduction (balance sheet liability repayment).

In Month 1 (Row 9), opening principal in D9 is $450,000.00. The accrued interest for the month is calculated by multiplying the opening balance by the monthly interest rate: =D9*(C$4/C$6). Notice the mixed cell references C$4 and C$6 with dollar signs locking the row number. This ensures that when the formula is copied down across 180 rows, the rate and payment frequency remain anchored to the header parameter block.

In cell G9, enter: =D9*(C$4/C$6) ($2,156.25). Once period interest is determined, the remaining portion of the fixed scheduled payment directly pays down loan principal in cell F9: =E9-G9 ($1,580.60). As principal decreases each month, subsequent interest charges shrink, shifting a progressively higher percentage of each fixed payment toward principal retirement.

The core engine of any financial debt schedule is the balance rollforward: Ending Balance = Beginning Balance - Principal Paid.

In Month 1, cell H9 calculates: =D9-F9 ($450,000.00 - $1,580.60 = $448,419.40). For Month 2 (Row 10), the beginning balance must inherit the previous period closing balance. Never hardcode or re-enter balances manually. In cell D10, enter the dynamic reference: =H9.

This single relative reference establishes an unbroken mathematical chain. Once set, you can copy cells D10:H10 down through all 180 periods. Any unscheduled prepayments or interest rate adjustments automatically ripple across all remaining periods without manual intervention.

Corporate lenders and CFOs evaluate debt service on an annual basis to verify compliance with financial covenants. In Row 23, construct the Year 1 Financing Totals rollup row beneath Period 12.

For Scheduled Payments (cell E23), sum the 12 monthly payments: =SUM(E9:E22) ($44,842.20). For Principal Paid (cell F23), sum total principal retired: =SUM(F9:F22) ($19,475.01). For Interest Paid (cell G23), sum total financing charges: =SUM(G9:G22) ($25,367.19). For Ending Balance (cell H23), reference the final period closing balance: =H22 ($430,524.99).

Notice the mathematical verification: Scheduled Payments ($44,842.20) exactly equals Principal ($19,475.01) plus Interest ($25,367.19). In Year 1, interest represents 56.57% of all payments made, illustrating the front-loaded nature of long-term commercial loans.