# How to create a tiered calculation

Hello,
I 'm trying to create a function that will return of tiered amounts.  For example, I have the the following tier
Tier                                         %
0 to 2,500,000                         0
2,500,00.01 to 5,000,000        4%
5,000,000 to 10,000,000         6%
10,000,000.01 to 15,000,000  8%
>=15,000,000.01                      In addition to the 8% above, 20,000 for each million above 15,000,000

Basically, if the input value is 16,000,000.  I want take 0% of the first tier, add it to 4% of the second tier, add that result to 6% of the third tier and so on
Asked:
###### Who is Participating?

Commented:
Try this pair of functions.  It worked for me - assuming I interpreted your request properly.

Function TieredCalc(dblAmt As Double) As Double

Dim dblPercentAmt As Double

dblPercentAmt = 0

If dblAmt > 2500000 Then   'Calculate first tiered amount
dblPercentAmt = (MinOf2(dblAmt, 5000000) - 2500000) * 0.04
End If

If dblAmt > 5000000 Then  'Calculate 2nd tiered amount + 1st tiered amount
dblPercentAmt = dblPercentAmt + (MinOf2(dblAmt, 10000000) - 5000000) * 0.06
End If

If dblAmt > 10000000 Then                        'Calculate 3rd tiered amount + first 2 tiered amounts
dblPercentAmt = dblPercentAmt + (dblAmt - 10000000) * 0.08
End If

If dblAmt > 15000000 Then                       'Calculate 20000 per million over 15 million
dblPercentAmt = dblPercentAmt + Int((dblAmt - 15000000) / 1000000) * 20000
End If

TieredCalc = dblPercentAmt

End Function

Function MinOf2(dblFirst As Double, dblSecond As Double) As Double

If dblFirst < dblSecond Then
MinOf2 = dblFirst
Else
MinOf2 = dblSecond
End If

End Function

John
0

Commented:
>>In addition to the 8% above, 20,000 for each million above 15,000,000

What would the "bonus" amounts be for the following values:

15,999,999.99
16,000,000.00
16,000,000.01
16,500,000.00
16,999,999.99

0

Commented:
I like that.  Well done John!
0

Author Commented:
The bonus amounts would be
Amount
15,999,999.99
16,000,000.00
16,000,000.01
16,500,000.00
16,999,999.99
0

Author Commented:
matthewspatrick:
The bonus amounts would be
Amount                      Bonus Amt
15,999,999.99                0
16,000,000.00             20,000
16,000,000.01             20,000
16,500,000.00             20,000
16,999,999.99             20,000
17,000,000                  40,000
0

Author Commented:
Thanks for the help.  I did come up with the following solution which also works but yours is much cleaner so I will use the one you came up with

Private Function GetESP(dblTotalPayment As Double) As Double
Dim dblIncentiveAmount As Double
Dim dblESP As Double

dblIncentiveAmount = dblTotalPayment
Dim dblTier1 As Double
Dim dblTier2 As Double
Dim dblTier3 As Double
Dim dblTier4 As Double
Dim dblTier5 As Double
Dim dblTotal As Double

dblTier1 = 0

If dblIncentiveAmount <= 2500000 Then
dblTier1 = 0
End If

If dblIncentiveAmount > 5000000 Then
dblTier2 = ((2500000) * 0.04)
End If

If dblIncentiveAmount > 10000000 Then
dblTier3 = ((10000000 - 5000000.01) * 0.06)
End If

If dblIncentiveAmount > 15000000 Then
dblTier4 = ((15000000 - 10000000.01) * 0.08)
End If

If dblIncentiveAmount > 16000000 Then

dblTier5 = ((dblIncentiveAmount - 15000000.01) * 0.08) + (Int(((dblIncentiveAmount - conMaximumIA) / 1000000)) * conOverAmt)
End If

dblTotal = dblTier1 + dblTier2 + dblTier3 + dblTier4 + dblTier5

GetESP = dblTotal

End Function
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.