1
School of Sciences and Engineering
ENGR 345/3222 - Engineering Economy
Fall 2019
Chapter 2
Using Spreadsheets for
Engineering Economy Applications
2
Single Payments (P & F), and Uniform Series
If some of the parameters don’t apply to a particular problem, they can be omitted
and zero is assumed. If the parameter omitted is an interior one, the comma must be
entered. The last two functions require that a series of numbers be entered into
contiguous spreadsheet cells, but the first five can be used with no supporting data.
3
Examples
Example on finding A given P: Example on finding F given P:
I put $5,000 in a bank today. At 5% I put $5,000 in a bank today. At 7%
interest, how much should I interest, how much money will be
withdraw every year (starting next in the account after 3 years?
year) to completely deplete the
account after 5 years?
Solution Solution
on Excel on Excel
Notice: Notice:
4
Examples
Example on finding P given F:
At i=10%, how much do I have to
deposit today if I want my account
to have $200 after 4 years?
Solution
on Excel
Notice:
5
What if we have a cash flow with
multiple payments? Getting P
Finding the present value P:
Example on finding P for a cash flow with multiple payments:
What is the equivalent present worth of the following cash flow?
P = ?? i = 8% per year
$50 $50 $50
1 2 4
0
3 5
$25
$50
$100 years
6
What if we have a cash flow with
multiple payments? Getting P
Finding the present value P:
Solution
Answer: The cash flow is
equivalent to negative
$114.77 at year 0
7
What if we have a cash flow with
multiple payments? Getting F
Finding the future value F and the annual equivalent A:
Unlike in the P, Microsoft Excel does not have an equation to get the
equivalent F or A for a complex cash flow diagram. In this case, we
use the NPV function to convert the entire cash flow diagram to an
equivalent present worth P, then use FV function to convert the P to
F, or use the PMT function to convert the P to A.
Example on finding F for a cash flow with multiple payments:
What is the equivalent future worth F of the following cash flow?
F = ??
$2,000 i = 6%
$1,000
3
0 1 2 4 5
$1,000 years
8
What if we have a cash flow with
multiple payments? Getting F
Solution
First, get the equivalent P using the NPV function, then convert it to F using the FV function.
P=X
$2,000 F = ??
i = 6% i = 6%
$1,000 $X
3
Then
0 1 2 4 5 0 1 2 3 4 5
$1,000 years years
9
What if we have a cash flow with
multiple payments? Getting A
For the same cash flow (Slide 7), what is the equivalent
annual payments A?
Solution: First, get the equivalent P using the NPV function, then convert it to A using
the PMT function.
10
Notice:
The PV, FV, and PMT functions always display the answer with
the opposite sign entered on the entered cash flow. Take this
into consideration while you interpret the results. Some people
prefer to put a negative sign before the function so that the
results come out with the correct sign (such as in slide 8 and 9)
- For example, here, the future equivalent of $5,000 after 3 years at
7% interest is +ve $6,125 not -ve $6,125. (the brackets mean
negative)
11
Arithmetic and Gradient Series
- There is no direct, single-cell spreadsheet function to
calculate P or A for an arithmetic or geometric gradient.
Use the NPV function to display P and the PMT function
to display A after entering all cash flows (base and
gradient amounts) into contiguous cells. General
formats for these functions are:
12
More options:
- Will be discussed later in upcoming lectures:
13
Exercise:
- Solve all of the examples in the previous PowerPoint
slides using Microsoft Excel functions.