This is compound interest calculated daily and topped up with deposits every month e.g Begin with $100, earn 2% interest earned daily even on weekends, and then come month end invest another $100 on the compounded amount.
Please assist in how I can achieve this in Excel and what the formula is.
The future value function in Excel is detailed here: https://exceljet.net/excel-functions/excel-fv-function
Assuming the nominal interest rate is 2% per annum compounded monthly.
Future value after depositing $100 each month for 12 months.
=FV(rate, nper, pmt, [pv], [type])
=FV(0.02/12,12,-100,0,1)
$1,213.08
This is the calculation the Excel function is doing.
With
n = 12
d = 100
r = 0.02/12
fv = d (1 + r)^1 + d (1 + r)^2 + ... + d (1 + r)^11 + d (1 + r)^12 = 1213.08
This can be converted to a formula.
which is equivalent to the one here: http://financeformulas.net/Future-Value-of-Annuity-Due.html
Daily Interest
The phrase "calculated daily" means mid-month cash flows have the partial month interest accumulated correctly. However, the $100 cash flows in the question are monthly so mid-period cash flows are not relevant and the above method can be used, (albeit with equal length months).
Nevertheless a daily calculation can be made.
The following assumes the nominal interest rate is 2% per annum compounded daily. This is not quite the same as 2% per annum compounded monthly - ref., (but the difference to the result amounts only to 1 cent.)
Screenshot showing Excel formulas
It is unlikely you would require daily interest to be calculated on calendar months i.e. with unequal number of days per month. However, if you did require it there is no short Excel formula for it.
I put the start date in A2, then A3 is
A2 + 1
Starting balance is in D2 and daily interest in E2 as you have it.
D3 is then
=D2+ROUND(D2*E$2,2)+IF(MONTH(A4)<>MONTH(A3),100,0)
That is the previous balance (D2), plus the days interest (D2*E$2), plus, if tomorrow is a different month than today (i.e. today is the last day of the month) $100.
Copy your row 365 times and you get your answer for the year.
