Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determine Months and Percent (VBA/Excel)

    I need to determine the number of periods (in months) and whole percentage points needed to generate an ending amount. For example: If the ending number I'm looking for is 14,000, how can I determine the combinations of percent (whole percentage points) and months (the total number of months must sum to 12) that will give me 14,000? Assume that the percents are multiplied by a larger number say 65,000.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine Months and Percent (VBA/Excel)

    Are you talking about a loan?
    Such calculations involve 5 quantities:
    - Present value
    - Future value
    - Number of periods
    - Periodic payment
    - Interest rate
    Given any 4 of these, you can calculate the 5th. You've told us that the future value is 14,000 and the number of periods is 12 (months). That leaves 3 unknowns: present value, periodic payment and interest rate. We'll need to need the present value and the periodic (monthly) payment to be able to determine the interest rate.

    Otherwise, please explain in more detail - perhaps with an example - what you want.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine Months and Percent (VBA/Excel)

    Sorry for not being clear; I just didn't want to bog down the problem with too many details. Usually you guys can get me started with minimal information. Let say that one wants to make some dollar contribution to their 401K (currently the max is $14,000). However, their employer only allows whole percentage contributions (e.g., 10%, 11%, etc--Depending on an employee's salary, getting to their dollar contribution can only be achieved with a fraction). What I would like to be able to do is take anyone's salary and determine what whole percentage(s) they can use to get their max dollar contribution or the total dollar contribution that they want to make if is less than $14,000. I'm assuming that in some cases, an adjustment will have to be made at some point in the year to the percent contribution to get the individual's contribution amount. I need the routine to find not only the percentage contribution, but also the break point(s) in the year at which the percentage(s) must be changed.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine Months and Percent (VBA/Excel)

    I'm sure that you have extra conditions in mind that you haven't made explicit, such as that the amounts should be distributed as evenly as possible over the months. If the percentage has to change, should the larger amounts be paid in the beginning of the year, or at the end, or in the middle? Without such information, it is impossible to give advice.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Determine Months and Percent (VBA/Excel)

    In the standard US payroll scenario, 401k deductions are made at an employee-elected whole percentage rate until the ceiling is hit (in this case $14,000). However, there are still wild cards in that employees can change their election at any time, and bonuses may or may not be subject to 401k deduction, so an actual history of deductions has to be kept. Any real world algorithm has to take into account actual deductions to date. (Part of this exposition is for Hans.)

    Let's say you are assuming no rate change in the election. The projector would be the target 401k contribution dollar amount divided by the annualized salary divided by the annual pay period frequency, rounded up to the nearest even 1.0%. I guess it would be:

    Function target401kpercent(salary As Double, target As Double, numAnnPayPeriods As Integer) As Double
    If CBool(salary * target) And salary > target Then
    If target > 14000 Then target = 14000
    target401kpercent = Round(target / salary / numAnnPayPeriods, 2)
    End If
    End Function

    Sub testfunction()
    Debug.Print Format(target401kpercent(50000, 10000, 24), "##0.0%")
    End Sub

    Does that test out to what you are looking for?
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •