You're onto something here, Yahtzee. As well as making the challenge of a game more interesting, this could contribute to an RPG having more of a feeling of being an epic. One thing I liked about the first Fable game was that it took place over the course of sixty-plus years and kept track. Whenever I reached the endgame, I always felt satisfied at seeing my hero be a hardened and scarred old man who'd seen many years of combat. Unfortunately, it was always a little weird to me that as a geriatric, he was much stronger and hardier than the scrawny twig he was as a fresh Guild graduate.
I think RPGs would benefit from a combination of the status quo and your proposal. This could be accomplished by automatically having certain stats atrophy with age while increasing others, though investing in certain perks would increase or decrease the rate. With the atrophy in strength, endurance, and perception, the character would lose access to abilities with heavy melee weapons or long-range combat. With increases in intelligence and wisdom, the character would have access to greater ability with magic and use of light, precise melee weapons (katanas, rapiers, etc). Of course, they wouldn't be allowed to switch halfway, that would just be silly.
Now, some would ask why any player would bother playing anything besides a mage in that case. The mage's endurance would still be decreasing, so the most powerful mage could be killed with one strike. Also, charisma could be a skill that could increase with age for warriors, given their renown in battle. This could allow them to recruit NPCs to assist them in combat. Obviously, mages could also recruit NPCs, but not nearly as many as warriors and not as strong either (they'd basically be recruiting magic apprentices who are there to carry the inventory).
Now that's a game I'd like to see!