5.7: Chapter Appendixes

Last updated
Save as PDF

Page ID: 150479

$ \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $

$ \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} $

$ \newcommand{\dsum}{\displaystyle\sum\limits} $

$ \newcommand{\dint}{\displaystyle\int\limits} $

$ \newcommand{\dlim}{\displaystyle\lim\limits} $

$ \newcommand{\id}{\mathrm{id}}$ $ \newcommand{\Span}{\mathrm{span}}$

( \newcommand{\kernel}{\mathrm{null}\,}\) $ \newcommand{\range}{\mathrm{range}\,}$

$ \newcommand{\RealPart}{\mathrm{Re}}$ $ \newcommand{\ImaginaryPart}{\mathrm{Im}}$

$ \newcommand{\Argument}{\mathrm{Arg}}$ $ \newcommand{\norm}[1]{\| #1 \|}$

$ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$

$ \newcommand{\Span}{\mathrm{span}}$

$ \newcommand{\id}{\mathrm{id}}$

$ \newcommand{\Span}{\mathrm{span}}$

$ \newcommand{\kernel}{\mathrm{null}\,}$

$ \newcommand{\range}{\mathrm{range}\,}$

$ \newcommand{\RealPart}{\mathrm{Re}}$

$ \newcommand{\ImaginaryPart}{\mathrm{Im}}$

$ \newcommand{\Argument}{\mathrm{Arg}}$

$ \newcommand{\norm}[1]{\| #1 \|}$

$ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$

$ \newcommand{\Span}{\mathrm{span}}$ $ \newcommand{\AA}{\unicode[.8,0]{x212B}}$

$ \newcommand{\vectorA}[1]{\vec{#1}} % arrow$

$ \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow$

$ \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $

$ \newcommand{\vectorC}[1]{\textbf{#1}} $

$ \newcommand{\vectorD}[1]{\overrightarrow{#1}} $

$ \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} $

$ \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} $

$ \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $

$\newcommand{\longvect}{\overrightarrow}$

$ \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} $

$\newcommand{\avec}{\mathbf a}$ $\newcommand{\bvec}{\mathbf b}$ $\newcommand{\cvec}{\mathbf c}$ $\newcommand{\dvec}{\mathbf d}$ $\newcommand{\dtil}{\widetilde{\mathbf d}}$ $\newcommand{\evec}{\mathbf e}$ $\newcommand{\fvec}{\mathbf f}$ $\newcommand{\nvec}{\mathbf n}$ $\newcommand{\pvec}{\mathbf p}$ $\newcommand{\qvec}{\mathbf q}$ $\newcommand{\svec}{\mathbf s}$ $\newcommand{\tvec}{\mathbf t}$ $\newcommand{\uvec}{\mathbf u}$ $\newcommand{\vvec}{\mathbf v}$ $\newcommand{\wvec}{\mathbf w}$ $\newcommand{\xvec}{\mathbf x}$ $\newcommand{\yvec}{\mathbf y}$ $\newcommand{\zvec}{\mathbf z}$ $\newcommand{\rvec}{\mathbf r}$ $\newcommand{\mvec}{\mathbf m}$ $\newcommand{\zerovec}{\mathbf 0}$ $\newcommand{\onevec}{\mathbf 1}$ $\newcommand{\real}{\mathbb R}$ $\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}$ $\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}$ $\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}$ $\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}$ $\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}$ $\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}$ $\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}$ $\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}$ $\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}$ $\newcommand{\laspan}[1]{\text{Span}\{#1\}}$ $\newcommand{\bcal}{\cal B}$ $\newcommand{\ccal}{\cal C}$ $\newcommand{\scal}{\cal S}$ $\newcommand{\wcal}{\cal W}$ $\newcommand{\ecal}{\cal E}$ $\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}$ $\newcommand{\gray}[1]{\color{gray}{#1}}$ $\newcommand{\lgray}[1]{\color{lightgray}{#1}}$ $\newcommand{\rank}{\operatorname{rank}}$ $\newcommand{\row}{\text{Row}}$ $\newcommand{\col}{\text{Col}}$ $\renewcommand{\row}{\text{Row}}$ $\newcommand{\nul}{\text{Nul}}$ $\newcommand{\var}{\text{Var}}$ $\newcommand{\corr}{\text{corr}}$ $\newcommand{\len}[1]{\left|#1\right|}$ $\newcommand{\bbar}{\overline{\bvec}}$ $\newcommand{\bhat}{\widehat{\bvec}}$ $\newcommand{\bperp}{\bvec^\perp}$ $\newcommand{\xhat}{\widehat{\xvec}}$ $\newcommand{\vhat}{\widehat{\vvec}}$ $\newcommand{\uhat}{\widehat{\uvec}}$ $\newcommand{\what}{\widehat{\wvec}}$ $\newcommand{\Sighat}{\widehat{\Sigma}}$ $\newcommand{\lt}{<}$ $\newcommand{\gt}{>}$ $\newcommand{\amp}{&}$ $\definecolor{fillinmathshade}{gray}{0.9}$

The following appendices provide hands-on guidance for applying time value of money concepts from Chapter 5. They focus on spreadsheet modeling best practices, responsible use of AI support tools, and step-by-step workflows for a BA II Plus financial calculator.

Appendix 5A: Using Spreadsheets for Financial Problem-Solving

1. Why Spreadsheets Matter in Finance

Spreadsheets such as Microsoft Excel and Google Sheets are core tools in finance because they allow you to build transparent models, test assumptions quickly, and document your logic. In a professional setting, the goal is not only to compute an answer, but to create an analysis that is auditable, repeatable, and easy to update when assumptions change.

Auditable: Another person (or your future self) can trace how an output was produced by following labeled inputs, formulas, and links without guesswork.
Repeatable: If the same inputs are used, the spreadsheet produces the same outputs every time. Results do not change because of hidden assumptions, manual overrides, or inconsistent formulas.
Easy to update: Assumptions can be changed in clearly marked input cells without rewriting formulas or breaking the model.

1.1 Excel and Google Sheets: What Advanced Students Should Know

Real-time collaboration: Shared workbooks support teamwork and review when files are clearly structured and protected.
Dynamic arrays and modern formulas: Functions such as SORT, FILTER, and UNIQUE reduce manual errors and improve transparency.
Power Query and Power Pivot (Excel): Tools for importing and cleaning large datasets, a common source of modeling error.
Data Analysis ToolPak (Excel): Provides regression and statistical tools used in applied finance and econometrics.
AI integration: Analyze Data and Copilot can accelerate exploration but do not replace financial judgment.

1.2 Practical Applications in Finance

Financial modeling: Pro forma statements, DCF valuation, and scenario analysis.
Budgeting and forecasting: Translating assumptions into projected financial statements.
Investment analysis: NPV, IRR, payback, and sensitivity analysis.
Loan amortization: Payment schedules using PMT, IPMT, and PPMT.
Data visualization: Charts and dashboards to communicate results.

2. Spreadsheet Math and Referencing Basics

Table 5A.1 – Spreadsheet Math Operators and Common Finance Uses

Operation	Symbol	Example	Common Use
Addition	+	`=A1+B1`	Summing cash flows
Subtraction	-	`=A1-B1`	Net cash flow
Multiplication	*	`=A1*B1`	Growth rates
Division	/	`=A1/B1`	Ratios
Exponentiation	^	`=A1^B1`	Compounding

3. Model Structure for Upper-Level Finance Work

Inputs: Clearly labeled assumptions.
Calculations: Formulas only, no hard-coded values.
Outputs: Interpretable results.

Modeling Tip: Use named ranges for key inputs to improve readability and reduce errors.

4. Time Value of Money and Valuation Functions

FV

=FV(rate, nper, pmt, pv, type)

PV

=PV(rate, nper, pmt, fv, type)

NPV

=NPV(rate, CF1:CFn) + CF0

IRR

=IRR(values, [guess])

NPER

=NPER(rate, pmt, pv, [fv], [type])

Advanced Note: Use XNPV(rate, values, dates) and XIRR(values, dates, [guess]) when cash flows are irregular.

Appendix 5C: Solving Annuities with the TI BA II Plus Calculator

This appendix demonstrates annuity setup and highlights common errors. Each scenario reinforces alignment between calculator inputs and spreadsheet logic.

Scenario 1: Monthly Savings (Annuity Due)

Deposit $200 monthly for 5 years at 6% APR, compounded monthly.

N = 5 × 12 = 60
I/Y = 6 ÷ 12 = 0.50
PMT = −200
PV = 0

FV ≈ $14,023.78

Simulated calculator inputs showing N equals 60, I/Y equals 0.50, PMT equals negative 200, PV equals 0, with future value computed as approximately 14,023.78. — **Figure 5C.1 – Simulated Calculator Inputs for Monthly Savings.** Values shown above each TVM key correspond to the problem inputs.

Table 5C.1 – Excel Inputs for FV Calculation (Annuity Due)

rate	NPER	PMT	PV	type
0.50,	60,	−200,	0,	1

=FV(0.50, 60, -200, 0, 1)

Search

Text Color

Text Size

Margin Size

Font Type