Tough compounding problem

[bobsuruncle]

I am building a spreadsheet for a solar project and am having some difficulty computing production accurately when taking into account the decline of the panel's performance.

Typically a panel will lose ~0.5% of its production versus 12 months prior. My spreadsheet is calculating on a quarterly basis.

Let's assume a solar panel that is rated at 100 watts of production in a month. For simplicity, degradation starts at the beginning Jan 1 max production.

So for the month of January the 100 watts of ideal production becomes [1-((1+0.005)^(1/12)-1)]*100 = 99.96 watts.

Then for February it is [1-((1+0.005)^(2/12)-1)]*100 = 99.92

For March it is [1-((1+0.005)^(3/12)-1)]*100 = 99.88 watts

In total for the period I would add those three to get 299.75 watts

But if I want to make the calculation once every 3 months, what is the formula? If I use March's 99.88%x100x3 I am penalizing the production by calculating 299.62 for a difference of 0.13. That doesn't sound like much but over time the compounding difference adds up.

Any help would be greatly appreciated!

 

[AlphaFrog]

Try something like this...

=((1-((1+0.005)^(1/12)-1))+(1-((1+0.005)^(2/12)-1))+(1-((1+0.005)^(3/12)-1)))*100


Or this where A1 is the 1st month of a quarter

=((1-((1+0.005)^(A1/12)-1))+(1-((1+0.005)^((A1+1)/12)-1))+(1-((1+0.005)^((A1+2)/12)-1)))*100

 

[RonB1111]

Try:
=SUM((1-((1+0.005)^({1,2,3}/12)-1))*100)

Or:
A simpler formula for March only would be:
=100*(1-0.005/12*3)

so, for the 1st qtr, use:
=SUM(100*(1-0.005/12*{1,2,3}))

 

[bobsuruncle]

Thanks to you both. I will give this a try tonight.

Cheers!