Title Page Background
DEBUG FN args: Array ( [0] => 221 [1] => [2] => [3] => agent ) BEFORE: Site: 221 LocID: Region: State: agent Experttype: Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 SITE IS NUMERIC, SWAPPING IFELSE numeric IFELSE else AFTER: Site: 221 Igate: njhud LocID: 221 Region: State: Experttype: agent Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: 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 = 221 AND i.igate IN ('cahud','ushud') AND m.expertType = 'agent' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'cahud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1; DEBUG FN args: Array ( [0] => 221 [1] => [2] => [3] => lender ) BEFORE: Site: 221 LocID: Region: State: lender Experttype: Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 SITE IS NUMERIC, SWAPPING IFELSE numeric IFELSE else AFTER: Site: 221 Igate: njhud LocID: 221 Region: State: Experttype: lender Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: 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 = 221 AND i.igate IN ('cahud','ushud') AND m.expertType = 'lender' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'cahud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1; DEBUG FN args: Array ( [0] => 221 [1] => [2] => [3] => other ) BEFORE: Site: 221 LocID: Region: State: other Experttype: Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 SITE IS NUMERIC, SWAPPING IFELSE numeric IFELSE else AFTER: Site: 221 Igate: njhud LocID: 221 Region: State: Experttype: other Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: 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 = 221 AND i.igate IN ('cahud','ushud') AND m.expertType = 'other' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'cahud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1;

74+ Seaside Avenue
Guilford, CT 06437

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $4,700,000
Beds: 4
Baths:
Status: Active
SqFt: 5163 Square Feet
Agency: Compass Connecticut, LLC

Map Details Show Map | Show StreetView




House Features

Accessibility Features: Accessible Elevator Installed
Appliances: Cooktop, Dishwasher, Washer, Microwave, Range, Dryer, Cooktop - Gas
Architectural Style: Colonial
Cooling: Zoned A/C, Ceiling Fan(s)
Heating: Zoned, Fireplace
Interior Features: Security System, Programmable Thermostat, Elevator, Open Floor Plan, Generator, Thermopane Windows, Auto Garage Door Opener, Audio System, Cable - Pre-wired, Energy Star Rated
Rooms: Basement, Bathroom 1, Bathroom 2, Bathroom 3, Bathroom 4, Bedroom 1, Bedroom 2, Bedroom 3, Bedroom 4, Laundry, Workshop
Security Features: Security System

Window Features

Exterior Features: Patio, Lighting, Gutters, Grill, Awnings, Covered Deck, Porch-wrap Around, Porch-screened
Parking Features: Garage
Patio and Porch Features: Patio, Porch, Deck
Roof: Shingle, Asphalt
Structure Type: Dock
View: Water
Waterfront Features: Waterfront, Direct Waterfront, Beach

Neighborhood/Schools

Elementary School/District: Calvin Leete
Middle/Junior High School/District:
High School/District: Guilford
Subvidision Name:

Description/Comments


Lot Size: 2 acre(s)
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 2013
Notes: Possibly NOWHERE on the Connecticut Shoreline will you find a home so luxuriously orchestrated. Attention to EVERY detail is obvious from the 47 Hubbardton Forge custom light fixtures, 7 heating and cooling zones, electronically controlled blinds, German manufactured NanaWall brand Bi-folding doors, heated floors, elevator, and gas generator. The list goes on ad infinitum. The residence, as grand and luxurious as it is, pales in comparison to the environment that surrounds the 3,960 square feet of primary conditioned living space. The residence is nestled on two scenic acres with the potential to add additional acreage including 587 linear feet of sandy beach and a 79 foot pier connecting to an aluminum ramp with float. Connecting this magnificent residence to the beach is a 225 linear foot mix of elevated walkways and steps. Enjoy panoramic views of the East River and Long Island Sound, over some wildlife abundant marsh grasses, from the home's 3,190 square feet of outdoor space. Continued below...
MlsNumber: --


Listing Provided By: Compass Connecticut, LLC, original listing
Phone: (203) 415-5439
Office Phone: (203) 245-1593
Agent Name: John Campbell
Disclaimer: Copyright © 2024 SmartMLS, Inc. 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

DEBUG FN args: Array ( [0] => njhud [1] => 0 [2] => sacramento-city [3] => CA [4] => agent [5] => 1 ) BEFORE: Site: njhud LocID: 0 Region: sacramento-city State: CA Experttype: agent Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 IFELSE else AFTER: Site: njhud Igate: njhud LocID: 0 Region: sacramento-city State: CA Experttype: agent Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: 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 = 221 AND i.igate IN ('cahud','ushud') AND m.expertType = 'agent' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'cahud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1;
Real Estate Expert Photo for Leidia Salto
Leidia Salto
ASCIIHouse Of Victory
Call Today!: (916) 768-3077

USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App