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
chtullu135Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
JAMcDoConnect With a Mentor 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
 
Patrick MatthewsCommented:
>>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
 
GRayLCommented:
I like that.  Well done John!
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
chtullu135Author 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
 
chtullu135Author 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
 
chtullu135Author 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.

All Courses

From novice to tech pro — start learning today.