I am trying to model potential revenue from a solar array at various points in time. I am able to do this if I make one large chart, but would like to create a formula that takes all assumptions into account such as deflating annual production, an inflation factor on the cost of energy, and a point in time at which the price of energy will drop, but then continue at the inflation rate. Please see notes below:
Goal: write a formula which calculates the cumulative revenue from a solar system taking into account utility rate inflation, system degradation, and a tiered contract in which after x number of years the rate will drop to a specified amount and then continue with the normal inflation rate
The following formula works to calculate revenue taking system degradation into account, though lacks the ability to factor the utility inflation rate in. It seems like I need some kind of array formula for this.
Variables
[PPA Term] - Length of time the PPA $/kwh will be used
[Annual Prod (kWh)] - year 1 production before any degradation
[PPA $/kWh] - cost of energy during PPA term
[Annual System Degradation] - annual depreciation of kwh generation
[Total Term] - total length of time I am running the analysis over
[$/kWh Post PPA] - Cost of energy once the PPA has expired
Here is a visual example of what is happening with energy rates and energy production
The below table whos what I am trying to get. I do not want to pull the information off the table though. It needs to be a standalone function that simply takes the above variables into account
Goal: write a formula which calculates the cumulative revenue from a solar system taking into account utility rate inflation, system degradation, and a tiered contract in which after x number of years the rate will drop to a specified amount and then continue with the normal inflation rate
The following formula works to calculate revenue taking system degradation into account, though lacks the ability to factor the utility inflation rate in. It seems like I need some kind of array formula for this.
Excel Formula:
=IF([@[PPA Term]]>[@[Total Term]],
([@[Annual Prod (kWh)]]*[@[PPA $/kWh]])*(1-(1-[@[Annual System Degradation]])^[@[Total Term]])/(1-(1-[@[Annual System Degradation]])),
([@[Annual Prod (kWh)]]*[@[PPA $/kWh]])*(1-(1-[@[Annual System Degradation]])^[@[PPA Term]])/(1-(1-[@[Annual System Degradation]]))
+([@[Annual Prod (kWh)]]*[@[$/kWh Post PPA]])*(1-(1-[@[Annual System Degradation]])^[@[Total Term]])/(1-(1-[@[Annual System Degradation]]))
-([@[Annual Prod (kWh)]]*[@[$/kWh Post PPA]])*(1-(1-[@[Annual System Degradation]])^[@[PPA Term]])/(1-(1-[@[Annual System Degradation]])))Variables
[PPA Term] - Length of time the PPA $/kwh will be used
[Annual Prod (kWh)] - year 1 production before any degradation
[PPA $/kWh] - cost of energy during PPA term
[Annual System Degradation] - annual depreciation of kwh generation
[Total Term] - total length of time I am running the analysis over
[$/kWh Post PPA] - Cost of energy once the PPA has expired
Here is a visual example of what is happening with energy rates and energy production
The below table whos what I am trying to get. I do not want to pull the information off the table though. It needs to be a standalone function that simply takes the above variables into account
| Year | O&M Expense /OpEx | Loan Payment | Total Annual Expenses | Cumulative O&M | Cumulative Loan Payment | Cumulative Total Expenses | Solar Production (kWh) | $/kWh | TTL Energy Cost | REC | Total Annual Revenue | Annual Cash Flow | Cumulative Revenue |
| 1 | $ (38,500) | $ (458,364) | $ (496,864) | $ (38,500) | $ (458,364) | $ (496,864) | 5,146,000 | $ 0.12000 | $ 617,520 | - | $ 617,520 | $ 120,656 | $ 617,520 |
| 2 | $ (39,270) | $ (458,364) | $ (497,634) | $ (77,770) | $ (916,729) | $ (994,499) | 5,109,978 | $ 0.12084 | $ 617,490 | - | $ 617,490 | $ 119,855 | $ 1,235,010 |
| 3 | $ (40,055) | $ (458,364) | $ (498,420) | $ (117,825) | $ (1,375,093) | $ (1,492,919) | 5,074,208 | $ 0.12169 | $ 617,459 | - | $ 617,459 | $ 119,040 | $ 1,852,469 |
| 4 | $ (40,857) | $ (458,364) | $ (499,221) | $ (158,682) | $ (1,833,458) | $ (1,992,139) | 5,038,689 | $ 0.12254 | $ 617,429 | - | $ 617,429 | $ 118,208 | $ 2,469,898 |
| 5 | $ (41,674) | $ (458,364) | $ (500,038) | $ (200,356) | $ (2,291,822) | $ (2,492,178) | 5,003,418 | $ 0.12340 | $ 617,399 | - | $ 617,399 | $ 117,361 | $ 3,087,297 |
| 6 | $ (42,507) | $ (458,364) | $ (500,872) | $ (242,863) | $ (2,750,186) | $ (2,993,049) | 4,968,394 | $ 0.12426 | $ 617,369 | - | $ 617,369 | $ 116,497 | $ 3,704,666 |
| 7 | $ (43,357) | $ (458,364) | $ (501,722) | $ (286,220) | $ (3,208,551) | $ (3,494,771) | 4,933,615 | $ 0.12513 | $ 617,338 | - | $ 617,338 | $ 115,617 | $ 4,322,005 |
| 8 | $ (44,224) | $ (458,364) | $ (502,589) | $ (330,444) | $ (3,666,915) | $ (3,997,359) | 4,899,080 | $ 0.12600 | $ 617,308 | - | $ 617,308 | $ 114,719 | $ 4,939,313 |
| 9 | $ (45,109) | $ (458,364) | $ (503,473) | $ (375,553) | $ (4,125,280) | $ (4,500,833) | 4,864,786 | $ 0.12689 | $ 617,278 | - | $ 617,278 | $ 113,805 | $ 5,556,591 |
| 10 | $ (46,011) | $ (458,364) | $ (504,375) | $ (421,564) | $ (4,583,644) | $ (5,005,208) | 4,830,733 | $ 0.12778 | $ 617,248 | - | $ 617,248 | $ 112,872 | $ 6,173,839 |
| 11 | $ (46,931) | $ (458,364) | $ (505,296) | $ (468,496) | $ (5,042,008) | $ (5,510,504) | 4,796,918 | $ 0.12867 | $ 617,217 | - | $ 617,217 | $ 111,922 | $ 6,791,056 |
| 12 | $ (47,870) | $ (458,364) | $ (506,234) | $ (516,365) | $ (5,500,373) | $ (6,016,738) | 4,763,339 | $ 0.12957 | $ 617,187 | - | $ 617,187 | $ 110,953 | $ 7,408,243 |
| 13 | $ (48,827) | $ (458,364) | $ (507,192) | $ (565,193) | $ (5,958,737) | $ (6,523,930) | 4,729,996 | $ 0.13048 | $ 617,157 | - | $ 617,157 | $ 109,965 | $ 8,025,400 |
| 14 | $ (49,804) | $ (458,364) | $ (508,168) | $ (614,997) | $ (6,417,102) | $ (7,032,098) | 4,696,886 | $ 0.13139 | $ 617,127 | - | $ 617,127 | $ 108,959 | $ 8,642,527 |
| 15 | $ (50,800) | $ (458,364) | $ (509,164) | $ (665,797) | $ (6,875,466) | $ (7,541,263) | 4,664,008 | $ 0.13231 | $ 617,097 | - | $ 617,097 | $ 107,932 | $ 9,259,624 |
