I agree with you somewhat: especially when starting a project, it just makes no sense to write tests up-front (again, unless the product is the tests).
That said, when making incremental changes to an existing project, tests (and in some cases TDD) have a lot of value. There comes a point where the project changes from a creative endeavor to a product. Then you need tests.
The problem is how you create a test suite at that point in time without being negatively influenced by having written the code. Most unit tests I've seen in the wild are BS for this reason: the person writing them just repeats the code they wrote to implement the feature, or at least makes the same bad assumptions. In most cases they mock out so much that the test bears no relation to the real world, and breaks with the slightest change to the code.
For this reason I prefer to write tests from the top-down instead of bottom-up. Start with a few end-to-end tests, making sure to cover the happy paths and a few of the more probable error cases. These few initial tests are more valuable than any that will come later, because they best correspond to how the product will be used, and they are least likely to break during later refactorings since they don't depend on any internals, making them very cheap to maintain. For smaller projects, these tests may be all you need.
Every level you go below that incurs an increasingly large maintenance cost, so you have to weigh it against how often the design of that component will change.
When I do write unit tests it will be for essentially pure functions where the domain of inputs is sufficiently small that you can do fairly exhaustive testing. For example, a function that determines whether a floating point value can be losslessly converted to a 32-bit integer is perfect for unit testing. Nothing needs to be mocked - whatever the code does under test is what it will do in production.
All of this is my preference, but the only thing that really matters is that you actually use your brain when deciding what tests you write: weigh up the costs of the test against the set of potential bugs that it can prevent. The real sin of TDD is that it teaches you to write thoughtless tests.