FIN4424 Exam 1 Excel

AVERAGE

=AVERAGE(number1, [number2], …) — Calculates the arithmetic mean of the selected numbers or range.

GEOMEAN

=GEOMEAN(number1, [number2], …) — Calculates the geometric mean of positive values, such as a range containing each return plus 1.

Geometric return with GEOMEAN

=GEOMEAN(return_plus_1_range)-1 — Calculates the constant compounded return from a range of growth factors.

IF

=IF(logical_test, value_if_true, value_if_false) — Returns one result when a condition is true and another result when it is false.

Nested IF

=IF(test1, result1, IF(test2, result2, result3)) — Classifies an observation into three outcomes by placing one IF function inside another.

IFS

=IFS(test1, result1, test2, result2, TRUE, default_result) — Tests multiple conditions in order and returns the result linked to the first true condition.

AND

=AND(logical1, [logical2], …) — Returns TRUE only when every listed condition is true.

OR

=OR(logical1, [logical2], …) — Returns TRUE when at least one listed condition is true.

IF with AND

=IF(AND(test1, test2), value_if_true, value_if_false) — Returns the true result only when both conditions are satisfied.

IF with OR

=IF(OR(test1, test2), value_if_true, value_if_false) — Returns the true result when either condition is satisfied.

Dummy-variable coding with IF

=IF(category_cell="F", 1, 0) — Converts a text category into a numerical 1/0 dummy variable for regression.

TREND

=TREND(known_y_values, known_x_values, new_x_values, [const]) — Fits a linear trend to known data and predicts values for one or more new X values.

FV

=FV(rate, nper, pmt, [pv], [type]) — Calculates the future value of a lump sum, annuity, or combination of both.

PV

=PV(rate, nper, pmt, [fv], [type]) — Calculates the present value of future equal payments, a future lump sum, or both.

PMT

=PMT(rate, nper, pv, [fv], [type]) — Calculates the equal periodic payment required for a loan or savings goal.

NPER

=NPER(rate, pmt, pv, [fv], [type]) — Calculates the number of periods needed for an investment, loan, or annuity.

RATE

=RATE(nper, pmt, pv, [fv], [type], [guess]) — Calculates the periodic interest rate or yield implied by a set of equal cash flows.

NPV

=NPV(rate, value1, [value2], …)+initial_cash_flow — Discounts cash flows beginning in period 1 and then adds the separate time-zero cash flow.

IRR

=IRR(values, [guess]) — Calculates the discount rate that makes the net present value of the complete cash-flow series equal to zero.