Just as Emirates A380 First Class or Singapore Airlines A380 Suites Class might be the most aspirational airline award redemptions, using points for an overwater villas seems to be one of the most aspirational hotel award redemptions.
The two most “popular” destinations with overwater villas are the Maldives and Tahiti/Bora Bora. Unfortunately the catch is that most of the resorts at these destinations don’t let you redeem points directly for an overwater bungalow. They usually have a suite on the premises of the property that they let you redeem points for, and then you can pay cash to upgrade to an overwater bungalow if you want. The cost of that can add up quickly, especially when you consider that most of these resorts are remote and require expensive (~$500 per person roundtrip) transport from the airport to the hotel.
Anyway, per this FlyerTalk thread, the InterContinental Thalasso Bora Bora seems to have recently started allowing award redemptions directly into overwater villas.

When you do an award night search you should see availability for an Emerald Overwater Villa.

For the dates I searched, the room would have gone for 104,625 XPF plus tax, which is ~$1,400 per night all-in.

The description of the Emerald Overwater Villa is as follows:
Overwater villa facing the island with beach view 100sqm spacious shaded terrace and big pontoon with sun lounges for sunbathing direct lagoon access freshwater outdoor shower 2 flat screen tvs sep living area internet access max 4 or 3 adults and 2 children at a charge up to 15yrs old
I don’t have any reason to believe this is a mistake, though at the same time I’m guessing availability will book up pretty quickly. IHG Rewards Club points can be purchased in a roundabout way for 0.7 cents each, so this is an opportunity to book an overwater villa in Bora Bora for $350 per night, which is a hell of a deal.

Membership is free and takes no more information than commenting as a guest. Earn status points for good contributions, and see fewer ads as your status grows.