I kinda like this age system of Firaxis for its audacity - just complete rejection of historical and commonly recognized age divisions, let's go for something that is optimal for the pacing, logic and gameplay of our ingame universe.
I really like it as well. They explicitly compared it with the narrative ordering of a book - or a series of books, and that's actually a framework that you can apply very well to pretty much
all creative productions that contain a form of progression (books, movies, music, games, etc). It is inherently engaging and thus fun to participate with.
The Middle Ages might not be a breakpoint historically, but they
were a breakpoint narratively. Great empires of old fractured and gave way to smaller states looking to make their own way. Of course, there's a bit of European focus in that specific view, but it's not as if other parts of the world haven't seen similar events, even if it might have been a few centuries earlier or later. And the ones that perhaps didn't see it at all... they still got their breakpoint when foreigners with more advanced technology arrived - in the New World, the breakpoint between Antiquity and Exploration is 1492, even though in Europe it could be as early as the fifth or sixth century depending on what you count.
One big question is when exactly does "modern era" begin: with French revolution and Napoleonic Wars, Congress of Vienna, or 1848? It is important for example regarding which era does Napoleon go to.
I would argue the modern era should start with a combination of the various forms of upheaval we saw in the time around Napoleon: Invention of the steam engine, innovations in warfare, cultural views from enlightenment, and so on.
I don't really see any precise date to put on it, and to be honest I think there shouldn't be one in the first place. In the United States it's as early as 1776 with the constitution, in France it's 1789 with the French Revolution, in some countries it's 1848 with their revolutions, and in yet other places it could be even later.
As for Napoleon, I'd say he's one of the drivers of the change, and should thus be a Modern leader, same with e.g. Ben Franklin. Although there's an alternative way to view it: If this initial upheaval is considered to still be part of the Age of Exploration, then the people who played a major role in it should be part of that Age too. Perhaps they could even have a leader bonus that (either through a specific mechanic or due to the natural interaction with game elements) gives them a benefit specifically in the crisis of the Age of Exploration.