Build a debt schedule in Excel by listing each month in a row, then calculating opening principal, new borrowing, principal repayments, interest, and closing principal. The example below uses a single cash-pay loan with monthly periods and interest calculated on the opening balance, so you can follow the balances without circular references.
A debt schedule makes it easier to forecast how much principal remains, when repayments occur, and how interest changes over time. It is a model of the loan terms you enter, not a substitute for them: confirm payment dates, interest conventions, fees, and repayment rules with the lender or a qualified professional. The amounts here are illustrative, not financial advice.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The formulas below also work in current Excel for Mac and Excel for the web.
- The loan’s opening balance, annual interest rate, payment frequency, repayment amount or rule, start date, and forecast length.
- A clear convention for the timing of draws, repayments, and interest. The example assumes monthly periods, repayments capped at a fixed principal amount, and interest based on opening principal.
- A blank worksheet and a consistent currency and date format. If you model more than one loan, set up a separate block for each facility or use clearly labeled sections.
Step-by-Step
-
Define the loan assumptions and period. Choose one row per month for a monthly loan, or use quarterly rows if payments and forecasts are quarterly. For this example, enter these labels and values in cells J2:J5: J2, Original borrowing, 100000; J3, Annual interest rate, 10%; J4, Monthly principal repayment, 5000; and J5, Start date, 1-Jan-2026. Enter the numbers as numbers, not text, and format J3 as a percentage. These assumptions make the schedule easy to update without editing formulas. The example assumes a full year of monthly periods and a nominal annual rate divided by 12; actual loan agreements may use daily accrual or a different convention.
-
Create and label the schedule columns. In row 9, use A for Period date, B for Beginning balance, C for Draws, D for Principal repayment, E for Annual rate, F for Interest expense, G for Ending balance, and H for Balance check. In row 10, enter the first period. Keep draws as a separate input column so an additional borrowing can be recorded in the period it occurs. Enter zero in C10 when there is no draw. If your loan has no further draws, you can fill zero down the forecast instead of deleting the column.
-
Build the dates and opening balances. Put the start date in the first schedule row, then advance one month at a time. With the start date in J5 and the first schedule date in A10, enter
=$J$5in A10; it returns 1-Jan-2026. With A10 holding 1-Jan-2026, enter=EDATE(A10,1)in A11 and fill it down; A11 returns 1-Feb-2026. EDATE is a standard Excel function. Format the dates using Home > Number Format and a date format that makes each period easy to read.With the original borrowing in J2, put
=$J$2in B10; it returns 100,000. With each row’s prior ending balance in column G, enter=G10in B11 and fill down; B11 returns 95,000 in this example. The first row starts at the original amount; every following row opens at the prior row’s closing amount. Locking the input references with dollar signs means they remain fixed when you copy formulas. -
Enter draws and calculate principal repayments. Type each new borrowing directly into column C in the period it is received. For the example, enter 0 in C10 and C11. With opening balance in B10, draws in C10, and the monthly principal cap in J4, enter
=MIN(B10+C10,$J$4)in D10 and fill it down. It returns 5,000 in the first period, and 5,000 in the second period when the opening balance is 95,000 and there are no new draws. The formula prevents repayment from exceeding the principal available before the payment. If the agreement requires a different repayment rule, replace the cap with that rule rather than assuming this example applies.This simplified formula assumes new draws are available for repayment in the same period. If the loan terms do not allow that, or draws happen after the repayment date, model the timing explicitly. For example, keep separate pre-draw and post-draw balances, or enter repayment amounts from the lender’s schedule. Avoid using a formula that allows principal repayments to become negative or exceed the amount owed.
-
Calculate monthly interest. This example calculates interest on opening principal only, excludes same-month draws from that period’s interest, and pays interest in cash rather than adding it to principal. With the opening balance in B10 and the annual rate in J3, enter
=B10*$J$3/12in F10 and fill it down. In the first period, the result is 100,000 × 10% ÷ 12, or 833.33. In the second period, with a 95,000 opening balance, the result is 791.67. These values are rounded for display; retain full precision in the cells.Put the rate used in E10 with
=$J$3and fill down; it returns 10% in each period. This extra column makes rate changes visible: enter a different rate for the affected period if the terms require it, then use that period’s rate in the interest formula. For example, if the annual rate is in E10 instead of J3, use=B10*E10/12in F10; with B10 at 100,000 and E10 at 10%, it returns 833.33. Use one of these approaches consistently, not both as competing rate inputs. -
Calculate ending principal and add a check. With beginning balance in B10, draws in C10, and principal repayments in D10, enter
=B10+C10-D10in G10 and fill it down. The first period ends at 95,000: 100,000 + 0 − 5,000. The second period ends at 90,000. Interest is shown separately in column F because this example treats it as cash interest paid, not as borrowing added to principal.With B10, C10, D10, and G10 holding the values above, enter
=B10+C10-D10-G10in H10 and fill down. It returns 0 when the row balances correctly. A nonzero result flags a broken link or an unintended edit. You can format H as currency or use a number format with two decimal places so small rounding differences are visible. -
Extend the forecast and review the ending date. Copy the formulas in A11, B11, and D10:H10 down for the required number of periods. In Excel for Microsoft 365 on Windows, select the completed formula cells and drag the fill handle at the lower-right corner down the rows; double-clicking the fill handle can fill alongside an adjacent continuous column. Check the final row rather than assuming the loan is fully repaid: with the example’s 100,000 opening balance and 5,000 monthly principal repayments, 20 payments of 5,000 bring the principal to zero, assuming no additional draws. Stop the schedule at the payoff date or continue with zero balances if you need a longer forecast.
-
Make the schedule readable and check it against the terms. Apply currency formatting to balances, draws, repayments, and interest; use percentage formatting for rates and date formatting for periods. Freeze the header row if the schedule is long, and label units and assumptions clearly. Review at least the first two rows by hand: the example starts at 100,000, accrues 833.33 interest in month one, repays 5,000 of principal, and closes at 95,000. Confirm the repayment date, interest basis, payment frequency, and maturity against the loan documents before relying on a forecast.
Common Mistakes
- Mixing annual and monthly rates. A 10% annual rate is not 10% per month. The example divides the annual rate by 12. If the lender supplies a monthly rate, do not divide it again.
- Adding interest to principal by accident. The example keeps interest in a separate cash expense column. If interest is payment-in-kind and capitalizes, the closing-balance calculation must add that interest to principal, and the borrowing balance will grow differently.
- Repaying more than the debt outstanding. A fixed payment entered manually in every row can create a negative balance in the payoff period. The MIN formula caps principal repayment at available principal plus draws.
- Using the wrong timing convention. Interest on opening balance, average balance, or daily balances can produce different results. A simple opening-balance calculation is only appropriate when it matches the intended model convention.
- Breaking a copied reference. Without dollar signs, a reference to the rate or original loan amount may shift as you fill formulas down. Lock fixed assumption cells and inspect the formula in a later row.
- Rounding inside formulas. Rounding every month can accumulate differences. Keep full precision in calculations and use cell formatting to control what is displayed.
Tips
- Keep assumptions in one clearly labeled area and color-code input cells differently from formulas. This helps another reviewer distinguish editable terms from calculated results.
- Separate each loan’s principal and interest schedule when facilities have different rates, maturities, currencies, or repayment rules. Add the closing balances together only in a separate summary.
- For a rate change or a one-off draw, enter the changed assumption in the period it applies and note the source or effective date next to the model. Do not hard-code a different rate into a formula without documenting it.
- Add a small summary above or beside the schedule showing total closing debt and interest by period. Link the summary to the schedule rather than retyping numbers, which reduces inconsistencies.
- Use a balance check in every row and investigate any nonzero result immediately. A check is useful even when the formula looks simple because it can catch overwritten cells and incomplete fills.
Troubleshooting
The balance turns negative. Check whether repayments exceed the opening balance plus draws, and confirm the MIN formula was filled into every repayment row. If the balance becomes negative only in the final period, cap the last payment at the remaining principal rather than forcing a full scheduled amount.
Interest looks far too high or low. Verify that the rate cell is stored as a percentage: 10% should be stored as 10%, not the number 10 formatted as a percentage. Confirm whether the rate is annual or monthly, and check whether the loan uses a 360-day or 365-day convention, daily accrual, or another basis. The example’s monthly division is not a substitute for the lender’s calculation method.
Dates skip or appear as numbers. Check that J5 and the period cells contain real Excel dates, not text. If a date displays as a serial number, select it and apply a date format from Home > Number Format. If EDATE is unavailable in a very old spreadsheet program, use a newer Excel version or enter the contractual dates as inputs; current Microsoft 365 includes EDATE.
The balance check is not zero. Compare the row’s beginning balance, draws, repayment, and ending balance. Check for a deleted formula, a repayment entered as a negative number even though the formula subtracts it, or a formula copied from the wrong row. Decide on one sign convention—this guide enters draws and repayments as positive inputs—and apply it consistently.
The schedule does not match a lender statement. The lender may calculate interest using daily balances, include fees, apply payments first to interest, or use different transaction dates. Compare the contractual method and dates, then adapt the model’s rows and formulas. Do not force the spreadsheet to match by inserting unexplained adjustments.
Frequently Asked Questions
What is a debt schedule in Excel?
A debt schedule is a period-by-period forecast of borrowing, principal repayments, interest, and outstanding debt. It helps show when debt is expected to be repaid and how interest changes as principal changes.
Should interest be calculated on the beginning or average balance?
Use the basis specified by the loan terms or required by your model. This guide uses opening balance for a simple monthly example. Average-balance or daily-balance methods may be more suitable for other terms, but they require different calculations.
How do I model interest that is added to the loan?
For payment-in-kind interest, add the period’s interest to principal in the closing-balance calculation instead of treating it only as a cash expense. Check how the agreement defines accrual and compounding, and avoid adding interest twice.
Can I build the schedule for quarterly or annual payments?
Yes. Set one row per payment period, advance dates at the correct frequency, and divide or otherwise calculate interest according to the applicable rate and day-count convention. Do not simply divide by 12 if the periods or loan terms differ.
How do I schedule a loan with variable rates or extra draws?
Record each additional draw in the period it occurs and maintain a rate input for each period, changing it on the effective date. Adjust the interest formula if the draw timing or rate convention requires it, and preserve a note of the assumptions used.