Formula please! [SOLVED]

  • Hi everyone!


    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)


    and


    2. How do I calculate the VALUE in total amount paid over those number of months for each Set


    and


    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!


    regards


    Harry

  • Hi AJW!


    Boy, you have me stumped! I have no idea what I am looking at. Can you help me out please?


    I see the y-values simply typed onto the graph. Is that correct, or should a line be drawn somewhere? Are my settings incorrect?


    What values are those typed as "y"?


    Why use only four of those "values" when the term is so much longer?


    What do I have to read up on to understand better what you are telling me, please?


    Thanks so far! Hope I catch on quickly!


    Kind regards


    Harry

  • You can algebra to find the answer to your first question if the rates are constant for each set (they were not for your posted example). If set 1 starts at a and increases at a rate of b and set 2 starts at c and increases at a rate of d then the number of years before the payments cross over is
    =INT(LN(c/a)/LN((1+b)/(1+d)))

  • Thanks Derk!


    I have no idea why converting the numbers to logarithms answers my problem, but I notice that it does, to some extent.


    The rates at which the annual increases take place are not fixed, however, as you noted in my example.


    I also noticed that ROUNDUP in the place of INT will suit my needs better, answering that the cross-over point will take place IN that year, while the INT provides the answer I actually asked, which is the year BEFORE. I have just changed my mind! Thanks a lot for the input.


    I've tried a lot of formulas, as per the attached example (Sheet Set2) and Comments. See if you can help me with shortcuts, if you can?


    Thanks, too, AJW.


    I just do not know how changing the way I gather the data from the actual lists answers my questions. I am quite happy with the way the lines are plotted and how the data is retrieved, but thanks for making me see a VERRY involved way of plotting the same detail!


    Hope to speak soon.


    Regards


    Harry

  • From the example3 workbook it appears that you are willing to actually calculate out all of the values. In that case, I think the attached answers your question 3.
    (somewhere the switch seems to have been made from months to years). Was this what you had in mind?

  • Derk!


    Your knowledge of algebra and the use therefof in Excel is absolutely OUTSTANDING! When I look down the "last post" column, the name that appears most appears to be Derk! Super!


    Thanks a lot buddy.


    That does the trick. Just shows, when you "know", everything seems easy. And I suppose when you "understand what you know", everything should be even easier!


    By the way, the months did seem to turn into years, didn't it? I noticed what I started doing, too, and mentioned it somewhere, I think.


    Have a great day!


    Harry

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!