Life Cycle Cost Spreadsheet Version 1.9, September, 2006, Copyright 2004 by Barringer & Associates, Inc., Humble, TX 77347, USA
For information contact Paul Barringer, P.E., Barringer & Associates, Inc. http://www.barringer1.com
[email protected]
This sheet is prepared for simple engineering calculations as an aid for making engineering decisions. Check with your local accountant for specific details before making financial decisions of great importance. Paul Barringer, P.E. Ph: 281-852-6810 FAX: 281-852-3749 Email:
[email protected] Website: http://www.barringer1.com
LCC Worksheet is a simple net present value calculation sheet for your use. LCC Example 1 is a short problem using Excel Solver (Tools/Solver) to find the maximum allowed capital expendit LCC Example 2 is a typical worksheet example for a single alternative.
USA
[email protected]
m allowed capital expenditure.
Life Cycle Cost Worksheet Discount Rate (%)--> 12% $0
Capital Costs:
<--Yellow Boxes Are For Data Input Project Life (35 yrs max)--> 20 Tax Provision (%)--> <--Net Present Value <--Internal Rate Of Return 0
1
2
3
Capital Acquisition Costs
Acquisition Costs: Program Management Costs Engineering Design Costs Engineering Data Costs Spare Parts & Logistics Costs Facilities & Construction Costs Initial Training Costs Technical Data Costs Documentation Costs Annual recuring costs Other periodic costs Disposal Costs
Savings: Annual Savings (use positive #s)
NPV & IRR Calculations:
0 1 2 3 Capital equipment $0 Costs $0 $0 $0 $0 Savings $0 $0 $0 Straight Line Depreciation $0 $0 $0 Profit Before Taxes $0 $0 $0 $0 Tax Provision @ 38% Of Profit Before Tax $0 $0 $0 $0 Net Income can be profit or loss $0 $0 $0 $0 Add Back Depreciation $0 $0 $0 Cash Flow (Net Income + Depreciation) $0 $0 $0 $0 Discount Factors @ 12% 1.0000 0.8929 0.7972 0.7118 Present Value $0 $0 $0 $0 Net Present Value $0 Internal Rate Return <--Requires at least one positive and one negative number in the present value ro Year-->
0
1
2
3
Are For Data Input Tax Provision (%)--> Internal Rate Of Return
38%
4
5
6
7
8
9
10
4
5
6
7
8
9
10
$0 $0 $0 $0 $0 $0 $0 $0 0.6355 $0
$0 $0 $0 $0 $0 $0 $0 $0 0.5674 $0
$0 $0 $0 $0 $0 $0 $0 $0 0.5066 $0
$0 $0 $0 $0 $0 $0 $0 $0 0.4523 $0
$0 $0 $0 $0 $0 $0 $0 $0 0.4039 $0
$0 $0 $0 $0 $0 $0 $0 $0 0.3606 $0
$0 $0 $0 $0 $0 $0 $0 $0 0.3220 $0
e number in the present value row 32 4
5
6
7
8
9
10
11
12
13
14
15
16
17
11
12
13
14
15
16
17
$0 $0 $0 $0 $0 $0 $0 $0 0.2875 $0
11
$0 $0 $0 $0 $0 $0 $0 $0 0.2567 $0
12
$0 $0 $0 $0 $0 $0 $0 $0 0.2292 $0
13
$0 $0 $0 $0 $0 $0 $0 $0 0.2046 $0
14
$0 $0 $0 $0 $0 $0 $0 $0 0.1827 $0
15
$0 $0 $0 $0 $0 $0 $0 $0 0.1631 $0
16
$0 $0 $0 $0 $0 $0 $0 $0 0.1456 $0
17
18
19
20
18
19
20
$0 $0 $0 $0 $0 $0 $0 $0 0.1300 $0
18
$0 $0 $0 $0 $0 $0 $0 $0 0.1161 $0
19
Exceeds Project Life
Exceeds Project Life
Exceeds Project Life
Exceeds Project Life
21
22
23
24
$0 $0 $0 $0 $0 $0 $0 $0 0.1037 $0
20
Exceeds Project Life
Exceeds Project Life
Exceeds Project Life
Exceeds Project Life
Exceeds Project Life
Exceeds Project Life
Exceeds Project Life
25
26
27
28
29
30
31
Exceeds Project Life
Exceeds Project Life
Exceeds Project Life
Exceeds Project Life
$0
32
33
34
35
Life Cycle Cost Worksheet Discount Rate (%)--> 12% $3,109,654
Capital Costs: Capital Acquisition Costs
<--Yellow Boxes Are For Data Input Project Life (35 yrs max)--> 20 Tax Provision (%)--> <--Net Present Value 25.00% <--Internal Rate Of Return 0 $3,497,367
1
2
3
4
Acquisition Costs: Program Management Costs Engineering Design Costs Engineering Data Costs Spare Parts & Logistics Costs Facilities & Construction Costs Initial Training Costs Technical Data Costs Documentation Costs Annual recuring costs Other periodic costs Disposal Costs
Savings: Annual Savings (use positive #s)
$1,319,500
$1,319,500
$1,319,500
$1,319,500
NPV & IRR Calculations:
0 1 2 3 4 Capital equipment $3,497,367 Question: How much capital can we afford to Costs $0 $0 $0 a 20 year $0 $0 discount rate Given project life, 12% Savings $1,319,500 $1,319,500 $1,319,500 $1,319,500 Hint: Straight Line Depreciation $174,868 $174,868 $174,868 1) Input annual savings$174,868 of $1,319,500 in the y Profit Before Taxes $0 $1,144,632 $1,144,632 $1,144,632 $1,144,632 2) Put $1 in cell D5 for initializing the calculati Tax Provision @ 38% Of Profit Before Tax $0 -$434,960 -$434,960 -$434,960 -$434,960 Set the iterative process for Goal Seek to Net Income can be profit or loss $0 $709,672 $709,672 $709,672 $709,672 change calculations to Automatic, place c Add Back Depreciation $174,868 $174,868 $174,868 3) Click on$174,868 cell F3 for IRR and notice that it re Cash Flow (Net Income + Depreciation) -$3,497,367 $884,540 $884,540 $884,540 $884,540 Excel needs to work on a formula where a Discount Factors @ 12% 1.0000 0.8929 0.7972 0.7118click on 0.6355 4) Click on Tools, Goal Seek, Present Value -$3,497,367 $789,768 $705,150 $629,598 $562,141 this is where set cell: C34 <--remember to value: 25% <--you can also use 0.25 Net Present Value $3,109,654 bynegative changing cell: D5. start withrow $1 Internal Rate Return 25.00% <--Requires at least one positive and one number in the<--we present value
Click OK and Goal Seek will adjust cell By convention, the IRR is based on cash flow
38%
te Of Return 5
$1,319,500 5
6
$1,319,500 6
7
$1,319,500 7
8
$1,319,500 8
9
$1,319,500 9
10
$1,319,500 10
11
$1,319,500
12
$1,319,500
11
h capital can we afford to spend on a project if we save $1,319,500/year? $0rate, and the $0 internal requirment $0 $025% internal $0 rate of return? $0 ect life, 12% discount$0 rate, 38% tax for a
12
$0 $1,319,500 $1,319,500 $1,319,500 $1,319,500 $1,319,500 $1,319,500 $1,319,500 $1,319,500 $174,868 $174,868 $174,868 $174,868 $174,868 $174,868 ngs of $1,319,500 in the yellow boxes of $174,868 row 19 for each of the 20$174,868 years. $1,144,632 $1,144,632 $1,144,632 $1,144,632 $1,144,632 $1,144,632 $1,144,632 $1,144,632 for initializing the calculations using Excel Goal Seek Tool to find how much capital we can afford. -$434,960 -$434,960 -$434,960 by-$434,960 -$434,960 -$434,960 -$434,960 -$434,960 process for Goal Seek to perform more accurately clicking on Tools, Options, Calculation, and $709,672 $709,672 $709,672 $709,672 $709,672 $709,672 $709,672 $709,672 ons to Automatic, place check mark in Iterations, change Max Iterations = 1000: Max Change = 0.0001 $174,868 $174,868 $174,868 $174,868 $174,868 $174,868 $174,868 $174,868 or IRR and notice that it refers to the formula in cell C34--this is an important clue because $884,540 $884,540 $884,540 $884,540 $884,540 $884,540 $884,540 $884,540 work on a formula where as cell F3 only reports the results. 0.5674 0.5066 0.4523 0.4039 0.3606 0.3220 0.2875 0.2567 ick on Goal Seek, $501,912 $448,135 $357,251 $318,974 $284,798 $254,284 $227,039 <--remember this is where the formula is $400,121 located
<--you can also use 0.25 l: D5. start withrow $132 to begin the iterative calculations er in the<--we present value Goal Seek will adjust cell D5 = ~$3,497,367 to meet the IRR = 25% with NPV = ~$3,109,654. RR is based on cash flow (row 30) rather than the present values (row 32).
n?
001
13
$1,319,500 13 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.2292 $202,714
14
$1,319,500 14 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.2046 $180,994
15
$1,319,500 15 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.1827 $161,602
16
$1,319,500 16 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.1631 $144,288
17
$1,319,500 17 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.1456 $128,828
18
$1,319,500 18 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.1300 $115,025
19
$1,319,500 19 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.1161 $102,701
20
$1,319,500 20 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.1037 $91,697
###
Life Cycle Cost Worksheet <--Yellow Boxes Are For Data Input Discount Rate (%)--> 12% Project Life (yrs)--> 20 Tax Provision (%)--> 38% -$125,465 <--Net Present Value <--Internal Rate Of Return
Capital Costs: Capital Acquisition Costs
Acquisition Costs: Program Management Costs Engineering Design Costs Engineering Data Costs Spare Parts & Logistics Costs Facilities & Construction Costs Initial Training Costs Technical Data Costs Documentation Costs Annual recuring costs Other periodic costs Disposal Costs
0 $75,000
1
2
3
4
5
Given a 20 year project life, 12% discount rate, and 38% tax rate. Equipment cost $75,000. Annual sustaining cost = $12,264/yr. Overhaul cos costs = $5,000 in year 20.
What is the net present value of the project? NPV=($125,465) and must b to find the most favorable NPV (this often means selection of the LEAST nega have two negative NPV's from two alternatives, you would select the least neg positive NPV "delta" between the alternatives.
$12,264
$12,264
$12,264
$12,264
$12,264
Savings: Annual Savings (use positive #s)
NPV & IRR Calculations:
0 1 2 3 4 5 Capital equipment $75,000 Costs $0 $12,264 $12,264 $12,264 $12,264 $12,264 Savings $0 $0 $0 $0 $0 Straight Line Depreciation $3,750 $3,750 $3,750 $3,750 $3,750 Profit Before Taxes $0 -$16,014 -$16,014 -$16,014 -$16,014 -$16,014 Tax Provision @ 38% Of Profit Before Tax $0 $6,085 $6,085 $6,085 $6,085 $6,085 Net Income can be profit or loss $0 -$9,929 -$9,929 -$9,929 -$9,929 -$9,929 Add Back Depreciation $3,750 $3,750 $3,750 $3,750 $3,750 Cash Flow (Net Income + Depreciation) -$75,000 -$6,179 -$6,179 -$6,179 -$6,179 -$6,179 Discount Factors @ 12% 1.0000 0.8929 0.7972 0.7118 0.6355 0.5674 Present Value -$75,000 -$5,517 -$4,926 -$4,398 -$3,927 -$3,506 Net Present Value -$125,465 Internal Rate Return <--Requires at least one positive and one negative number in the present value row 32
6
7
8
9
10
11
12
13
14
15
t rate, and 38% tax rate. ng cost = $12,264/yr. Overhaul cost = $20,000 in year 10. Disposal
oject? NPV=($125,465) and must be compared to other alternatives means selection of the LEAST negative NPV). Remember when you atives, you would select the least negative, and you will then have a ves.
$12,264
6 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.5066 -$3,130
$12,264
7 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.4523 -$2,795
mber in the present value row 32
$12,264
8 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.4039 -$2,495
$12,264
9 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.3606 -$2,228
$12,264 $20,000
10 $32,264 $0 $3,750 -$36,014 $13,685 -$22,329 $3,750 -$18,579 0.3220 -$5,982
$12,264
11 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.2875 -$1,776
$12,264
12 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.2567 -$1,586
$12,264
13 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.2292 -$1,416
$12,264
14 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.2046 -$1,264
$12,264
15 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.1827 -$1,129
16
$12,264
17
$12,264
18
$12,264
19
$12,264
20
$12,264 $5,000
16 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.1631 -$1,008
17 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.1456 -$900
18 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.1300 -$803
19 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.1161 -$717
20 $17,264 $0 $3,750 -$21,014 $7,985 -$13,029 $3,750 -$9,279 0.1037 -$962