Lecture Notes for
Class 13: Using Excel for Financial Analysis
Objective:
The aim of this class
is to understand how to apply Excel for financial modeling and analysis,
enabling students to make informed financial decisions and assessments.
Topics:
1. Financial
Functions in Excel
Excel provides various
built-in financial functions that help analyze financial scenarios and make
calculations related to investments, loans, and cash flows.
a. Present Value
(PV):
- Definition: The present value function calculates the
current value of a future sum of money or stream of cash flows, discounted
at a specific interest rate.
- Formula: =PV(rate, nper, pmt, [fv], [type])
- rate: Interest rate for each period.
- nper: Total number of payment periods.
- pmt: Payment made each period (if any).
- fv: Future value (optional).
- type: When payments are due (0 = end of period, 1 = beginning).
Example: To find the present value of receiving $1,000
in 5 years at an annual interest rate of 5%:
=PV(5%, 5, 0, 1000)
b. Future Value
(FV):
- Definition: The future value function calculates the
amount of money that an investment will grow to after a specified period
at a given interest rate.
- Formula: =FV(rate, nper, pmt, [pv], [type])
Example: To find out how much $1,000 will grow to in 5
years at an annual interest rate of 5%:
=FV(5%, 5, 0, -1000)
c. Net Present
Value (NPV):
- Definition: The NPV function calculates the present
value of a series of cash flows, taking into account a specified discount
rate.
- Formula: =NPV(rate, value1, [value2], ...)
Example: To calculate the NPV of cash flows of $200,
$300, and $400 with a discount rate of 5%:
=NPV(5%, 200, 300, 400)
d. Internal Rate of
Return (IRR):
- Definition: The IRR function calculates the internal
rate of return for a series of cash flows, which is the discount rate that
makes the NPV of those cash flows equal to zero.
- Formula: =IRR(values, [guess])
Example: To calculate the IRR for an investment with
cash flows of -$1,000 (initial investment), $300, $400, and $500 over the next
three years:
=IRR(A1:A4) // Where A1 to A4 contain the cash flows.
2. Creating
Financial Models
a. Budgeting:
- Definition: Budgeting is the process of creating a
plan to spend your money. It involves forecasting future financial
outcomes based on historical data and expected income and expenses.
- Steps to Create a Budget Model:
- Identify Income Sources: List all expected income (salary,
investments, etc.).
- Estimate Expenses: Include fixed costs (rent, bills) and
variable costs (food, entertainment).
- Create a Summary: Use Excel to create a summary of total
income, total expenses, and the resulting net income.
Example of a Simple
Budget Model:
|
A |
B |
|
Income |
|
|
Salary |
$3,000 |
|
Investments |
$500 |
|
Total Income |
=SUM(B2:B3) |
|
Expenses |
|
|
Rent |
$1,200 |
|
Utilities |
$300 |
|
Groceries |
$400 |
|
Entertainment |
$200 |
|
Total Expenses |
=SUM(B6:B9) |
|
Net Income |
=B4-B10 |
b. Forecasting:
- Definition: Forecasting involves predicting future
financial outcomes based on historical data and trends.
- Steps to Create a Forecast Model:
- Gather Historical Data: Collect past financial data (sales,
expenses).
- Analyze Trends: Identify patterns in the data.
- Create Forecasts: Use Excel functions like FORECAST to
predict future values.
Exercise:
- Build a Simple Financial Model for
Budgeting:
- Use the structure provided above to
create your own budgeting model.
- Populate your income and expense
categories with realistic numbers and calculate your net income.
- Use Financial Functions to Analyze
Investment Scenarios:
- Analyze a hypothetical investment by:
- Calculating the present value of
expected future cash inflows.
- Calculating the future value of an
investment after a set period.
- Evaluating the net present value of a
series of cash flows.
- Determining the internal rate of return
for a proposed investment.
Conclusion
By mastering Excel’s
financial functions and creating effective financial models, students will be
able to perform financial analysis, budget effectively, and make informed
investment decisions. These skills are crucial for personal finance management
and professional roles in finance and accounting.
