Nearly always, these started as "prototype" systems that became so useful, so quickly that they end up as "production" systems.
This is the power of Excel. But...
When spreadsheets get this large and complicated they are incredibly fragile and become hugely difficult to maintain and bug fix. It is just too easy to make an accidental change and not know your spreadsheet is broken. The VaR example given in the OP is just typical. Sure, you can protect sheets but as a developer/maintainer you need access and then you can very easily break things, especially because version control with spreadsheets can be next to impossible.
The moment you start taking your spreadsheets seriously, as a business process, is the moment you need to consider very seriously recoding them as a bespoke system using conventional software development.