3

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.

  • Welcome to PF&M. We prefer questions that show research effort. How far have you got on your own? – Rupert Morrish Feb 1 '18 at 22:49
  • I have interest that is calculated daily on a spread sheet where i can change the principal and the interest rate but i don't know how to add the monthly deposit amount into the formula. ($D$2*(1+$E$2)^A2)* Where column D is my Principal and column E is the interest rate. That formula is on column B – Traders Day Feb 1 '18 at 23:01
  • Would it be simpler to have 12 separate cells? – RonJohn Feb 1 '18 at 23:02
  • If that is the easier option so be it will be fine by me – Traders Day Feb 1 '18 at 23:12
  • How does your formula for the month account for daily rounding? – Rupert Morrish Feb 2 '18 at 0:26
2

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.

enter image description here

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

enter image description here

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.