I think you may have answered your own question, at least in part:
> I always seem to find an excuse as to why it's not worth continuing, *right before actually accomplishing anything* [emphasis mine]
Certainly "diminishing returns" and "the skill of consistency" are real issues, but is it also possible there's something psychological going on here, like a fear of success?
This is something I've struggled with for all of my adult life – not with success per se, but with the way succeeding at something defines who/what you are. For instance, way back when I was a freshman in college, I poured myself into a big assignment with singular focus, and created something that got a lot of attention and notoriety. Important people took notice, I won some awards, and everyone started asking what I was working on next. It felt great and I did start another big project, but quickly found myself crushed under all these perceived expectations... And although I finished it, the result was underwhelming. I sort of dropped off everyone's radar at that point, and I'm not sure I've ever really recovered.
Like other commenters have said, you could have written your post verbatim about me. I get into things with an initial boost of motivation that often borders on obsession, go deep down the rabbit hole, cobble together some sort of proof of concept, and then suddenly lose interest, as you described, right at the point where I could actually produce something.
So how do we get past this? Figuring that out may be my life's work. But speaking of life, one of the best tips that has helped me is, "If you want to figure out what to do with your life, work backward from your death". Which is to say, think about what your obituary will say, how you want to impact people, what your legacy will be. Then think about what you need to learn/build/accomplish to get there, and work your way, step by step, back to now.
It's not a perfect system by any means, but it does help keep me "on track" when I feel like tossing something aside because I suddenly lose motivation. I tell myself, This is how I get to point B, which goes to C, to D, to E. And the fact that I want "point E" is very unlikely to change.
The other tip I'll leave you with is: Trust your past self. You from [some time ago] decided [activity] was worth doing. So it probably is! It may not feel like that now, but trust that Past You made the right call about things, even if they may not have known the full scope of what they were getting into. I often struggle to put in another hour/day on some project I don't really want to do anymore, because the alternative looks so enticing: Start something new! Dick around on Reddit! Watch someone else do the thing on YouTube! Etc. So I remind myself that, for me 1) life isn't about relaxing; it's about doing stuff, and 2) when I do relax, I enjoy it so much more if I feel like I've "earned" it.
Anyway best of luck, friend. At least you've already learned you're far from alone on this!