How to solve this problem?

[jackoat]

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

 

[Tom Ogilvy]

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????

 

[jackoat]

Hi Tom

Sorry for the ambiguity. Referring to my rates again, let's call the
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 rat
(L2)applies. (i.e. A=9%, B=7%, C=5%). Similarly, when employee i
exactly 55 years old, L3 applies and finally L4 kicks in on his 65t
birthday.

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

Regards

Jac

 

[Tom Ogilvy]

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