I actually tried once to calculate the cost of establishing a colony and upgrading it completely.
The number of colonists one has to send depends on the initial chance of success, which goes up as one sends more colonists, according to some bizarre formula I worked out, which ended up being so complicated I could not solve it algebraically and had to just work it out by trial and error. Anyway, it seems to me like you should need to send about twelve colonists in order to get to 1000 population, assuming you have an initial success chance of 60% and no natives, which seems about average to me. (Provinces with natives would likely have a lower success chance).
So, assuming we send twelve colonists, and each colonist costs about Ð60 (eth seems like a good letter to use for ducats), then you would spend about Ð720 building up the colony. Add in a tax collector (Ð50), a fortress (Ð129 if you stay as Offensive as you can without getting the siege penalty, which is what I normally do), a chief judge (Ð100), and a governor (Ð100), and you end up with a total cost of Ð1099 for the colony.
This would imply that a colony costs about as much as twenty-one warships at full naval and plutocracy, or as much a manufactory if you haven't built many manufactories, which is quite expensive.
So I played a game as England and later Britain (modified the files) in which I did not found colonies, except for Bermuda, the Falklands, Fiji, and Ceylon as strategic bases, and concentrated instead on improving the British Isles. I found that I made an acceptable amount of money, but that there just wasn't anything to do. Eventually I broke down and starting colonizing stuff, just out of boredom.
So my vote is that colonies are not worth it if you are trying to conquer stuff in Europe (which I wasn't in my Britain game), but if you are not trying to conquer stuff in Europe, then they are useful so that you have something to do. (Of course, they did also double my census tax income, which would be quite useful in MP, but since I only play SP it really does not matter if I have a lot of money, and in SP I have the luxury of playing for fun rather than to survive.)