Need to derive fixed rate of decline. I know initial value, nper, & sum of "principal"

I have solar panels with a known expected output for year 1, and a total aggregate expected output by end of year 20. This total is < year 1 * 20 since the panels degrade over time. I was able to find the fixed rate of their decline using an array and trial & error, but I'd like to know if there's a better way to solve this type of problem with a function or a math formula. thanks!

The formula I listed above assumes that decline is in fixed amount and not a rate

If the quantity, or money amount decrease by a fixed rate then you would have to make use of iterative methods to solve for such rate

An alternate option is to use Excel GRADIENT function whereby passing it the PV, NPER, FV, and 0% as interest rate and have it find the constant rate of increase or decrease

Thank you for your suggestion. I think I implemented your formula properly, but the result, -22.85., is not meaningful to me. Perhaps you can help illuminate further? My initial input is 4718, my aggregate output is 90,018. (The 20th year's output is 4290.) By my methods, I come up with a 0.449% decline / year. Thanks again.

Originally Posted by ronden

Thank you for your suggestion. I think I implemented your formula properly, but the result, -22.85., is not meaningful to me. Perhaps you can help illuminate further? My initial input is 4718, my aggregate output is 90,018. (The 20th year's output is 4290.) By my methods, I come up with a 0.449% decline / year. Thanks again.

Well as I stated in my second reply that I noticed your figures were declining by a constant RATE whereas the formula I listed in my first reply applies when figures decline by a constant AMOUNT.

And as I stated in my second reply that when figures are declining by a constant RATE a different equation is used which we are unable to solve using algebraic manipulation and we have to make use of iterative methods to find the growth or decline rate.


Here is the equation for this purpose

Initial Output = io
Aggregate Total = at
Decline rate = d

io (1-(1-d)^n) / (1-(1-d)) = at

or

-at + io (1-(1-d)^n) / (1-(1-d)) = 0

As it is clear looking at this equation that we are unable to solve for d using rules of algebra thus we have to use iterative methods

Here is a solution to your problem by using Newton Raphson method

guess = 10%
N = 20
Initial Output (io) = 4718
Aggregate Toral (at) = 90018

-AT + IO [1-(1-d)^20] / [1-(1-d)] = 0

f(i) = -90018 + 4718 x [1-(1-d)^20] / [1-(1-d)]

fn = 4718 x [1-(1-d)^20]
fn' = 4718 x [20*(1-d)^19]
fd = [1-(1-d)]
fd' = 1

f'(i) = 0 + ( fd * fn' - fn * fd') / fd^2

i0 = 0.1
f(i1) = -48573.9866
f'(i1) = -286973.7663
i1 = 0.1 - -48573.9866/-286973.7663 = -0.0692628116935
Error Bound = -0.0692628116935 - 0.1 = 0.169263 > 0.000001

i1 = -0.0692628116935
f(i2) = 101848.9073
f'(i2) = -2092740.6061
i2 = -0.0692628116935 - 101848.9073/-2092740.6061 = -0.0205950948223
Error Bound = -0.0205950948223 - -0.0692628116935 = 0.048668 > 0.000001

i2 = -0.0205950948223
f(i3) = 25298.7472
f'(i3) = -1149778.2815
i3 = -0.0205950948223 - 25298.7472/-1149778.2815 = 0.00140805798306
Error Bound = 0.00140805798306 - -0.0205950948223 = 0.022003 > 0.000001

i3 = 0.00140805798306
f(i4) = 3090.3887
f'(i4) = -881408.6116
i4 = 0.00140805798306 - 3090.3887/-881408.6116 = 0.00491425101937
Error Bound = 0.00491425101937 - 0.00140805798306 = 0.003506 > 0.000001

i4 = 0.00491425101937
f(i5) = 63.9869
f'(i5) = -845179.1121
i5 = 0.00491425101937 - 63.9869/-845179.1121 = 0.00498995914645
Error Bound = 0.00498995914645 - 0.00491425101937 = 7.6E-5 > 0.000001

i5 = 0.00498995914645
f(i6) = 0.0289
f'(i6) = -844414.5405
i6 = 0.00498995914645 - 0.0289/-844414.5405 = 0.00498999342676
Error Bound = 0.00498999342676 - 0.00498995914645 = 0 < 0.000001

Decline rate = 0.49900%

As you noted it finds your decline rate of 0.499%

Now as I stated earlier that rather than inventing your own solutions to solve this problem, you may want to use this Excel GRADIENT function which uses a totally different equation and is a multi-purpose financial function that finds an arithmetic or geometric gradient.

And using tadGRADIENT I get a result of -0.499% the minus sign denoting a decline rate rather than an incline rate.

RATE	0%
TAXRATE	0%
NPER	20
PMT	-4718
PV	0
FV	90018
TYPE	0
GTYPE	0
COMPOUNDING	1
PERIOD	1
DISTRIBUTION	1
GTYPE	1
GRADIENT	tadGRADIENT(0%,0%,20,-4718,0,90018,0,0,,1,1,1,1)
GRADIENT	-0.499%

Wow. Boy am I impressed by your desire to help others! Thanks again for all your input, and sorry I hadn't seen your 2nd reply b4 I replied to you. Your tad apps/functions look impressive, and gradient would have saved me a lot of time, but as you can see, I eventually figure out a formula using iterations. (I only wish you could set the number of iterations at the worksheet level instead of the program level. I'm surprised MS did it that way, but I guess there's a technical reason for it.) Anyway, thanks again for all your input. It's always great to see the generosity of strangers.

PS I love Excel, and am thinking of selling my services upon retiring in about 6 years. Your works helps inspire me.