8-A-i

APPENDIX 8-A. USER INSTRUCTIONS FOR LIFE-CYCLE COST AND 

PAYBACK PERIOD SPREADSHEETS

TABLE OF CONTENTS

8-A.1 STARTUP..............................................................................................................8-A-1

8-A.1.1 Electric Motors Worksheet Overview ...................................................................8-A-1

Summary Results...................................................................................................8-A-1

Life-Cycle Cost and Payback Calculation. ............................................................8-A-2

Definitions .............................................................................................................8-A-2

Rebuttable Payback................................................................................................8-A-2

Energy Use.............................................................................................................8-A-2

Equipment Price.....................................................................................................8-A-2

Sectors and Applications........................................................................................8-A-2

Energy Price...........................................................................................................8-A-2

Energy Price Trend ................................................................................................8-A-2

Discount Rate.........................................................................................................8-A-3

Lifetime ..............................................................................................................8-A-3

Base Case Efficiency Distribution.........................................................................8-A-3

Forecast Cells.........................................................................................................8-A-3

8-A.2 BASIC INSTRUCTIONS FOR OPERATING THE LIFE-CYCLE COST 

SPREADSHEETS..................................................................................................8-A-3
8-A-1

APPENDIX 8-A. USER INSTRUCTIONS FOR LIFE-CYCLE COST AND 

PAYBACK PERIOD SPREADSHEETS

To execute the life-cycle cost (LCC) spreadsheet, it is necessary for the user to have the 

appropriate hardware and software tools. The U.S. Department of Energy (DOE) assumed the 

user has a reasonably current computer operating under the Windows operating system. The 

development team uses relatively new systems and has not defined the minimum system 

requirements. At a minimum, users need Microsoft Excel to execute the spreadsheet. For full 

functionality in running Monte Carlo simulations, users will need a copy of a spreadsheet add-in 

called Crystal Ball, in addition to Excel. Without Crystal Ball, one can still use the LCC 

spreadsheet model, but will not be able to examine inputs and outputs as distributions. 

Approximate results are provided through a sample calculation that uses average values for the 

inputs and outputs, as displayed in the “Summary” worksheet.

8-A.1 STARTUP

The LCC spreadsheet is a stored Excel file. It can be found on the DOE website at 

http://www1.eere.energy.gov/buildings/appliance_standards/commercial/electric_motors.html. 

Open the file. (Each computer system will have a unique setup for loading a file. Users should 

refer to their software manuals if they have problems loading the spreadsheet file.) For users 

new to Excel and/or Crystal Ball, section 8.8.2 contains basic instructions for operating the LCC 

spreadsheets.

8-A.1.1 Electric Motors Worksheet Overview

LCC spreadsheet for electric motors contains the following worksheets:

Summary Results

This worksheet contains the input selections and the summary results tables of installed 

price, energy use, operating costs, LCC, and payback. 

The left-hand section of the worksheet, controlling the Monte Carlo simulation, provides 

a means to change the user and simulation options. Simulation options are used to set the 

electricity price trend and the number of trials for the Monte Carlo simulation. In addition, the 

user may select among several sensitivity scenarios, including varying the equipment price, retail 

discount factor, whether the calculations consider the effects of the cubic relation between speed 

and power requirement (RPM effect).

The right-hand section of the worksheet summarizes the mean LCC and payback period 

(PBP) values from the distribution results produced by the simulation. This is a reporting step –

values are not automatically updated. The results presented by DOE on this sheet were calculated 

using the default input values for electricity price trend, equipment price, retail discount factor, 

and no RPM effect.
8-A-2

LCC and Payback Calc (Life-Cycle Cost and Payback Calculation)

The spreadsheet reports the results of the calculation for the example scenario on the 

Summary worksheet. This example scenario allows users to produce provisional answers without 

performing a Monte Carlo simulation. The Summary worksheet of the LCC spreadsheet shows 

the results from this worksheet.

Definitions

This worksheet contains values used to populate the spreadsheet’s form elements.

Rebuttable Payback

This worksheet calculates and presents the rebuttable presumption payback period for 

each of the eight representative units.

Energy Use

This worksheet calculates the annual electricity use of the representative equipment

classes.

Equipment Price

This worksheet calculates the retail equipment price and total installed cost inputs for 

each representative unit. Inputs are derived from the baseline and incremental manufacturer costs 

of the engineering spreadsheet.

Sectors and Applications

This worksheet calculates the input data regarding sector, application, hours of operation, 

and motor loading for each representative unit.

Energy Price

This worksheet calculates retail electricity price distribution input data for industrial,

commercial, and agricultural sectors.

Energy Price Trend

This worksheet contains the price trends of electricity; this trend represents the growth 

rate of electricity prices relative to the price in 2010. DOE took price data and forecasts from the 

DOE Energy Information Administration (EIA)'s Annual Energy Outlook 2011(AEO 2011) and 

the American Recovery and Reinvestment Act AEO-release for the period up to year 2035. To 

estimate the trend after 2035, DOE followed past guidelines provided to the Federal Energy 
8-A-3

Management Program by EIA and used the average rate of change during 2025–2035 for 

electricity prices.

Discount Rate

This worksheet contains the discount rate analysis. 

Lifetime

This worksheet contains the distributions of the age (in years) for each representative unit

which equipment is retired from service. Motor lifetime is, in part, a function of the hours of 

operation.

Base Case Eff Dist (Base Case Efficiency Distribution)

Contains market efficiency distribution in the year the standard takes effect.

Forecast Cells

This worksheet contains the statistical results from the most recent simulation.

8-A.2 BASIC INSTRUCTIONS FOR OPERATING THE LIFE-CYCLE COST 

SPREADSHEETS

1. Once you have downloaded the LCC file from the Web, open the file using Excel. At the 

bottom, click on the tab for sheet “Summary.” 

2. Use Excel’s View/Zoom commands at the top menu bar to change the size of the display 

to make it fit your monitor.

3. You can interact with the spreadsheet by clicking choices or entering data using the 

graphical interface that comes with the spreadsheet. Select choices from the various userselectable options.

4. Click the “Run” button to run the simulation using DOE’s parameters.

To produce custom sensitivity results using directly Crystal Ball, select Run from the Run

menu (on the menu bar). To make basic changes in the Run sequence, including altering the 

number of trials, select Run Preferences from the Run menu. After each simulation run, the user 

needs to select Reset (also from the Run menu) before Run can be selected again. Once Crystal 

Ball has completed its run sequence, it will produce a series of distributions. Using the menu 

bars on the distribution results, it is possible to obtain further statistical information. The time 

taken to complete a run sequence can be reduced by minimizing the Crystal Ball window in 

Excel. A step-by-step summary of the procedure for running a distribution analysis is outlined 

below:

1. Find the Crystal Ball toolbar (at top of screen).

2. Click on Run from the menu bar.
8-A-4

3. Select Run Preferences and choose either Monte Carlo or Latin Hypercube.a Select 

number of Trials (DOE suggests 10,000).

4. To run the simulation, choose the following sequence (on the Crystal Ball toolbar): Run, 

Reset, Run

5. Now wait until the program informs you that the simulation is completed.

DOE provides the following instructions to view the output generated by Crystal Ball:

1. After the simulation has finished, click on the Windows tab bar labeled Crystal Ball to 

see the distribution charts.

2. The LCC savings and paybacks are defined as Forecast cells. The frequency charts 

display the results of the simulations, or trials, performed by Crystal Ball. Click on any 

chart to bring it into view. The charts show the low and high endpoints of the forecasts. 

The View selection on the Crystal Ball toolbar can be used to specify whether cumulative 

or frequency plots are to be shown.

2a. To calculate the probability that a particular value of LCC savings will occur, either 

type 0 in the box by the left arrow, or move the arrow key with the cursor to 0 on 

the scale. The value in the Certainty box shows the likelihood that the LCC savings 

will occur. 

2b. To calculate the certainty of the payback period being below a certain number of 

years, insert that value in the far-right box.

3. To generate a printed report, select Create Report from the Run menu. The toolbar 

choice of Forecast Windows allows you to select the charts and statistics in which you 

are interested. For further information on Crystal Ball outputs, refer to Understanding 

the Forecast Chart in the Crystal Ball manual.

 a

Because of the nature of the program, there is some variation in results due to random sampling when MonteCarlo 

or Latin Hypercube sampling is used.