I’ve been trying to work out a formula (in excel) to calculate interest due, but haven’t quite got my head around how it might work.

Situation (for discussion):
A saving account pays 4% APR
I deposit $250 / month into account (for 1 year, 12 equal payments totalling $3000 on the first day of each month)
Interest is calculated daily, paid out in lump sum annually.

At first I thought this was a case of applying 1/365th of the interest rate on the current amount in the account each day, but as the applied interest is (ever so slightly) changing the value of the amount currently in the account on a daily basis, it feels more like a calculus problem that I can’t quite sort out in my head. So I was wondering if there is an easy (or at least doable) way to accurately calculate this (using excel preferably)? I’d like something that could be easily tweaked for different % rates and deposit amounts etc…

To compare nominal rates with different compounding periods we calculate an effective annual rate where the effective annual rate is r, i is the nominal rate and n is the number of compounding periods per year and:

r = (1 - r/n)**n - 1

So your 4% APR compounded daily yields an effective rate of 4.0808%. That is the effect of interest applied daily changing the value of the amount currently in the account on a daily basis. As you can see it’s quite small and dividing by 365 would not be terribly inaccurate, but if you want to be accurate use the above formula.

The 2nd part of your problem is you want to calculate the future value of an annuity with payments of $250 for 12 months, no initial value, at 40.0808% annually. You can do this in excel with the formula

I thought I had sorted it (in a long-winded fashion) by setting up 12 separate calculations in excel using the formula A=P(1+r/(number of days in month)^r and then having the total at end of each month add in $250.00 (to reset P) and then repeat. But my total comes to $3071.53, so I guess I’m doing something wrong along the way. Probably with the interst rate as I just divided by 12 to work on a monthly basis:

First month =250*(1+0.333/31)^0.333
Second month =C5*(1+0.333/28)^0.333 (where C5 is total of first month +£250.00) etc…

Well we’re using different rates which accounts for a minuscule difference. Without looking into it too deeply and without seeing your Excel sheet, I think you are implicitly assuming the payments are made at the beginning of the period rather than the end, so you have 1 extra month of interest accruing. It’s not actually wrong, just a different assumption.

I would suggest not messing around with your rate anymore than you have to. You can calculate an effective monthly rate that incorporates the effect of the daily compounding with the formula I posted above. Then you just have a monthly effective rate, with no need to divide by days of the month in your formulas.

Incidentally, using different numbers of days per month will also change the result by a few pennies.

When you’re doing future or present value calculations it helps me to think of the periods as irreducible. It’s just 12 periods. It doesn’t matter if they’re 12 days 12 months or 12 years - it’s just a simple formula with n = 12. The meaningfulness is taken into account by finding the correct effective rate.