I think we all get into these situations from time to time. Who doesn't want to feel desired, loved or accepted? I know I do. I just don't want to be the one picking up the entire bill for it, and then have to worry about the consequences when I end the arrangement as soon as the law of diminishing returns kicks in. Also, good sex does addle the brain. Trouble is, women produce way more oxytocin than men, while men are more ego led.
Whatever you decide, whenever you're ready, it's safest if the decision to end it is his. You'll figure out how, don't worry.
He may surprise you and be totally understanding when you tell him you're having to concentrate on paying off that huge stack of bills on the fridge. Not sure I would invent a terminally ill relative - fate has a hideous way of tripping one up. Not that I'm superstitious, noooo, not at all.