Module 4:

Mastering Excel for Financial

Analysis and Formula
Construction
Mr. Jonelle Angelo S. Cenita, MSIT, LPT
BAISEL2X: PROFESSIONAL ELECTIVE 2
Learning Objectives:
• Understand the common mistakes made while building
formulas in Excel and explain how to use named ranges
to enhance formula robustness and transparency.
• Demonstrate the ability to create and troubleshoot
complex Excel formulas using essential financial
functions, logical functions, and lookup/reference
functions.
• Appreciate the importance of robust and transparent
formula-building practices and their impact on the
accuracy and reliability of financial analysis in Excel.
Building Robust and Transparent
Formulae
General Causes of Mistakes in Formula-Building
• Mistakes in formula-building often stem from the
following:
• Human Error
• Complexity in Formulae
• Inconsistent Data References
• Lack of Documentation
• Unclear Assumptions
Human Error
• Miskeying data, copying incorrect values, or referencing
the wrong cells.
• Example: A formula intended to sum A1:A10 is
accidentally entered as =SUM(A1:A9), missing a value.
Complexity in Formulae
• Overly complicated formulas that are difficult to read
and understand, leading to errors or misinterpretation.
• Example:
• Formula: =IF(AND(A1>10, B1<5), A1*B1, IF(A1=0, C1/D1,
A1+B1))
• This is difficult to read and understand. Breaking it into
smaller steps or intermediate cells would reduce confusion
and errors.
Inconsistent Data References
• Using mixed or relative references improperly, causing errors
when formulas are copied across cells.
• Example:
• When copying =$A$1+B2 down a column, the absolute reference
$A$1 remains constant, but B2 updates incorrectly due to relative
referencing.
Lack of Documentation
• Failing to document the purpose of the formula or model,
resulting in misuse or misunderstanding.
• Example: A formula like =C1*C2/12 is used to calculate monthly
interest but lacks comments explaining the logic or purpose,
making it unclear for future users.
Unclear Assumptions
• Not explicitly stating assumptions behind calculations, which
can lead to incorrect interpretations.
• Example:
• A budget calculation assumes all months have 30 days without
stating this explicitly. If a user later inputs 31-day months, results
will be inaccurate.
• Formula: =DailyExpense*30 without clarifying "30 days assumed per
month."
Examples of Common Mistakes in Formula-Building
• Here are some examples of mistakes that often occur
when building formulae:
• Circular References
• Hardcoding Values
• Missing Parentheses
• Incorrect Range Selections
• Not Accounting for Errors
• Copy-Paste Issues
Circular References
• A formula unintentionally references itself, causing errors
or endless calculations.
• Example: A formula in cell A1 references B1, but B1 also
references A1 (e.g., A1 = B1 + 1 and B1 = A1 - 1).
• This causes an error or infinite loop unless iterative
calculations are enabled.
Hardcoding Values
• Embedding fixed numbers directly into formulas, making
updates difficult and error-prone.
• Example: A discount calculation is entered as =A1*0.10
instead of referencing a discount rate in a separate cell
(e.g., =A1*B1).
• This makes updating the discount rate for all formulas time-
consuming and error-prone.
Missing Parentheses
• Incorrectly structured formulas, e.g., =(A1+B1*C1)
instead of =((A1+B1)*C1), leading to unexpected results.
• Example: =A1+B1*C1 calculates B1*C1 first (due to
operator precedence) and then adds A1.
• If the intended logic was to add A1 and B1 first, the correct
formula should be =(A1+B1)*C1.
Incorrect Range Selections
• Selecting blank cells or unrelated ranges that distort
calculations.
• Example: Summing a range with blank cells:
=SUM(A1:A10) when only A1:A5 contain data.
• This inflates results if blank cells later contain unintended
values.
Not Accounting for Errors
• Ignoring potential issues like #DIV/0!, #VALUE!, or #REF!,
which make results unreliable.
• Example: Dividing by a cell that could be zero: =A1/B1.
• If B1 is zero, the formula returns a #DIV/0! error. A better
approach: =IF(B1=0, "Error", A1/B1).
Copy-Paste Issues
• Copying formulas without adjusting references, causing
incorrect outputs.
• Example: Copying =SUM(A1:A5) from column A to
column B results in =SUM(B1:B5).
• If this behavior isn’t intended, use absolute references like
=SUM($A$1:$A$5) to lock the range.
Use of Named Ranges
• Named ranges improve formula transparency and reduce
errors:
• What Are Named Ranges?
• Benefits of Using Named Ranges
• Best Practices for Named Ranges
What Are Named Ranges?
• A named range is a descriptive label assigned to a cell or
range of cells.
• Example: Naming A1:A10 as SalesData, so instead of
=SUM(A1:A10), you can use =SUM(SalesData).
Named Ranges
Named Ranges
Benefits of Using Named Ranges:
• Improved Readability:
• Example: =SUM(Expenses) is easier to understand than
=SUM(B1:B10).
• Easier Management:
• If the range for SalesData changes from A1:A10 to A1:A15,
update the named range once, and all formulas using it will
adjust automatically.
• Error Prevention:
• Reduces the chance of referencing incorrect cells by
providing clear labels.
Best Practices for Named Ranges:
• Use meaningful and consistent names, such as Revenue
or Expenses.
• Avoid names that are overly long, ambiguous, or similar
to Excel functions (e.g., SUM, AVERAGE).
• Document the purpose of named ranges in a dedicated
sheet for easy reference.
Essential Excel Functions for
Financial Analysis
Key Financial Functions Using Excel
• 1. Present Value (PV): Calculates the current value of
future payments, considering a fixed interest rate.
• Formula in Excel:
• rate: Interest rate per period.
• nper: Total number of periods.
• pmt: Payment made each period.
• fv: Future value (optional, default is 0).
• type: Optional, 0 for end-of-period payments (default) or 1
for beginning-of-period payments.
Key Financial Functions Using Excel

