Back in Lan Party days pounding red bull after red bull I was still easily the first one to bed by far (which is never a good thing to be asleep around a bunch of wired up young immature males late at night...)
...of course, sometimes I will get up early, do some stuff around the house, drive to the shops, drive back to the house, put everything away, cycle to work, and I still won't feel like I've woken up properly until I've drunk my coffee and read my email.
For me, it's all about the ritual of drinking the coffee rather than the caffeine content. I'm quite sure that if someone were to replace it with decaf and not tell me, it'd work just as well.
Everyone's different.
The effects can also be different from what's expected. I've tried to stay awake one night, as I had a deadline, by drinking to cans of redbull in quick succession. Didn't seem to work, I fell asleep half an hour later.
Thing is, a fly landing on the other side of the room would startle and wake me up. I couldn't sleep indeed, not by not falling asleep, but by waking up all the time from random city noises.
I drink afternoon coffee all the time and am usually fine. I tried it 2 days ago and couldn't sleep at all. My unscientific speculation is that eating a light dinner that night combined with the coffee was too much.