Results 1 to 5 of 5

20050413, 15:26 #1
 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.

20050413, 15:48 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20050413, 17:15 #3
 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%, etcDepending 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.

20050413, 19:19 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20050413, 23:02 #5
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Determine Months and Percent (VBA/Excel)
In the standard US payroll scenario, 401k deductions are made at an employeeelected 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