It varied according to the colony in question. Some were very profitable, others were not profitable but were instead maintained for strategic reasons or simply as a matter of national pride.

Broadly speaking, places where crops such as sugar or tobacco were grown with slave labour were very profitable, such as the Carribbean islands.

A classic example of the different ways in which colonies were valued by Britain and France can be seen in the outcome of the Seven Years War. Britain had occupied both Quebec and the surrounding Canadian landmass known as 'New France', and Martinique and Guadalupe, Carribbean islands known for their sugar production.

Ultimately they could only keep one in negotiations, and it was a heated topic of debate as to which was more valuable. The islands were clearly more profitable, and Canada did not have much to offer economically beyond fur trapping and fishing. However, Canada had great strategic value in the sense that if Britain kept it, it removed the French threat to the Thirteen American Colonies, and cemented British control over North America.

In the end they chose Canada, for its strategic value. It could be argued that this did not really work, as it has been argued that the expense of maintaining Canada's defence, as well as American concerns about the incorporation of its Catholic population, ultimately contributed to the tensions which would lead to the American Revolution. I've also seen it suggested that France was entirely happy to keep the islands at the expense of Canada, but it's obviously impossible to know for sure how the reverse decision would have gone.

Whether the empires were overall a net 'loss' or not isn't something I have an answer for, and I suspect it is not possible to answer considering the various iterations both empires went through in the centuries they existed. My main point, though, is that the 'value' of a colony was not solely determined by its financial profitability. This was a factor, of course, but in many cases colonies were seen not as sources of income but as investments, from which the colonists hoped to gain strategic or defensive advantages, rather than money.