FIN3130 Financial Modeling
Lesson 2c
Time Value of Money
Chapter 6
1
(6) The Financial Analysis of
Leasing
1.
2.
3.
4.
5.
6.
A Simple Example
The Equivalent-Loan Method
Rationale
Calculating the highest acceptable Lease
Rental
Setting the accuracy of the
FIN3130 Financial Modeling
VBA 02
For-Next Loop 1
1
Warm-up Exercise
Write a program called Lesson02p01 that produces the
following table using 3 lines of assignment statements.
A
1
2
3
1
2
3
In VBA, Rnd produces a uniformly distributed random
number in [
FIN3130 Financial Modeling
VBA 03
For-Next Loop 2
1
Warm-up Exercise
Write a program called Lesson03p01 that produces the
following table of random numbers using a For-Next loop
A
B
C
for each column.
1
2
3
4
5
6
0.50795
0.826454
0.653081
0.200194
0.60243
FIN3130 Financial Modeling
Lesson 11c
Chapter 17
1
(17) The Binomial OptionPricing Model
1.
2.
3.
4.
5.
What is BOPM
N-period BOPM: Example
Computing using Worksheet, VBA Sub,
VBA Function
Computing without using trees
Non Standard Options
2
What is the B
FIN3130 Financial Modeling
Lesson 07 Worksheet
Getting Data from the Internet
I am with group _ today.
The group members are:
1) _
2) _
3) _
4) _
5) _
6) _
Learning Objective
This series of activities brings the group through learning how to get data from
FIN3130 Financial Modeling
Lesson 8c
Chapter 19
1
(19) The Black-Scholes Model
1.
Implementation of the Black-Scholes
formula
2.
In spreadsheet
Through VBA
Implied Volatility
In spreadsheet
Through VBA
2
The Black Scholes Formula
C SN d1 Xe
rT
N d2
wher
FIN3130 Financial Modeling
Lesson 8b
Chapter 16
1
(16) Introduction to Options
1.
2.
3.
4.
Payoff diagrams
Option strategies
Arbitrage strategies
Put-Call Parity
2
Here are examples of options traded on Oct 3 2006:
A
B
E
F
G
H
I
J
Stock
Option
Stock
Optio
FIN3130 Financial Modeling
Lesson 6c
Chapter 15
1
(15) Value at Risk
1.
What is VaR (Normal Distribution)
1.
2.
3.
2.
3.
4.
Plotting the distribution
Probability of loss
Loss given probability
The Lognormal Distribution
What is VaR (Lognormal Distribution
FIN3130 Financial Modeling
Lesson 06 Worksheet
Do-While/Until Loop, Black Litterman Model, VaR
I am with group _ today.
The group members are:
1) _
2) _
3) _
4) _
5) _
6) _
Learning Objective
This series of activities brings the group through learning the
FIN3130 Financial Modeling
Lesson 1c
Chapter 35, 33
1
(33, 35) Basic Excel Functions
1.
2.
3.
Solver
Regression
Financial Functions
2
Solver
Used after a model is set up in the
worksheet.
The Solver finds the input parameter that
yields a specific require
On 6 February 2010 ABN AMRO Bank N.V. (registered with the Dutch Chamber of Commerce under number 33002587) changed
its name to The Royal Bank of Scotland N.V. and on 1 April 2010 ABN AMRO Holding N.V. changed its name to
RBS Holdings N.V. and all referen
FIN3130 Financial Modeling
VBA 04
If-then-else 1
1
Warm-up Exercise
Write a program called Lesson04p01 that produces a
column of random numbers (using Rnd function) in cells
A1:A100
Without erasing the output of the previous program, write
a program calle
FIN3130 Financial Modeling
VBA 05
If-then-else 2
1
Warm-up Exercise
Clien
t
Service
Media Value
Non
1
Online 25,000
Advisory
2
Advisory Phone 25,000
Non
3
Phone 90,000
Advisoryrecords of brokerage clients
Copy the above transaction
in cells A1 to D5. Advi
Burger King Holdings Inc.
Assumptions
Income statement
Sales growth
COGS as % of sales
Depreciation as % of beginning fixed assets
SG&A as % of sales
Effective tax rate
Dividend payout rate
Weighted average shares outstanding
Assets
Operating current asse
Burger King Holdings Inc.
Assumptions
Income statement
Sales growth
COGS as % of sales
Depreciation as % of beginning fixed assets
SG&A as % of sales
Effective tax rate
Dividend payout rate
Weighted average shares outstanding
Assets
Operating current asse
Burger King Holdings Inc.
Assumptions
Income statement
Sales growth
COGS as % of sales
Depreciation as % of beginning fixed assets
SG&A as % of sales
Effective tax rate
Dividend payout rate
Weighted average shares outstanding
Assets
Operating current asse
Burger King Holdings Inc.
Assumptions
Income statement
Sales growth
COGS as % of sales
Depreciation as % of beginning fixed assets
SG&A as % of sales
Effective tax rate
Dividend payout rate
Weighted average shares outstanding
Assets
Operating current asse
Burger King Holdings Inc.
Assumptions
Income statement
Sales growth
COGS as % of sales
Depreciation as % of beginning fixed assets
SG&A as % of sales
Effective tax rate
Dividend payout rate
Weighted average shares outstanding
Assets
Operating current asse
Burger King Holdings Inc.
Assumptions
Income statement
Sales growth
COGS as % of sales
Depreciation as % of beginning fixed assets
SG&A as % of sales
Effective tax rate
Dividend payout rate
Weighted average shares outstanding
Assets
Operating current asse
Units
Unit price
10
11
12
13
14
15
16
17
18
265
280
295
310
325
340
Shortcuts
Ctrl +
C
X
V
R
D
Z
Y
Editing
Ctrl + +
Ctrl + -
insert selection
delete selection
Ctrl+Enter
F2; Ctrl+Enter
Multiple fill
Multiple fill from active cell
Shift + space
Ctrl + spac
FIN3130 Financial Modeling
Lesson 7: Getting data from
the Internet
Chapter 41
1
(41) Information from the Web
1.
2.
3.
4.
5.
6.
7.
Copy and Paste
Dynamic Web Queries
Web Queries: The iqy Files
Parametric Web Pages
Web Queries: Parameters
Web Queries: CSV
How to Train a Dragon (The Ancient Chinese Style)
?
Legend says that the Beggar Sect in ancient China was
the custodian of a very powerful martial art, called the
Eighteen Palms that Subdue the Dragon. Anyone who
masters this martial art, would become ver
FIN3130 Financial Modeling
Lesson 8d
Pricing of Structure
Products
Chapter 19 1
Structured Product 1: Principal
Protection + Participation in
Suppose
Upside
Let S&P500 index be S0 now and ST 5 years
later.
Initial amount = $1,000
No interest payments with
FIN3130 Financial Modeling
Lesson 5d
Portfolio Management 2
Chapter 12
1
(12) Efficient Portfolios without
Short Sales
1.
2.
3.
GMVP w/o short sales
MVP given required return w/o short
sales
Efficient Frontier w/o short sales
1.
VBA Program to Create the
FIN3130 Financial Modeling
Lesson 5e
Portfolio Management 2
Chapter 10
1
(10) Alternative VarianceCovariance Matrices
Alternatives to Exact
Var-Cov
Single Index, Constant Corr, Shrinkage
2
Motivation
The Var-Cov matrix obtained empirically may not be the
FIN3130 Financial Modeling
Lesson 02 Worksheet
Time Value of Money
I am with group _ today.
The group members are:
1) _
2) _
3) _
4) _
5) _
6) _
Learning Objective
This series of programs leads one to discover several time value of money related models us
FIN3130 Financial Modeling
Lesson 2b
Time Value of Money
Chapter 1
1
(1) Basic Time Value Models
Summary
Loan Tables
Retirement Problem
2
Model C1: Loan Table
Given
The loan amount, interest rate, # periods.
Find
The required payment per period (in order
FIN3130 Financial Modeling
Lesson 1d
Chapter 35, 33
1
(33, 35) Basic Excel Functions
1.
2.
3.
4.
if, sumif, sumifs, countif, countifs
Time and Date functions
Index
Offset
2
The IF Function
The If function returns a value based on a
condition.
Format:
IF(l