You have been approached by a developer with South Carolina solar project. Your
ID: 2783614 • Letter: Y
Question
You have been approached by a developer with South Carolina solar project. Your manager wants to understand how attractive S.C. market could be or not the particular opportunity is feasible and meets the company’s investment criteria:
The Following part is the information regarding the project:
Investment Date: 2014
Commercial Operation Date: 2015
Life of Plant: 20 years (assume no terminal value)
Depreciation Assumptions: 5-year straight line depreciation (assume no salvage value)
Production per-year: 50,000 MWh
Price (you get for each MWh you deliver): $150/MWh (Price will be escalating at the rate of CPI)
CPI: 2.5%
Capital Expenditure: $40,000,000 (Cost of the Plant)
Operating Expenses: Operating expenses (COGS) are 25% of EBITDA for year 1 and escalating each year at CPI.
Investment Tax Credit: South Carolina provides 3% investment tax credit on the initial investment.
Taxes: 35%
First find the operating cash flows, investment and depreciation then the cash flow summary in order to calculate. Use Excel.
Based on the information provided, please calculate the IRR and NPV based on the after-tax cash flows and discount rate is 8%. What is your assessment?
Explanation / Answer
The initial capital investment i.e. FCinv = 40,000,000
but south carolina provides 3% investment tax credit on the initial investment thus the initial investment is FCinv*(1-tax)
=40,000,000*(1-0.03)
=40,000,000*(0.97)
=38,800,000
Now depreciation is for 5 years to zero and hence
=40,000,000/5
=8,000,000
Given COGS is 25% of EBITDA thus if sales is 100 and cost is 20 then EBITDA is 80 and so COGS makes 20/80=25% of EBITDA
Thus COGS = 20/100=20% of sales
After tax operating cashflow = (sales-COGS)*(1-tax)+(Tax*Depreciation)
But depreciation tax shield ie. (Tax*Depreciation) can only be gained for first 5 years
Terminal year after tax non operating cashflow = salvage+WCinv-Tax(salvage-book value)
=0+0-0.35(0-0)
=0
Lets see the spreadsheet below to calculate NPV:
Year
Sales=production*price (which will grow at 2.5%), thus formula will be for year 1: 50000*150 and year 2: 50000*150(1.025) and year 3: 50000*150(1.025)^2
COGS as earlier calculated is 20% of the sales in year 1 and increasing by 2.5% there after so year 2: year1*(1.025) and year 3: year2*(1.025)
Depreciation as calculated for first 5 years
Thus after tax cash flow=(sales-COGS)*(1-tax)+(tax*deprecition)
2014
0
-38800000
2015
1
7500000
1500000
8000000
6700000
2016
2
7687500
1537500
8000000
6797500
2017
3
7879688
1575938
8000000
6897438
2018
4
8076680
1615336
8000000
6999873
2019
5
8278597
1655719
8000000
7104870
2020
6
8485562
1697112
4412492
2021
7
8697701
1739540
4522804
2022
8
8915143
1783029
4635874
2023
9
9138022
1827604
4751771
2024
10
9366472
1873294
4870566
2025
11
9600634
1920127
4992330
2026
12
9840650
1968130
5117138
2027
13
10086666
2017333
5245066
2028
14
10338833
2067767
5376193
2029
15
10597304
2119461
5510598
2030
16
10862236
2172447
5648363
2031
17
11133792
2226758
5789572
2032
18
11412137
2282427
5934311
2033
19
11697440
2339488
6082669
2034
20
11989876
2397975
6234736
NPV
$16,999,742.22
IRR
14.33%
You can also calculate NPV by inserting all the cashflows in your financial calculator and pressing CPT NPV at 8%
and similarly for IRR by pressing CPT and then I/Y
The initial capital investment i.e. FCinv = 40,000,000
but south carolina provides 3% investment tax credit on the initial investment thus the initial investment is FCinv*(1-tax)
=40,000,000*(1-0.03)
=40,000,000*(0.97)
=38,800,000
Now depreciation is for 5 years to zero and hence
=40,000,000/5
=8,000,000
Given COGS is 25% of EBITDA thus if sales is 100 and cost is 20 then EBITDA is 80 and so COGS makes 20/80=25% of EBITDA
Thus COGS = 20/100=20% of sales
After tax operating cashflow = (sales-COGS)*(1-tax)+(Tax*Depreciation)
But depreciation tax shield ie. (Tax*Depreciation) can only be gained for first 5 years
Terminal year after tax non operating cashflow = salvage+WCinv-Tax(salvage-book value)
=0+0-0.35(0-0)
=0
Lets see the spreadsheet below to calculate NPV:
Year
Sales=production*price (which will grow at 2.5%), thus formula will be for year 1: 50000*150 and year 2: 50000*150(1.025) and year 3: 50000*150(1.025)^2
COGS as earlier calculated is 20% of the sales in year 1 and increasing by 2.5% there after so year 2: year1*(1.025) and year 3: year2*(1.025)
Depreciation as calculated for first 5 years
Thus after tax cash flow=(sales-COGS)*(1-tax)+(tax*deprecition)
2014
0
-38800000
2015
1
7500000
1500000
8000000
6700000
2016
2
7687500
1537500
8000000
6797500
2017
3
7879688
1575938
8000000
6897438
2018
4
8076680
1615336
8000000
6999873
2019
5
8278597
1655719
8000000
7104870
2020
6
8485562
1697112
4412492
2021
7
8697701
1739540
4522804
2022
8
8915143
1783029
4635874
2023
9
9138022
1827604
4751771
2024
10
9366472
1873294
4870566
2025
11
9600634
1920127
4992330
2026
12
9840650
1968130
5117138
2027
13
10086666
2017333
5245066
2028
14
10338833
2067767
5376193
2029
15
10597304
2119461
5510598
2030
16
10862236
2172447
5648363
2031
17
11133792
2226758
5789572
2032
18
11412137
2282427
5934311
2033
19
11697440
2339488
6082669
2034
20
11989876
2397975
6234736
NPV
$16,999,742.22
IRR
14.33%
You can also calculate NPV by inserting all the cashflows in your financial calculator and pressing CPT NPV at 8%
and similarly for IRR by pressing CPT and then I/Y
Year
Sales=production*price (which will grow at 2.5%), thus formula will be for year 1: 50000*150 and year 2: 50000*150(1.025) and year 3: 50000*150(1.025)^2
COGS as earlier calculated is 20% of the sales in year 1 and increasing by 2.5% there after so year 2: year1*(1.025) and year 3: year2*(1.025)
Depreciation as calculated for first 5 years
Thus after tax cash flow=(sales-COGS)*(1-tax)+(tax*deprecition)
2014
0
-38800000
2015
1
7500000
1500000
8000000
6700000
2016
2
7687500
1537500
8000000
6797500
2017
3
7879688
1575938
8000000
6897438
2018
4
8076680
1615336
8000000
6999873
2019
5
8278597
1655719
8000000
7104870
2020
6
8485562
1697112
4412492
2021
7
8697701
1739540
4522804
2022
8
8915143
1783029
4635874
2023
9
9138022
1827604
4751771
2024
10
9366472
1873294
4870566
2025
11
9600634
1920127
4992330
2026
12
9840650
1968130
5117138
2027
13
10086666
2017333
5245066
2028
14
10338833
2067767
5376193
2029
15
10597304
2119461
5510598
2030
16
10862236
2172447
5648363
2031
17
11133792
2226758
5789572
2032
18
11412137
2282427
5934311
2033
19
11697440
2339488
6082669
2034
20
11989876
2397975
6234736
NPV
$16,999,742.22
IRR
14.33%
You can also calculate NPV by inserting all the cashflows in your financial calculator and pressing CPT NPV at 8%
and similarly for IRR by pressing CPT and then I/Y
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.