Based on the excel sheet attached, can you please help me and explain to me how to calculate the the payback period and IRR based on the cost SAVINGS? There are three items- the traditional, the alternative, and the LED to be compared. Additionally, I will need to repeat the analysis using 3% of capital and 10% cost of capital.

Many thanks!!

A B C D E F G H 1 GoGreen Supercenter - Energy Savings and Parking Lot Lighiting System Case 2 LED Lighting Cost Analysis 3 Cost of capital 6% 5 Luminaire Prices 6 Typical 41,000 7 Alternate 51,000 8 LED 94,000 9 10 Total Pole Luminaire Total 11 Total Installation Costs (including Luminaire costs) # of Poles Per Pole Cost Prices Costs 12 Typical 23 $ 1,600 36,800 41,000 77,800 13 Alternate 33 $ 1,600 S 52,800 S 51,000 S 103,800 14 LED 33 $ 1,600 52,800 94,000 S 146,800 15 16 Annual Annual 17 Total Electricity Costs kWI $/kWh Costs 18 Typical 233,000 $0.09 $ 20,970.00 19 Alternate 161,000 $0.09 $ 14,490.00 20 LED 98,000 $0.09 S 8,820.00 21 22 23 Annual 24 Annual Maintenance Costs Costs 25 Typical 3,476.00 26 Alternate 5,476.00 27 LED 1,840.00 28 29 30 t 0 1 2 3 31 Typical 32 Total Installation costs S 77,800 Cost of Capital 6% Cost of Capital 3% Cost of Capital 10% + Ready $2 Accessibility: Good to goA B C D E F G H J K L M N P 29 30 t 0 1 2 3 5 6 7 8 9 10 31 Typical 32 Total Installation costs $ 77,800 33 Annual electricity costs $ 20,970.00 $ 20,970.00 $ 20,970.00 $ 20,970.00 $ 20,970.00 $ 20,970.00 $ 20,970.00 $ 20,970.00 $ 20,970.00 $ 20,970.00 34 Annual maintenance costs S 3,476.00 $ 3,476.00 $ 3,476.00 $ 3,476.00 $ 3,476.00 $ 3,476.00 3,476.00 3,476.00 $ 3,476.00 $ 3,476.00 35 Total Life Cycle costs S 77,800 $ 24,446 24,446 $ 24,446 $ 24,446 $ 24,446 $ 24,446 $ 24,446 S 24,446 $ 24,446 S 24,446 36 37 NPV (Typical) $ 257,724.69 =NPV(D3,F35:035)+E35 38 IRR(Typical) 39 40 t 0 1 2 3 5 6 7 8 9 10 41 Alternate 42 Total Installation costs $ 103,800 43 Annual electricity costs $ 14,490.00 $ 14,490.00 $ 14,490.00 $ 14,490.00 $ 14,490.00 $ 14,490.00 $ 14,490.00 $ 14,490.00 $ 14,490.00 $ 14,490.00 44 Annual maintenance costs S 5,476.00 $ 5,476.00 $ $ 5,476.00 $ 5,476.00 $ 5,476.00 $ 5,476.00 $ 5,476.00 $ 5,476.00 $ 5,476.00 $ 5,476.00 45 Total Life Cycle costs S 103,800 $ 19,966 $ 19,966 $ 19,966 $ 19,966 $ 19,966 $ 19,966 19,966 $ 19,966 $ 19,966 $ 19,966 46 47 NPV (Alternate) $ 250,751.50 =NPV(D3,F45:045)+E45 48 49 50 0 1 2 3 5 6 7 8 9 10 51 LED 52 Total Installation costs $ 146,800 53 Annual electricity costs $ 8,820.00 $ 8,820.00 $ 8,820.00 $ 8,820.00 $ 8,820.00 $ 8,820.00 $ 8,820.00 $ 8,820.00 $ 8 8,820.00 $ 8,820.00 54 Annual maintenance costs 1,840.00 $ 1,840.00 $ 1,840.00 $ 1,840.00 1,840.00 $ 1,840.00 $ 1,840.00 $ 1,840.00 $ 1,840.00 $ 1,840.00 55 Total Life Cycle costs $ 146,800 $ 10,660 S 10,660 S 10,660 $ 10,660 $ 10,660 $ 10,660 $ 10,660 10,660 $ 10,660 $ 10,660 56 57 NPV (LED) $ 225,258.53 =NPV(D3,F55:055)+855 58 59 Cost of Capital 6% Cost of Capital 3% Cost of Capital 10% +