40-year modelling spreadsheet - Revision history

Ruby: /* Sheet 1) - Information and Ongoing income & expenditure */

2025-11-24T16:09:16Z

Sheet 1) - Information and Ongoing income & expenditure

← Older revision		Revision as of 16:09, 24 November 2025
Line 33:		Line 33:
	Expenses to input: '''void percentage'''; '''house [[insurance \| insurance]]'''; '''maintenance'''; '''council tax'''; '''bank charges'''; '''accountant costs'''; '''other expenses.'''		Expenses to input: '''void percentage'''; '''house [[insurance \| insurance]]'''; '''maintenance'''; '''council tax'''; '''bank charges'''; '''accountant costs'''; '''other expenses.'''

	For co-ops applying for a RR loan, rental income will need to be below [https://lha-direct.voa.gov.uk/ 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.		For co-ops [[applying for a RR loan]], rental income will need to be below [https://lha-direct.voa.gov.uk/ 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.

	For insurance, maintenance, and council tax - you can input these as either actual costs or estimated. This won't have any impact on the model and is more to give some background to the figures.		For insurance, maintenance, and council tax - you can input these as either actual costs or estimated. This won't have any impact on the model and is more to give some background to the figures.

Edarin: /* Sheet 4) - New Loans */

2025-05-26T18:29:25Z

Sheet 4) - New Loans

← Older revision		Revision as of 18:29, 26 May 2025
Line 52:		Line 52:

	===Sheet 4) - New Loans===		===Sheet 4) - 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 1st year the spreadsheet is set in, whereas future can be set up to start at any point in the next 40 years.		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 1st year the spreadsheet is set in, whereas future can be set up to start at any point in the next 40 years.

			There are three types of interest rate for mortgage-type loans: '''fixed''', '''variable''' and '''RR rate'''. The RRrate stands for RR loan that are at the lowest interest rate. Indeed this lower interest rate is based on the average interest rate requested by RR investors which might fluctuate. For RR loan at 6% however this should be set as a fixed rate.

	====Automatic Loanstock Refinancing====		====Automatic Loanstock Refinancing====

Edarin: /* Sheet 3) - Existing Loans */

2025-05-26T18:20:22Z

Sheet 3) - Existing Loans

← Older revision		Revision as of 18:20, 26 May 2025
Line 45:		Line 45:

	===Sheet 3) - Existing Loans===		===Sheet 3) - Existing Loans===
	This page calculates how much '''already existing loans''' money the coop owes. This could be a mortgage, a "mortgage-type" loans (e.g. RR loans that are repaid on a monthly basis), or loanstock.		This page calculates how much '''already existing loans''' money the coop owes. This could be a mortgage, a "mortgage-type" loans (e.g. RR loans that are repaid on a monthly basis), or loanstock (i.e. loans being reimbursed at once at the end of their maturity period).

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

Edarin: /* Sheet 2) - Income and expenses needed at the start */

2025-05-26T18:18:48Z

Sheet 2) - Income and expenses needed at the start

← Older revision		Revision as of 18:18, 26 May 2025
Line 40:		Line 40:
	One reason you might be filling in this spreadsheet is, for instance, you are modelling for an upfront cost to the coop. You can use the sheet (shorthand - '''Day1 Inc&Exp''') to represent initial costs that are not repeated year on year.		One reason you might be filling in this spreadsheet is, for instance, you are modelling for an upfront cost to the coop. You can use the sheet (shorthand - '''Day1 Inc&Exp''') to represent initial costs that are not repeated year on year.

	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.'''		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.''' The mortgage fees can be either inputted manually or automatically derived from the new mortgage information.

	If you are not taking out any loans, ensure you remove any loan fees on this sheet.		If you are not taking out any loans, ensure you remove any loan fees on this sheet.

	===Sheet 3) - Existing Loans===		===Sheet 3) - Existing Loans===

Edarin: /* Sheet 1) - Information and Ongoing income & expenditure */

2025-05-26T18:17:05Z

Sheet 1) - Information and Ongoing income & expenditure

← Older revision		Revision as of 18:17, 26 May 2025
Line 30:		Line 30:

	Income to input: '''rental income''' and '''other income'''.		Income to input: '''rental income''' and '''other income'''.

	Expenses to input: '''void percentage'''; '''house [[insurance \| insurance]]'''; '''maintenance'''; '''council tax'''; '''bank charges'''; '''accountant costs'''; '''other expenses.'''		Expenses to input: '''void percentage'''; '''house [[insurance \| insurance]]'''; '''maintenance'''; '''council tax'''; '''bank charges'''; '''accountant costs'''; '''other expenses.'''

	For co-ops applying for a RR loan, rental income will need to be below [https://lha-direct.voa.gov.uk/ 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.		For co-ops applying for a RR loan, rental income will need to be below [https://lha-direct.voa.gov.uk/ 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.

	For insurance, maintenance, and council tax - you can input these as either actual costs or estimated.		For insurance, maintenance, and council tax - you can input these as either actual costs or estimated. This won't have any impact on the model and is more to give some background to the figures.

	===Sheet 2) - Income and expenses needed at the start===		===Sheet 2) - Income and expenses needed at the start===

Sim.brighton: /* Sheet 1) - Information and Ongoing income & expenditure */

2025-05-26T13:35:29Z

Sheet 1) - Information and Ongoing income & expenditure

← Older revision		Revision as of 13:35, 26 May 2025
Line 33:		Line 33:

	For co-ops applying for a RR loan, rental income will need to be below [https://lha-direct.voa.gov.uk/ 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.		For co-ops applying for a RR loan, rental income will need to be below [https://lha-direct.voa.gov.uk/ 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.

			For insurance, maintenance, and council tax - you can input these as either actual costs or estimated.

	===Sheet 2) - Income and expenses needed at the start===		===Sheet 2) - Income and expenses needed at the start===

Sim.brighton: /* Information required to enable filling in the spreadsheet */

2025-05-26T13:28:16Z

Information required to enable filling in the spreadsheet

← Older revision		Revision as of 13:28, 26 May 2025
Line 19:		Line 19:
	# '''Local Housing Allowance rate''' - LHA is calculated by inputting the postcode of the property on the [https://lha-direct.voa.gov.uk/ government LHA calulator]. The figure we generally use 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.		# '''Local Housing Allowance rate''' - LHA is calculated by inputting the postcode of the property on the [https://lha-direct.voa.gov.uk/ government LHA calulator]. The figure we generally use 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 (£6 per month = £72 per year).		# '''Bank charges''' - Many RR coops bank with Coop bank (free), or Unity Trust Bank (£6 per month = £72 per year).
			# '''RR member payments''' Check your bank statement for how much you pay RR (quarterly) if you are a member of RR. This will correspond to a drop-down menu on the spreadsheet.
	# '''Any other regular income''' - For example - regular donations.		# '''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.		# '''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.

Sim.brighton: /* Information required to enable filling in the spreadsheet */

2025-05-26T13:12:42Z

Information required to enable filling in the spreadsheet

← Older revision		Revision as of 13:12, 26 May 2025
Line 12:		Line 12:
	# '''Rental income''' - Be careful to input this correctly, either per week or per month		# '''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.		# '''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'' </br> [[file:a Radical Routes loan statement.png]] </br> </br>		# '''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'' </br> [[file:a Radical Routes loan statement.png]] </br> </br>
	# '''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. </br>		# '''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. </br>
	# '''Annual council tax'''		# '''Annual council tax'''

Sim.brighton: /* Sheet 5) - Year 1 Breakdown */

2025-05-26T13:01:47Z

Sheet 5) - Year 1 Breakdown

← Older revision		Revision as of 13:01, 26 May 2025
Line 57:		Line 57:
	This page shows you how viable the plan is in Year 1, broken down by months 1-12. Each column represents 1 month, and you need to scroll sideways, to the right, to see all 12 months.		This page shows you how viable the plan is in Year 1, broken down by months 1-12. Each column represents 1 month, and you need to scroll sideways, to the right, to see all 12 months.

	If you need to input specific income or expenses in Year 1 in a specific month, you can input this here. For example -		If you need to input specific income or expenses in Year 1 in a specific month, you can input this here.
	You are doing renovations on a bedroom and the ~~extra~~ rent becomes available in the 6th month after the spreadsheet begins. In this case you would input the full rents in the 'Info and Ongoing Inc&Exp' sheet, and then, reduce 1 rent level for the first 5 months. ~~It should go up in~~ the ~~following~~ months.
			For example -
			You are going to start doing renovations on a bedroom in month 3, and then the rent from that bedroom only becomes available in the 6th month after the spreadsheet begins. In this case you would:

			1) input the extra maintenance cost in 'Year 1 breakdown', under the 'Money Out' section, in the 3rd month, in a purple cell - either in Cell H42 (maintenance), or Cell H50 (other) is fine.
			2) input the full rents in the 'Info and Ongoing Inc&Exp' sheet, and then in the 'Year 1 Breakdown' sheet, reduce 1 rent level for the first 5 months. This would represent that the first 5 months has 1 less rent coming in.

	===Sheet 6) - Yearly Breakdown===		===Sheet 6) - Yearly Breakdown===

Sim.brighton: /* Sheet 5) - Year 1 Breakdown */ adding examples

2025-05-26T11:07:26Z

Sheet 5) - Year 1 Breakdown: adding examples

← Older revision		Revision as of 11:07, 26 May 2025
Line 55:		Line 55:

	===Sheet 5) - Year 1 Breakdown===		===Sheet 5) - Year 1 Breakdown===
	This page shows you how viable the plan is in Year 1, broken down by months 1-12. Each column represents 1 month, and you need to scroll sideways, to the right, to see all 12 months.		This page shows you how viable the plan is in Year 1, broken down by months 1-12. Each column represents 1 month, and you need to scroll sideways, to the right, to see all 12 months.

			If you need to input specific income or expenses in Year 1 in a specific month, you can input this here. For example -
			You are doing renovations on a bedroom and the extra rent becomes available in the 6th month after the spreadsheet begins. In this case you would input the full rents in the 'Info and Ongoing Inc&Exp' sheet, and then, reduce 1 rent level for the first 5 months. It should go up in the following months.

	===Sheet 6) - Yearly Breakdown===		===Sheet 6) - Yearly Breakdown===