Here is a short explanation and example of daily interest and monthly compounding that will hopefully be helpful to you.
Suppose a bank's interest rate is 5% nominal compounded monthly. The measure "monthly compounding" is the key to calculating the effective annual rate, from which can be calculated the daily rate.
See https://en.wikipedia.org/wiki/Effective_interest_rate#Calculation
nominal rate = i = 0.05
effective rate = r = (1 + i/12)^12 - 1 = 0.0511619
daily rate = d = (1 + r)^(1/365) - 1 = 0.000136711
Then suppose three deposits are made
days from 12-Aug-14
12-Aug-14 1000 0
18-Mar-15 2000 218
10-Jun-15 2500 302
What is the balance x
on 22-Oct-16?
22-Oct-16 is 802 days after 12-Aug-14.
Using the number of days and the daily rate, the balance is found by solving this equation.
1000/(1 + d)^0 + 2000/(1 + d)^218 + 2500/(1 + d)^302 - x/(1 + d)^802 = 0
∴ 5340.17 = x/(1 + d)^802
∴ x = 5958.94
Reciprocally, Excel's XIRR function will calculate the effective annual rate from the cashflows. The nominal monthly compounded rate can then be calculated from the effective annual rate.
