0 ITEMS
How to Model a Bank Loan and Its Repayment Schedule
A bank loan is modelled by fixing its terms, calculating the level monthly payment, splitting each payment into interest and principal, and testing the result against earnings. This guide builds that model for a USD 400,000 five-year loan and shows what each step tells an owner or controller.
Key Takeaways
- In this example, a USD 400,000 loan at 9.00% over 60 months needs a level monthly payment of USD 8,303.34, before the fee.
- In this example, interest is charged on the opening balance, so year 1 carries USD 33,308.10 of interest and year 5 only USD 4,692.11.
- On assumed EBITDA of USD 220,000, a coverage ratio of 2.21 lets earnings fall 43.4% before an assumed 1.25 covenant is breached.
- In this example, equal-principal repayment saves USD 6,700.53 of interest but lifts the first payment to USD 9,666.67.
- In this example, a one percentage point higher rate adds USD 195.48 to each monthly payment, so models need rate sensitivity.
A bank loan model needs four inputs and one repayment rule
A bank loan model starts with four inputs: the amount borrowed, the annual interest rate, the term in months and any upfront fee. The repayment rule then sets the schedule. Amortisation is the gradual repayment of a loan’s principal through scheduled payments, and an amortising term loan keeps the payment steady while the split between interest and principal shifts every month.
The loan type decides which rule applies. The Federal Deposit Insurance Corporation (FDIC) examination manual describes term loans as amortising loans that are typically repaid over a period longer than one year. Working capital is the money a business uses for day-to-day operations, and the manual says working capital lines typically have no scheduled amortisation and require interest-only payments (FDIC, 2025). Most term loans under the US Small Business Administration (SBA) 7(a) program are repaid with monthly payments of principal and interest from business cash flow (SBA, 2026).
Take the rate from the lender’s offer and watch market direction
The 9.00% rate in this guide is illustrative, and a real model should take the rate from the lender’s written offer.
Market surveys show direction rather than level. In the euro area, a net 24% of small and medium-sized enterprises (SMEs) reported higher bank loan interest rates in the first quarter of 2026, according to the European Central Bank survey on access to finance (ECB, 2026). A net balance is the share of firms reporting an increase minus the share reporting a decrease.
Applying for credit is common. In the 12 months before the autumn 2025 survey, 38% of US employer firms applied for a loan, line of credit or merchant cash advance, and applicants at small banks were more likely to be fully approved (57%) than those at other lenders (Federal Reserve Banks, 2026). Templates for the lending side sit in the eFinancialModels Banking category.
Worked example: the level payment comes from the PMT function
In this example, a company borrows USD 400,000 at a fixed 9.00% a year, repaid in 60 monthly payments made at the end of each month, with a 1.00% arrangement fee, a charge the bank deducts when it releases the funds. The monthly rate is the annual rate divided by 12, or 0.75%. Excel’s payment function, called PMT, solves for the level payment, as in =PMT(9%/12,60,-400000), where the negative loan amount makes the result positive: payment = loan x r / (1 – (1 + r)^-n), where r is the monthly rate and n is the number of payments. With r = 0.0075 and n = 60, (1 + r)^-n is 0.638700, so the denominator is 0.361300 and the payment is 3,000.00 divided by 0.361300. The result is USD 8,303.34 a month, held in the workbook as the unrounded USD 8,303.3421, so a recomputation from the rounded payment can differ by a cent in the later figures.
The inputs sheet holds every assumption of the loan model, and each blue cell can be changed. Source: eFinancialModels Analysis
Each row of the schedule follows three rules. Interest equals the opening balance times the monthly rate, principal equals the payment minus interest, and the closing balance equals the opening balance minus principal. Month 1 charges USD 3,000.00 of interest, repays USD 5,303.34 of principal and closes at USD 394,696.66.
The calculation sheet turns the loan terms into a month-by-month schedule and a check cell that reads OK. Source: eFinancialModels Analysis
Worked example: interest falls and principal rises every year
Because interest is charged on the opening balance, the same payment carries less interest each month. Over the five years of the level-payment loan, interest totals USD 98,200.53 and principal totals USD 400,000.00, so total repayments are USD 498,200.53.
| Year | Interest (USD) | Principal (USD) | Closing balance (USD) |
|---|---|---|---|
| 1 | 33,308.10 | 66,332.01 | 333,667.99 |
| 2 | 27,085.70 | 72,554.41 | 261,113.58 |
| 3 | 20,279.59 | 79,360.51 | 181,753.07 |
| 4 | 12,835.03 | 86,805.08 | 94,947.99 |
| 5 | 4,692.11 | 94,947.99 | 0.00 |
Interest and principal by year for the example loan; each year’s payments total about USD 99,640, and single cells can differ by a cent because of rounding.
Interest and principal paid each year, drawn from the schedule in the example workbook. Source: eFinancialModels Analysis
Worked example: debt service coverage tests affordability
Lenders test whether earnings cover the payments. The debt service coverage ratio (DSCR) measures how many times earnings cover the interest and principal due on debt in the year. The FDIC examination manual lists one form as net income plus depreciation and amortisation divided by current maturities of long-term debt, meaning the principal due within a year. It lists a related fixed charge coverage ratio as earnings before interest, tax, depreciation and amortisation (EBITDA) divided by interest expense plus lease expense plus current maturities (FDIC, 2025). This guide measures earnings with EBITDA and divides by annual interest and principal, which is the fixed charge form without leases.
In this example, assumed EBITDA of USD 220,000 and annual debt service of USD 99,640.11 (twelve payments, calculated on the unrounded payment of about USD 8,303.34, with no leases) give a DSCR of 2.21 (220,000 divided by 99,640.11 is 2.208). A covenant, a condition written into the loan agreement, sets an assumed minimum of 1.25. EBITDA would have to fall to USD 124,550.14, a drop of 43.4%, before the covenant is breached. Rolling the test forward each year needs a full forecast, and eFinancialModels lists forecasting models under its Financial Projections tag.
The example as a four-step workflow, from loan terms to an affordability test, in USD. Source: eFinancialModels Analysis
Worked example: fees, rate shocks and repayment style change the cost
The 1.00% fee is USD 4,000.00, so net proceeds are USD 396,000.00 while every payment of USD 8,303.34 stays due. The effective cost, the annual rate that equates net proceeds to the payments, rises to 9.43% as a nominal annual rate (the monthly rate times 12), against the 9.00% headline rate.
A rate shock of one percentage point, to 10.00%, stands for a higher offer rate or a variable-rate reset and lifts the payment to USD 8,498.82, an extra USD 195.48 a month, roughly USD 2,346 a year. Equal-principal repayment, which repays USD 6,666.67 of principal every month plus interest on the falling balance, starts at USD 9,666.67 and would cost USD 91,500.00 in total interest on the same loan, USD 6,700.53 less than the USD 98,200.53 of the level-payment version. Level payments protect early cash flow; equal principal saves interest but demands more cash in the first years.
The results sheet summarises payment, cost, coverage and the alternatives in one view. Source: eFinancialModels Analysis
Frequently Asked Questions
How is a balloon loan modelled differently from a fully amortising loan?
A balloon loan leaves part of the principal unpaid until maturity. Enter the balloon as the future value argument of the payment function, with the opposite sign to the loan amount, so the level payment covers interest and only the principal that is not deferred. The schedule then ends with a closing balance equal to the balloon, and the model needs a refinancing or cash line that shows where that final amount comes from.
How should a variable-rate loan appear in the schedule?
Give the schedule a rate column instead of one fixed input. Each row then takes that period’s rate, charges interest on the opening balance and, when the lender resets the instalment, recalculates the payment from the remaining balance and remaining months. A table of expected reset dates and rates, with a low, base and high path, turns the schedule into a simple sensitivity test of repayment risk.
What changes if payments are quarterly instead of monthly?
Only the period arithmetic changes. Divide the annual rate by four, count the number of quarters in the term, and run the same payment formula. A five-year schedule shrinks to twenty rows, each payment is roughly three times larger, and total interest rises slightly because the balance falls later within each year. Check whether the lender quotes a nominal rate or an effective annual rate before dividing it.
Where do loan repayments appear in the cash flow statement?
Split every payment into its two parts. Interest is an expense in the income statement, while principal repaid is a financing outflow in the cash flow statement. An accountant should confirm how interest paid is presented under the company’s reporting framework. The balance sheet carries the closing balance, with the next twelve months of principal shown as a current liability and the rest as long-term debt.
How does an early prepayment alter the schedule?
Add an extra principal column. A prepayment reduces the opening balance of the following month, so all later interest falls. The borrower then chooses between keeping the payment and finishing early or recalculating a lower payment over the original term. The first choice saves more interest in total, and loan agreements should be checked for prepayment fees before the saving is booked.
Conclusion
A bank loan model comes down to four inputs, one payment formula, a schedule that separates interest from principal, and a coverage test against earnings. Finance teams that want a ready-made repayment calculator can start from the eFinancialModels Debt Amortization Table template, which calculates how long a monthly installment takes to repay a debt and lets the user vary the interest rate, installment and debt amount. The model covers one fixed-rate term loan; revolving facilities, several lenders, tax treatment of interest and the exact covenant definitions in a loan agreement need confirmation with the lender or an accountant.
