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

[ronden]

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!

[martindwilson]

well if they start at 100% and decline at a fixed rate each year you can use solver make sure solver addin is loaded
Click the Microsoft Office Button Button image, and then click Excel Options.
Click Add-Ins, and then in the Manage box, select Excel Add-ins.
Click Go.
In the Add-Ins available box, select the Solver Add-in check box, and then click OK.
Tip If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in.
If you get prompted that the Solver Add-in is not currently installed on your computer, click Yes to install it.
After you load the Solver Add-in, the Solver command is available in the Analysis group on the Data tab.
see attachment

[MoneyMaker]

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!

20/2 [ initial_value*2 + (20-1) * decline ] = aggregate_output

10 [ initial_value*2 + (19) * decline ] = aggregate_output

20 * initial_value + 190 * decline = aggregate_output

190 * decline = aggregate_output - 20 * initial_value

decline = (aggregate_output - 20 * initial_value ) / 190

Replace initial_value with the expected output for year 1 ,and aggregate_output with respective value and you have your answer

[MoneyMaker]

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

[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.

[MoneyMaker]

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

Please Login or Register  to view this content.

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%

[ronden]

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.