Question

Use Excel to calculate the following assuming you want to set up a college account for your kid and that you want to accumulate $50,000 in the fund at the end of 10 years.

a) If you can invest at 6% per year, how much would you need to put into the fund at the end of each year? Be prepared to describe how you did the calculation.

b) If you can invest at 6% per year, how much would you need to put into the fund at the beginning of each year? Be prepared to describe how you did the calculation.

c) If you can invest $1,000 at the end of every six months, approximately what interest rate would you need to find for the investment to meet your $50,000 goal in 10 years? Be prepared to describe how you did the calculation.

Answer 1

Answers:
a)
Accumulation amount at end of 10 years (given in the question)	$50,000
Rate of interest (given in the question)	6% per year
Amount to contribute at END of each year
(use PMT formula in excel to calculate the amount required to contribute each year)	PMT(rate of interest -6%, no of years-10, present value-0, future value-50000 (target), type(end or beginning of the period)
	PMT(0.06,10,0,-50000,0)
Amount to contribute at END of each year=	$3,793.40
b)
Accumulation amount at end of 10 years (given in the question)	$50,000
Rate of interest (given in the question)	6% per year
Amount to contribute at beginning of each year
(use PMT formula in excel to calculate the amount required to contribute each year)	PMT(rate of interest -6%, no of years-10, present value-0, future value-50000 (target), type(end or beginning of the period)
	PMT(0.06,10,0,-50000,1)
Amount to contribute at beginning of each year=	$3,578.68
c)
Accumulation amount at end of 10 years (given in the question)	$50,000
Amount to invest at end of every six months	$1,000
Rate of interest to calculate:
(use RATE formula in excel to calculate the rate of interest)	RATE(no of period -10 years *2 half year (20),payment -1000, present value-0, future value-50000 (target), type(end or beginning of the period)
	RATE(20,1000,0,-50000,0)
Rate of interest required to meet the goal:	8.80%