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;

50158 Ehrenberg
Ehrenberg, AZ 85334

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $249,900
Beds: 2
Baths: 2
Status: Active
SqFt: 671
Agency: Horizon Manufactured Homes INC

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: --
Property Type: Other
Year Built: 2020
Notes: This must see custom-built tiny home boasts two bedrooms and two bathrooms, featuring a thoughtfully designed loft. Nestled in a picturesque location overlooking the river, the residence embraces an open-concept layout. The living space is illuminated by natural light streaming through a sliding glass door that opens onto a spacious 10' x 11" covered front porch. The kitchen is equipped with modern amenities, including stainless steel refrigerator, stove, microwave range hood, and dishwasher, along with a stylish stainless steel farmhouse sink. Enjoy the convenience of 2" faux wood blinds in most windows and an all-in-one washer & dryer combo hookup. For climate control, there are ceiling fans in the living room and the downstairs bedroom, while a mini-split air conditioning system extends its comfort to the living room, bedroom, and loft. Additionally, the property offers practical features like a 16' x 35' covered carport and a 16' x 24' rooftop deck that affords stunning sunset views over the river. ABOUT THE COMMUNITY Experience the perfect riverside getaway at our location, offering you over 600 feet of pristine beach along the stunning Colorado River. There's ample space for all your family and friends to join in on the fun. Embrace the enchanting river nights as you relax beneath illuminated palapas, or opt for a cozy beachside fire using the conveniently provided fire pits. For those seeking adventure, take advantage of the launch area to set your toys afloat, allowing for exhilarating cruises along the captivating Colorado River. With access to an impressive stretch of over 75 miles of waterway, the possibilities are endless. And if you're an explorer at heart, venture onto the numerous trails just beyond the park's borders, spanning thousands of miles. Uncover local mining sites or engage in gem hunting in the Quartzite area. For the ultimate adventure, embark on the Arizona Peace Trail, a multi-day journey encompassing the scenic route from Yuma to Kingman and everything in between. Thrill-seekers will be delighted to know that our location boasts some of the finest off-roading opportunities around. And for those who come with boats in tow, take advantage of our recently added courtesy dock and launch ramp, ensuring your waterborne adventures are both seamless and enjoyable. Whether you're seeking relaxation or exhilaration, our destination offers an array of activities to cater to every preference.
MlsNumber: 11224560


Listing Provided By: Horizon Manufactured Homes INC, original listing
Name: Horizon Manufactured Homes INC
Phone: (951) 537-6850
Office Name: Horizon Manufactured Homes INC
Office Phone: (951) 537-6850
Agent Name: Brian Ralls
Disclaimer: Copyright © 2024 My State MLS. 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