Need help, Looping calc to find break even point between 2 yearly costs
I am trying to write a calculation to work out when the cost of an item with a high initial outlay and low running costs pays for itself against an item with no initial outlay but high running costs. This is slightly complicated by the fact that this is being done on a summary field.
After a survey of a customers premises I have identified all their currently installed lighting and have alternatives with much lower running costs. I end up with a related table with say 25 fluorescent lights of 4 different lengths and their LED alternatives.
I have three summary fields to give me the total cost of all the installed lighting in year 1 , the total cost of all the alternative lighting in year 1 and the total cost of all the alternative lighting after year 2.
year 1 includes all the initial cost of the new lights, Year 2 gives the incremental cost ie year 1 plus the running cost for year 2
I could write this using a case statement with 10 tests, as pay back will be less than 10 years, but I'm sure it can be done more elegantly and I thought I should learn how to do it.
It could also be done with a script that loops, and a script trigger but again I think I should learn how to do it with a calculation.
This is essentially the calculation I need.
A = installed lighting year 1
B = LED lighting year 1
C = LED lighting Year 2
D = C - B (cost of running the LED lights for a year)
E = B - D ( initial cost of the LED Lights)
Find when A* years > E + ( D * years)
How do I create the Loop in a calculation field?