Business Loan Repayment and DSCR in Excel
Calculate business loan repayments, build an Excel amortisation schedule and reconcile debt service coverage with base and downside cash forecasts.
A business loan repayment model links the contractual payment schedule to forecast cash available for debt service. This Excel example calculates a fixed-payment loan, separates interest from principal and tests debt service coverage ratio (DSCR) under base and downside cash assumptions. For the document checklist and application preparation, see the business bank-loan application guide.
Choose consistent repayment and coverage assumptions
Record the loan amount, nominal or effective rate convention, payment frequency, first payment date, term, fees and any balloon amount. Identify existing facilities and reconcile the proposed draw to the forecast use of funds. A payment formula cannot by itself establish whether the business can afford the debt.
Use operating cash adjusted for working capital, cash tax and maintenance capex to construct the teaching cash bridge below. Ask the lender how its own assessment treats those items, owner distributions, leases and other debt obligations. Keep the numerator and denominator on the same assessment period.
Reproduce the repayment calculation
This original hypothetical example uses a $250,000 fully amortising loan, an 8% nominal annual interest rate and 60 monthly payments in arrears. The rate is a teaching assumption, with no current-market or approval claim. Assume no fees, rate changes, balloon payment or payment holidays.
| Cell | Input or calculation | Value or formula | Output |
|---|---|---|---|
| B2 | Loan principal | 250000 | $250,000 |
| B3 | Nominal annual interest | 8% | 8% |
| B4 | Term in years | 5 | 5 |
| B5 | Number of monthly payments | =B4*12 | 60 |
| B6 | Monthly payment, positive outflow | =-PMT(B3/12,B5,B2) | $5,069.10 |
| B7 | Twelve months of debt service | =B6*12 | $60,829.18 |
| B8 | Total payments | =B6*B5 | $304,145.91 |
| B9 | Total interest | =B8-B2 | $54,145.91 |
Keep the unrounded payment in formulas. The final lender schedule can differ through rounding, daily interest and fees. Microsoft's PMT documentation explains its constant-rate, constant-payment assumptions. Our loan calculator offers a separate repayment and total-interest comparison using those basic inputs.
For a monthly schedule, put period numbers 1 through 60 in A15:A74. B15 is =$B$2, C15 is =B15*$B$3/12, D15 is =$B$6-C15, and E15 is =B15-D15. B16 is =E15; copy C15:E15 down and roll opening balances forward. The first payment includes $1,666.67 interest and $3,402.43 principal, leaving $246,597.57. The final balance should be approximately zero, allowing floating-point rounding.
Explain repayment coverage transparently
For this teaching example, define cash available for debt service as forecast operating cash after working-capital movements, cash tax and maintenance capex, before debt payments and owner distributions. This definition must be reconciled with the lender's own calculation. Accounting profit alone can conceal a cash shortfall.
| Annual cash bridge | Base | Downside |
|---|---|---|
| Operating cash before listed adjustments | $140,000 | $120,000 |
| Increase in working capital | -$15,000 | -$15,000 |
| Cash tax assumption | -$15,000 | -$15,000 |
| Maintenance capex | -$10,000 | -$10,000 |
| Cash available for debt service | $100,000 | $80,000 |
| Proposed annual debt service | $60,829.18 | $60,829.18 |
| Illustrative DSCR | 1.64x | 1.32x |
| Cash remaining after debt service | $39,170.82 | $19,170.82 |
DSCR here is =CashAvailable/AnnualDebtService. The table assumes no existing debt. Add payments on other included facilities to the denominator and check leases, balloons and other commitments with the lender. The tax line is an assumed cash outflow, with no tax calculation or advice supplied.
A ratio above one means the defined annual cash exceeds the defined annual debt service. It does not establish approval or covenant compliance. Ask the lender to specify the numerator, included debts, assessment period and required level. Assess month-by-month liquidity as well: annual coverage can coexist with a deficit before seasonal receipts arrive.
Check the month cash becomes tight
Suppose opening cash is $8,000 and January operating cash after the listed adjustments is $2,000 before the loan payment. Deducting the unrounded $5,069.10 payment leaves $4,930.90. If February contributes zero before debt service, its closing cash becomes -$138.20. Later receipts may restore a positive annual balance, but the February payment still needs funding or an agreed timing change.
Use actual collection and payment dates in a monthly forecast. Keep loan draws as financing receipts, principal as debt reduction and interest separate from principal. Compare the lowest closing cash with the operating buffer before accepting a repayment profile.
Frequently asked questions
How do I calculate monthly business loan repayments in Excel?
Use PMT with a rate per payment period, the number of payments and the loan principal. For a nominal annual rate paid monthly, divide the rate by twelve and multiply years by twelve. A leading minus sign displays the payment as a positive cash outflow. Include separate treatment for fees, variable rates or a balloon.
How do I split a loan repayment into interest and principal?
For a simplified monthly constant-rate schedule, calculate interest as opening debt times the monthly rate. Principal repaid is the payment less interest, and closing debt is opening debt less principal. Roll closing debt into the next period and reconcile the final balance to the contractual repayment terms.
How do I calculate DSCR from a cash-flow forecast?
Divide the defined cash available for debt service by the included principal and interest payments for the same period. Reconcile working-capital changes, tax, maintenance capex and other adjustments. Use the selected lender's definition and include all facilities it requires.
Is profit the same as cash available for loan repayments?
No. Credit sales, stock purchases, supplier payment timing, tax and capital expenditure can create differences between profit and cash. Build a cash bridge and a dated forecast before judging repayment capacity.
Can annual DSCR hide a monthly cash shortage?
Yes. Annual cash may exceed annual debt payments while individual months have insufficient funds before seasonal receipts arrive. Check opening cash, collection dates, monthly repayments and the minimum cash balance alongside annual coverage.
Why can a loan schedule finish with a small residual balance?
Rounding each payment or interest charge can leave a residual even when the theoretical payment fully amortises the loan. Keep precision in the model, compare with the lender schedule and adjust the final payment only under the agreed contractual treatment.
Related reading
For a forecast that links repayments to profit, cash flow and the balance sheet, see the three-way financial model guide.