Compound Interest Earned With Periodic Withdrawals

Hi KingAir and welcome to the forum,

Instead of doing a formula, why not do it a day at a time. You have all the info. See my example sheet.

Originally Posted by KingAir

I am looking for a formula which will compute interest earned (not ending account balance) for the following scenario:
1) Set $ amount (say 19250) is deposited on January 1.
2) Annual interest rate is .95%
3) Interest is compounded daily
4) Fixed withdrawals of $1750 are taken each and every month on the first day of each month

The total interest can be approximated as follows:

	A	B
1	Init Bal Date	1/1/2014
2	Init Bal	19,250.00
3	Annl Int Rate	0.9500%
4	Withdrawal	1,750.00
5	Monthly Int Rate	0.0792%	B5: =(1+B3/365)^(365/12)-1
6	# Periods	11	B6: =INT(NPER(B5,B4,-B2))
7	Final Bal	91.96	B7: =FV(B5,B6,B4,-B2)
8	Total Int	91.96	B8: =B7-B2+B6*B4

Note: It is only a coincidence that final balance equals total interest. Replace 1750 with 1700 to see the difference.

Of course, you can combine the formulas into a single formula.

The approximation assumes equal months (365/12 days), which is not realistic.

A more accurate calculation requires a monthly schedule like the following:

	E	F	G
1		Int	Bal
2	1/1/2014		19,250.00
3	2/1/2014	15.54	17,515.54	F3: =G2*((1+$B$3/365)^(E3-E2)-1)
4	3/1/2014	12.77	15,778.31	G3: =G2+F3-$B$4
5	4/1/2014	12.74	14,041.04
6	5/1/2014	10.97	12,302.01
7	6/1/2014	9.93	10,561.94
8	7/1/2014	8.25	8,820.19
9	8/1/2014	7.12	7,077.31
10	9/1/2014	5.71	5,333.02
11	10/1/2014	4.17	3,587.19
12	11/1/2014	2.90	1,840.08
13	12/1/2014	1.44	91.52
14	Total Int	91.52		F14: =SUM(F3:F13)

The formulas in F3:G3 are copied down through F13:G13.

Arguably, earned interest should be rounded monthly. So the formula in F3 should be:

=ROUND(G2*((1+$B$3/365)^(E3-E2)-1),2)

However, some financial institutions accumulate fractional interest and add it to the account appropriately.

(On the other hand, one mutual fund that I know of rounds interest on a daily basis. That is unusual, in my experience.)

-----
[EDIT] Slightly better design for the monthly schedule:

	E	F	G	H
1		Int	Withdraw	Bal
2	1/1/2014			19,250.00
3	2/1/2014	15.54	1750.00	17,515.54	F3: =H2*((1+$B$3/365)^(E3-E2)-1)
4	3/1/2014	12.77	1750.00	15,778.31	H3: =H2+F3-G3
5	4/1/2014	12.74	1750.00	14,041.04
6	5/1/2014	10.97	1750.00	12,302.01
7	6/1/2014	9.93	1750.00	10,561.94
8	7/1/2014	8.25	1750.00	8,820.19
9	8/1/2014	7.12	1750.00	7,077.31
10	9/1/2014	5.71	1750.00	5,333.02
11	10/1/2014	4.17	1750.00	3,587.19
12	11/1/2014	2.90	1750.00	1,840.08
13	12/1/2014	1.44	1750.00	91.52
14	Total Int	91.52			F14: =SUM(F3:F13)