Why is renaming files with sed and mv printing '$'\r'

This is happening because your input file File-Rename.csv has Windows-style CRLF line endings instead of Unix-style LF - the $'\r' is the shell's way of representing the carriage return character.

You can "correct" you command by changing the final sed expression from s/$// (which doesn't actually do anything - $ in a regular expression is a zero-length assertion that matches the end of the line, but doesn't actually consume a character) to s/\r$//

Alternatively, convert the input file using dos2unix

HOWEVER this approach to renaming files is problematic - in particular, it will fail if either the old or new name contains spaces or certain shell special characters - and even permits code injection¹. Instead I'd suggest something like

while IFS=, read old new; do 
  mv -vi -- "$old" "$new"
done < <(sed 's/\r$//' File-Rename.csv)

or

while IFS=, read old new; do 
  echo mv -vi -- "$old" "${new%$'\r'}"
done < File-Rename.csv

(remove the echo once you are happy with the proposed commands).

Note that this approach will itself fail for certain names that are legal within the CSV format - in particular those containing quoted embedded commas ("foo,bar",baz for example).

¹_{think what happens if someone enters a filename like foo;rm * for example}