I've found a nice NPM package to print the dependency tree into console. Of course if you don't mind using/installing NPM/Node.JS.
Considering other solutions, this is the most simple one, you don't need to write your own code or register something, and you get just such dependency tree as you expect. But it works only with packages.config format.
I can't believe this functionality is absent in free Visual Studio editions or nuget.exe too.
I Can Has .NET Core (GitHub repository) produces nice graphs of NuGet dependencies along with a Graphviz representation. And as its name implies, you also get .NET Core compatibility information for free.
If you prefer to run it locally on your computer, I Can Has .NET Core also offers a console mode.
Since this is an old question, it is important to note the following:
This is a built-in feature in the new csproj format. In Visual Studio 2017 and up, open the Solution Explorer and you can find you packages like:
{Your project}->Dependencies->Packages
You can open each NuGet dependency tree and run with it recursively, effectively seeing not only the dependency tree for specific packages, but also which NuGet packages your project actually installs.
Another option you have is to use the nuget-deps-tree npm package.
It supports both the packages.config format and the newer assets format used by .NET projects.