Leave Roxul Safe’n’Sound insulation exposed in a furnace room

While closed cell foam will perform a bit better than fiberglass in noise reduction, there are much better ways to tackle this for the same or lower cost. What you really need for sound isolation is to add mass between the living units and to mechanically separate them.

If both living areas are sharing a wall, building with double studs that are staggered will prevent sound from traveling through the studs from one dwelling to the next. Another great option for soundproofing is to use double layers of 1/2 inch drywall or a single layer of 5/8 inch drywall on each side (or at least on your side) instead of a single layer of 1/2 inch (typical).

I've used 5/8 inch drywall everywhere in my home that I've remodeled and even without insulation in the interior walls, it's helped tremendously. I'd skip the cost of cell foam for interior walls and only use it where needed for air sealing and insulation from heat and cold.

As with any answer, you should check your local codes. However, Going with R-30 on top and R-13 on the sides is standard and will give you very good value. If you can, you should plan on putting R-30 into the 'diagonals' too, that is, where the rafters are above the knee-walls but below the ceilings. If you don't have enough room now, you can screw some 2x4s onto the bottom of the rafters to gain more room: it'll be worth it in the end.

Two things to remember: you don't have to or want to insulate away from living space. So, from your diagram, you would insulate vertically up the knee walls, but don't insulate in the area you close off, as you won't gain any value from it. Two, make sure when you insulate the sloped area where the rafters are, make sure you put baffles underneath the plywood before you install the insulation, to leave space for air to flow from the soffit vents up to the ridge vent.