Daily compounding different interest rates depending on value

[RolandSix]

Hello to all whose expertise far exceeds mine. I am trying to figure out the formula and format that will provide me with the calculation of daily interest on an account balance where the interest rate changes based on the account balance value. Example: 0-2500 = x%; 2501-10K = y%; 10,001 - 25K = z%; etc. I've been trying to configure branched IF commands, and am trying to understand VLOOKUP; not very versed in all the nuances.

My hopes are to develop a template to run scenarios using periodic/variable additional deposits and calculate outcomes. TIA

 

[Cubist]

Need some further clarification.
Suppose your balance is 9000, then is your calculation tiered i.e. x% of 2500 then y% applies to the remainder (9000-2500).
Or simply y% of 9000?

 

[Peter_SSs]

@RolandSix
A couple of other administrative things too:

• I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
  
  
• Please note #14 of the Forum Rules regarding use of all caps in posts or thread titles. I've edited the title this time.

 

[RolandSix]

Need some further clarification.
Suppose your balance is 9000, then is your calculation tiered i.e. x% of 2500 then y% applies to the remainder (9000-2500).
Or simply y% of 9000?

Thank you for the response. My understanding is the interest rate on this account is balance-specific. If balance increases/declines across a threshold, the rate for that threshold is active going forward.

 

[RolandSix]

@RolandSix
A couple of other administrative things too:

• I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
  
  
• Please note #14 of the Forum Rules regarding use of all caps in posts or thread titles. I've edited the title this time.

Thank you, Peter for this. I updated Account Details, and apologize for being at variance with Forum Rules with my first ever post. Will not happen again.

 

[Peter_SSs]

Thank you, Peter for this. I updated Account Details,

Great, thanks for doing that.

and apologize for being at variance with Forum Rules with my first ever post. Will not happen again.

We understand that you are new here (in relation to posting) and it takes a while to get to know the place.

 

[kvsrinivasamurthy]

In B2 copied down

=A2*(1/365)*INDEX($H$2:$H$5,MATCH(A2,$F$2:$F$5,1))

Interest % is taken for 1 year so 1/365.

 

[kvsrinivasamurthy]

Interest % is taken for 1 year so 1/365.
If interest calculation is for a period in one go, say for 50 days, then UDF with VBA code is required. Is it ok .

 

[kvsrinivasamurthy]

I forgot in post#7
In A3 copied down

=A2+B2

 

[kvsrinivasamurthy]

Here is UDF code for 'Daily interest. The code calculates compound interest for the days, from date to date, with respective interest rate in table.
If required the interest table can also be incorporated in code.

Function DailyInterest(P As Double, d1 As Long, d2 As Long)
Dim Prng As Range, Irng As Range
Dim T&, temp As Double, tempP As Double, I As Double

Set Prng = Range("F3:F6"): Set Irng = Range("H3:H6")
tempP = P
For T = d1 To d2
I = tempP * WorksheetFunction.Index(Irng, WorksheetFunction.Match(tempP, Prng, 1)) / 365
tempP = tempP + I: I = 0
Next T
DailyInterest = tempP - P
End Function

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List
This function can be used like other functions in excel.
Save file as .xlsm