It also ignores the possibility that energy costs will go up over those 10 years. Essentially you are fixing your energy costs indefinitely.
Of course I'm not-quite normal kind of guy regardless. I paid too much for a very efficient house. I own my house. I own my cars. To me, the idea of turning unknown future costs into known present costs is fantastic. Not a lot of people agree with that concept.
Also worth it to add in this context that the 5 or 6 solar-dependent/off-grid homes I've seen built and lived in have been anything but reliable. It seems to take a while to fine tune the system to the point of not having a blackout every 3 months.
When you are talking about a purchase which you live with day to day for 10+ years, "Return on Investment" is never going to be a straight financial calculation.
People don't buy a house based on straight ROI, nor are most home improvements based purely on ROI. I don't see why solar should be the exception.
In real terms, the S&P 500 price return was about 3.2% p.a. since the peak in 1968, 5.2% since 1988, and less than 3% since 2000.
Real total (ie with dividends) return p.a. over a decade has been between -4% and 17%, with an average of around 7%.
As things stand currently, I'd not plan on more than 6% total return in real terms over the next two decades.
Obviously you sell the entire house and all improvements are reflected in increased value of the house.
So no, the value of solar is not zero at the end of 10 years.
Don't bet or estimate, we have mountains of data on this: https://www.nrel.gov/docs/fy12osti/51664.pdf
There is a massive assumption in there that you can have a non-risky investment with a 10% rate of return idefinately.
Given that we can barely go 10 years from crisis to crisis (2008 and covid) this assumption cannot possibly be justified.
Solar panels generate cashflow in the form of electricity but they depreciate over time. So the payback period (in the form of cashflow) has to be much shorter than for an apartment or home.
Moreover, people should not buy a house with the expectation that it will inevitably be worth more in 20 years than it is today. Appreciation of land values is far from a universal law of nature.
We have birthrates of 1.6 in most of Europe, and the global population is about to peak in every contry except in African countries*
If it didn't cost over 30K to pull power to the house, it'd be impossible to make the math work for solar. If you don't count the climate externalities I doubt there's one city on Earth where the payback period (before incentives) is less than 10 years.
So there's no point in making a spreadsheet at this point, really. You're either buying solar out of need, a desire to "green" your life further, or you're being paid by the government to do it, or buying at a loss.