This document provides a complete analysis of the Domogo Rendementscalculator (Return on Investment Calculator) for home battery systems. The Excel workbook is a sophisticated financial modeling tool that calculates the ROI for installing battery storage systems, considering:
- Energy consumption patterns
- Dutch energy market specifics (saldering/net metering phase-out)
- Battery specifications and degradation
- VAT implications for private vs. business use
- Dynamic and fixed energy contracts
- Financing options
Key Complexity Areas:
- Multi-year projections (20+ years) with inflation and market changes
- Dutch net metering (saldering) policy phase-out calculations
- Battery degradation modeling using annuity-based depreciation
- Complex VAT reclaim calculations based on usage type
- Dynamic energy pricing scenarios
The Calculator sheet collects all user inputs. These are non-formula cells that users fill in:
- Name (Voor- en achternaam):
Calculator!H8 - Address (Straat en huisnummer):
Calculator!H10 - Postal Code (Postcode):
Calculator!H12 - City (Plaats):
Calculator!K12 - Email:
Calculator!H14 - Phone (Telefoonnummer):
Calculator!H16
- Grid Consumption (Verbruik):
Calculator!R30- kWh consumed from grid per year - Feed-in to Grid (Teruglevering):
Calculator!R32- kWh returned to grid per year - Solar Production (Opwek):
Calculator!R34- kWh generated by solar panels per year - Direct Consumption (calculated):
R34 - R32 - Total Consumption (calculated):
R30 + (R34 - R32)
- Connection Type (Aansluiting):
Calculator!R84- "1 fase" or "3 fase" - Battery Brand (Merk):
Calculator!Q86- Selected from dropdown - Inverter Model (Omvormer):
Calculator!Q88- Selected from dropdown - Battery Product (Batterij):
Calculator!M90- Full product selection from catalog
- Installation Date:
Calculator!R110- Date when battery will be installed - Energy Supplier:
Calculator!R112- New energy provider - Battery Mode (current):
Calculator!P114- Operating mode until saldering ends- Options: "Actief handelen" (Active trading), "Alleen zelfconsumptie" (Self-consumption only)
- Battery Mode (future):
Calculator!P116- Operating mode after saldering - Future Mode Start Year:
Calculator!R118- Year when future mode starts
- Purchase Price Battery:
Calculator!R125- Invoice amount for battery - Purchase Price Other:
Calculator!R127- Other purchase costs - Installation Costs: Various cells for different installation types
- Maintenance Contract:
Calculator!R160- Annual maintenance cost - Financing Duration:
Calculator!R167- Months of financing - Interest Rate:
Calculator!R169- Annual interest rate for financing
Location: Stamgegevens sheet, rows 8-36 (years 2025-2053)
Key columns:
- A: Year (jaar)
- C: VAT percentage for batteries (BTW-% batterijen)
- D: Maximum VAT refund (maximale BTW teruggave)
- E: VAT to pay per kWh (af te dragen BTW per kWh)
- F: VAT to pay per 1000 Wp (af te dragen BTW per 1000 Wp)
- H: Annual inflation rate (jaarlijkse inflatie)
- J: Energy tax inclusive VAT tier 1 (energiebelasting incl BTW)
- K: Energy tax threshold in kWh (vanaf KwH)
- L: Energy tax inclusive VAT tier 2 (energiebelasting incl BTW)
- M: Annual adjustment percentage (jaarlijkse aanpassing)
- N: Net metering percentage (percentage saldering) - 100% until 2026, then 0%
- P: Fixed delivery cost electricity (vastrecht stroom nieuw)
- Q: Fixed delivery cost gas (vastrecht gas nieuw)
- S: Delivery cost low tariff (leveringskosten laag)
- T: Delivery cost high tariff (leveringskosten hoog)
- W: Gas conversion factor (omreken factor gas)
- Y: Feed-in tariff (tarief teruglevering)
- AA: Effective minutes per quarter (effectieve minuten per kwartier) - for active trading
- AC: Correction tariff trading (correctie tarief handel)
- AE: Payout percentage (uitbetalings %)
Location: Producten sheet, rows 8+ (each row is a product)
Key columns:
- H: Product (full name, formula-generated)
- I: Brand (Merk) - e.g., "AlphaESS", "BYD", "Huawei"
- J: Model - e.g., "Smile3,6 binnen", "Battery-Box Premium HVS"
- K: Module capacity (kWh per module)
- L: Number of modules (Aantal)
- M: Total capacity (Capaciteit) - calculated:
K * L - N: Phase connection (Aansluiting) - "1 Fase" or "3 Fase"
- O: Round-trip efficiency (RTE) - e.g., 0.96 (96%)
- P: Guaranteed cycles (Cycles) - e.g., 10000
- Q: Cycles display (shown to user)
- R: Remaining capacity after warranty (Restcapaciteit) - e.g., 0.7 (70%)
- S: Warranty years (Garantie)
- T: Inverter power (Omvormer) - kW
- U: Price including VAT (Prijs in BTW)
- V: Price excluding VAT (Prijs Ex BTW) - calculated
- W: Invoice batteries (factuur Batterijen)
- X: Invoice installation (factuur Installatie)
- Y: VAT refund (factuur BTW teruggave)
- Z: Invoice outside VAT (factuur buiten BTW)
- AA: Emergency power (Noodstroom) - "Ja"/"Nee"
- AB: Price per kWh
- AC-AE: IP ratings and outdoor suitability
- AF-AJ: Physical dimensions and weight
- AL: Control method (Aansturing)
- AM: Mounting type (Bevestiging)
Location: Degradatie sheet
Uses financial annuity functions to model battery degradation over time:
- Calculates degradation percentage based on total cycles vs. guaranteed cycles
- Uses
CUMIPMTfunction to create degradation curve - Provides year-by-year degradation percentages
Location: Cycli sheet
Tracks battery cycles per year:
- Daily cycles based on battery operating mode
- Annual cycles calculation
- Cumulative cycles over lifetime
- Efficiency tracking
Location: financieel nadeel sheet
Calculates three scenarios:
- Original costs (with full saldering):
Sheet!G31 - Current costs (current saldering %):
Sheet!M31 - Post-saldering costs (no saldering):
Sheet!S31
Formula pattern:
Costs = (Consumption × Rate) + Energy_Tax - (Feed_in × Compensation) + Fixed_Costs
Key calculations:
- Delivery tariff with/without energy tax
- Feed-in compensation based on saldering percentage
- Fixed delivery costs
Location: Opbrengsten (Returns) sheet
For each year (rows starting at 12):
- Year counter: Column B
- Within warranty?: Column C -
IF(A12 > Warranty_Years, "Nee", ...) - Inflation factor: Column D
- Daily cycles: Column F - from Cycli sheet
- Annual cycles: Column G -
F × 365 × Year_Percentage - Cumulative cycles: Column H -
SUM(G$12:G12) - Battery efficiency: Column I
- Guaranteed cycles: Column K
- Remaining capacity: Column L
- Degradation percentage: Column M - from Degradatie sheet
- Cumulative degradation: Column N -
SUM(M$10:M12) - Average capacity: Column P -
(1 + (1 - N12)) / 2 - Effective capacity: Column Q -
P12 × Original_Capacity - Energy stored: Column R -
Q12 × G12
Location: Opbrengsten sheet (continued)
For each operating mode and tariff structure:
- Self-consumption savings: Direct use of battery-stored energy instead of grid purchase
- Active trading savings: Buy low (night), sell high (day)
- Feed-in reduction: Less energy returned to grid at unfavorable rates
Formula components:
Savings = (Battery_Energy × Efficiency × Price_Delta) - Losses
Where Price_Delta depends on:
- Time-of-use tariff difference (for active trading)
- Grid price vs. feed-in tariff (for self-consumption)
- Energy tax applicable to purchases
Location: BTW sheet
Complex VAT scenarios:
- Private user, no VAT reclaim: Simple purchase with VAT
- Private user, VAT reclaim:
- Maximum refund:
Stamgegevens.MaxVAT(e.g., €2494) - Minus:
kWh_capacity × VAT_per_kWh - Minus: Future VAT on feed-in electricity
- Maximum refund:
- Business user, no KOR:
- Full VAT reclaim on invoice
- Minus: VAT on feed-in
- Business user, with KOR (kleine ondernemersregeling - small business scheme):
- Similar to private user scenario
Key formula pattern (simplified):
VAT_Refund = MIN(
Invoice_VAT,
MaxVAT - (Capacity_kWh × VAT_per_kWh) - Future_Feed_in_VAT
)
Location: Kosten sheet
Yearly costs tracked:
- Purchase costs: Year 1 only
- VAT refund: Year 2 (based on Dutch tax timing)
- Maintenance: Annual, inflation-adjusted
- Financing costs: Monthly payments if financed
- Connection upgrade: One-time cost if needed
Location: Calculator sheet, rows 188-210
Key metrics:
- Cumulative costs: Investment + ongoing costs over time
- Cumulative returns: Energy savings accumulated
- Net ROI: Returns - Costs
- Payback period: Year when cumulative ROI becomes positive
- Average annual return: Total returns / years
- ROI multiple: Total returns / total investment
User Inputs (Calculator sheet)
↓
┌───┴─────────────────────────────────────────────┐
│ ↓
│ Product Selection (Productselectie) Stamgegevens (Master Data)
│ ↓ ↓
│ Product Lookup (Producten) Jaargegevens (Yearly Data)
│ ↓ ↓
│ ├→ Battery Specs ├→ Inflation rates
│ ├→ Price ├→ Energy tax rates
│ ├→ Efficiency ├→ Saldering %
│ └→ Warranty/Cycles └→ Energy prices
│ ↓ ↓
└────────┬────────────────────────────────────────┘
↓
Installation Date & Mode Selection
↓
┌────┴────┐
↓ ↓
Cycli Degradatie
(Battery (Battery
usage) degradation)
↓ ↓
└────┬────┘
↓
Opbrengsten (Returns/Savings per year)
├→ Energy stored
├→ Self-consumption savings
├→ Trading savings
└→ Feed-in changes
↓
┌────┴────┐
↓ ↓
financieel BTW
nadeel (VAT calculations)
(Saldering
impact)
↓ ↓
└────┬────┘
↓
Kosten (Total costs per year)
├→ Purchase
├→ VAT refund
├→ Maintenance
└→ Financing
↓
Final ROI Summary (Calculator sheet bottom)
├→ Cumulative returns
├→ Cumulative costs
├→ Net ROI
└→ Payback period
-
Initial Setup (Year 0)
- User enters current energy usage
- Selects battery product from catalog
- Product specs loaded (capacity, efficiency, cycles, warranty)
- Master data loaded for installation year
-
Yearly Iteration (Years 1-20+)
- Load year-specific parameters from Stamgegevens
- Calculate battery degradation for current year
- Calculate cycles used in current year
- Calculate energy savings based on:
- Battery capacity (degraded)
- Operating mode
- Energy prices (inflated)
- Saldering percentage
- Calculate costs:
- Maintenance (inflated)
- Financing (if applicable)
- One-time costs in specific years
-
Cumulative Calculations
- Running total of costs
- Running total of savings
- Net position calculation
- Identify payback year
Direct Consumption:
R36 = R34 - R32
Where: R34 = Solar production, R32 = Feed-in to grid
Total Consumption:
R38 = R30 + R36
Where: R30 = Grid consumption, R36 = Direct consumption
Battery Capacity (from product):
R92 = VLOOKUP($M$90, Producten!$H$7:$AA$1007, 6, FALSE)
Looks up column M (capacity) from Producten sheet based on selected product
Battery Efficiency:
R94 = VLOOKUP($M$90, Producten!$H$7:$AA$1007, 8, FALSE)
Guaranteed Cycles:
R98 = VLOOKUP($M$90, Producten!$H$7:$AA$1007, 10, FALSE)
Remaining Capacity After Warranty:
R100 = VLOOKUP($M$90, Producten!$H$7:$AA$1007, 11, FALSE)
Consumption Cost (Original Scenario):
= (Consumption_kWh × Delivery_Tariff)
+ Energy_Tax
+ Fixed_Costs
Feed-in Compensation (With Saldering):
= MIN(Consumption_kWh, Feed_in_kWh) × (Delivery_Tariff + Energy_Tax)
+ MAX(0, Feed_in_kWh - Consumption_kWh) × Feed_in_Rate
Post-Saldering Costs:
= (Consumption_kWh × Rate)
+ Energy_Tax
- (Feed_in_kWh × Feed_in_Rate)
+ Fixed_Costs
+ Feed_in_Penalty_Costs
Installation Date:
E3 = IF(ISNUMBER(Calculator!R110), Calculator!R110, NOW())
Start Year:
E5 = YEAR(E3)
Year Fraction (First Year):
E7 = Days_Remaining_in_Year / 365
Annual Inflation Rate for Year N:
D14 = VLOOKUP($A14, Stamgegevens!$A$8:$AE$36, Stamgegevens!$H$1)
Energy Tax for Year N:
F14 = VLOOKUP($A14, Stamgegevens!$A$8:$AE$36, Stamgegevens!$J$1)
Cycles Per Day (Year N):
D10 = IF(Calculator!$R$118 > 0,
IF(B10 < Calculator!$R$118,
Calculator!$U$114, // Current mode cycles
Calculator!$U$116), // Future mode cycles
Calculator!$U$114) // Default mode cycles
Annual Cycles:
E10 = D10 × 365 × Year_Fraction
Cumulative Cycles:
F10 = SUM($E$10:E10)
Total Degradation Percentage:
D7 = ((Total_Cycles / Guaranteed_Cycles) × (1 - Residual_Capacity)) × 100
Fictive Interest Rate (for annuity-based degradation):
D9 = RATE(Lifespan_Years, -D7/10, D7, 0, 0)
Annual Degradation (Year N):
C16 = -(CUMIPMT($D$9, $D$8, $D$7, B16, B16, 0)) / 100
Remaining Capacity (Year N):
G16 = G15 - F16
Where F16 = (Previous_Year_Degradation × Year1_Factor) + (Current_Year_Degradation × Year2_Factor)
Within Warranty Check:
C12 = IF(A12 > Calculator!$R$96, "Nee",
IF(H11 < K12, "Ja", "Nee"))
Daily Cycles:
F12 = Cycli!Q10
Annual Cycles:
G12 = F12 × 365 × Year_Fraction
Cumulative Cycles:
H12 = SUM(G$12:G12)
Average Capacity (accounting for degradation):
P12 = (1 + (1 - Cumulative_Degradation)) / 2
Effective Capacity:
Q12 = P12 × Original_Capacity
Annual Energy Stored:
R12 = Q12 × G12
Annual Savings (simplified):
= (Energy_Stored × Efficiency × Price_Delta)
- (Cycles × Fixed_Cost_per_Cycle)
VAT Percentage:
D3 = IF(D13="nee", 0,
VLOOKUP(Jaargegevens!$E$5, Stamgegevens!$A$8:$AE$36, Stamgegevens!$C$1))
VAT Fraction:
E3 = IF(D3=0, 0, D3/(1+D3))
Invoice VAT Amount:
E5 = ROUND(Invoice_Amount × VAT_Fraction, 0)
Maximum VAT Refund:
E9 = VLOOKUP(Year, Stamgegevens!$A$8:$AE$36, Stamgegevens!$D$1)
VAT per kWh:
E11 = VLOOKUP(Year, Stamgegevens!$A$8:$AE$36, Stamgegevens!$E$1)
Final VAT Refund:
= MIN(Invoice_VAT,
Max_Refund - (Capacity_kWh × VAT_per_kWh) - Future_Feed_in_VAT)
Purchase Costs (Year 1):
H6 = Calculator!R125 + Calculator!R127
VAT Refund (Year 2):
J7 = IF(BTW!$X$11 >= B7, -BTW!$V$11, 0)
Net Purchase Cost:
K7 = H7 - J7
Annual Maintenance (Inflated):
= Base_Maintenance × (1 + Cumulative_Inflation)
Financing Monthly Payment:
= PMT(Monthly_Interest_Rate, Total_Months, -Loan_Amount)
CREATE TABLE products (
id SERIAL PRIMARY KEY,
brand VARCHAR(100) NOT NULL,
model VARCHAR(200) NOT NULL,
phase_type VARCHAR(20) NOT NULL, -- '1_phase' or '3_phase'
module_capacity_kwh DECIMAL(6,2) NOT NULL,
module_count INTEGER NOT NULL,
total_capacity_kwh DECIMAL(6,2) GENERATED ALWAYS AS (module_capacity_kwh * module_count) STORED,
roundtrip_efficiency DECIMAL(4,3) NOT NULL, -- 0.96 = 96%
guaranteed_cycles INTEGER NOT NULL,
residual_capacity DECIMAL(4,3) NOT NULL, -- 0.70 = 70%
warranty_years INTEGER NOT NULL,
inverter_power_kw DECIMAL(5,2),
price_incl_vat DECIMAL(10,2) NOT NULL,
installation_cost DECIMAL(10,2),
has_emergency_power BOOLEAN DEFAULT FALSE,
ip_rating_inverter VARCHAR(10),
ip_rating_battery VARCHAR(10),
suitable_outdoor BOOLEAN DEFAULT FALSE,
width_mm INTEGER,
depth_mm INTEGER,
height_mm INTEGER,
weight_kg DECIMAL(6,2),
control_method VARCHAR(100),
mounting_type VARCHAR(100),
notes TEXT,
active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_products_phase ON products(phase_type);
CREATE INDEX idx_products_active ON products(active);CREATE TABLE master_data_yearly (
id SERIAL PRIMARY KEY,
year INTEGER NOT NULL UNIQUE,
vat_rate DECIMAL(5,4) NOT NULL, -- 0.21 = 21%
max_vat_refund DECIMAL(10,2) NOT NULL,
vat_per_kwh DECIMAL(6,2) NOT NULL,
vat_per_1000wp DECIMAL(6,2) NOT NULL,
inflation_rate DECIMAL(6,5) NOT NULL,
energy_tax_tier1_incl_vat DECIMAL(8,5) NOT NULL,
energy_tax_threshold_kwh INTEGER NOT NULL,
energy_tax_tier2_incl_vat DECIMAL(8,5) NOT NULL,
energy_tax_adjustment_rate DECIMAL(6,5),
saldering_percentage DECIMAL(4,3) NOT NULL, -- 1.0 = 100%, 0.0 = 0%
fixed_cost_electricity DECIMAL(8,2) NOT NULL,
fixed_cost_gas DECIMAL(8,2),
delivery_cost_low DECIMAL(8,5),
delivery_cost_high DECIMAL(8,5),
gas_conversion_factor DECIMAL(5,2),
feed_in_tariff DECIMAL(8,5),
effective_minutes_per_quarter INTEGER,
trading_correction_tariff DECIMAL(8,5),
payout_percentage DECIMAL(4,3),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_master_data_year ON master_data_yearly(year);CREATE TABLE customers (
id SERIAL PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(255),
phone VARCHAR(50),
street_address VARCHAR(255),
postal_code VARCHAR(20),
city VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_customers_email ON customers(email);CREATE TABLE calculations (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
created_by INTEGER, -- user_id if you have authentication
-- Energy usage inputs
grid_consumption_kwh DECIMAL(10,2) NOT NULL,
grid_feed_in_kwh DECIMAL(10,2) NOT NULL,
solar_production_kwh DECIMAL(10,2) NOT NULL,
-- Product selection
product_id INTEGER REFERENCES products(id),
-- Installation details
installation_date DATE NOT NULL,
energy_supplier VARCHAR(100),
connection_upgrade_cost DECIMAL(10,2) DEFAULT 0,
-- Battery modes
current_battery_mode VARCHAR(50) NOT NULL, -- 'active_trading' or 'self_consumption'
future_battery_mode VARCHAR(50),
future_mode_start_year INTEGER,
-- Costs
purchase_price_battery DECIMAL(10,2) NOT NULL,
purchase_price_other DECIMAL(10,2) DEFAULT 0,
installation_cost DECIMAL(10,2),
maintenance_annual DECIMAL(10,2) DEFAULT 0,
-- Financing
financing_months INTEGER DEFAULT 0,
interest_rate DECIMAL(6,5) DEFAULT 0,
-- VAT scenario
vat_scenario VARCHAR(50) NOT NULL, -- 'private_no_reclaim', 'private_reclaim', 'business_no_kor', 'business_kor'
-- Calculation metadata
status VARCHAR(20) DEFAULT 'draft', -- 'draft', 'calculated', 'archived'
calculation_version INTEGER DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_calculations_customer ON calculations(customer_id);
CREATE INDEX idx_calculations_status ON calculations(status);CREATE TABLE calculation_results (
id SERIAL PRIMARY KEY,
calculation_id INTEGER REFERENCES calculations(id) ON DELETE CASCADE,
-- Summary results
total_investment DECIMAL(12,2) NOT NULL,
total_lifetime_savings DECIMAL(12,2) NOT NULL,
net_roi DECIMAL(12,2) NOT NULL,
payback_period_years DECIMAL(5,2),
average_annual_return DECIMAL(10,2),
roi_multiple DECIMAL(6,3),
-- Financial impact of saldering
original_annual_cost DECIMAL(10,2),
current_annual_cost DECIMAL(10,2),
post_saldering_annual_cost DECIMAL(10,2),
annual_disadvantage DECIMAL(10,2),
-- VAT details
vat_refund_amount DECIMAL(10,2) DEFAULT 0,
vat_refund_year INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_results_calculation ON calculation_results(calculation_id);CREATE TABLE calculation_yearly_details (
id SERIAL PRIMARY KEY,
calculation_id INTEGER REFERENCES calculations(id) ON DELETE CASCADE,
year_number INTEGER NOT NULL, -- 1, 2, 3, ... (relative to installation)
calendar_year INTEGER NOT NULL, -- 2025, 2026, ...
-- Battery performance
daily_cycles DECIMAL(6,3),
annual_cycles DECIMAL(8,2),
cumulative_cycles DECIMAL(12,2),
degradation_percentage DECIMAL(6,4),
cumulative_degradation DECIMAL(6,4),
effective_capacity_kwh DECIMAL(8,3),
within_warranty BOOLEAN,
-- Energy metrics
energy_stored_kwh DECIMAL(10,2),
energy_delivered_kwh DECIMAL(10,2),
-- Financial metrics
annual_savings DECIMAL(10,2),
cumulative_savings DECIMAL(12,2),
annual_costs DECIMAL(10,2),
cumulative_costs DECIMAL(12,2),
net_position DECIMAL(12,2),
-- Market parameters (for reference)
inflation_rate DECIMAL(6,5),
saldering_percentage DECIMAL(4,3),
energy_price_avg DECIMAL(8,5),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_yearly_calculation ON calculation_yearly_details(calculation_id, year_number);CREATE TABLE battery_modes (
id SERIAL PRIMARY KEY,
code VARCHAR(50) NOT NULL UNIQUE,
name_nl VARCHAR(100) NOT NULL,
name_en VARCHAR(100),
daily_cycles_default DECIMAL(4,2),
description_nl TEXT,
description_en TEXT
);
-- Seed data
INSERT INTO battery_modes (code, name_nl, name_en, daily_cycles_default) VALUES
('active_trading', 'Actief handelen', 'Active Trading', 1.50),
('self_consumption', 'Alleen zelfconsumptie', 'Self-consumption Only', 1.00),
('peak_shaving', 'Pieken wegwerken', 'Peak Shaving', 0.80);CREATE TABLE energy_suppliers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
contract_type VARCHAR(50), -- 'dynamic', 'fixed', 'hybrid'
active BOOLEAN DEFAULT TRUE
);customers (1) ----< (M) calculations
|
| (M:1)
v
products
calculations (1) ----< (1) calculation_results
|
|
+----< (M) calculation_yearly_details
master_data_yearly (lookup table)
GET /api/products
GET /api/products/:id
POST /api/products [Admin]
PUT /api/products/:id [Admin]
DELETE /api/products/:id [Admin]
GET /api/products/filter?phase=3_phase&minCapacity=5
Example Response:
{
"id": 1,
"brand": "AlphaESS",
"model": "Smile3,6 binnen",
"phaseType": "1_phase",
"totalCapacityKwh": 3.6,
"roundtripEfficiency": 0.96,
"guaranteedCycles": 10000,
"residualCapacity": 0.70,
"warrantyYears": 10,
"priceInclVat": 7499,
"hasEmergencyPower": true
}GET /api/master-data/years
GET /api/master-data/years/:year
POST /api/master-data/years [Admin]
PUT /api/master-data/years/:year [Admin]
Example Response:
{
"year": 2025,
"vatRate": 0.21,
"maxVatRefund": 2494,
"inflationRate": 0.032,
"energyTaxTier1InclVat": 0.12286,
"salderingPercentage": 1.0,
"fixedCostElectricity": 5.99,
"feedInTariff": -0.02
}GET /api/customers
GET /api/customers/:id
POST /api/customers
PUT /api/customers/:id
DELETE /api/customers/:id
GET /api/calculations
GET /api/calculations/:id
POST /api/calculations
PUT /api/calculations/:id
DELETE /api/calculations/:id
POST /api/calculations/:id/calculate
GET /api/calculations/:id/results
GET /api/calculations/:id/yearly-details
POST /api/calculations/:id/duplicate
POST /api/calculations - Request Body:
{
"customerId": 123,
"gridConsumptionKwh": 3500,
"gridFeedInKwh": 2500,
"solarProductionKwh": 3500,
"productId": 5,
"installationDate": "2025-12-01",
"energySupplier": "Frank Energie",
"currentBatteryMode": "active_trading",
"futureBatteryMode": "active_trading",
"futureModeStartYear": 2030,
"purchasePriceBattery": 7499,
"purchasePriceOther": 0,
"installationCost": 1000,
"maintenanceAnnual": 150,
"financingMonths": 0,
"interestRate": 0,
"vatScenario": "private_reclaim"
}POST /api/calculations/:id/calculate - Response:
{
"calculationId": 456,
"status": "calculated",
"results": {
"totalInvestment": 8499,
"totalLifetimeSavings": 15234.50,
"netROI": 6735.50,
"paybackPeriodYears": 8.3,
"averageAnnualReturn": 761.73,
"roiMultiple": 1.79,
"originalAnnualCost": 945.00,
"currentAnnualCost": 945.00,
"postSalderingAnnualCost": 1256.30,
"annualDisadvantage": 311.30,
"vatRefundAmount": 1245.00,
"vatRefundYear": 2
},
"yearlyDetails": [
{
"yearNumber": 1,
"calendarYear": 2025,
"effectiveCapacityKwh": 3.6,
"degradationPercentage": 0.0015,
"annualSavings": 542.30,
"cumulativeSavings": 542.30,
"annualCosts": 8499.00,
"cumulativeCosts": 8499.00,
"netPosition": -7956.70
},
// ... more years
]
}GET /api/calculations/:id/yearly-details?year=5
{
"yearNumber": 5,
"calendarYear": 2029,
"dailyCycles": 1.5,
"annualCycles": 547.5,
"cumulativeCycles": 2737.5,
"degradationPercentage": 0.0075,
"cumulativeDegradation": 0.0375,
"effectiveCapacityKwh": 3.465,
"withinWarranty": true,
"energyStoredKwh": 1897.38,
"annualSavings": 678.45,
"cumulativeSavings": 3289.12,
"annualCosts": 154.32,
"cumulativeCosts": 9271.60,
"netPosition": -5982.48
}This is the core endpoint that replicates all Excel calculation logic.
Process:
- Load calculation inputs from database
- Load product specifications
- Load master data for all relevant years
- Calculate degradation curve
- Iterate through each year (1-20):
- Calculate battery performance (degradation, cycles, capacity)
- Calculate energy savings based on mode
- Calculate costs (maintenance, financing)
- Calculate VAT refund (if applicable, in year 2)
- Accumulate totals
- Identify payback year
- Calculate summary metrics
- Store results in database
- Return complete results
GET /api/battery-modes
GET /api/energy-suppliers
GET /api/calculations/:id/export/pdf
GET /api/calculations/:id/export/excel
GET /api/reports/summary?startDate=2025-01-01&endDate=2025-12-31
from decimal import Decimal
from datetime import date, datetime
from typing import Dict, List, Optional
from dataclasses import dataclass
@dataclass
class CalculationInput:
"""Input parameters for ROI calculation"""
# Energy usage
grid_consumption_kwh: Decimal
grid_feed_in_kwh: Decimal
solar_production_kwh: Decimal
# Product specs
battery_capacity_kwh: Decimal
roundtrip_efficiency: Decimal
guaranteed_cycles: int
residual_capacity: Decimal
warranty_years: int
# Installation
installation_date: date
current_battery_mode: str
future_battery_mode: Optional[str]
future_mode_start_year: Optional[int]
# Costs
purchase_price: Decimal
installation_cost: Decimal
maintenance_annual: Decimal
connection_upgrade: Decimal
# Financing
financing_months: int
interest_rate: Decimal
# VAT
vat_scenario: str
@dataclass
class YearlyResult:
"""Results for a single year"""
year_number: int
calendar_year: int
# Battery performance
daily_cycles: Decimal
annual_cycles: Decimal
cumulative_cycles: Decimal
degradation_percentage: Decimal
cumulative_degradation: Decimal
effective_capacity_kwh: Decimal
within_warranty: bool
# Energy
energy_stored_kwh: Decimal
energy_delivered_kwh: Decimal
# Financial
annual_savings: Decimal
cumulative_savings: Decimal
annual_costs: Decimal
cumulative_costs: Decimal
net_position: Decimal
# Market parameters
inflation_rate: Decimal
saldering_percentage: Decimal
energy_price_avg: Decimal
@dataclass
class CalculationResult:
"""Complete calculation results"""
total_investment: Decimal
total_lifetime_savings: Decimal
net_roi: Decimal
payback_period_years: Optional[Decimal]
average_annual_return: Decimal
roi_multiple: Decimal
# Saldering impact
original_annual_cost: Decimal
current_annual_cost: Decimal
post_saldering_annual_cost: Decimal
annual_disadvantage: Decimal
# VAT
vat_refund_amount: Decimal
vat_refund_year: Optional[int]
# Yearly breakdown
yearly_details: List[YearlyResult]
class BatteryROICalculator:
"""Main calculation engine for battery ROI"""
def __init__(self, master_data_service, product_service):
self.master_data = master_data_service
self.products = product_service
def calculate(self, input_data: CalculationInput) -> CalculationResult:
"""
Main calculation method - orchestrates all sub-calculations
"""
# Step 1: Load master data for all years
start_year = input_data.installation_date.year
master_data = self._load_master_data(start_year, 20)
# Step 2: Calculate degradation curve
degradation_curve = self._calculate_degradation_curve(
input_data.guaranteed_cycles,
input_data.residual_capacity,
20 # lifespan years
)
# Step 3: Calculate financial impact of saldering
saldering_impact = self._calculate_saldering_impact(
input_data, master_data[start_year]
)
# Step 4: Calculate VAT refund
vat_refund, vat_year = self._calculate_vat_refund(
input_data, master_data[start_year]
)
# Step 5: Yearly calculations
yearly_results = []
cumulative_savings = Decimal(0)
cumulative_costs = Decimal(0)
payback_year = None
for year_num in range(1, 21):
calendar_year = start_year + year_num - 1
year_data = master_data.get(calendar_year)
if not year_data:
break
# Calculate for this year
year_result = self._calculate_year(
year_num=year_num,
calendar_year=calendar_year,
input_data=input_data,
master_data=year_data,
degradation=degradation_curve[year_num - 1],
cumulative_savings=cumulative_savings,
cumulative_costs=cumulative_costs,
vat_refund=vat_refund if year_num == vat_year else Decimal(0)
)
cumulative_savings = year_result.cumulative_savings
cumulative_costs = year_result.cumulative_costs
# Check for payback
if payback_year is None and year_result.net_position >= 0:
payback_year = year_num + (
abs(yearly_results[-1].net_position) /
year_result.annual_savings
) if yearly_results else year_num
yearly_results.append(year_result)
# Step 6: Calculate summary metrics
total_investment = cumulative_costs - cumulative_savings + cumulative_costs
return CalculationResult(
total_investment=yearly_results[0].cumulative_costs,
total_lifetime_savings=cumulative_savings,
net_roi=cumulative_savings - yearly_results[0].cumulative_costs,
payback_period_years=payback_year,
average_annual_return=cumulative_savings / len(yearly_results),
roi_multiple=cumulative_savings / yearly_results[0].cumulative_costs,
original_annual_cost=saldering_impact['original'],
current_annual_cost=saldering_impact['current'],
post_saldering_annual_cost=saldering_impact['post_saldering'],
annual_disadvantage=saldering_impact['disadvantage'],
vat_refund_amount=vat_refund,
vat_refund_year=vat_year,
yearly_details=yearly_results
)
def _calculate_year(
self,
year_num: int,
calendar_year: int,
input_data: CalculationInput,
master_data: dict,
degradation: Decimal,
cumulative_savings: Decimal,
cumulative_costs: Decimal,
vat_refund: Decimal
) -> YearlyResult:
"""Calculate results for a single year"""
# Determine battery mode for this year
if (input_data.future_mode_start_year and
calendar_year >= input_data.future_mode_start_year):
mode = input_data.future_battery_mode
else:
mode = input_data.current_battery_mode
# Daily cycles based on mode
daily_cycles = self._get_daily_cycles(mode)
# Year fraction (first year might be partial)
year_fraction = self._calculate_year_fraction(
input_data.installation_date, calendar_year, year_num
)
# Annual cycles
annual_cycles = daily_cycles * Decimal(365) * year_fraction
cumulative_cycles = (
cumulative_cycles + annual_cycles if year_num > 1
else annual_cycles
)
# Effective capacity after degradation
effective_capacity = (
input_data.battery_capacity_kwh *
(Decimal(1) - degradation)
)
# Energy stored and delivered
energy_stored = effective_capacity * annual_cycles
energy_delivered = (
energy_stored * input_data.roundtrip_efficiency
)
# Calculate savings based on mode
annual_savings = self._calculate_annual_savings(
mode=mode,
energy_delivered=energy_delivered,
master_data=master_data,
input_data=input_data
)
# Calculate costs
annual_costs = self._calculate_annual_costs(
year_num=year_num,
input_data=input_data,
master_data=master_data,
vat_refund=vat_refund
)
# Accumulate
cumulative_savings += annual_savings
cumulative_costs += annual_costs
return YearlyResult(
year_number=year_num,
calendar_year=calendar_year,
daily_cycles=daily_cycles,
annual_cycles=annual_cycles,
cumulative_cycles=cumulative_cycles,
degradation_percentage=degradation,
cumulative_degradation=degradation, # Simplified
effective_capacity_kwh=effective_capacity,
within_warranty=year_num <= input_data.warranty_years,
energy_stored_kwh=energy_stored,
energy_delivered_kwh=energy_delivered,
annual_savings=annual_savings,
cumulative_savings=cumulative_savings,
annual_costs=annual_costs,
cumulative_costs=cumulative_costs,
net_position=cumulative_savings - cumulative_costs,
inflation_rate=master_data['inflation_rate'],
saldering_percentage=master_data['saldering_percentage'],
energy_price_avg=master_data['energy_price_avg']
)
def calculate_degradation_curve(
guaranteed_cycles: int,
residual_capacity: Decimal,
lifespan_years: int
) -> List[Decimal]:
"""
Calculate battery degradation using annuity method
This replicates the Excel CUMIPMT-based degradation calculation.
The degradation follows an annuity curve where more degradation
happens in early years.
"""
import numpy_financial as npf
# Total degradation over lifetime
total_degradation = Decimal(1) - residual_capacity
# Calculate fictive interest rate for annuity
# This makes degradation front-loaded (more in early years)
rate = npf.rate(
nper=lifespan_years,
pmt=-float(total_degradation) / 10,
pv=float(total_degradation),
fv=0
)
# Calculate degradation for each year
degradation_curve = []
cumulative = Decimal(0)
for year in range(1, lifespan_years + 1):
# Year's degradation using cumulative interest
year_degradation = Decimal(
abs(npf.cumipmt(rate, lifespan_years,
float(total_degradation), year, year, 0))
)
cumulative += year_degradation
degradation_curve.append(cumulative)
return degradation_curve
def calculate_annual_savings(
mode: str,
energy_delivered_kwh: Decimal,
grid_consumption_kwh: Decimal,
grid_feed_in_kwh: Decimal,
master_data: dict
) -> Decimal:
"""
Calculate annual energy cost savings from battery
Args:
mode: 'active_trading' or 'self_consumption'
energy_delivered_kwh: Energy delivered by battery
grid_consumption_kwh: Customer's grid consumption
grid_feed_in_kwh: Customer's grid feed-in
master_data: Yearly market parameters
Returns:
Annual savings in EUR
"""
if mode == 'active_trading':
# Active trading: charge at night (low price), discharge at day (high price)
# Simplified: use price delta between high and low tariff
price_delta = (
master_data['delivery_cost_high'] -
master_data['delivery_cost_low']
)
# Effective trading minutes vs. total minutes in quarter hour
effective_factor = (
master_data['effective_minutes_per_quarter'] / Decimal(15)
)
# Savings = energy × price_delta × efficiency × effective_factor
savings = (
energy_delivered_kwh *
price_delta *
effective_factor *
(Decimal(1) - master_data['trading_correction_tariff'])
)
else: # self_consumption
# Self-consumption: avoid buying from grid
# Savings = energy × (purchase_price - feed_in_price)
purchase_price = (
master_data['delivery_cost_avg'] +
master_data['energy_tax']
)
feed_in_compensation = (
master_data['feed_in_tariff'] *
master_data['saldering_percentage']
)
price_delta = purchase_price - feed_in_compensation
# Limit to actual consumption (can't save more than you consume)
max_savings_kwh = min(
energy_delivered_kwh,
grid_consumption_kwh
)
savings = max_savings_kwh * price_delta
return savings
def calculate_vat_refund(
vat_scenario: str,
battery_capacity_kwh: Decimal,
invoice_amount: Decimal,
calendar_year: int,
master_data: dict
) -> tuple[Decimal, int]:
"""
Calculate VAT refund amount and year
Args:
vat_scenario: 'private_no_reclaim', 'private_reclaim',
'business_no_kor', 'business_kor'
battery_capacity_kwh: Battery capacity
invoice_amount: Total invoice including VAT
calendar_year: Installation year
master_data: Master data for the year
Returns:
Tuple of (refund_amount, refund_year)
"""
if vat_scenario == 'private_no_reclaim':
return Decimal(0), None
vat_rate = master_data['vat_rate']
vat_fraction = vat_rate / (Decimal(1) + vat_rate)
invoice_vat = invoice_amount * vat_fraction
if vat_scenario == 'business_no_kor':
# Full VAT reclaim (minus future feed-in VAT)
# Simplified: return invoice VAT (feed-in VAT calculated separately)
return invoice_vat, 2 # Year 2 (Dutch tax timing)
# Private with reclaim or business with KOR
max_refund = master_data['max_vat_refund']
vat_per_kwh = master_data['vat_per_kwh']
# Calculate refund
refund = min(
invoice_vat,
max_refund - (battery_capacity_kwh * vat_per_kwh)
)
# Ensure non-negative
refund = max(Decimal(0), refund)
return refund, 2
def calculate_saldering_impact(
grid_consumption_kwh: Decimal,
grid_feed_in_kwh: Decimal,
delivery_tariff: Decimal,
energy_tax: Decimal,
feed_in_tariff: Decimal,
fixed_costs: Decimal
) -> dict:
"""
Calculate financial impact of saldering (net metering) phase-out
Returns dict with:
- original: Annual cost with 100% saldering
- current: Annual cost with current saldering %
- post_saldering: Annual cost with 0% saldering
- disadvantage: Difference between post-saldering and original
"""
# Original scenario (100% saldering / net metering)
# Consumption and feed-in offset each other at full rate
net_consumption = grid_consumption_kwh - grid_feed_in_kwh
if net_consumption > 0:
# Net consumer
original_cost = (
net_consumption * (delivery_tariff + energy_tax) +
fixed_costs
)
else:
# Net producer (rare for battery customers)
original_cost = (
abs(net_consumption) * feed_in_tariff +
fixed_costs
)
# Post-saldering scenario (0% saldering)
# Pay for all consumption, get minimal compensation for feed-in
consumption_cost = (
grid_consumption_kwh * (delivery_tariff + energy_tax)
)
feed_in_compensation = grid_feed_in_kwh * feed_in_tariff
post_saldering_cost = (
consumption_cost -
feed_in_compensation +
fixed_costs
)
# Disadvantage
disadvantage = post_saldering_cost - original_cost
return {
'original': original_cost,
'current': original_cost, # Could interpolate based on current %
'post_saldering': post_saldering_cost,
'disadvantage': disadvantage
}
def calculate_financing_costs(
principal: Decimal,
annual_interest_rate: Decimal,
months: int
) -> List[Decimal]:
"""
Calculate monthly financing costs
Returns list of monthly costs (principal + interest)
"""
import numpy_financial as npf
if months == 0 or principal == 0:
return [Decimal(0)]
monthly_rate = annual_interest_rate / Decimal(12)
# Monthly payment
monthly_payment = Decimal(
abs(npf.pmt(
float(monthly_rate),
months,
float(principal)
))
)
# Return monthly payment for each month
return [monthly_payment] * monthsclass MasterDataService:
"""Service for master data operations"""
def get_year_data(self, year: int) -> dict:
"""Get master data for specific year"""
pass
def get_year_range(self, start_year: int, years: int) -> dict:
"""Get master data for range of years"""
pass
def update_year_data(self, year: int, data: dict) -> bool:
"""Update master data for year"""
pass
class ProductService:
"""Service for product operations"""
def get_product(self, product_id: int) -> dict:
"""Get product by ID"""
pass
def search_products(
self,
phase: Optional[str] = None,
min_capacity: Optional[Decimal] = None,
max_capacity: Optional[Decimal] = None
) -> List[dict]:
"""Search products with filters"""
pass
class CalculationService:
"""Service for calculation operations"""
def __init__(self, calculator: BatteryROICalculator):
self.calculator = calculator
def create_calculation(self, data: dict) -> int:
"""Create new calculation"""
pass
def execute_calculation(self, calculation_id: int) -> CalculationResult:
"""Execute calculation and store results"""
calc_data = self._load_calculation(calculation_id)
input_data = self._map_to_input(calc_data)
# Execute calculation
result = self.calculator.calculate(input_data)
# Store results
self._store_results(calculation_id, result)
return result
def get_results(self, calculation_id: int) -> CalculationResult:
"""Get stored calculation results"""
passdef calculate_year_fraction(
installation_date: date,
calendar_year: int,
year_number: int
) -> Decimal:
"""
Calculate fraction of year for first/last year
First year might be partial if installation is mid-year
"""
if year_number > 1:
return Decimal(1)
year_start = date(calendar_year, 1, 1)
year_end = date(calendar_year, 12, 31)
if installation_date.year != calendar_year:
return Decimal(0)
days_in_year = (year_end - year_start).days + 1
days_remaining = (year_end - installation_date).days + 1
return Decimal(days_remaining) / Decimal(days_in_year)
def get_daily_cycles(mode: str) -> Decimal:
"""
Get daily battery cycles based on operating mode
These are defaults; could be configured per customer
"""
cycles_map = {
'active_trading': Decimal('1.5'),
'self_consumption': Decimal('1.0'),
'peak_shaving': Decimal('0.8')
}
return cycles_map.get(mode, Decimal('1.0'))
def apply_inflation(
base_amount: Decimal,
inflation_rate: Decimal,
years: int
) -> Decimal:
"""Apply compound inflation"""
return base_amount * ((Decimal(1) + inflation_rate) ** years)-
Saldering Phase-out
- Critical to Dutch market
- Current: 100% in 2025, 100% in 2026, 0% from 2027
- Major driver of battery ROI (batteries become more valuable when saldering ends)
-
Battery Degradation
- Uses annuity-based curve (front-loaded degradation)
- More realistic than linear degradation
- Based on cumulative cycles vs. guaranteed cycles
-
VAT Scenarios
- Complex Dutch tax rules
- Different for private vs. business
- Maximum refunds, per-kWh deductions
- Feed-in VAT implications
-
Operating Modes
- Active trading: Arbitrage between time-of-use tariffs
- Self-consumption: Maximize direct use, minimize grid interaction
- Different cycle counts and savings calculations
Essential validations:
- Grid consumption ≥ 0
- Grid feed-in ≥ 0
- Solar production ≥ grid feed-in
- Installation date in future or recent past
- Product exists and is active
- Master data exists for installation year + 20 years
- Battery mode is valid
- VAT scenario is valid
- Interest rate ≥ 0 if financing used
- Pre-calculate degradation curves (can be cached per product)
- Batch-load master data for all years
- Consider caching calculation results
- Async processing for long calculations
- Pagination for yearly details API responses
-
Dynamic Energy Prices
- Currently uses average/fixed prices
- Could integrate real-time day-ahead pricing
- Hour-by-hour optimization
-
Multiple Batteries
- Support for system expansion
- Stacking multiple units
-
Solar Panel Integration
- Currently assumes panels exist
- Could model panel + battery combinations
- Panel degradation over time
-
Tax Scenario Modeling
- What-if analysis for different tax rules
- Sensitivity analysis
-
Comparison Mode
- Compare multiple battery options side-by-side
- Compare with/without battery scenarios
- Export Stamgegevens to CSV → Import to
master_data_yearly - Export Producten to CSV → Import to
products - Validate data integrity
- Implement degradation calculation
- Implement yearly iteration logic
- Implement savings calculations
- Unit test against Excel results
- Implement REST endpoints
- Integrate calculation engine
- Add result storage
- API testing
- Input form matching Excel Calculator sheet
- Results display
- Yearly breakdown charts
- PDF export
- Compare API results vs. Excel for 10+ scenarios
- Edge case testing
- Performance testing
- User acceptance testing
The Domogo Rendementscalculator is a sophisticated financial modeling tool with complex business logic specific to the Dutch energy market. The key challenges in migration are:
- Accuracy: Ensuring calculations match Excel exactly, especially degradation curves and VAT logic
- Master Data Management: Keeping yearly parameters up-to-date as regulations change
- Performance: Multi-year iterations can be computationally intensive
- Flexibility: Supporting various scenarios (VAT, modes, financing) without code changes
The proposed database schema normalizes the data while preserving all calculation inputs and results. The API structure provides clear separation of concerns and allows for future enhancements. The Python functions replicate the Excel formulas with proper typing and error handling.
Critical Success Factors:
- Exact calculation parity with Excel (validation dataset needed)
- Clear documentation of business rules
- Comprehensive test coverage
- Maintainable code structure for long-term regulatory changes