Need help with macro and for loop

  • Hello all,


    I need help with writing code in VBA.

    I want to do a calculation for multiple years where te end value of year 2021 is the start value of year 2022.

    Also I want to save the sum of one specific column for all the years and for example 5% of this sum of this column in 2021 will be used to increase the starting value in 2022.

    I already tried some VBA code and made a similar, but simple version of my problem. (see attachment).


    Thank you in advance!


  • Hello and Welcome to the Forum :)


    Why do you want to create a macro ... when a few formulas can produce the same result ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Because the original problem is quite large and includes more some increasing variabeles per age, like salary.


    The original problem must represent a pension fund, where the column old is the value of your pension at t,

    then the column new: you add some percentage of your salary to your pension, and column end is the value at the end of the year.

    Next year, you start with the value of the end of the previous year.


    To make it more difficult: the total value at the start of the year 2021, will be invested in some stock or bonds and if the return is positive, then a percentage of this return will be add to your stariting value in 2022.

  • To get a contributor to help you for your next step, in my opinion you need to explain clearly what you need ...


    and if possible, show the final expected result in your file ... even if it is added manually

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I'm sorry, I will try but most of the variable names are Dutch. In the file I made the important sheets red.


    So we start with the employee tab. For now this is a list of 100 fake persons, but I would like this macro to work for any number of employees.

    A person consist of a Name(naam), Date of Birth(Geboortedatum), sex(geslacht, 1 is Male, 2 is Female), Salary(Salaris), FTE(full time percentage) and value of the pension t = 0 (aanspraak).


    Then to the calculation tab and top left we see a counter(teller) and the start year (2021). For every person at year t, we start with a pension value at t=0, then a part of your salary will be added to your start value(column K) and then the value at the end of the year is column O. This column O for the year 2021 is equal to the starting value in 2022.

    The last 3 columns (Q, R, S) calculate the present value of the Pension.


    In the pensionfund tab we start in 2021 with the liabilities of the fund, cel C5, then there are some costs but these are not important. We assume at the start that the assets of the pensionfund are equal to the liabilities at the start. This we invest and we get an return Assets and Bonds for 2021. At the end of the year we have the total value of the Asset. For the coverage ratio(dekkingsgraad) we divide these two and if it is larger than 100%, the pension are increased the next year. So C24 in the calculation for the year 2021 is used to set the level of indexation in column G in calculation tab in year 2022 and so on.


    My problem now is that yellow column in year t, must be the green column in year t + 1.

    And the orange column in year t+1 depends on !pensionfund C24 of year t


    I already tried some work in the macro tab, but I did not succeed

    The VBA code I wrote can be activated by the commandbutton in the first tab.


    I hope it is clear

    Many thanks in advance


    kind regards

  • Hello Frank,


    Thanks a lot for all your efforts ... to explain ... :)


    If my understanding is correct ...In the Sheet Calculation ... in the cell G2 .... you could test following formula


    Code
    =INDEX('Pensionfund '!$24:$24,MATCH(Teller,'Pensionfund '!$15:$15,0))*Calculation!F2


    Let me have your feedback ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks!


    It works with Match(Teller-1, ....)


    Do you also know how the output of your macro in year t, can be used as input for you macro in year t + 1.

    Now the input for the Macro is a matrix which refers to the calculation tap.

    If write in the VBA code:


    if Teller> start Then

    input =output

    Else

    input = starting values

    end if


    But the starting values refers to cells in the calculation tab. Now if I overwrite the Input with the output, these references disappear.

  • OK ...


    Before taking a look at your macro ... just wanted to know if, at least, my understanding of your logic was correct or not ....;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    Could you explain the logic of this section of your macro :

    Code
    For i = 2021 To [2021 + Aantal_jaar]
            [Teller] = i
            [inputrij].Copy
            [start_output].Offset(i, 0).PasteSpecial Paste:=xlPasteValues
    Next i

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Sorry ... but meant the logic ... or if you prefer the process in order to reach the final result ...


    1. When you design a loop with i ... for 60 years ... why would you need Teller ....


    2. To copy ... avoid Select and Selection ... which only slows down the whole process ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • OK ... for macros ... no problem ... it is your first experience ... Welcome on Board ;)


    Regarding the logic ... in the Sheet Macro starting in Row 110 ... what should be calculated ... ????


    Just use plain English to describe the expected result ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Each row represents an employee with a fictive starting value(column D). For these employees, we determine their age (column C).


    If an employee is younger than 68, we determine their contribution for this year which depend on age and salary (Column F) and their benefit is 0 (column G)

    If an employee is older than 68, thier contribution = 0 (column F) and we determine their benefit for this year(column G), which depend on their starting value.


    Finally, in column H, we have the value at the start + their contribution - their benefits and this is equal to the value at the end of the year.


    Then next year, the fictive starting value in column D is now equal to the value of the end of the previous year, and so on, Now the years are printed next to each other, but I would like that column H(year 2021) is column Q for year 2022. Then repeat the same steps.


    Column J, K and L are not important for now. These columns are used to calculate the present value of the future cash flows, which is not necessary at the moment.

  • Thanks a lot for your explanations ...


    So, my understanding is the following :


    With your sample of 107 participants to the plan, you would need the macro to generate the projection over 60 years for all participants


    Is that right ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

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