Student Center | Instructor Center | Information Center | Home

Engineering Economy, 5/e

Leland Blank, Texas A&M University
Anthony Tarquin, University of Texas - El Paso

Factors: How Time and Interest Affect Money

Microsoft® Excel Spreadsheet Exercise #2

Contributed by
Dr. Jeffrey Adler, P. E.- MindBox, Inc.
(formerly of Civil Engineering Rensselaer Polytechnic Institute)

How to use this section: Each exercise requires the development of an Excel spreadsheet. The exercises are keyed to sections in the text Engineering Economy, 5th edition, by Blank and Tarquin. Appendix A of the text is a complete primer for using Excel and the financial functions pertinent to engineering economic analysis. Sample problems are included in this appendix for setting up each function.

The spreadsheet exercises presented here are especially well suited to an engineering economy course with laboratory sessions or activities that help a student become more adept with spreadsheet-based solutions.

Spreadsheet Exercise #2

Modeling Cash Flow Streams

Exercise Objective: Introduce you to modeling cash flows.

Blank and Tarquin Text Reference: Chapter 2 and Appendix A.

Problem Statement: Part 1 - Develop your version of an Excel worksheet that computes the present value of a cash flow sequence. The worksheet should use three global variables: interest rate, i; uniform gradient, G; and initial year cash flow, A0. Develop the spreadsheet for a total of 10 periods. The spreadsheet should compute PV three different ways:

Using the NPV function of Excel
Summing all the present values obtained using the P/F formula 1/(1+i)n
Cumulatively developing total PV by summing each year's PV amount

The example below illustrates these three approaches.

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=gif::Spreadsheet Exercise 2::/sites/dl/free/0072432349/22704/image001.gif','popWin', 'width=343,height=385,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif">Spreadsheet Exercise 2 (6.0K)</a>Spreadsheet Exercise 2

Helpful comment: Cell formulas used:

C11 = $B11 * (1+$D$4)^-$A11
D11 = $D10 + $B11

Part 2
Use the Excel spreadsheet developed in Part (1), but now for the global values A0 = $25,000; G = $5,000; i = 10%.

Part 3
Develop an Excel graph of the method 3 (cumulative summing) PV (y-axis) versus year (x-axis)

2002 McGraw-Hill Higher Education
Any use is subject to the Terms of Use and Privacy Policy.
McGraw-Hill Higher Education is one of the many fine businesses of The McGraw-Hill Companies.