Community Land Trust Fund

Delivering Affordable Sustainable Housing
How to use the Workbook:

Before you start

Download ‘Before you start’ as a Word file

The workbook has been set up for new build development, for a range of procurement options and tenure types.  Works to existing buildings are too vaired in character to be capable of incorporation into a standard appraisal spreadsheet, as they can encompass everything from an “existing satisfactory” house requiring only catch-up repairs, to conversion of a derelict non-residential building to houses or flats.  The different assumptions for works to existing buildings, in terms of works costs, on-costs and time-scales, do not lend themselves to a standard appraisal tool such as this.  However, the same principles can be applied in the construction of a bespoke spreadsheet.

Before starting to complete the workbook you should review the checklist below to ensure you have all the information required in order to complete the appraisal.

Checklist

The following checklist shows the information you will require in order to complete the model.  It may be worth printing off a copy to complete as you proceed.

  Description Information complete
1 Procurement type: Traditional, Design & Build or Off the Shelf Yes/No
2 Tenure mix and numbers of each tenure type Yes/No
3 Date initial purchase of site complete Yes/No
4 Start on site date Yes/No
5 Practical completion date Yes/No
6 Funding sources (excluding bank funding) – grant, local authority grants or other funding including community funding. Yes/No
7 Local Authority Yes/No
8 Year rental income will commence – for Social Rent schemes Yes/No
9 There are default values for the following variables. You will need to review these for your individual scheme:

  • Interest rate on debt during development period
  • Interest rate on long term funding after practical completion
  • Length of long term loan in years
  • Discount rate
  • Management costs per unit per year
  • Maintenance costs per unit per year
  • Building Fund/Major repairs allowance as % of build cost
  • Voids rate
  • Amount of equity sold for HomeBuy and/or Shared Equity
  • Inflation rates for rental income, management and maintenance costs
 Yes/No
10 Estimate of development costs split between land, build costs and on-costs.Open market value of completed units Yes/No
11 Open market value of completed units Yes/No
12 1999 open market values of Social Rent units Yes/No
13 Size in m2 of each type of property Yes/No
14 Date of sale – for HomeBuy, Shared Equity and Outright Sale Yes/No
15 Estimate of rents for Affordable Rent and Market Rent Yes/No

Entering data

The workbook should be completed in the following order:

  • Project Information
  • Development Cash Flow
  • Social Rent (as applicable)
  • Affordable Rent (as applicable)
  • HomeBuy (as applicable)
  • Market Rent (as applicable)
  • Shared Equity (as applicable)
  • Outright sale (as applicable).

All compulsory fields are indicated by **

A checklist tab is included in the workbook. This allows you to indicate which sheets are complete and which are not. This can be used as a prompt to ensure all sheets are completed.

This tab also includes a table of warnings. The warnings will only appear if obvious errors have been made in the completion of the workbook.  An example of this is having practical completion before the start on site date.

In all worksheets, manual input is required where a field is shaded blue. Where a field is shaded yellow, a calculation is included in the cell. Where better information is available, you can be over-write it with your own data. The output sheets are all password protected to avoid them being overwritten inadvertantly, thus corrupting the results. The password is CLT if you do wish to over-write them deliberately.

Some versions of Excel will ask if you wish to have macros enabled. If prompted, settle to have them enabled.

Risk Management

The workbook also has an important role to play in risk management.  As well as illustrating the financial consequences of a particular course of action, it should also be used to test the sensitivity of an appraisal to changes such as building cost overruns, delays in the development period or delays in sales.  Therefore, once an appraisal has been constructed, a number of those key variables can be changed in order to see whether the financial consequences can be sustained by the CLT. 

We suggest that the following are tested:

  • Development costs increase by 10%
  • Construction period increases by 10%
  • Sale prices (if the scheme contains outright sale, equity share or HomeBuy) decrease by 10%
  • Rent levels reduced by 10%
  • Sales delayed by 3 months.

Download Financial Appraisal Tool

Financial Appraisal Tool Excel file (2.78 MB)

This will take about 7 minutes to download using a dial-up connection and about 2 minutes with a broadband connection.

Some versions of Excel will ask if you wish to have macros enabled. If prompted, settle to have macros enabled.