You take your chances in September-October. Being from the Boston area, some of the worst storms at sea occur during those months, but then it's not a given that you'll encounter a storm and the price of your ticket reflects the chances you are taking. Also, the Majesty is an older smaller ship, which translates into a rougher sail, should you encounter bad weather. Personally, I wouldn't book this ship during those months in the Atlantic. But I wouldn't hesitate on a larger, newer ship.
For less noise and movement, book a cabin mid-ship on a lower deck away from the elevators.
I've never been to Bermuda in the fall, but I've heard the weather is pleasant enough, though not exactly beach weather--low to mid-70s on a clear day.