I don’t think most people living in these “opportunity zones” are going to be happy with people basically being tax incentivized to make housing more expensive in the area in which they live, thus making life harder for them.
If you improve a mansion to be a fancier mansion, you don't increase the supply of housing.
If you improve a small lot to be a duplex or an apartment complex, you increase the supply of housing.
The latter wouldn't raise the net price of housing, but would still be a worthwhile investment. It depends on what local policy is, namely if building new housing is illegal or not.
Making one house fancier definitely increases rents on neighboring properties. If I live in a rundown house in a nice neighborhood and fix it up, everyone else's house increases in value. Same goes for making 1 house really nice in a bad area.
It is complex and there are many "it depends". If they own a house and want to move [for some personal reason] they will be happy that they can get more for their house. If they have lived there for years and watched the neighborhood go downhill they may be happy that things are improving again. Of course the other side is if they are happy with cheap rent they will be unhappy if rent increases because there is now more demand.
