Loan Amortization Schedule

About this template

Two loans compared in one table

Three sheets: ‘Dashboard’ · ‘Input data’ · ‘Usage guide’. ‘Input data’ carries two deliberately different loans, rounds 1 to 6 of each. Rows 2-7 are a working capital loan of KRW 120m at 4.9% p.a. over 36 months, level payment; rows 8-13 a facility loan of KRW 200m at 5.4% p.a. over 60 months, equal principal. Twelve columns run No. · Loan · Round · Payment date · Repayment type · Opening balance · Principal · Interest · Payment · Closing balance · Cumulative interest · Notes.

Three places to change

1. Put your actual principal in the opening balance of the first round (F2 and F8). Every row below refers to the closing balance above it, so the chain follows.
2. Change the rate inside the interest formula: in =ROUND(F2*0.049/12,0), 0.049 is 4.9% a year.
3. For level payment, edit rate, total rounds and principal in I2’s =ROUND(PMT(0.049/12,36,-120000000),0). For equal principal, edit G8’s =ROUND(200000000/60,0).

How the two methods diverge

• Level payment keeps the payment flat, fixed here at 3,591,122, with every row from round 2 down reading =$I$2. Inside it the principal grows from 3,101,122 to 3,164,956 while the interest falls from 490,000 to 426,166.
• Equal principal fixes the principal at 3,333,333 (=$G$8) and lets only the interest fall with the balance, so the payment slides from 4,233,333 to 4,158,333. Heavier early, lighter in total interest.
• Monthly interest is remaining balance × annual rate ÷ 12 under both. Closing balance is =F2-G2, cumulative interest =K2+H3.
• Summary by repayment type keeps them apart: through round 6, Level payment 21,546,732 and Equal principal 25,174,998.

Adding rounds

The sheet takes 20 input rows, so eight are left after the twelve sample rows. To run out to round 36 and round 60, copy the last filled row and paste it down: opening balance refers to the row above, so balances and cumulative interest continue by themselves, and the round where the balance reaches zero is maturity. For a loan with a grace period, set the principal to 0 on those rows and leave the interest standing.

Comparing the two on total interest

Six rounds in, the sample has accrued 2,749,018 of interest on level payment and 5,175,000 on equal principal — different loans at different rates, so not comparable as they stand. But on identical terms, equal principal always costs less interest, because more principal is repaid early and the balance falls faster; the trade is a heavier payment while cash is tightest. To compare properly, fill two blocks with the same principal and rate under different loan names and read the last cumulative interest cell in each.

The trend table is updated by hand

‘Interest burden by year (KRW 10k)’ holds Year 1 143 · Year 2 108 · Year 3 70 · Year 4 44. The four value rows are typed in; only Total 365 and Average 91.25 are formulas. Once the schedule is complete, carry each year’s interest across in units of ten thousand won, and you can see when the burden eases and when a further loan becomes affordable.

What goes wrong

• Typing the opening balance on every round — the reference breaks, and an extra repayment part way through never shows up.
• Folding early repayment fees or stamp duty into the payment. The column holds contracted repayments only.
• Running two loans as one block. F8 is not a reference — it is the principal, 200,000,000, typed as a number. When you add a loan, type its first opening balance as a figure and let the rows below refer upward.