Table Of Contentintroductory
Financial Economics
with Spreadsheets
introductory
Financial Economics
with Spreadsheets
Cornelis v an d e P anne
Department o f E conomics
University o f C algary
Canada
he harwood a cademic p ublishers
ap Australia • C anada • C hina • F rance • G ermany • I ndia • J apan
Luxembourg • M alaysia • T he N etherlands • R ussia • S ingapore • S witzerland
Copyright© 1999 OPA (Overseas Publishers Association) :-.LV. Published by l icense under t he
Harwood A cademic Publishers imprint, p art of T he G ordon and Breach Publishing G roup.
i\11 rights reserved.
No p art o f t his book m ay b e reproduced or u tilized in any form or b y any means, electronic
or m echanical, including p hotocopy a nd r ecording, or b y a ny information storage o r r etrieval
system, without p ermission in writing f rom the publisher.
Amsteldijk 166
1st Floor
1079 LH Amsterdam
The Netherlands
British L ibrary Cataloguing i n P ublication D ata
A catalogue record for this book is available from the British Library.
ISBN 90-5702-363-6
Microsoft, A utoSum, Excel, Office and \ 'Vindows are either r egistered trademarks or
trademarks of M icrosoft C orporation i n the United States and/or o ther c ountries.
Contents
Preface ............................................................................ i x
Acknowledgements ............................................................... x i
part 1 Spreadsheets a nd Budgets
Chaptert Spreadsheet B ackground and Basics
l.l The Essence o f Spreadsheets .......................................... 2
1.2 The Background o f Spreadsheets ..................................... 3
1.3 The Spreadsheet at S tartup ........................................... 4
1.4 A Travel Expense Claim ............................................... 6
1.5 Formulas ............................................................ 7
1.6 File Operations ...................................................... 8
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.1
. . . . . . . . . . . . . . . . . . . .
Assignments ........................................................ 1 1
Chapter2 Simple Budgets w ith Spreadsheets
2.1 A Simple B udget S preadsheet ........................................ 1 3
2.2 Changing Columns and Rows . . . . . . . . . . . . . . . . . . . . . . . . . 1. 5. . . . . . . . . . . . .
2.3 Ranges a nd Commands . . . . . . . . . . . . . . . . . . . . . . .. .. . .. .. . . . . . .1 .7 . . . . . . .
2.4 Cell F ormatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1. 7. . . . . . . . . . . . . . . . . .
2.5 Moving and C opying . ............................................... 2 0
2.6 Copying Formulas .................................................. 2 4
Exercises ........................................................... 2 5
Assignments ........................................................ 2 6
Chapter3 A Spreadsheet for a Project Budget
3.1 Case S tudy Data a nd R equirements .................................. 2 9
3.2 Spreadsheet Organization ........................................... 3 0
3.3 Workbook and Sheets ............................................... 3 2
3.4 Building the P roject B udget S preadsheet ............................. 3 3
3.5 Cell a nd R ange N ames .............................................. 3 5
3.6 Printing the S preadsheet ............................................ 3 6
3.7 Worksheet Protection ............................................... 3 7
3.8 Data and Results .................................................... 3 7
Exercises ........................................................... 3 8
Assignments ........................................................ 3 9
Chapter 4 Graphs and Charts
4.1 Introduction ....................................................... 4 1
4.2 TheSimpleColumnChart .......................................... 4 1
4.3 Group Column Charts .............................................. 4 6
4.4 Bar C harts .......................................................... 4 8
Chapter 4 Graphs a nd Charts (continued)
4.5 Line C harts ......................................................... 4 9
4.6 Scatter Charts . ...................................................... 5 2
4. 7 Pie C harts .......................................................... 5 3
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5.4 .
. . . . . . . . . . . . . . . . . . .
Assignments ........................................................ 5 4
part2 B asic C oncepts o f f inancial Economics
Chapter 5 Calculations w ith Interest R ates
5.1 Simple I nterest . ..................................................... 5 8
5.2 Compound Interest ................................................. 5 9
5.3 The Effect o f Changing the Compounding Period .................... 62
5.4 The Future Value of Repeated Constant Payments .................... 6 5
5.4.1 Explicit S preadsheet Calculation . .................................... 6 5
5.4.2 Algebraic C alculation ............................................... 6 6
5.4.3 Spreadsheet Formula Calculation . ................................... 6 7
5.4.4 Annual Payment .................................................... 6 8
5.5 Spreadsheet Features ................................................ 6 8
5.5.1 The Fill Command . ................................................. 6 8
5.5.2 Creative Copying ................................................... 6 8
5.5.2.1 Cumulative Sum .................................................... 7 0
5.5.2.2 First D ifference ..................................................... 7 0
5.5.2.3 Arithmetic a nd G eometric Series .................................... 7 0
Exercise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7 1.
. . . . . . . . . . . . . . . . . . . .
Assignment . ........................................................ 7 1
Chapter6 Mortgage Loans
6.1 Loans w ith O ne Repayment ......................................... 7 2
6.2 Loans w ith C onstant Repayments ................................... 7 4
6.3 Loans w ith C onstant Total Payments . ................................ 7 5
6.4 Mortgage Loans .................................................... 7 6
6.5 Monthly Mortgage Payments ........................................ 8 0
6.6 The Term o f a Mortgage Loan ....................................... 8 1
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8.4 .
. . . . . . . . . . . . . . . . . . .
Assignments ........................................................ 8 4
Chapter7 Present V alues of P eriodic A mounts
7.1 Discounting and P resent Value ...................................... 8 6
7.2 The Present Value o f Study Allowances a nd Annuities ................. 8 8
7.3 Overview of Constant Cash Flows, a nd Related
Formulas and Contracts .......................................... 9 2
7.4 Retirement Pensions ................................................ 9 5
Exercises ........................................................... 9 8
Assignments ........................................................ 9 8
part 3 P roject E valuation
Chapter 8 Basic C oncepts of P roject Evaluation
8.1 Introduction ...................................................... 1 02
8.2 The Net Present Value o f a Cash Flow ............................... 1 03
8.3 Application to a R eal Estate P roject ................................. 1 04
8.4 The Appropriate Interest Rate for Discount Purposes ................ 1 06
8.5 The Internal Rate o f Return ........................................ 1 07
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .0 9. . . . . . . . . . . . . . . . . . . . 1
Assignments . ...................................................... 109
Chapter 9 Cash Flow A nalysis
9 .1. Discounted C ash Flow ............................................. 1 13
9.2. Cash B alances ..................................................... 1 15
9.3. ProjectFinancing .................................................. 1 16
Assignments . ...................................................... 1 19
Chapter 1 0 Sensitivity A nalysis and Data T ables
10.1 Sensitivity Analysis ................................................ 1 21
10.2 The D ata, Table C ommand . ........................................ 1 24
10.3 Sensitivity Analysis using the Data, Table Command ................ 1 27
10.4 Loans and Sensitivity Analysis ...................................... 1 30
Exercises .......................................................... 1 33
Assignments . ...................................................... 1 33
Chapter 1 1 Evaluation of R esource Extraction Projects
11.1 Oil and Gas Production ............................................ 1 3 7
11.2 Revenues a nd C osts ................................................ 1 38
11.3 An Oil P rospect Evaluation . . . . . . . . . . . . . . . . . . . . . . . . . . 1.4 0. . . . . . . . . . . . .
Exercises .......................................................... 145
part 4 D atabase M anagement A pplications
Chapter 1 2 Basic Database T ools
12.1 Introduction ...................................................... 1 48
12.2 DataForm:ADatabaseEditingTool ................................ 1 49
12.3 SortingData . ...................................................... 1 50
12.4 Filtering Records .................................................. 1 52
12.5 Criteria Range a nd Advanced Filter ................................. 1 54
Exercises .......................................................... 157
Assignment ....................................................... 1 57
Chapter13 looking u p Data
13.1 ThePasteFunctionMenu .......................................... 1 58
13.2 The VLOOKUP F unction .......................................... 1 60
13.3 Looking Up D atabase Information ................................. 1 61
Chapter 1 3 Looking u p Data (continued)
13.4 A Lookup f or a P ricing Schedule .................................... 1 62
13.5 Piecewise L inear F unction a nd I ncome Tax ............. . .... . ...... 163
Exercises .......................................................... 1 65
Assignment ........... . .................................... . ...... 165
Chapter 1 4 Frequencies a nd Quantiles
14.1 Frequency Distributions f or V alues .......... . ...................... 166
14.1.1 Bin R ange ......................................................... 1 67
14.1.2 TheHistogramAdd-ln . ............................................ 1 68
14.1.3 The F requency Function ........................................... 1 69
14.2 Rank a nd Percentrank .......................... ................... 1 71
14.3 The Median, Q uartiles, and P ercentiles ............................. 1 72
14.4 Percentile B ased G roups ........................................... 174
14.5 Frequency Distributions f or Labels ................................. 1 75
Exercises .......................................................... 1 76
Assignment .............. . .... . ................................... 1 76
Chaptef15 Statistical Functions for Databases
15.1 Gas Holdings a nd S tatistical F unctions ............... . ............. 178
15.2 Database S tatistical F unctions ............ . .......... . .............. 180
15.3 Label Frequency Distribution ...................... . ............... 1 82
15.4 A Comparison of Averages ......................................... 1 84
15.5 Derivation of the Gas Supply Curve . ................................ 185
Exercises .......................................................... 1 88
Assignments . ...................................................... 1 88
Chapter 1 6 Investment Portfolio Evaluation
16.1 Date and T ime F unctions ............................... . .......... 1 90
16.2 Investment Evaluation f or Non-Integral P eriods .................... 1 91
16.3 Final H oldings f or I nvestment P ortfolio ........... . .... . ........... 1 93
16.4 Evaluation o f the I nvestment P ortfolio .............................. 1 95
Exercises ............................... . ......... . ................ 1 98
Assignments ...................................... ................. 1 99
Index ............................... . .......... . .... . .................... . ..... 202
Preface
For more than a decade, spreadsheets have been the principal tool for performing
tasks r elated t o m anagement, a dministration, a nd e conomics. S preadsheet c ommands
and f unctions h ave b een d esigned w ith t hese t asks i n m ind.
Manuals and books explaining spreadsheets concentrate on computational
details without any background in f inance o r economics, on which the corresponding
spreadsheets features a re based. O n the other hand, there exist a large n umber ofbooks
dealing w ith f inance and e conomics, w hich h ave been written u nder t he a ssumption
that t he r eader w ill h ave a t m ost a c alculator a vailable f or calculations.
This book l inks both approaches, and d emonstrates and explains both spread
sheet features and financial economics b ackground b y p resenting a large variety of
basic applications i n b usiness a nd e conomics. I t integrates t he s preadsheet t ool i n the
explanation o f the m aterial, s o that t he r eader b ecomes f amiliar w ith b oth.
The book is, in the first p lace, i ntended for p ersons who are using the basic features
of the M icrosoft® E xcel s preadsheet b ut wish to e xtend t heir u se of the s preadsheet f or
a variety of b usiness and personal applications. For example, engineers who have
some management functions may wish to u se the concepts o f engineering economics
in terms of spreadsheets, and accountants who are familiar with economic
evaluations, may wish to use the advanced spreadsheets tools to enhance their
analysis. But also persons w ithout m uch o f a background i n business or e conomics,
who are using s preadsheets c an u tilize the b ook t o obtain a n u nderstanding o f and a
capability to work with spreadsheet functions and commands related to business
and e conomics.
Secondly, the material can be used in courses in business and economics. The
sixteen chapters, divided into four parts, provide ample material for at least one
course. S ome c hapters m ay a lso be u sed a s supplements f or certain o ther c ourses. T he
notes o n w hich t he b ook i s based w ere originally d eveloped f or a c ourse o n c omputer
applications i n e conomics.
ix
