Yes, it really is that useful. The ability to directly connect to machines on my home network without mucking with VPNs, port forwarding, etc is fantastic. The key thing it does is reduces friction when creating or using anything that could be accessed from off the local network. The ability to assign static addresses to custom DNS entries makes it even better. The more services that throw up IPv6 support the more useful it gets. If I could pull from Github over IPv6... or run SSL Labs TLS tests against an IPv6 VM...
In particular, it is better than other VPN/port forwarding type solutions because it works transparently and universally. It works on your phone (where VPN can be more tricky). It works in environments where you aren't allowed to install special relay software. It works when you are a guest somewhere else without them having to change their network setup.
As for cloud services... yes and no. In a world with direct addressing you open up many opportunities for innovation in the home cloud appliance market. However, many cloud services are made affordable because they can leverage shared infrastructure, sell usage metadata, or add and remove capacity instantly. Whether home based solutions could be competitive in price and reliability without those abilities. I see some categories of service that it makes sense for, others, less so.
Security is a concern, yes, but one that can and should be addressed by good security tools and practices (firewalls, automatic security updates, good backups, not running services you don't use, etc) not by crippling your network with NAT.