1. The best mods will still be free. Why? The best mods are created by highly dedicated individuals or teams who have been modding for years simply because they love the game. Look at JFD, whoward, etc etc. The best mods are also built on a modded foundation (like whoward's modified DLL), which paid mods would not be able to do considering the licenses and permissions involved, which brings me to my second point.
2. Paid mods will be more difficult to make. The modding community builds on itself. Someone will release an excellent mod which quickly become a standard, integrated feature across the board. That can't happen with paid mods, because the original creator would have to agree to have his work used in the paid mod, and I can GUARANTEE you that many of them will not. Paid mods will not be approved if all parties do not accept the payment terms. This means that paid mods will be, for the most part, individual efforts - not group efforts.
3. The most vibrant modding community is not on Steam. Most diehards don't count on Steam for their modding home. Sure, they might download it here, but the community exists somewhere else (Civfanatics, to be exact). This is a community built around the shared love of the game who create for themselves and for each other. This is an established community which paid mods will be largely irrelevant to.
In summary, paid mods will have little impact on the Civ V modding community.