Excel for Financial
Modelling
Mastering Advanced Functions and Formulas
Yogesh Jaiyawala
Asst. professor, SPSU
Importance of Excel
• Flexibility: Excel allows you to create custom financial models tailored
to your specific needs, from simple budgeting to complex valuation.
• Scalability: It can handle large datasets and complex calculations,
making it ideal for modeling a wide range of financial scenarios.
• Accessibility: Excel is widely used across industries, ensuring
compatibility and ease of sharing models with colleagues and clients.
• Versatility: From financial statements to investment analysis, Excel
supports a plethora of financial functions and features.
• Visualization: Excel's charting capabilities enable you to present your
financial models visually, aiding in decision-making.
Advanced Excel Functions
• Net Present Value (NPV): fundamental financial function used to
evaluate the profitability of an investment or project.
• Internal Rate of Return (IRR): IRR helps us find the rate of return at
which the net present value of cash flows becomes zero.
• Payment Functions: Excel offers a range of payment functions, including
PMT, PPMT, and IPMT, which are invaluable for loan and mortgage
calculations.
• Statistical Functions: Functions like AVERAGE, STDEV, and CORREL to
analyse financial data and assess risk.
• Lookup and Reference Functions: Functions like VLOOKUP and
HLOOKUP, which are crucial for retrieving data from large datasets.
• Date and Time Functions: Financial modeling often involves working
with dates and time. Functions like DATE, EDATE, and EOMONTH help us
manage financial timelines.
• Text Functions: Functions like CONCATENATE and TEXT are used for
formatting financial reports and statements
Building a Financial Model
[Link] the Objective: Clearly state the purpose of your
financial model. Are you analysing an investment
opportunity, forecasting future revenues, or assessing
project feasibility?
[Link] Data: Collect all relevant financial data, historical
records, and market research to be used as inputs into your
model.
[Link] Structure: Design the overall structure of your
financial model. Determine what variables and assumptions
will be included, and how they will interact with each other.
[Link] Assumptions: Identify and document the
assumptions you will make about future scenarios. E.g.,
growth rates, inflation, interest rates, and more.
Building a Financial Model
5. Data Cleaning: Cleanse the data of any errors or inconsistencies that
could affect your model's integrity.
6. Formulas and Functions: Apply appropriate formulas and functions
to calculate values within your model.
7. Sensitivity Analysis: Perform sensitivity analysis to assess how
changes in assumptions impact your model's outputs.
8. Documentation: Thoroughly document your model, including
assumptions, formulas, and data sources. This is crucial for transparency
and auditability.
9. Validation: Validate your model by comparing its outputs with
historical data or known results. Ensure that it produces accurate and
reliable predictions.
10. Presentation: Finally, present your model's findings in a clear and
comprehensible manner, often using charts, graphs, and summary
reports.
Data Cleaning and Preparation
• Data cleaning is a crucial step in the financial
modeling process.
• Significance: Ensuring that your dataset is clean and
error-free is vital for the accuracy and reliability of your
financial model.
• Data validation
• Outlier detection
• Handling missing data
• Standardization and normalization
• Data transformation
• Data integrity
• Data autiditing
Formulas for Financial Modeling
• Net Present Value (NPV): Calculates the PV of cash flows, accounting for the
time value of money.
• Internal Rate of Return (IRR): Determines the rate at which the NPV of cash
flows equals zero, representing the project's return.
• Discounted Cash Flow (DCF): Evaluates an investment's value by discounting
future cash flows.
• Profitability Index (PI): Measures the profitability of an investment by
comparing the present value of benefits to the present value of costs.
• Compound Annual Growth Rate (CAGR): Computes the annual growth rate of
an investment over multiple periods.
• Sensitivity Analysis: Assesses how changes in variables impact the model's
outputs.
• Break-Even Analysis: Identifies the point at which revenue equals costs,
resulting in neither profit nor loss.
• Regression Analysis: Establishes relationships between variables for forecasting
Functions for Financial Modeling
• PV: Calculates the present value of an investment or loan.
• FV: Computes the future value of an investment or loan.
• NPV: Evaluates the net present value of a series of cash flows.
• IRR: Determines the internal rate of return for cash flows.
• RATE: Calculates the periodic interest rate for an investment or loan.
• SUM: Adds a range of values.
• AVERAGE: Computes the average of a set of values.
• IF: Makes logical comparisons and returns different values based on
conditions.
• VLOOKUP: Searches for a value in a table and returns a corresponding
value.
• HLOOKUP: Similar to VLOOKUP but searches horizontally.
• MATCH: Searches for a specified value in a range and returns its position
Financial Function Examples
• Scenario 1: You're evaluating an investment in a new project. You
anticipate cash flows of 50,000 annually for five years, with an initial
investment of 150,000. The discount rate is 10%.
• Scenario 2: You have a potential investment with projected cash flows of
-20,000, 25,000, 30,000, 35,000, and 40,000 for five years. Determine
the IRR.
• Scenario 3: You're planning to take out a loan for 100,000 with an annual
interest rate of 5% over ten years. What will your annual payments be?
• Scenario 4: You're saving for retirement and deposit 5,000 annually into
an account with a 7% interest rate. How much will you have after 30
years?
