Question

Create a new What SUP Present Value Analysis a) Using the ch7-01 file to start your work, modify that worksheet to do the following: I) calculate the annuity payment required at the end of each year for the next 4 years at 4 percent such that the value in 4 years is $95000 and illustrate the annual value of that investment II) calculate the future value of an investment of $20000 invested for 4 years at a rate of 3 percent and illustrate the annual value of that investment. III) calculate the future value of an investment of $8500 each year for 4 years at a rate of 3 percent and illustrate the annual value of that investment. IV) calculate the initial investment required to obtain a future value of $10500 assuming a rate of 3 percent for 4 years and illustrate the annual value of that invetment. b) save the file as ch7-01 _student_name (replacing student_name with your name) c) Print the resulting worksheet

A1 what sup C D 1 What SUP 2 Ch 07-01 3 Present Value Analysis Year Interest Annui Investment 6 Future need: 7 Term (in years): 8 Interest rate: 9 Annuity payment required: 10 $95,000.00 4 496 4 12 13 Current investment: 14 Interest rate: 15 Term (in years): 16 Investment value in future Year Interest lInvestment ($20,000.00) 3% 4 4 18 19 20 Current annuity investment: 21 Interest rate: 22 Term (in years): 23 Investment value in future: Year Interest Annui Investment ($8,500.000 3% 4 4 25 26 27 Future need: 28 Interest rate: 29 Term (in years): 30 Invest now: Year Interest lInvestment $10,500.00 3% 4 4 32

Answer 1

Answer 1
			Year	Interest	Annuity	Investment
Future Need	$95,000		0	$0	$0	$0
Term (in years)	4		1	$0	$22,372	$22,372
Interest rate	4%		2	$895	$22,372	$45,638
Annuity Payment required	$22,372		3	$1,826	$22,372	$69,835
			4	$2,793	$22,372	$95,000
Working
Using Future value of annuity formula , we can calculate the annuity payment required
Future value of annuity = P x {[(1+r)^n -1]/r}
Future value of annuity = $95000
P = Annuity payment required = ?
r = interest rate per year = 4%
n = number of years = 4
$95000 = P x {[(1+0.04)^4 -1]/0.04}
P = $95000 / 4.246464
P = 22371.55
Annuity Payment required = $22,372
Answer 2
			Year	Interest	Investment
Current Investment	-$20,000		0		-$20,000
Interest rate	3%		1	-$600	-$20,600
Term (in years)	4		2	-$618	-$21,218
Investment value in future	-$22,510		3	-$637	-$21,855
			4	-$656	-$22,510
Answer 3
			Year	Interest	Annuity	Investment
Current Annuity Investment	-$8,500		0	$0	-$8,500	-$8,500
Interest rate	3%		1	-$255	-$8,500	-$17,255
Term (in years)	4		2	-$518	-$8,500	-$26,273
Investment value in future	-$36,628		3	-$788	-$8,500	-$35,561
			4	-$1,067	$0	-$36,628
Answer 4
			Year	Interest	Investment
Future need	$10,500		0		$9,329
Interest rate	3%		1	$279.87	$9,609
Term (in years)	4		2	$288.27	$9,897
Invest now	$9,329		3	$296.91	$10,194
			4	$305.82	$10,500
Present value of future value = FV x (1+r)^-n
r = interest rate = 3%
n = term in years = 4
FV = Future value = $10500
Present value of future value = 10500 x (1+0.03)^-4
Present value of future value = 9329.11
Invest now = $9,329