Tiered Savings Calculator for Solar Lease

[trebeh]

My wife and I are researching solar power systems and are trying to consolidate our bids into one spreadsheet. I'm stuck with with how to build a formula that shows savings by company by tier. Here's what I mean...

Our power company charges us by tiers. Much like income taxes, the first Tier is charged at a lower rate than the second tier. The second is lower than the third. And the third is lower than the fourth. Here's an example...

(For this question assume we use 1094 kw per month.)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Tier Range[/TD]
[TD]Tier Usage[/TD]
[TD]Rate[/TD]
[TD]Cost[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tier 1[/TD]
[TD]0 to 336[/TD]
[TD]336[/TD]
[TD]$0.17[/TD]
[TD]$57.12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tier 2[/TD]
[TD]337 to 437[/TD]
[TD]101[/TD]
[TD]$0.20[/TD]
[TD]$20.20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tier 3[/TD]
[TD]437 to 672[/TD]
[TD]235[/TD]
[TD]$0.40[/TD]
[TD]$94.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tier 4[/TD]
[TD]672 to 1094[/TD]
[TD]422[/TD]
[TD]$0.42[/TD]
[TD]$177.24[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Additionally, each Solar company is offering a different solar rate and system size. For example...

Company A - 722kw per month at $0.147 per kw
Company B - 760kw per month at $0.18 per kw
Company C - 607kw per month at $0.15 per kw

Here's where I get stuck. I want to apply the solar companies' rates to our usage in reverse (by tier) and calculate savings. Here's an example...

If Company A can produce 722 kw per month, then my Tier 4 usage of 422 kw is at $0.147 per kw (instead of $0.42) and my Tier 4 cost drops from $177.24 (422 x .42) to $62.03 (422 x .147). I then have 300 kw remaining (722 - 422 = 300) to apply to Tier 3 and Tier 2.

Since Tier 3 usage is 235 kw, I have enough remaining solar (300) to cover all of that tier and my cost drops from $94 (235 x 0.40) to $34.55 (235 x .147).

That still leaves me with 65 kw of solar for Tier 2. But since my Tier 2 usage is 101, I will apply the solar rate of $0.147 to 65kw and the remaining usage of 36 kw will get charged the current rate of $0.20.

So, it will look something like this...

[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]Tier Range[/TD]
[TD]Tier
Usage[/TD]
[TD]SDGE
Rate[/TD]
[TD]SDGE
Cost[/TD]
[TD]Company A
722 kw/mo
Usage[/TD]
[TD]Co. A
Rate[/TD]
[TD]Co. A
Cost[/TD]
[TD]New
SDGE
Usage[/TD]
[TD]New
SDGE
Cost[/TD]
[TD]New
Total[/TD]
[/TR]
[TR]
[TD]Tier 1[/TD]
[TD]0 to 336[/TD]
[TD]336[/TD]
[TD]$0.17[/TD]
[TD]$57.12[/TD]
[TD]0[/TD]
[TD]$0.147[/TD]
[TD]$0.00[/TD]
[TD]336[/TD]
[TD]$57.12[/TD]
[TD]$57.12[/TD]
[/TR]
[TR]
[TD]Tier 2[/TD]
[TD]337 to 437[/TD]
[TD]101[/TD]
[TD]$0.20[/TD]
[TD]$20.20[/TD]
[TD]65[/TD]
[TD]$0.147[/TD]
[TD]$9.56[/TD]
[TD]36[/TD]
[TD]$7.20[/TD]
[TD]$16.76[/TD]
[/TR]
[TR]
[TD]Tier 3[/TD]
[TD]437 to 672[/TD]
[TD]235[/TD]
[TD]$0.40[/TD]
[TD]$94.00[/TD]
[TD]235[/TD]
[TD]$0.147[/TD]
[TD]$34.55[/TD]
[TD]0[/TD]
[TD]$0.00[/TD]
[TD]$34.55[/TD]
[/TR]
[TR]
[TD]Tier 4[/TD]
[TD]672 to 1094[/TD]
[TD]422[/TD]
[TD]$0.42[/TD]
[TD]$177.24[/TD]
[TD]422[/TD]
[TD]$0.147[/TD]
[TD]$62.03[/TD]
[TD]0[/TD]
[TD]$0.00[/TD]
[TD]$62.02[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD]1094[/TD]
[TD][/TD]
[TD]$348.56[/TD]
[TD]722[/TD]
[TD][/TD]
[TD]$106.14[/TD]
[TD]372[/TD]
[TD]$64.32[/TD]
[TD]$170.46[/TD]
[/TR]
</tbody>[/TABLE]


How do I build a formula (or table) that will automatically calculate the savings for each company by Tier that accounts for different solar system sizes and solar rates? I would like the last 4 columns (highlighted in red) to calculate automatically.

Ultimately, I just want to be able to type in a solar system rate and size and have it tell me what my new bill (by tier) will be.

Thanks for the help.

Tom