Loan repayment and amortisation schedule

Know what a loan costs before you sign

A loan offer usually tells you three things: the amount, the monthly instalment and a rate. It rarely tells you what you will actually pay over the whole term, how much of each instalment is interest, or what you would save by paying a little extra. This free workbook answers all of that. Enter the loan amount, the yearly interest rate, the number of months and the date of the first payment, and it produces the monthly payment and a complete schedule, line by line, to the final payment.

How a reducing-balance loan works

With a standard reducing-balance loan, interest is worked out each month on the amount you still owe. At the start the balance is large, so most of each payment is interest and only a little repays the loan. As the balance falls, the interest part falls and the principal part grows. The payment itself stays the same. The schedule shows this clearly: for every payment, the date, the opening balance, the interest, the principal repaid, any extra payment and the closing balance.

• Monthly payment is calculated with the standard formula from the amount, rate and term.
• Total interest is the cost of borrowing, over and above the amount you borrowed.
• Final payment date is the date the balance reaches zero, which is earlier if you make extra payments.
• Interest as a share of the loan puts the total cost into perspective.

The power of extra payments

Enter any amount you can add each month in the extra payment cell and watch what happens. Every extra shilling goes straight to the principal, so next month's interest is lower, and the loan ends sooner. Even a modest extra payment can take months off a loan and save a large amount of interest. The summary compares the total interest with and without your extra payments, and shows the interest saved. Before you commit to extra payments, check that your loan allows them without a penalty, because some lenders charge a fee for early repayment.

Flat rates are more expensive than they look

Some lenders quote a flat rate. They charge the interest on the original amount for the whole term, as if you still owed all of it, and divide the total by the number of months. Because you are repaying the loan gradually, you owe less each month, but the interest does not fall. The result is that the true yearly rate, measured on the reducing balance, is about twice the flat rate. The second sheet in the workbook takes the amount, a flat rate and a term and gives the monthly payment and the equivalent reducing-balance rate. A flat rate of ten per cent is closer to eighteen per cent. When you compare offers, always compare them on the same basis.

Look beyond the rate

Fees change the real cost of a loan. Processing fees, insurance, legal fees, valuation fees and excise duty on fees are paid at the start or added to the loan, and they raise the effective rate, especially for short loans. Ask for the total cost of credit in writing, and compare lenders on what you will pay in total, not only on the quoted rate.

Using the schedule

1. Enter the amount, the yearly rate, the term in months and the first payment date.
2. Read the monthly payment and the total interest.
3. Try different terms and rates to see how they change the cost. A longer term lowers the payment but raises the total interest.
4. Add an extra payment and see how much you save.
5. Put the monthly payment into your cash flow forecast and your budget.

Loans and tax

Interest on money borrowed for business purposes is generally a deductible business expense, which reduces your taxable profit, while the repayment of the principal is not. Interest on a loan for your home has its own limit. The rules depend on how the money is used, so keep the loan agreement and the lender's statements with your records, and ask your accountant how the interest should be treated. Our corporation tax calculator shows the effect of deductions on a company's tax.

Before you borrow

Ask whether the loan is affordable in a bad month, not only a good one, and use the start-up costs calculator and the cash flow forecast in this collection to test it. A loan that can only be repaid if everything goes right is a risk to the business and to anything you have pledged as security.

This workbook is a calculation tool and not financial advice. Real loans may use different rounding, day counts, fees or repayment structures, so check the figures against your loan agreement. For borrowing decisions, speak to your bank or an adviser, and check tax matters with the Kenya Revenue Authority.

Related guides, calculators and templates

• Guide: How to build a 12-month cash flow forecast for a small business
• Guide: How to price your products and services: markup, margin and break-even
• Guide: Turnover tax for small businesses in Kenya: rate, limits and when to use it
• Another template: 12-month cash flow forecast
• Another template: Break-even and pricing calculator
• Another template: Budget versus actual tracker

Need something different? Request a custom template, browse all guides or see every Excel template.