40-year modelling spreadsheet

The 40-year modelling spreadsheet is also called the financial modelling spreadsheet, the Radical Routes spreadsheet, the 40-year spreadsheet or the business plan.

This spreadsheet gets updated regularly, sometimes multiple times a year, by Catalyst Collective. At the time of writing, in May 2025, the most up to date version is version 3.3.5. The latest version of the spreadsheet is available in the RRModel Spreadsheet online file storage.

The spreadsheet works best when downloaded to a device, and opened Libreoffice - in the .ods file format. Libreoffice is a free, open source office suite similar to Microsoft Office. Once Libreoffice is downloaded, the spreadsheet software we recommend using, and that the spreadsheet was designed to be used on, is called LibreOffice Calc. It is possible to use Google Sheets or Microsoft Excel however we have noticed glitches in the calculations and protected cells, therefore advise against it.

Filling in the spreadsheet

It can be quite overwhelming to look at this spreadsheet. Some people with more spreadsheet experience have taught themselves how to use it. We recommend collecting all of the figures needed before trying to fill it in. Radical Routes Finance Group are happy to be contacted for advice or support to fill this in.

Information to collect before filling in the spreadsheet

If you are trying to fill in the spreadsheet, it will help to have the following information to hand. This guidance has come about from filling in the spreadsheet for an already housed coop. Much of it will also be applicable to an unhoused coop, and in some places estimate figures will need to be used.

Cash in the bank

This is total money in the co-op's name, in all co-op bank accounts, including savings.

Fixed assets

Find this information on the most recent balance sheet, representing the value of all property owned by the coop. This is zero if your co-op does not own any property.

Rental income

Be careful to input this correctly, either per week or per month

The most recent mortgage statement

The information needed from this document is 1) capital balance, 2) interest rate, and 3) monthly payment.

Radical Routes loan statement

The header of this document should say ""Co-operative & Community Finance"" as well as Radical Routes because CCF administer RR loans. The information needed from this document is 1) capital balance, 2) interest rate, and 3) monthly payment

Outstanding loanstock details

This should include 1) starting dates, 2) length in years, 3) interest rate, 4) amount, 5) simple/compound interest. The name of the lender should only include the initials, so for example loanstock from Zara Chowdhary should appear as 'ZC'. This is so that when the spreadsheet is shared externally, the loanstock investors are not identifiable.

Annual maintenance spend

This could be the average over 3 years, or an annual maintenance budget, or an estimate with a minimum of £250 per bedroom per year

Local Housing Allowance rate

LHA is calculated by inputting the postcode of the property on the government LHA calulator. The figure we use generally is the shared accommodation rate - correlating to one person paying 1 rent per bedroom. However, other rates may be applicable if, for example, a member is entitled to the 1 bedroom rate if they are receiving disability benefits, more bedrooms if they have dependents etc.

Bank charges

Many RR coops bank with Coop bank (free), or Unity Trust Bank (£72 per year, or £6 per month)

Any other regular income

For example - regular donations

Any other regular expenses

For example, travel, website hosting costs, internet or a building service charges. This should not include electric, gas or water bills as RR recommends these costs are dealt with as separate from the co-op's expenses, and housing co-ops charge separately for bills. The spreadsheet may or may not include internet expenses, depending on whether this is paid for by the coop.

How to input data into the spreadsheet

Most cells are protected, and so cannot be edited. It is recommended to keep the spreadsheet in protected mode so as not to change any of the formulae. Only the purple cells are editable. Orange sections are for existing coops, and yellow sections are for new coops, or purchasing a new property.

Inputting data by Sheet

Sheet name: Info and Ongoing Inc&Exp

Data to input - rental income; other income

For co-ops applying for a RR loan, rental income will need to be below LHA. Co-ops should be aware of the risk to losing their exemption for corporation tax if they are receiving any income that is not rent from members.

Data to input - Expenses - void percentage; house insurance; maintenance; council tax; bank charges; RR member payments; accountant; other expenses.

Sheet name: Day1 Inc&Exp

Data to input - Cash in the bank; day 1 expenditures (such as property purchase cost, mortgage fees, RR loan fees, Day 1 maintenance work, surveys etc. Scrolling down to the bottom of this page you should also input the value of any existing properties.

Sheet name: Existing Loans

This page calculates how much already existing loans money the coop owes in mortgages, other "mortgage-type" loans that are repaid on a monthly basis, or loanstock.

The capital balance reading date is the last date covered on the most recent loan statement.

The remaining capital balance is the amount of the loan that the co-op is due to repay - this could be in the tens or hundreds of thousands of pounds.

New Loans

This page models for any new mortgage-type loans, future mortgage-type loans, new loanstock, and future loanstock. The difference between new and future is that new starts from the year the spreadsheet is set in, whereas future can be set up to start at any point in the next 40 years.

Automatic Loanstock Refinancing

There is also a function in the 'Future Loanstock' section to turn on automatic refinancing. This is where the spreadsheet automatically adds in extra loanstock when it is needed. You can turn this on/off by selecting yes/no in the dropdown. To see the where the automatic loanstock has been added in, go to the Yearly Breakdown page - Money In - Loanstock In, and scroll across to see any non-zero figures. Loanstock In also appears as green vertical bars on the graph at the bottom of the Yearly Breakdown page.

Yearly Breakdown

Check that the rate of inflation is set to 3%, and the rent increase rate is set to at least 2%.

There is a 3% increase in the ‘Mortgage Interest rate change’ sheet over the first 10 years. This is based on Ecology Building Society's stress testing policy. The actual interest rate is inputted per loan in the New Loans or Existing Loans sheet. The lender's interest rate (often Ecology Building Society, or Triodos) includes the Bank of England 'base rate' that fluctuates.

Testing the viability of the spreadsheet

Once all sheets have been filled in, there are a number of things to check that the business plan is viable/sustainable, that income covers all expenditure, and that the bank balance remains positive.

On the Info and Ongoing Inc&Exp sheet, scroll to the bottom of the page, and check that the ongoing surplus is positive and ideally above £1200 per year.

On the Year 1 Breakdown sheet, scroll to the bottom of the page, and check that the balance is positive. Scroll to the right and check all 12 month bank balances are positive.

The Yearly Breakdown page is the main page to pay attention to to see that the business plan is viable. The bank balance needs to be positive at all times. On version 3.3.5 of the spreadsheet, the Bank Balance appears on Row 93, but in other versions it may appear higher or lower than this.

At the bottom of the Yearly Breakdown page, there is a graph that represents the bank balance over 40 years. This should have an upward trend, which means that the co-op is accumulating money over time and therefore can cover any unexpected costs.