I'm fond of some of the trappings of the monarchy. I like the pomp and tradition, there is a certain nostalgia to it. However, it is totally anachronistic, anti-democratic and frankly embarrassing for us as a nation, especially given recent revelations about racism, Prince Andrew etc. I used to buy the "soft diplomacy" angle, but I'm now of the view that they do at least as much harm as they do good. As for the tourist income, just think how much more it would make if you opened up all of the palaces properly for visitors etc.
Perhaps they could have modernised more effectively and more quickly in order to carve out a more relevant role for themselves in the modern era, but the boat has sailed on that one now. I think they're done, but I don't think they'll go voluntarily. It will be interesting to see which political party will be brave enough to suggest a referendum first. I guess they won't risk it until they're pretty sure that the public are supportive. Constitutionally, I don't even know if a referendum would be enough to remove them, though I can't imagine them actually trying to hang on if it came to it.
I do feel a bit anxious about what we'd put in its place. An apolitical ceremonial president would be good, I think. I definitely wouldn't be in favour of a political head of state.
It feels to me like the tide is really turning now. The rot began to set in years ago, when Charles and Diana were both unable to keep their private lives private. I think the whole thing with Andrew did a huge amount of damage, especially as the family did not appear to investigate in any way. The latest revelations about racism may prove to be the final nail in the coffin.