My answer is different: The equation COPtotal = COP1*COP2/(COP1+COP2-1) is not relevant to heat pumps in series, but relevant only to heat pumps in CASCADE. In heat pumps in CASCADE, the first heat pump extract heat from the Winter air... the secondary heat pump (water to water) only concentrates the heat... it does not ADD heat from the winter air... insteady it extract heat from the water already pre-heated and reinjects that heat to obtain higher temperature but the same about of energy plus the waste heat consumed by the compressors. In other words, heat pumps in Cascade CONCENTRATE heat, (steals heat to return it).

WIth heat pumps in series, 5 heat pumps in series all take air from the winter sky, add it to a stream of water that gets hotter by 5 degrees C at every stage... if there are 5 stages, that 25 degrees C of heat is added in 5 degree steps. This heat is partly from the winter's air, and partly the heat given up by the compressor (useful waste heat). The first heat pump doesn't work as hard.... good COP.... but the last heat pump works really hard. You can assume the COP is only 45% of the "Carnot" efficiency. You then perform an iterative 5, steps (5 lines in Excel) calculation.... adding up to a heat pump consumption....and a resulting COP

My answer is different: The equation COPtotal = COP1*COP2/(COP1+COP2-1) is not relevant to heat pumps in series, but relevant only to heat pumps in CASCADE. In heat pumps in CASCADE, the first heat pump extract heat from the Winter air... the secondary heat pump (water to water) only concentrates the heat... it does not ADD heat from the winter air... instead it extracts heat from the water already pre-heated and reinjects that heat to obtain higher temperature but the same about of energy (plus the waste heat consumed by the compressor of the water to water heat pump). In other words, heat pumps in Cascade CONCENTRATE heat, (steals heat to returns it).

With heat pumps in series, 6 heat pumps in series all take air from the winter sky, add it to a stream of water that gets hotter by 5 degrees C at every stage... if there are 6 stages, that 30 degrees C of heat is added in 5 degree steps. This heat is partly from the winter's air, and partly the heat given up by the compressor (useful waste heat). The first heat pump doesn't work as hard.... good COP because it lifts from 7°C air to 35°C water.... but the last heat pump works really hard (from 7°C to 60°C water).

You can assume the COP is only 45% of the "Carnot" efficiency. You then perform an iterative calculation in 6 steps (6 lines in Excel) calculation.... adding up to a heat pump consumption....and a resulting COP