I appreciate the having the Simulate Paper check box enabled really makes the image look ugly but how true of a representation is it to the final print
Yep, the "Make my image look ugly" is a rather famous reaction. Andrew can fill you in on the genesis.
However, as Andrew notes in his video tutorial, it does the best job of showing what the printed image will appear like. The way I initially setup soft proofing is to fill a blank image with white, soft proof it using either Colorimetric or Perceptual - it doesn't matter which - and profile the monitor so that the white image matches in tint and luminance the appearance of the same, unprinted paper on the print viewing station. Adjust the profiling software CCT to match as close as possible. Finally, tweak the xy coordinates (if your software permits) to match the tint. This is an iterative process. Also adjust the cd/m^2 so the luminance matches.
Once you do that you can then soft proof with different papers with differing whites and they should match reasonably closely. Papers that have a higher L* white will appear less subdued and papers with lower L*s will be more subdued.
There is one side effect to be aware of. When viewing an image normally (without soft proofing it) the image will appear slightly brighter than the print in the viewing stand because the "white" is that of a perfectly white paper and paper's actual white levels aren't perfect. A typical glossy paper reflects only between 85% and 90% of the light hitting it. This is why "show paper color" makes the image appear more subdued. It reduces brightness of white from the "perfect" paper to what it actually is.
Also, the impact of "show black ink" depends on how "black" the paper can print. You can see a bit of this in Andrew's video where he focuses on the CC black patch (actually about L*=20) The effect is much more noticeable with matte prints that have lower DMax values. It's highly useful when you are printing low key images to see things like how well BPC is handling deep shadow areas. At least if the profile is correctly constructed. Some Epson canned profiles (like the ones for my 9800) incorrectly handle low L* values in their reverse profile tables. Profiles made with I1Profiler or ColorMunki correctly handle this.
One very big caveat to the above is that it doesn't work very well with OBA laden papers. That's a whole can of worms. While you can also get good matches using M1 Profiles and a modern viewing station that does a good job simulating D50 with all it's uV, it is not cheap and can be confusing unless you geek out on this stuff. Best to stick with no or low OBA paper and keep things simple. Then you don't have to worry about whether your profiles were made with M0, M1, or M2 as there is little difference and no difference if you aren't using OBA paper.