The Return URL is where PayPal sends the buyer after they've completed everything on the PayPal pages.
In the case of Payments Standard that would typically happen after the payment is completed, and then the return URL may be used in conjunction with PDT (Payment Data Transfer) to pass data to the return URL so that you may display it for the buyer as a thank you / receipt page. With API calls the ReturnURL redirection from PayPal generally happens prior to the payment being completed.
Notify URL is used with IPN (Instant Payment Notification) which is very similar to PDT except that IPN notifications are sent as a "silent POST" to your listener script on your server. These notifications will be sent in real-time with all transactions on your PayPal account so you can automate procedures for things like payments, refunds, disputes, cleared e-checks, etc.