Both Inspirations and Frenzies give a 10-day +1 to the inspired colonist's mood just for happening.
Inspirations also give an additional 3-day +1 mood bonus when completed / used. (For example, after doing a surgery with Inspired: Surgery)
Why? Well, because Inspiration Feels Good!
(Yes, I know this is probably unbalanced.)