Title Page Background
HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 99 AND i.igate IN ('usahud','azhud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'azhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 99 AND i.igate IN ('usahud','azhud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'azhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 99 AND i.igate IN ('usahud','azhud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'azhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1;

29-3794 Hawaii Belt Rd
Hakalau, HI 96710

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $2,595,000
Beds: 2
Baths: 2
Status: Active
SqFt: 2092
Agency: Hilo Brokers, Ltd.

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 3 acre(s)
Property Type: Residential
Year Built: 2020
Notes: Located on the beautiful Hamakua coast, this 3.37 acre oceanfront property is a sanctuary of nature and boasts over 750 feet of coastline, stunning ocean and coastal views, a stream with multiple waterfalls, over 60 varieties of producing fruit and nut trees, and countless ornamentals. The home is designed to complement this beautiful ecosystem and invites you to experience its loving embrace. See the property video tour: bit.ly/3U0q6pj<br /> <br /> From the home, wander down your private path with dappled sunshine and coastal views, and be awed by the 150’ stream and waterfall dropping into the ocean below. Walk upstream through a 1917 cane-era tunnel to view another stunning waterfall.<br /> <br /> The home has a huge open-concept living area viewing the coast. With a floor-to-ceiling, all-glass sliding wall, enjoy scenic views from the kitchen, living area, or while playing a game of pool. The primary bedroom faces Northeast with views of the ocean and sounds of waves breaking below. The ensuite bath features a Jacuzzi tub and a separate walk-in shower, both with ocean views. The 2nd ensuite bedroom offers space for your home office or guests. The main bath, also with a Jacuzzi, is accessed from both the 2nd bedroom and the kitchen/living area. A 10' covered lanai totaling over 1,500 sf keeps the house cool all summer and blends indoor/outdoor living. Very private and quiet, your own personal sanctuary!<br /> <br /> The adjacent separate oceanfront lot is included and features a 720 sf building with bath, kitchenette, ocean views, and septic, and is ready for you to develop further. Stroll through the variety of producing fruit trees: avocado, mango, mangosteen, lychee, grapefruit, star apple, rollinea, pamplemousse, lemon, lime, soursop, tangelo, Abiu, egg fruit & much more! Both parcels are serviced by county water, and power is off-grid via a photovoltaic system with a new 20kW Li-Fe-P battery.<br /> <br /> This property is comparable to no other!
MlsNumber: 706507


Listing Provided By: Hilo Brokers, Ltd., original listing
Name: Hilo Brokers, Ltd.
Phone: (808) 969-9400
Office Name: Hilo Brokers Ltd
Office Phone: (808) 969-9400
Agent Name: Kona Moran
Disclaimer: Copyright © 2024 Hawaii Information Service. All rights reserved. All information provided by the listing agent/broker is deemed reliable but is not guaranteed and should be independently verified.


USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App