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

575 Avenstoke Road
Waddy, KY 40076
Peter doCouto with Plum Tree Realty, original listing - (513) 443-5060

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $549,900
Beds: 4
Baths: Full: 2, ½: 1
Status: Active
SqFt: 3886 Square Feet
Agency: Plum Tree Realty
Agency Phone: (513) 443-5060

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 5 acre(s)
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 1988
Notes: Dreaming of a wonderful 5.0 acre home in the country? Well, you've found it! Deceptively modest from the street, this outstanding and comfortable home unveils itself as 3900 sq ft of living space, a dozen rooms, multiple decks for entertaining, and even a private glass walled Atrium-like retreat! The nicely rolling land is manicured well and will really show itself as the weather progresses. Enter from the modest 'country porch' to the front hall of this well thought out split floor plan home. Ahead is the very large living room with lots of natural light and a cozy wood burning fireplace. To the left is the kitchen/Dining room which is colossal! Measuring 19.5'x33' it boasts natural cabinets, stainless appliances, room for a large island, a bay of windows overlooking the tree line, and it's own private deck! On the far side of the top floor, you'll enter the totally inviting Primary Suite. Comprised of two rooms and an en-suite, it is 540 sq ft! Casement windows for light and natural breezes, dual Barn doors for privacy and separation, make this unique space totally customizable. Downstairs and you'll find 3 generous bedrooms, a large Family room with wet bar and a full bath. So many rooms, and so much space! This home is in move-in condition and waiting for you! It has been very well maintained ,, has a newly installed (2024) Dual fuel HVAC system, New paint, a chimney that has been rebuilt and tuck-pointed, newly designed rear concrete walkway and so much more! The large laundry room is on the top level, the 2.5 car garage has a new pellet wood stove for winter comfort. Out back you'll find a large shed with a corral for dogs/pets. Come and discover this amazing property and Make it Yours!
MlsNumber: --
ListingId: 25005536


Listing Provided By: Plum Tree Realty, original listing
Phone: (859) 509-0051
Office Phone: (513) 443-5060
Agent Name: Peter doCouto
Disclaimer: Copyright © 2025 Lexington-Bluegrass Association of Realtors. All rights reserved. All information provided by the listing agent/broker is deemed reliable but is not guaranteed and should be independently verified.


Local Real Estate Expert

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 = 1103 AND i.igate IN ('usahud','mdhud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'mdhud', 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;
Real Estate Expert Photo for Jeremy Byers
Jeremy Byers
ASCIIRE/MAX Premier Properties
Call Today!: (502) 439-7150

USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App