Solved

How to create a tiered calculation

Posted on 2011-02-25
6
474 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

738 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