The cost of the monarchy isn't actually as much as you might think. Some years ago in a discussion I worked out that a year's budget for the Civil List was about 12 minutes worth of the national welfare budget. I think the monarchy is much better value, put in those terms. Admittedly there are other factors that can be considered - some news outlets have been reporting about the very substantial estates passed from generation to generation without the inheritance tax that would be levied against anyone else (the Duchy of Cornwall being one such example) but since ultimately it belongs to the Crown anyway it seems like it would just be an opportunity for more government waste to take something that belongs to the Crown, force its sale so it could be taxed, for the government to then waste the money. It would seem like a good way to get some money now at the price of costing more money later.
The idea of a hereditary monarchy is certainly at odds with the more modern approach of elections, especially when it confers such a level of privilege. My impression is certainly that the Queen understood very well her immense privilege and dedicated her life to serving the people of the UK and Commonwealth. Some of her children appeared to take the privilege for granted - Prince Andrew was dubbed "Randy Andy" and "Air Miles Andy" by the media, although there's obviously merit in the observation that the media tells us what they want us to see. I believe it was the now-King Charles who started the push for a slimmed down monarchy, putting more emphasis on the members who are actually going to be working royals and trimming the spending on those further from the core of it.
The passing of the Queen is certainly a strange time. In the US the head of state is potentially changed every four years, with eight years being the maximum. In the UK elections are held no less frequently than every five years and even though there aren't term limits it's very rare for a Prime Minister to serve for extended periods (Margaret Thatcher served as PM for ten years, which was an unusually long time). The Queen reigned for 70 years. A majority of people in the UK weren't even alive when she took the throne. Assuming King Charles enjoys comparable health to the late Queen Elizabeth he can be expected to reign for 20-25 years, by which time William will be in his 60s and could be expected to reign for maybe 30 years.
If my understanding is correct the monarch actually has a lot more power than people appreciate, even if much of it is theoretical. I believe when a law is passed by Parliament it still requires royal assent so, in theory at least, the monarch could refuse to sign it. After an election the monarch invites the leader of the winning party to form a government and, in theory, I believe the monarch could ignore the election results and invite anyone of their choosing to form a government. In practice if the monarch ever did such a thing it would trigger all sorts of problems and almost certainly accelerate the demise of the monarchy.
The monarchy certainly drives a good chunk of tourism in and around London and around other royal residences. I think there's something about an actual working monarchy that is more appealing than the kind of setup in France where the buildings remain but the monarchy is long since gone. I think there's also something about having a nominal head of state who is outside of politics and who isn't driven by short-term electoral advantage, even if they have very limited power that they can practically wield.
My hope is that King Charles can carry the late Queen Elizabeth's mantle and continue the monarchy.