McGraw-Hill OnlineMcGraw-Hill Higher EducationLearning Center
Student Center | Instructor Center | Information Center | Home
Glossary
Web Links
Solving FE Problems
Learning Objectives
Chapter Overview
Spreadsheet Exercise #10
Chapter Review T/F Quiz
Matching Quiz
FE Exam Prep Quiz
Feedback
Help Center


Engineering Economy, 5/e
Leland Blank, Texas A&M University
Anthony Tarquin, University of Texas - El Paso

Effects of Inflation

Microsoft® Excel Spreadsheet Exercise #10

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 #10

Modeling Cash Flow Streams with Adjustment for Inflation

Exercise Objective: Introduce you to modeling cash flow streams with inflation.

Blank and Tarquin Text Reference: Chapter 14

Problem #1 Statement - Cash flow modeling: Consider your salary over the next 5 years. We will examine your earnings with respect to inflation. (You can start with the spreadsheet from Exercise #2 and revise it as needed.)

The global variables are:

  • A(0) - initial year salary
  • g - expected geometric escalation (percent increase per year)
  • if - inflation-adjusted (market) interest rate
  • f - expected inflation rate per year

Part 1:
Use the initial values of the global variables shown in the following spreadsheet image and compute the following:

  1. Interest rate not adjusted for inflation (use the real interest rate)
  2. Equivalent salary in today's (real) dollars per year
  3. Present Worth using NPV function and using the equivalent PW per year.
<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=gif::Present Values Spreadsheet::/sites/dl/free/0072432349/22720/ex10.gif','popWin', 'width=460,height=401,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif">Present Values Spreadsheet (7.0K)</a>Present Values Spreadsheet

Helpful comment: Cell formulas used:

Inflation Free Interest Rate: D10 = (D7-D8)/(1+D8)
C17 = PV($D$7,A17,0,-B17)
D17 = PV($D$8,A17,0,-B17,0)
E17 =PV($D$10,A17,0,-D17)

Use the following initial values to answers parts 2 and 3:

A(0) = $50,000, g = 6%, if= 10%, f = 2%

Part 2: Create the spreadsheet
Print out the spreadsheet with the initial values and the spreadsheet that shows the formulas. Explain in your own words how to interpret the 4 columns of the spreadsheet.

Part 3: Prepare a chart
Create a line graph of actual dollars vs. today's (real) dollars over the five year period for the initial values in part 2. Develop graphs for f = 2%, 6%, and 10% per year. Explain each graph. Comment on the purchasing power gained and/or lost in comparison to the initial year salary.

Problem #2 Statement- Consider the investment problem in exercise #6. Take the set of mutually exclusive alternatives derived for this exercise. Let the MARR = 12% per year represent the interest rate without adjustment for inflation. Do the following:

Part 1: Modify spreadsheet to account for inflation
Set up a spreadsheet that can be used to evaluate these alternatives considering the effects of inflation.

Part 2: PW Analysis of Alternatives

  1. Assume that the interest rate without inflation is 12%. Perform a PW analysis if inflation is expected to be 2% per year over the planning horizon. Which alternative is best?
  2. If inflation is expected to be 6%, which alternative is best?

Part 3: Sensitivity Analysis
Create a graph that depicts PW (y-axis) vs. f (inflation rate) for an inflation-free MARR of 12% for all mutually exclusive alternatives. Which projects are best for the different values of f between 1% and 10% per year?