Background
I have a T-Mobile Money (via BankMobile, a division of Customers Bank) interest checking account.
I'm trying to track earnings per month but I'm a bit confused by their fine print and their tiered APY scheme.
Overall Question
How can I calculate monthly and yearly earnings on excel/gsheets for my account, given my bank's fine print and two APY schemes?
The Fine Print Text
Relevant sections are below; full contact as PDF.
An interest rate of 3.93%, with an Annual Percentage Yield (APY) of 4.00%, will be paid on balances up to and including $3,000.00 in your Account.
An interest rate of 1.00%, with an APY of 1.00%, will be paid on balances above $3,000 in the Account.The APY for this tier will range from 4.00% to 2.79% depending on the balance in the Account (calculation based on a $5,000 average daily balance).
We use the average daily balance method to calculate interest on your Account. This method applies a periodic rate to the average daily balance in the Account for the period. The average daily balance is calculated by adding the principal in the Account for each day of the period and dividing that figure by the number of days in the period. Interest begins to accrue no later than the business day we receive credit for the deposit of noncash items (for example, checks). Interest on your Account will be compounded and credited on a monthly basis at the end of your monthly statement cycle.
The Fine Print Image
While, I have copied and pasted the text of the contact above for search and discovery reasons, I am also adding a screenshot of the above information to show where I am confused, and to highlight the parts of the contact that I would like to reference in my question.
Attempts to Understand
According to this question: Does a savings account's advertised "APY" account for compound interest? The FDIC clearly defines the formula banks must use to compute APY here.
The formula being
APY = 100*[(1 + interest/principal)^(365/Days in term) -1]
And according to this question: https://money.stackexchange.com/a/83879/5306
Most savings accounts compound interest daily and credit earned interest monthly.
Is that what I'm dealing with here? Since my bank is calculating interest daily but applying it at the end of the month, it still means the balance is growing at a monthly compounded interest rate, correct? This seems to confirm the language underlined above in blue.
And does "The APY for this tier" language underlined above in red above contradict the previous sentence?
Attempts to Track Earnings With a Spreadsheet
With a starting balance of $3,000 and 17 days in the first month, I received an interest payout of $5.81
To check this math, let's use the following spreadsheet:
The periodic rate formula:
=<Interest Rate>/365
My understanding of the language underlined above in green:
Average Daily Balance=(<days in first month> * <principal>)/<days in first month>
So if we are dealing with monthly compound interest the formula would be:
First month's Interest=Principal * Periodic Rate * Days in the First Month
Which gives us:
$5.49 = $3,000 * (B2/365) * 17
Which is less than what my bank paid me. What am I failing to see?
According to this online calculator, my interest payout for the first month should have been $5.57
dirty price
and aclean price
but I'm not sure. I have updated my question above.