How to solve this problem?

Hi

Let's say an employer pays a worker an additional amount - a certain % of his salary EVERY SINGLE MONTH into three different accounts, A, B and C according to his age. For example,

for age 35-45, rate A = 10%, rate B= 8% and rate C=6%
age 45 - 55, rate A = 9%, rate B= 7%, rate C=5%
age 55 - 65, A= 7%, B = 5 %, C= 4%
age 65 - 75 A= 6%, B=4%, C= 3%

I am trying to calculate the total amount that an employee would have received from his current age to a specified age. So, how much TOTAL additional amount would the employee have accumulated in each of the three accounts (i.e. total A, total B, total C) from his current age at 47 years 0 month to the specified age at 57 years 3 months? How should I go about it? What Excel functions can I use? The user would have to enter his date of birth and the future age in question.

Anyone out there who can help, please! Thanks!!

Regards

Jack

Your table is ambiguous.

you show two sets of rates for ages 45, 55 and 65

Since resolution appears to be at the month level, when is an employee
considered to be a year older. Always use the first of the month, last of
the month, round up for dates 16-31 and down for 1 - 15 - what is the
method????

--
Regards,
Tom Ogilvy

"jackoat" <jackoat.1t546l_1122991573.9181@excelforum-nospam.com> wrote in
message news:jackoat.1t546l_1122991573.9181@excelforum-nospam.com...
>
> Hi
>
> Let's say an employer pays a worker an additional amount - a certain %
> of his salary EVERY SINGLE MONTH into three different accounts, A, B
> and C according to his age. For example,
>
> for age 35-45, rate A = 10%, rate B= 8% and rate C=6%
> age 45 - 55, rate A = 9%, rate B= 7%, rate C=5%
> age 55 - 65, A= 7%, B = 5 %, C= 4%
> age 65 - 75 A= 6%, B=4%, C= 3%
>
> I am trying to calculate the total amount that an employee would have
> received from his current age to a specified age. So, how much TOTAL
> additional amount would the employee have accumulated in each of the
> three accounts (i.e. total A, total B, total C) from his current age at
> 47 years 0 month to the specified age at 57 years 3 months? How should I
> go about it? What Excel functions can I use? The user would have to
> enter his date of birth and the future age in question.
>
> Anyone out there who can help, please! Thanks!!
>
> Regards
>
> Jack
>
>
> --
> jackoat
> ------------------------------------------------------------------------
> jackoat's Profile:
http://www.excelforum.com/member.php...o&userid=25800
> View this thread: http://www.excelforum.com/showthread...hreadid=392135
>

Hi Tom

Sorry for the ambiguity. Referring to my rates again, let's call them Line 1, Line 2, Line 3 and Line 4 for easy reference

for age 35-45, rate A = 10%, rate B= 8% and rate C=6% (L1)
age 45 - 55, rate A = 9%, rate B= 7%, rate C=5% (L2)
age 55 - 65, A= 7%, B = 5 %, C= 4% (L3)
age 65 - 75 A= 6%, B=4%, C= 3% (L4)

What I'm trying to mean is the day the employee turns 45, the new rate (L2)applies. (i.e. A=9%, B=7%, C=5%). Similarly, when employee is exactly 55 years old, L3 applies and finally L4 kicks in on his 65th birthday.

So now, can the problem be solved? Thanks for your help.

Regards

Jack

Make a blank sheet the active sheet.
change
db = True for a verbose printout

leave
db = False just for the results. Lightly tested.

Sub AA()
Dim ar(1 To 4)
Dim dt As Date, dtE As Date, dtS As Date
Dim dtStart As Date, dtEnd As Date
Dim d As Long, dtBirth As Date
Dim monPay As Double
Dim db As Boolean ' debug print flag
db = False
ar(1) = Array(0.1, 0.08, 0.06)
ar(2) = Array(0.09, 0.07, 0.05)
ar(3) = Array(0.07, 0.05, 0.04)
ar(4) = Array(0.06, 0.04, 0.03)
dtStart = #1/1/1972#
dtEnd = Date
dtBirth = #10/17/1942#
monPay = 1000

d = Day(DateSerial(Year(dtEnd), Month(dtEnd) + 1, 0))
dtE = DateSerial(Year(dtEnd), Month(dtEnd), d)
d = Day(DateSerial(Year(dtStart), Month(dtStart) + 1, 0))
dtS = DateSerial(Year(dtStart), Month(dtStart), d)
dt = dtS
k = 2
If db Then
Cells.Clear
Range("A1:G1") = Array("Date", "Birth Date", _
"AGE", "Rate Line", "A", "B", "C")
End If
Do While dt <= dtE
age = Evaluate("DateDif(" & _
CLng(dtBirth) & "," & CLng(dt) _
& ",""y"")")
idex = Int((age - 35) / 10) + 1
If idex > 0 and idex < 5 Then
a = a + monPay * ar(idex)(0)
b = b + monPay * ar(idex)(1)
c = c + monPay * ar(idex)(2)
End If
If db Then
Debug.Print Format(dt, "mmm dd, yyyy"), _
Format(dtBirth, "mmm dd, yyyy"), age, idex
Cells(k, 1) = Format(dt, "mmm dd, yyyy")
Cells(k, 2) = Format(dtBirth, "mmm dd, yyyy")
Cells(k, 3) = age
If idex > 0 and idex < 5 Then
Cells(k, 4) = "L" & idex
Cells(k, 5) = monPay * ar(idex)(0)
Cells(k, 6) = monPay * ar(idex)(1)
Cells(k, 7) = monPay * ar(idex)(2)
End If
End If
d = Day(DateSerial(Year(dt), Month(dt) + 2, 0))
dt = DateSerial(Year(dt), Month(dt) + 1, d)
k = k + 1
Loop
msg = "A: " & Format(a, "$ #,##0.00") & vbNewLine _
& "B: " & Format(b, "$ #,##0.00") & vbNewLine _
& "C: " & Format(c, "$ #,##0.00")
MsgBox msg
If db Then
Cells(k, 1) = msg
Debug.Print msg
End If
End Sub

--
Regards,
Tom Ogilvy

"jackoat" <jackoat.1t5vyg_1123027546.0827@excelforum-nospam.com> wrote in
message news:jackoat.1t5vyg_1123027546.0827@excelforum-nospam.com...
>
> Hi Tom
>
> Sorry for the ambiguity. Referring to my rates again, let's call them
> Line 1, Line 2, Line 3 and Line 4 for easy reference
>
> for age 35-45, rate A = 10%, rate B= 8% and rate C=6% (L1)
> age 45 - 55, rate A = 9%, rate B= 7%, rate C=5% (L2)
> age 55 - 65, A= 7%, B = 5 %, C= 4% (L3)
>
> age 65 - 75 A= 6%, B=4%, C= 3%
> (L4)
>
> What I'm trying to mean is the day the employee turns 45, the new rate
> (L2)applies. (i.e. A=9%, B=7%, C=5%). Similarly, when employee is
> exactly 55 years old, L3 applies and finally L4 kicks in on his 65th
> birthday.
>
> So now, can the problem be solved? Thanks for your help.
>
> Regards
>
> Jack
>
>
> --
> jackoat
> ------------------------------------------------------------------------
> jackoat's Profile:
http://www.excelforum.com/member.php...o&userid=25800
> View this thread: http://www.excelforum.com/showthread...hreadid=392135
>