✅ 1. Core Excel Functions for Financial Analysis

These foundational functions are useful for organizing, calculating, and extracting financial data efficiently.

🔹 A. SUM()

  • Use: Adds up a range of numbers.

  • Example: =SUM(B2:B10) totals all values from B2 to B10.

🔹 B. IF()

  • Use: Applies conditional logic.

  • Syntax: =IF(logical_test, value_if_true, value_if_false)

  • Example: =IF(A2>1000, "Large", "Small")

🔹 C. VLOOKUP() / XLOOKUP()

🔸 VLOOKUP() (vertical lookup):

  • Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • Example: =VLOOKUP("Apple", A2:C100, 2, FALSE) — finds “Apple” and returns value from column 2.

🔸 XLOOKUP() (modern replacement):

  • More flexible: allows searching left or right, exact match by default.

  • Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

🔹 D. INDEX() + MATCH()

  • More powerful and flexible than VLOOKUP

💰 2. Core Financial Functions

These are essential for valuation, cash flow analysis, loan modeling, and more.

🔹 A. NPV() – Net Present Value

  • Calculates the present value of future cash flows, discounted at a specific rate.

  • Syntax: =NPV(rate, value1, [value2], ...)

🔹 B. IRR() – Internal Rate of Return

  • Returns the rate at which NPV = 0 (investment breakeven rate).

  • Syntax: =IRR(values)

  • Example: =IRR(B2:B7) where B2 contains the initial investment (negative) and others are returns.

🔹 C. PMT() – Payment Function

  • Calculates loan or annuity payments.

  • Syntax: =PMT(rate, nper, pv, [fv], [type])

  • Example: =PMT(5%/12, 60, -20000) — monthly payment on a $20,000 loan over 5 years at 5% annual interest.

🔹 D. FV() – Future Value

  • Projects future value of an investment.

  • Syntax: =FV(rate, nper, pmt, [pv], [type])

  • Example: =FV(0.06, 10, 0, -10000) — future value of a $10,000 investment after 10 years at 6% annual return.

🔄 3. Logical & Nested Formulas for Financial Modeling

Combining multiple functions enables advanced logic for dynamic models.

🔹 Nested IF() Statements

Used to apply multiple conditions.

🔹 IFERROR()

Prevents models from breaking due to errors (e.g., divide by 0 or missing data).

🔹 CHOOSE() for Scenario Analysis

Selects from a list based on index.

  • Use with Data Validation for interactive scenario models.

🔹 AND(), OR() for Multiple Conditions

  • AND(A2>1000, B2="Active")

  • OR(A2="Buy", A2="Hold")

🧠 Best Practices for Financial Formulas

Tip Why It Matters
Use named ranges Makes formulas easier to read and audit
Keep inputs separate Supports transparency and flexibility
Color code inputs (blue) and formulas (black) Improves usability
Use comments and documentation Helps others understand the logic
Use absolute references ($A$1) when needed Avoids errors when copying formulas

✅ Summary Table

Function Use Case
SUM Total revenue, costs
IF, AND, OR Conditional logic in models
VLOOKUP/XLOOKUP Data retrieval from tables
INDEX-MATCH Flexible lookup and matching
NPV / IRR Investment valuation
PMT / FV Loan/Investment forecasting
PreviousNext