Question

What is the future value of 20 periodic payments of $4,000 each made at the beginning of each period and compounded at 8%?

Build an Excel worksheet to verify your calculation of #1. Show the calculation year by year (periodic amount, payment, balances etc.)

Given the information of #1 above, how much you would have to save every year (period) if you want to have 2 million dollars of cash after 20 years (periods)

Answer 1

1)	Using the formula for finding out the FV of an annuity due,
	the FV of the 20 periodic payments = 4000*1.08*(1.08^20-1)/0.08 =					$ 1,97,691.69
2)	Year	Periodic Amount	Balance for Interest calculation	Interest	Ending balance
	1	4000.00	4000.00	320.00	4320.00
	2	4000.00	8320.00	665.60	8985.60
	3	4000.00	12985.60	1038.85	14024.45
	4	4000.00	18024.45	1441.96	19466.40
	5	4000.00	23466.40	1877.31	25343.72
	6	4000.00	29343.72	2347.50	31691.21
	7	4000.00	35691.21	2855.30	38546.51
	8	4000.00	42546.51	3403.72	45950.23
	9	4000.00	49950.23	3996.02	53946.25
	10	4000.00	57946.25	4635.70	62581.95
	11	4000.00	66581.95	5326.56	71908.51
	12	4000.00	75908.51	6072.68	81981.19
	13	4000.00	85981.19	6878.49	92859.68
	14	4000.00	96859.68	7748.77	104608.46
	15	4000.00	108608.46	8688.68	117297.13
	16	4000.00	121297.13	9703.77	131000.90
	17	4000.00	135000.90	10800.07	145800.97
	18	4000.00	149800.97	11984.08	161785.05
	19	4000.00	165785.05	13262.80	179047.86
	20	4000.00	183047.86	14643.83	197691.69
3)	$4000 saved for 20 periods at 8% interest with compounding will
	yield a FV of 197691.69.
	For an FV of $1, the amount to be deposited each period is
	= 4000/197691.69
	So for an FV of 2000000,the period amount to be deposited = 2000000*4000/197691.69 =	$       40,467.05
	CHECK:
	FV = 40467.05*1.08*(1.08^20-1)/0.08 =	$       20,00,000

YEARLY FIGURES FOR [3]

Year	Periodic Amount	Balance for Interest calculation	Interest	Ending balance
1	40467.05	40467	3237	43704
2	40467.05	84171	6734	90905
3	40467.05	131372	10510	141882
4	40467.05	182349	14588	196937
5	40467.05	237404	18992	256396
6	40467.05	296863	23749	320612
7	40467.05	361080	28886	389966
8	40467.05	430433	34435	464868
9	40467.05	505335	40427	545761
10	40467.05	586228	46898	633127
11	40467.05	673594	53888	727481
12	40467.05	767948	61436	829384
13	40467.05	869851	69588	939439
14	40467.05	979906	78393	1058299
15	40467.05	1098766	87901	1186667
16	40467.05	1227134	98171	1325305
17	40467.05	1365772	109262	1475034
18	40467.05	1515501	121240	1636741
19	40467.05	1677208	134177	1811385
20	40467.05	1851852	148148	2000000