How to model loan repayments in sherloc

To bring a loan repayment into your forecast, use Create assumption to add it in two places: your profit and loss and your balance sheet.

1. Work out your monthly repayment

From your loan agreement, note your monthly repayment figure, split between interest and capital if you can. This is the amount you’ll be entering.

Example: a loan repaid over 5 years might come to roughly £900 a month, say £150 interest and £750 capital.

2. Add the interest on your profit and loss

Select Create assumption. In the Create Assumption drawer, give the assumption a name, then pick the Profit And Loss sheet and the line you use for loan interest (for example Interest Paid).

Under When should the assumption happen, choose All months. Under What the assumption is modelling, pick Replace the figure if that line is otherwise empty, or Increase / decrease with £ Fixed amount if it already has a figure you want to add to. Enter your monthly interest figure, then select Add assumption.

sherloc doesn’t recalculate interest as your loan balance falls, so the figure you enter is the figure used in every month.

3. Reduce your loan balance on your balance sheet

Select Create assumption again, name it, and pick the Balance Sheet sheet. Choose your loan line. If your balance sheet splits the loan into a current portion and the rest, choose the line that holds the balance being repaid. The current portion line isn’t changed by this assumption.

Choose All months, then Consecutive monthly change. Set Change by to £ Fixed amount, and under Change each month enter your monthly capital repayment as a negative number, then select Add assumption.

The first month already includes the first repayment, and the balance then steps down by the same amount every month. Your cash at bank falls with it automatically, so your balance sheet stays balanced.

Example: a £12,000 loan repaid £1,000 a month steps down to £11,000, then £10,000, and so on.

4. Check the numbers

Look at your profit and loss and your balance sheet to confirm both assumptions have landed on the right lines and are tracking through the months you expect. If your loan finishes within the forecast, you may want to check the month it reaches nil, as the balance keeps stepping down each month for as long as the assumption runs.