The answer is an unequivocable "It depends". I have three zoom lenses for my Canon EOS 20D: the EF-S 10-22mm, the EF-S 17-85mm, and the EF 100-400mm. These are, respectively, a 2.2x optical zoom lens, a 5x optical zoom lens, and a 4x optical zoom lens. If you look at the entire range across all four lenses, I have a 40x optical zoom.
Most typically, a compact camera has a "wide" setting equivalent to 35mm on a 35mm film camera. If that's the case, 15x optical zoom would equate to a '35-525mm' lens on a 35mm film camera. If the camera is billed as being extra wide, then the field of view at the widest setting would be equivalent to 28mm on a 35mm film camera, for a range of 28-420mm. (My money's on this particular possibility, without looking at the camera's documentation in any way.)
But it gets even more complicated than that. The Nikon camera you're looking at doesn't have a sensor that's the same size as a 35mm film camera. So to get an equivalent to 28-420mm lens on a 35mm film camera, you would need to get lenses covering the range from 18mm up to 280mm. Very easily done; Nikon's 18-70mm and 70-300mm lenses will do the job nicely. (although I can't comment on the image quality of these lenses, I'd expect them to at the very least be on par with a compact ultrazoom, and they'd probably be a bit better.)
You'll probably see some improvement; the sensor in the D40 is larger than that in any compact camera, so it's less susceptible to noise (handwave, handwave.) You would see a much greater jump with a higher quality lens, but it all comes down to how much you want to spend, and what you want to do with your photography.