I think it's not a hard boundary between retro and not retro, and more of a grey area. I also think there might be 3 categories. Modern, not modern, and retro. It's also going to be (at least partly) based on feelings and nostalgia rather than any more measurable quality.
If you say its "you can't comfortably do every day tasks on the hardware today", well, Windows 7 no longer has proper support from Microsoft causing people to move on from it, but I could probably do my work for the day on my 386 with W3.1 and MS Works installed - just writing a report and basic excel stuff today. XP sits in the middle but is even more vulnerable than 7 is now and you need an old version of spotify, no steam support, old browsers etc.
If you say "the only reason you would buy it is because of its inherent value and your desire to have it and not for the computing power it provides", or similarly "it has been comprehensively superseded so why would you use it" then I think the cut off would be Pentium 4 as the youngest retro PCs. But you can get a similar age Socket 939 with PCI-E which is not that retro. Core 2 Duos and DDR2 ("not modern") are literally pennies and are usable in Windows 10 but Pentium 4's and DDR RAM are starting to get more expensive.
Some of the PCs we build just for fun, like my C2Q 4890Xfire PC, are not really retro but offer no modern gaming value - the £40 I spent on the two graphics cards could have bought me a second hand R9 280 or something which could at a stretch play modern-ish games.
Even when playing retro games there is retro within retro. The easiest way to play 3D accelerated games from say 1998 to 2007 is XP or 7 on a core 2 Duo and pretty much any PCI-E graphics card, but we chose to do it on a range of much worse systems - any of which I would class as retro because of that.
Vista and a 8800GT are retro because of their status rather than age. W7 and an 8600GT is not retro because they don't have that status.
For me personally, 775 is not retro, 478 is retro, 939 is sort of a grey area, 754 is retro and AM2 is not. Vista is retro, arguably more so than XP as you have to specifically want to make a Vista PC to do it. Nvidia 9000 series is not retro, 8000 series is grey area, 7000 series is retro. AMD 5000 series is not, 1000 series is, the ones in between are a grey area - which is quite a big grey area.