It's going to vary a lot CPU to CPU.
That being said I think you'll stop seeing gains from additional cores after 4.
There are 4 big threaded things in VALORANT.
1 : Main Thread : Gameplay,Character,AbilityLogic,UI,etc. Usually 100% usage
2 : Render Thread: CPU side rendering things like occlusion, draw call submission. Usually 50-100% usage
3 : Audio Thread : Audio processing engine, ~25% usage
4 : Worker threads : Animation, Particles, Misc things. ~3% usage for each. There can be a lot of work threads depending on game events.
So you're really looking at really benefiting from 3 cores or maybe 4. You can reduce the latency on some things but I would expect to see diminishing returns after 4+ cores. After that, you're better cranking up clock speeds.
Of course, this assumes you have no other programs running on your computer. Depending on what you're running in the background you might benefit for more cores for that.