• Example in Excel: Calculate the present value of receiving

$1,000 annually for 5 years at a 5% interest rate:
• Formula: =PV(5%, 5, -1000)
• Result: $4,329.48
• (This indicates that $1,000 received annually for 5 years is
worth $4,329.48 today at a 5% interest rate.)
Key Financial Functions Using Excel
• 2. Future Value (FV): Computes how much an investment
will grow over time with regular payments or an initial
deposit.
• Formula in Excel:
• rate: Interest rate per period.
• nper: Total number of periods.
• pmt: Payment made each period.
• pv: Present value or initial deposit (optional).
• type: Optional, 0 for end-of-period payments or 1 for
beginning-of-period payments.
Key Financial Functions Using Excel

• Example in Excel: Calculate the future value of monthly

payments of $100 for 10 years at a 6% annual interest
rate:
• Formula: =FV(6%/12, 10*12, -100)
• Result: $16,387.93
• (This means the investment will grow to $16,387.93 after 10
years with $100 monthly payments and a 6% annual interest
rate.)
Key Financial Functions Using Excel
• 3. Net Present Value (NPV): Determines the profitability
of an investment by calculating the difference between
the present value of cash inflows and outflows.
• Formula in Excel:
• rate: Discount rate or required rate of return.
• value1, value2, …: Series of cash flows over time.
Key Financial Functions Using Excel

• Example in Excel: Evaluate an investment with these cash

flows:
• Initial investment (Year 0): -$5,000
• Year 1: $2,000
• Year 2: $3,000
• Year 3: $1,500
• Discount rate: 10%.
Key Financial Functions Using Excel
• Steps in Excel:
• 1. Enter cash flows in cells:
• A1: -5000, A2: 2000, A3: 3000, A4: 1500.
• 2. Use the NPV formula:
• =NPV(10%, A2:A4) + A1
• 3. Result: $424.49
• (This indicates a positive NPV, making the investment
worthwhile.)
Key Financial Functions Using Excel
• 4. Internal Rate of Return (IRR): Calculates the discount
rate at which the NPV of an investment equals zero,
representing its profitability in percentage terms.
Key Financial Functions Using Excel

