I don't know of anyone specifically asking for a neuropsychiatrist though I'd imagine every dept would want one, especially in the VA given the high number of soldiers coming back with TBIs. Every once in awhile, most clinicians encounter a case where having a neuropsychiatrist would help and pulling in the neurologist doesn't really help because often-times they cannot fill the void between the two fields. Wise dept heads want one expert in every area of psychiatry (e.g. forensic, C&L, child) in their dept should the need for consultation arise.
(I just had a neuro consult on a patient where the consultant was devoid of the fact that seizures could sometimes create psychotic-like symptoms, and had a discussion with her where she was less than courteous. This guy in the past was once had his psychosis completely stabilized by seizure meds alone and no he's not bipolar disordered so don't come up with the suggestion that maybe the seizure med acted as a mood stabilizer--it was Dilantin.)
I also don't believe there is an actual board-certification for it (someone correct me if I'm wrong), thus leading to lower odds that you could get a higher reimbursement for it should you graduate. Several institutions base pay on the amount of education, specifically using board-certification as a method, but doing further education in non-recognized areas do not qualify in some systems.
Despite this, this is a field that is needed and you may be able to exploit this need and convert it into something highly useful in terms of monetary compensation. As with any fellowship, don't go into it for the money but do it because you have a genuine interest in the field.
I don't know how competitive it is and because I don't believe it's something that's on the board certifiable panel I don't know if there's organized data sets on what it's like in terms of trends. Only thing I can think of is maybe ask the PDs of the existing programs if no one here gives you an answer.