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

655 Appaloosa Road
Tarpon Springs, FL 34688
Jennifer Davida with FUTURE HOME REALTY INC, original listing - (813) 855-4982

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $2,399,000
Beds: 6
Baths: Full: 5
Status: Active
SqFt: 4629 Square Feet
Agency: FUTURE HOME REALTY INC
Agency Phone: (813) 855-4982

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 10 acre(s)
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 1979
Notes: GATED 10-Acre POOL HOME located in Keystone Ranchettes catering to NATURE & EQUESTRIAN lovers featuring a (5) STABLE BARN with tack room, storage, water, fans, and lighting. Enjoy riding your horses on your own property or nearby county park trails. DETACHED BUILDING (900 sq ft.), several ponds throughout the property including one rock-lined one with a small bridge and hook-up for a fountain, (2) pastures, (3) deep water wells, WHOLE HOME water purification system (ultraviolet & reverse osmosis), R.V. junction box with 30-amp, 50-amp, and 120-amps. DOG KENNELS, inside PET UTILITY room for cleaning and bathing, (4) Zones of HVAC units to afford efficiency, (2) SEPTIC TANKS (2020), 500-gallon propane tank, CUSTOM BUILT Commercial Grade OUTDOOR KITCHEN with surround sound on entire patio, 3-car garage, UPGRADED screened pool cage (no-see-um screening), NEW floor tile for the entire outdoor living space, several drainpipes throughout the property, and NEW WINDOWS. Executive-sized home with 4 bedrooms (currently made into 6 bedrooms) and 5 bathrooms with an oversized living room (700+ sq. ft.) with floor-to-roof ceilings, tons of natural light pouring in, CUSTOM Art-Deco glass entry door, and (3) sets of double glass-sliding doors out to the lanai (featuring a covered area), inside laundry with sink, Billard room, step-down family room with stone-façade fireplace, and extra sun room off the kitchen. The OUTDOOR KITCHEN boasts detailed stonework and leathered granite counters, ample electric outlets, lighting, surround sound, TV hook-ups, gas range, grill, griddle, dual refrigerators, sink, storage, and (3) vented hoods. PRIMARY OWNERS RETREAT has been renovated to include painting, custom built walk-in closet, new flooring, all remodeled bathroom with stone & tile work from floor-to-ceiling, gun closet, private water closet, jacuzzi jet tub, and vanity counter. Bathrooms have been remodeled to include tiled showers, new toilets, hardware, mirrors, lighting, and granite counters. NO DEED RESTRICTIONS. Property can be subdivided with 2.5-acre minimum lot size (BUYER TO VERIFY). Buy a Little Piece of Florida Now!
MlsNumber: --
ListingId: TB8307473


Listing Provided By: FUTURE HOME REALTY INC, original listing
Phone: (727) 480-5031
Office Phone: (813) 855-4982
Agent Name: Jennifer Davida
Disclaimer: Copyright © 2024 Stellar 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