You're right. A NAP is not an alliance or even a collaboration. It just means the players do not want war *for now*. There can only be 1 winner, unless it is a team game, and so, war is inevitable (unless the players otherwise get defeated by other nations).
I wouldn't go anywhere near as far as saying " when you see one past around turn 5, be suspicious." I never offer NAPs to nations I haven't even found yet. Rarely if we don't share a border or expect to shortly share one. Nor have I gotten many offers from distant nations.
So a NAP offer later in the game means: "Hey, looks like we're going to be neighbors. Are you willing to be peaceful or do you want to fight?"