Considering all things equal, the return with 100% cash purchase will have a better return if your alternate return for the cash is less than the mortgage rate.
Let's use two scenarios. Let's say you have $100k in the bank and a $100k property is available.
Scenario 1 is to buy the property with the $100k cash.
Scenario 2 is to buy the property with $0 down and a $100k mortgage. But you have the $100k still in the bank that you can invest.
Scenario 2 is the better option if you can get a better return from investing the $100k than the mortgage rate. If you're mortgage rate is 4% but you can get a 5% return investing the $100k in some other asset, then scenario 2 works better. If you leave the $100k in the bank earning 1%, then scenario 1 will give you a better return.
Note that appreciation for this comparison doesn't matter. There are some other nuances including the ability to write off your mortgage interest at applicable tax rates as well as incurring closing costs on obtaining a mortgage.
Simply put this is leverage: borrowing money to invest. Hopefully you're borrowing money at a rate lower than you can get by investing the funds.
If you don't have $100k, then there's only one option: scenario 2.
