Solved

How to create a tiered calculation

Posted on 2011-02-25
6
431 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:chtullu135
6 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34982398
>>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
 
LVL 3

Accepted Solution

by:
JAMcDo earned 500 total points
ID: 34986189
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
 
LVL 44

Expert Comment

by:GRayL
ID: 34989703
I like that.  Well done John!
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:chtullu135
ID: 34997399
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 Comment

by:chtullu135
ID: 34997408
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 Closing Comment

by:chtullu135
ID: 34997622
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now