Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to create a tiered calculation

Posted on 2011-02-25
6
Medium Priority
?
495 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 93

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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

877 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