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 = 1123 AND i.igate IN ('usahud','lahud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'lahud', 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 = 1123 AND i.igate IN ('usahud','lahud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'lahud', 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 = 1123 AND i.igate IN ('usahud','lahud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'lahud', 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;

664 Stamps Rd
Merryville, LA 70653

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $1,300,000
Beds: 5
Baths: 6
Status: Active
SqFt: 6500
Agency: NextHome, Inc

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 34 acre(s)
Property Type: Residential
Year Built: --
Notes: Travel down the winding driveway to this AMAZING CUSTOM BUILT 2-Story 5 bed 6 bath home on 34+/- BEAUTIFUL acres with 7000 sqft barn to include 4500 in heated and cooled sqft, ALL situated in a SECLUDED setting! THIS GOREGOUS property has many LOVELY attributes to offer!! MORE LAND AVAILABLE! Call or Text Nichole Maricle with NextHome Bayou Pines at 208.262.6952<br /> <br /> -6500+/- heated/cooled sqft with 10,000 total under roof<br /> -Large Living<br /> -Den with LOFT<br /> -Gym<br /> -EXQUISITE Library<br /> -LARGE Mud room<br /> -HUGE Owners Suite with HIS and HERS<br /> separate bathrooms!!<br /> -FORMAL office<br /> -STORAGE everywhere<br /> -Upstairs kitchenette and Laundry<br /> -Safety Escape ladder from 2nd floor<br /> -Spray foam insulated<br /> -Multiple Tankless Hot water Heaters<br /> -Generac Generator with 1000 gallon in<br /> ground propane tank<br /> -Screened-In porches spanning front and<br /> back of home<br /> BARN OFFERS:<br /> - Multiple Heated and cooled rooms with<br /> kitchen and bath<br /> -Generac generator with 500-gallon propane tank<br /> -tankless hot water heater<br /> -10X15 game cleaning are with<br /> -heated/cooled upstairs storage<br /> -TONS of OPENED storage area for Tractors, Campers,<br /> Recreational toys...etc<br /> -3 RV hookups out from Barn area
MlsNumber: 61-8


Listing Provided By: NextHome, Inc, original listing
Name: NextHome, Inc
Office Name: NextHome Bayou Pines
Office Phone: (337) 202-1018
Agent Name: Nichole Maricle
Disclaimer: Copyright © 2024 NextHome, Inc. 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