Question

10)You have just retired with $1,000,000 in savings. This is the amount that you will be drawing down for the rest of your life. You expect to earn 6% and withdraw $70,000 per year. You also want to leave an inheritance of $100,000 to your favorite charity. How long can you rely on your savings? Assume that your first withdrawal will occur one year from today.

Use Excel’s =NPER(RATE,PMT,PV,[FV],[TYPE]) function.

RATE is 6%, PMT is $70,000, PV is $1,000,000, FV is $100,000, and TYPE is 0 since payments are made at the end of the period. If the [type] argument is omitted, it takes on the default value of 0.

			A	B	C	D	E
1			Annual Rate	6%
2			Payments	$ 70,000
	3		Present Value	- $ 1,000,000
	4		Future Value	$100,000

6

5
6	Years	=NPER(B1,B2,B3,B4)

			A	B	C	D	E
	1		Annual Rate	6%
	2		Payments	$ 70,000
	3		Present Value	- $ 1,000,000
4			Future Value	$100,000
5
6			Years

a) You are offered an investment that will cost $975 and will pay you interest of $80 per year for the next 10 years. The investment will pay $1,000 at the end of the 10 years. What is the rate of return if you purchase this investment?

Use Excel’s =RATE(NPER,PMT,PV,[FV],[TYPE]) function.

NPER is 10, PMT is $80, PV is $975, FV is $1,000, and TYPE is 0 since payments are made at the end of the period. If the [type] argument is omitted, it takes on the default value of 0.

We use PV as a negative value to represent a cash outflow that results from money being invested. In that case, PMT and FV will be positive to represent cash inflows from the investment.

			A	B	C	D	E
1			Years	10
2			Payments	$ 80
	3		Present Value	- $ 975
	4		Future Value	$ 1,000
	5
6			Annual Rate	=RATE(B1,B2,B3,B4)

			A	B	C	D	E
1			Years	10
2			Payments	$ 80
3			Present Value	- $ 975
	4		Future Value	$ 1,000
	5
	6		Annual Rate

b) You are planning to send your daughter to college in 20 years. You estimate that you will need $100,000 at that time to pay for tuition, room, and board. How much money would you need to invest at the beginning of each year to achieve your goal assuming that you can earn a rate of return of 10% per year?

Use Excel’s =PMT(RATE,NPER,PV,[FV],[TYPE]) function.

7

RATE is 10%, NPER is 20, PV is $0, FV is $100,000, and TYPE is 1 since payments are made at the beginning of the period.

			A	B	C	D	E
	1		Annual Rate	10%
	2		Years	20
	3		Present Value	$0
4			Future Value	$ 100,000
5			Type	1
6
7			Payments	=PMT(B1,B2,B3,B4,B5)

			A	B	C	D	E
	1		Annual Rate	10%
	2		Years	20
	3		Present Value	$0
4			Future Value	$ 100,000
5			Type	1
6
7			Payments

Answer 1

10)
	RATE (I/Y)	6.00%
	PV	(1,000,000.0)
	PMT	70,000.0
	FV	100,000.0
	N (Years)	31.86
a)
	PMT	80.00
	NPER	10
	PV	-975
	FV	1000
	RATE (I/Y)	8.38%
b)
	RATE (I/Y)	10%
	NPER	20
	PV	0
	FV	100000
	PMT	($1,745.96)