Commercial loan analyser
What a business loan costs and what it asks of cash flow: the debt service, the interest and fees, the balance left at maturity, and how all of it moves if rates rise.
Results
A worked example from the inputs shown. With JavaScript switched on, the results change as you change the inputs.
$1,740,571 is still owed at maturity, because the repayments are set over 20 years and the loan runs 5. It must be repaid or refinanced then, on terms not known today.
Debt service, a month
$16,234.38
Principal and interest, monthly
Total interest
$714,634
Over 5 years
Total fees
$26,000
Upfront $20,000, ongoing $6,000
Due at maturity
$1,740,571
A lump sum to repay or refinance
Total repayments
$2,714,634
Including the amount due at maturity
Effective annual cost
8.21%
Interest and fees, as a yearly rate
Debt service if rates rise 2 points
$18,773.42
Up $2,539.04 a month
Borrowing $2,000,000 at 7.60% a year, the monthly debt service starts at $16,234.38, and the loan costs $714,634 in interest and $26,000 in fees over its term.
Including fees, the cost is 8.21% a year. If rates were 2 points higher, total interest would be $913,895.
A lender will also look at the borrower's cash flow, security and covenants; this tool shows the loan's cost and repayments, not whether it would be approved.
Show the figures
| Year | Balance owed |
|---|---|
| 0 | $2m |
| 1 | $2m |
| 2 | $1.9m |
| 3 | $1.9m |
| 4 | $1.8m |
| 5 | $1.7m |
Show the figures
| Year | Interest | Principal | Fees |
|---|---|---|---|
| 1 | $150k | $44k | $21k |
| 2 | $147k | $48k | $1.2k |
| 3 | $143k | $52k | $1.2k |
| 4 | $139k | $56k | $1.2k |
| 5 | $135k | $1.8m | $1.2k |
Show the figures
| Amount | |
|---|---|
| Borrowed | $2m |
| Interest | $715k |
| Fees | $26k |
| Total paid | $2.7m |
Show the figures
| Value | |
|---|---|
| Rate as entered | $715k |
| 1 point higher | $814k |
| 2 points higher | $914k |
| 3 points higher | $1m |
Scenarios
Keep the base case and try other assumptions beside it. Each scenario starts from the base case and keeps only the inputs you change in it.
Year by year5 years
| Year | Payments | Interest | Principal | Fees | Balance at year end |
|---|---|---|---|---|---|
| 1 | $194,813 | $150,477 | $44,336 | $1,200 | $1,955,664 |
| 2 | $194,813 | $146,987 | $47,825 | $1,200 | $1,907,839 |
| 3 | $194,813 | $143,223 | $51,589 | $1,200 | $1,856,250 |
| 4 | $194,813 | $139,163 | $55,649 | $1,200 | $1,800,600 |
| 5 | $1,935,384 | $134,783 | $1,800,600 | $1,200 | $0 |
| Total | $2,714,634 | $714,634 | $2,000,000 | $6,000 | $0 |
The last year's payments include the amount due at maturity. Upfront fees of $20,000 are paid at drawdown and are not in the yearly rows.
If rates rise
| Debt service, a month | Total interest | Interest and fees | |
|---|---|---|---|
| As entered | $16,234.38 | $714,634 | $740,634 |
| 1 point higher | $17,483.26 | $813,893 | $839,893 |
| 2 points higher | $18,773.42 | $913,895 | $939,895 |
| 3 points higher | $20,102.12 | $1,014,512 | $1,040,512 |
Every rate in the schedule moved up by the same amount. Debt service is the principal and interest repayment.
The full schedule, repayment by repayment, is in the downloads.
Download
The inputs, results and schedules of the scenario on screen. Files are made in your browser; nothing is sent anywhere.
What it does
The Term loan tab models a business loan in any of five structures: principal and interest; interest only for a period and then principal and interest; interest only with the principal due at maturity; principal and interest with a balloon; and a bullet, where interest is added to the balance and everything is due at the end. It keeps the loan's term, when it must be repaid or refinanced, apart from the amortisation term the repayments are set over, which is often longer.
The Revolving facility tab works out the cost of an overdraft or line of credit from the limit, the average amount drawn and the fees. Use the scenarios to set two or three facilities side by side.
Understanding your results
- Debt service
- The repayment due each month or quarter: interest, and principal once it is being repaid. It is the figure a lender sets against the business's cash flow.
- Due at maturity
- What is still owed when the term ends: a balloon, the whole principal on an interest only or bullet loan, or what remains when repayments are set over a longer amortisation term. It has to be repaid or refinanced then.
- Effective annual cost
- Interest and fees together, as a yearly rate on the money received. It lets loans with different fees and structures be compared.
- If rates rise
- The same loan with every rate 1, 2 and 3 percentage points higher. Variable rate business loans reprice with their reference rate.
- Cost on the money drawn
- For a revolving facility, the yearly cost of interest and fees divided by the average amount drawn. Fees on the limit raise it when much of the limit goes unused.
The method
How this is calculated
Repayments. Interest each period is the balance times the yearly rate divided by the number of repayments a year (12 or 4). The principal and interest repayment is the level amount that would clear the balance over the amortisation term:
S = B × i / (1 − (1 + i)^−m)
where B is the balance, i the periodic rate and m the repayments left in the amortisation term. With a balloon, the repayment clears the loan down to the balloon instead. If the amortisation term is longer than the loan term, whatever remains at maturity is due then.
Interest only and bullet. Interest only repayments are the interest charged. A bullet makes no repayments: interest is added to the balance each period, so the amount owed at maturity is B × (1 + i)^n.
Rates. With a reference rate and a margin, the rate is their sum. A rate change applies from the year entered, and the repayment is worked out again over the rest of the amortisation term.
Fees. The establishment fee is a percentage of the facility, paid at drawdown with any drawdown fee. Account fees are spread evenly over the year. A line fee is charged on the facility limit, which this model takes to step down with the scheduled repayments.
Effective annual cost. The yearly rate at which the amount drawn, less upfront fees, equals the present value of every repayment, fee and the amount due at maturity.
Revolving facility. Yearly cost = average drawn × rate + limit × line fee + undrawn amount × undrawn fee + account fees. The average drawn is an assumption you set; a real balance moves every day.
Figures keep full precision and are rounded for display. Lenders calculate interest daily and set their own payment dates, so their schedules differ slightly. See How the tools work.
Assumptions and limitations
The defaults are an illustration: a $2 million five year facility with repayments set over 20 years at 7.60% a year, a 1% establishment fee and $1,200 a year of account fees. They are our assumptions, not market rates or a lender's terms. The Reserve Bank publishes the average rates on new loans to small, medium and large businesses each month in its Statistical Table F7, on its lenders' interest rates page. Use the figures in your term sheet.
The tool does not model covenants, security, hedging, break costs or the terms of a refinance at maturity, all of which a lender sets. Business loans are not regulated like home loans, and terms vary widely. The results are estimates for general information, not an offer of credit, credit assistance or advice, and they do not show whether a lender would approve the loan.
Learn more
- Commercial lending explained: the products and the tests that set how much a lender will lend.
- Inside the credit decision: how a lender assesses a business loan, with a stress test.
- The strategic use of debt: when borrowing adds value and when it does not.
- The stress tester, to see how a business's coverage holds up if rates rise or revenue falls.