I have been pondering over this solution for some time now and cannot even seem to get a starting point with the formula!
In the attached file I have two sets of values. Each set shows values for each month of a year. These values are then increased by a percentage at the end of the year in question and stay at that increased value for the next year, after which they increase again.
The initial monthly value for Set1 is lower than that of Set2. The annual adjustment, however, is higher in Set1.
When these Sets are compared, one sees (picture a graph with each successive year's values shown in your mind's eye) that one starts off at a lower value and has a steeper incline on the line in the "pictured graph above", while the other starts off higher, but has a shallower incline.
When superimposed in this "mind's eye graph", the lines cross at some stage. Can you see that?
Now here are my questions:
1. How do I calculate the NUMBER of monhly amounts that will be paid until just before this "cross over" point is to be reached (remember that the values of Set1 and Set2 will probably not add up to exactly meet at this point)
2. How do I calculate the VALUE in total amount paid over those number of months for each Set
3. How many months will it take for the amounts paid in Set2 to equal the total amounts paid in Set1. This is not the same as question 2 above, remember. There we calculate the premiums paid per Set till the cross over point is reached, while over here we calculate the time it takes for the total amounts paid in each Set to be as close to equal as possible. These values will probably not meet exactly again, therefore I need the number of months till just before the amount paid in Set2 would surpass the amount paid in Set1.
Intricate enough, I hope!
I'm hoping for an Excel formula, but if VBA will have to be used, so be it. I'll build the code in somewhere.
Let's see what you can do!