How does an excel sheet identifies and differentiates between cash in-flows and out-flows to calculate NPV, IRR or MIRR ?

Flow out of its value be negative 

Flow in of its value be postive 

Example 

NPV(rate,value1,value2, ...)

NPV(.05;-10000;3000;4200;............)

The initial cost of the investment after one year from the day the value1  is negative

Example 

IRR(values,guess)

IRR(-1000;3000;4200;6000)

Preliminary cost value is negative

Example 

MIRR(values,finance_rate,reinvest_rate)

MIRR(-10000;3000;4200;6000)

Preliminary cost value is negative

Sign of the value plus or negative will recognize if certain amount is inflow or outflow.  Negative will be used for cash outflows and positive for cash inflows