Cumulative Interest Formula plus monthly deposit

[pagination1]

Hi there, I'm trying to formulate a cumulative interest formula, with monthly deposits.

I can create a formula which simply takes a principal amount, applies an annual interest rate, number of years, but I can't figure out how to add a monthly deposit into the calculation.

The formula I currently have is:

=FV(C6/C8,C7*C8,0,-C5)

Where:

C5= Current Amount
C6 = Annual Interest Rate
C7 = Total Investment Years
C8 = Compounding Periods per Yr.

I want to add a C9, which is the monthly deposit amount, but I just can't figure this out.

I'd be really grateful for any help!

Many thanks,

Nick

 

[StephenCrump]

Perhaps:

	A	B	C	D	E	F
1
2
3
4
5		Amount	100,000
6		APR	5%
7		Years	2
8		Periods	12	p.a.
9		Instalment	$4,387.14
10
11		FV at t=	10	months
12			$59,542.48
13
14		t	Start	Interest	Repay	End
15		1	100,000.00	416.67	-4,387.14	96,029.53
16		2	96,029.53	400.12	-4,387.14	92,042.51
17		3	92,042.51	383.51	-4,387.14	88,038.88
18		4	88,038.88	366.83	-4,387.14	84,018.57
19		5	84,018.57	350.08	-4,387.14	79,981.51
20		6	79,981.51	333.26	-4,387.14	75,927.63
21		7	75,927.63	316.37	-4,387.14	71,856.85
22		8	71,856.85	299.40	-4,387.14	67,769.12
23		9	67,769.12	282.37	-4,387.14	63,664.35
24		10	63,664.35	265.27	-4,387.14	59,542.48
25		11	59,542.48	248.09	-4,387.14	55,403.44
26		12	55,403.44	230.85	-4,387.14	51,247.14
27		13	51,247.14	213.53	-4,387.14	47,073.54
28		14	47,073.54	196.14	-4,387.14	42,882.54
29		15	42,882.54	178.68	-4,387.14	38,674.07
30		16	38,674.07	161.14	-4,387.14	34,448.08
31		17	34,448.08	143.53	-4,387.14	30,204.47
32		18	30,204.47	125.85	-4,387.14	25,943.18
33		19	25,943.18	108.10	-4,387.14	21,664.14
34		20	21,664.14	90.27	-4,387.14	17,367.27
35		21	17,367.27	72.36	-4,387.14	13,052.50
36		22	13,052.50	54.39	-4,387.14	8,719.74
37		23	8,719.74	36.33	-4,387.14	4,368.94
38		24	4,368.94	18.20	-4,387.14	0.00
39
Sheet4

Cell Formulas
Range	Formula
C9	C9	=PMT(i/N,Yrs*N,-Amount)
C12	C12	=FV(i/N,C11,Instalment,-Amount)
C15	C15	=Amount
D15:D38	D15	=C15*i/N
E15:E38	E15	=-Instalment
F15:F38	F15	=SUM(C15:E15)
C16:C38	C16	=F15

Named Ranges
Name	Refers To	Cells
Amount	=Sheet4!$C$5	C15, C12, C9
i	=Sheet4!$C$6	C12, C9, D15:D38
Instalment	=Sheet4!$C$9	C12, E15:E38
N	=Sheet4!$C$8	C12, C9, D15:D38
Yrs	=Sheet4!$C$7	C9