• Formula in Excel:
• values: Range of cash flows (including the initial investment).
• guess: Optional estimated return rate (default is 10%).
Key Financial Functions Using Excel
• Example in Excel:
• Calculate the IRR for the following cash flows:
• Initial investment (Year 0): -$5,000
• Year 1: $2,000
• Year 2: $3,000
• Year 3: $1,500.
Key Financial Functions Using Excel
• Steps in Excel:
• 1. Enter cash flows in cells:
• A1: -5000, A2: 2000, A3: 3000, A4: 1500.
• 2. Use the IRR formula:
• =IRR(A1:A4)
• 3. Result: 15%
• (This means the investment yields an annual return of 15%.)
Logical Functions
• 1. IF Function: Performs a logical test and returns a value
if the test is TRUE and another value if FALSE.
Logical Functions

• Formula in Excel:
• condition: The logical test (e.g., A1 > 60).
• value_if_true: The result if the condition is TRUE.
• value_if_false: The result if the condition is FALSE.
Logical Functions
• Example in Excel: Determine whether a student passes a
course (≥60 for pass, <60 for fail):
• Enter a score in cell A1 (e.g., 75).
• Use the formula:
• =IF(A1>=60, "Pass", "Fail")
• Result: "Pass" (if A1 is 75).
Logical Functions
• 2. AND Function: Returns TRUE if all conditions are TRUE;
otherwise, returns FALSE.
Logical Functions

• Formula in Excel:
• condition1, condition2, …: Logical conditions to evaluate.
Logical Functions
• Example in Excel: Check if a person qualifies for a loan (income
> $50,000 AND credit score > 700):
• Enter income in A1 (e.g., 55,000) and credit score in B1 (e.g., 750).
• Use the formula:
• =AND(A1>50000, B1>700)
• Result: TRUE (if A1 is 55,000 and B1 is 750).
Logical Functions
• 3. OR Function: Returns TRUE if at least one condition is TRUE;
otherwise, returns FALSE.
Logical Functions

• Formula in Excel:
• condition1, condition2, …: Logical conditions to evaluate.
Logical Functions
• Example in Excel: Check if a student qualifies for a retest (score
in either Math or English < 60):
• Enter Math score in A1 (e.g., 58) and English score in B1 (e.g., 70).
• Use the formula:
• =OR(A1<60, B1<60)
• Result: TRUE (if A1 is 58 and B1 is 70).
Logical Functions
• 4. Nested IF Function: Use multiple IF statements to handle
more than two conditions.
Logical Functions

• Formula in Excel:
• condition1: First logical test.
• value1: Result if condition1 is TRUE.
• condition2: Second logical test if condition1 is FALSE.
• value2: Result if condition2 is TRUE.
• value3: Result if both conditions are FALSE.
Logical Functions
• Example in Excel: Determine grade categories based on scores:
• A1 contains the score (e.g., 85).
• Grade categories: ≥90 = "A", ≥75 = "B", otherwise = "C".
• Formula:
• =IF(A1>=90, "A", IF(A1>=75, "B", "C"))
• Result: "B" (if A1 is 85).
Lookup and Reference Functions
• 1. VLOOKUP:
• Searches for a value in the first column of a range and returns a
value in the same row from a specified column.
• Formula:
Lookup and Reference Functions
• 1. VLOOKUP:
Lookup and Reference Functions
• 2. HLOOKUP:
• Similar to VLOOKUP but searches for a value in the first row of
a range and returns a value in the same column from a
specified row.
• Formula:
Lookup and Reference Functions
• 2. HLOOKUP:
Lookup and Reference Functions
• 3. INDEX:
• Returns the value of a cell based on a specified row and column
number.
• Formula:
Lookup and Reference Functions
• 3. INDEX:
Lookup and Reference Functions
• 4. MATCH:
• Finds the relative position of a value in a row or column.
• Formula:
Lookup and Reference Functions
• 4. MATCH:
Lookup and Reference Functions
• 5. Combining INDEX and MATCH:
• Use MATCH to find the row/column number and INDEX to
retrieve the corresponding value.
• Formula:
Lookup and Reference Functions
• 5. Combining INDEX and MATCH: