[1M] Rainwater harvesting excel simulation

Last updated on 

Created a simulation in a group of 6 to decide on components of a rainwater harvesting system based on real-life weather data:

Rainwater collection size,
Storage size,
Pump system,
Filtration system,
Disinfection system,
Energy solution.

Input parameters

ParametersChoice (these are random for now)Notes
Daily Consumption541Average daily consumption for 5 years (L/day)
Rainwater collection:
Roof catchment1000m^3, 50m^3, or 100 m^3
Additional Catchment33Area of additional catchment ($150/m^2)
Collection Tank10000Volume of collection tank (L) (400/1500/2500/10000)
Storage:
Storage Tank Volume321 to 50m^3 at $275/m^3
Storage Tank x5
Storage Tank y-3
Storage Tank Tower11 for yes, 0 for no
Tower Height51
Total Tank height:7.5m (need to calculate from topography map)
Pump:
Pump choiceAChoose from Pump A, B, C or none
Filtration:
Filter LocationBeforeFilter located before storage tank or after
Filter Size (first one)11μ, 5μ or 200μ (THESE NEED TO BE IN INCREASING ORDER) (ONLY TWO ALLOWED)
Filter Size (second one)51μ, 5μ or 200μ
Filter Size (third one)01μ, 5μ or 200μ
Disinfection:
UV disinfection sytem5036W or 50W
Chemical DisinfectionOzoneChlorine or Ozone
Power System:
Power StrategyDieselSolar or Diesel
# of Batteries1
Battery Capacity2000
Battery Eff. IN0.96
Battery Eff. OUT0.96
If solar system:
Number batteries3$390/battery
Solar Panel Model260HES-260, SW-80, or HES-305P (number only)
Number of Panels 7HES-260 - $500/Panel, SW-80 - $205/panel, or HES-305P - 450/Panel
Inverter1Required for solar panels
If Diesel system:
Diesel Generator1Required for Diesel generator (4000W)
Constant
Density of water1000kg/m^3
Diameter20mm
Pipe Friction Factor0.05
Restriction Loss coeff10(for both pipe lines)
Cf experimental22281

Derived parameters and part evaluation

Min TankAverage Volume Captured1.019931507Filter coefficient5576.9343
Reference Cost 85,000Filter coefficient0.25447Pipe length:12.4m
Additional water (50L/day $75Pipe length9.5Water flowrate:24.9477305LPM
X distance of catchment2.9U0.5586160276Pump pressure:131.2012479kPa
Y distance of catchment0Pipe Area (m^2)0.0003141592654Diesel change frequenct (days)121Diesel replacement value(From graph)1.728358209
a20500Chlorine change frequency (days)0Chlorine replacement value(From graph)0
b0Severity (Health)(chlorine first then Diesel)4
c-73575Solar Panel Area1.6Severity(Environmental)(cl frist then diesel3
velocity of water to house (m/s)1.894472061Solar Panel Efficiency0.17
flow rate to house (m^3/s)0.000595165951Solar Panel Cost500E_health3.456716418
Average Water Collected1.06464274E_Env5.185074627
Pump (A/B/C)A1064.64274E=E_health + E_Env8.641791045
Volume water until filters need replace (L)20000E_min0
running hours until pump needs replace900E_max34
200micrometer filter? (1/0)1if this is 1, 5 is 20,000L. Maintenance: 20
if this is 1 ,1 is 15,000L Maintainance: 15
SatisfactionS(E):(0.5)(1+cos((E-E_min)/(E_max-E_min))PI)84.88911138
5 micrometer filter? (1/0)1
if this is 1, then lifetime for 1 is instead 20,000L , Maintenance: 20
No Pre filter (0)0When 0, lifetime for 5 is 10000, Maintenance: 101 is 5000 Maintenance: 5Flow rate to tank0.13
chlorine or ozone (1/2)1Maintanance 300 days
2Inventory

Power system greenhouse gas emissions calculations

kgCO2e for existing system (centralized)8825.6125
GASkgCO2e for burning diesel (for 5 years and 3.25kgCO2/L)3879.828058
Total kgCO2e for diesel power7777.828058
Relative Green House Gas emmissions (G)88.12791246
Satisfaction S(G)5.336214084
S(G) = ((1/2) * (1 + cos((G - Gmin)/(Gmax - Gmin) * PI()))) *100
Requirement Check:REQUIREMENT MET
SOLAR
Total kgCO2 for Solar Power6700kgCO2 left for solar panels
Relative Green House Gas emmissions (G)75.91541097
Satisfaction S(G)20.74526906
2
S(G) = ((1/2) * (1 + cos((G - Gmin)/(Gmax - Gmin) * PI()))) *100
3Requirement Check:REQUIREMENT MET

Cost calculations

Upfront Costs($)Maintenance CostsModelled for 5 years
Catchment Area Costs4950Filters5400
Catchment Tank Costs2000UV 550
Storage Tank Costs8800Chlorine5500
Water Tower Costs4286.85557Ozone0
Piping Costs558Pump0
Filter Costs235Solar Panel0
UV Costs750Diesel generator (fuel)3250
Chemical Disinfectant Costs4000Diesel generator (oil change)0
Pump Costs640
Power System Costs3640
Total Costs
39059.85557
Relative cost (%) to $85000
45.95277126

Daily simulation according to daily weather data

DateDaily Rainfall (mm)Volume Captured (m^3)Volume in Tank (m^3)Water Pumped (m^3)
Catchment Tank Volume (m^3)
Energy to move water (J)Pump Energy (J)Mass O3 (g)Energy for O3 (J)Energy for UV (J)Energy by battery for UV (J)
Energy generated by generator for battery (J)
Total Energy (J)Volume Diesel (L)Total Diesel Volume:Consumption not met?Hours of Sunlight in a dayAvailable Solar Energy (J/m^2)Area RequiredExtra energy required (J)Energy in Batteries (J)Mass chlorine solution requred (g)(resets when mass is 3kg since that is maintenence interval)(resets at volume required to change filters)(resets at running hours required to change pump)(resets at amount needed to refuel)(resets at amount needed to change oil)Maintanence on this day? 1 for yes 0 for noMaintenwnc for chorine? 1 for yesMaintenwnc for filter? 1 for yesMaintenwnc for pump? 1 for yesMaintenece gfor diesel? 1 for yesMaintenece for oil change?
1/1/20157.81.03741.03741.03741.03740136.1081746194.44024945.1871037400043200004320000432000014694194.441.049585317238.75864970817136008.575043441-4498125.5626098125.56207.480.207481037.40.69304901291.0495853171.049585317000000
1/2/201510.1331.17040.1330.133017.4497659724.928237110.66513300004320000432000043200005650024.9280.40357320920817136000-13000603.272160000026.60.234081170.40.78190145051.4531585261.453158526000000
1/3/201513.41.78222.95261.78221.78220233.8268641334.03837728.9111782200043200004320000432000022142334.041.5815952880817136000.31648811763072931.2918527068.71356.440.590522952.61.9725241143.0347538153.034753815000000
1/4/201564.28.538611.49128.53868.538601120.2749761600.39282242.6938538600043200004320000432000089707600.396.40768574208171360041.5385922573453417.08-54926348.371707.722.2982411491.27.6768506049.4424395579.442439557000000
1/5/201532.24.282615.77384.28264.28260561.8824644802.689234821.4134282600043200004320000432000047146802.693.36762876408171360059.5664980529119252.8-84045601.17856.523.1547615773.810.5378990912.8100683212.81006832010000
1/6/20152.80.372416.14620.37240.3724048.8593447369.79906391.86237240004320000432000043200008044069.7990.574576414208171360053.74047092-10702320.19-73343280.9774.480.0744816146.210.7866859213.3846447313.38464473000000
1/7/20150016.1462000000043200004320000432000043200000.308571428608171360045.32170925-14277427.2-59065853.7700.0744816146.210.7866859213.6932161613.69321616000000
1/8/20150016.1462000000043200004320000432000043200000.308571428608171360036.98987732-14277427.2-44788426.5700.0744816146.210.7866859214.0017875914.00178759000000
1/9/20150.80.106416.25260.10640.1064013.9598127819.942589680.53210640004320000432000043200005384019.9430.38457285308171360029.27897206-13255968.06-31532458.5221.280.0957616252.610.8577678714.3863604514.38636045000000

Final criteria evaluation

ConsumptionRelative costHealth & environmental risk exposurePower system greenhouse gas emissionsMaintenance occurrencesOn demand flow rateReliabilityLand use
min1602502010152200
max6901153410085403650.1
Satisfaction0.81733980920.87212346990.84889111380.053362140840.99656493160.92908483620.87036186880.4413839724
weight0.150.150.150.10.10.10.20.05
weighted satisfaction0.005336214084
Total Satisfaction0.7747959222
Requirements Met?

Sareen I, Saito H, Grewal H, Kimiaeimehr S, Kaver M A  2020