This is an interesting question and one I think about often. I started in 1990 and it's the one thing I've been interseted in year after year.
For me the motivation comes from a couple of areas. The first is progression. I'm not a pro and never will be but I still want to see how good I get. Progression has been a double edged sword though, as there have been many times that I felt like giving up because I wasn't where I should be.
The second is the zen experience that the was mentioned. Being at one with nature and the mountain, forgetting lifes problems, finding flow are all reasons why I love to ride. I'm a strong introvert so its a way to recharge as well.
Third is the pure thrill of it. There are few sports that have the access and rush.