Question

I am using Excel to try and solve these problems but can find the answers or figure out why my answers are wrong, please help.

1. Today, your roommate purchased an annual perpetuity of $1,000. The payments on his perpetuity will begin in one year. You purchased an annual perpetuity of $1,000. Your payments begin immediately. Assuming a 10% discount rate for these perpetuities, which of the following statements is true?

A. Your perpetuity is worth $1,000 more than the roommate’s.

B. The roommate’s perpetuity is worth $909.09 more than yours.

C. The perpetuities are of equal value today.

D. Your perpetuity is worth $909.09 more than the roommate’s.

E. The roommate’s perpetuity is worth $1,000 more than yours.

2. You invest $2,000 in an account which pays 9.5% compounded quarterly and $2,500 in an account paying 8% compounded quarterly. If you leave the money in the accounts, how many years will it take for the two accounts to have equal values?

3. If you deposit $2,500 at the end of each six months into an account that pays 5.5% interest compounded quarterly, how much will be in the account in 5 years?

4. You just won the lottery! You wish to put enough money away so that you can withdraw $6,000 per month for 40 years. You can earn 9% rate on any funds you deposit.  How much will you have to deposit now to meet your goal?

5. An investment offers to pay you $7,000 per month for the next 10 years (The payments start a month after you purchase the investment). If you require 12% rate of return, how much should you pay for this investment? (Answers are rounded.)

6. You borrow $80,000 at 10 percent, compounded quarterly. How many years will it take to pay back the loan if the quarterly payment is $2,700?

7. You plan to retire in 30 years with $1 million. You have an investment available that provides a rate of return of 9% per year, compounded monthly. How much will you have to invest every month to reach your retirement goal?

Answer 1

1	PRESENT DAY=End of Year0
	End of Year	0	1	2	3	4	5	6	………………	up to infinity
	Cash flow of roommate		$1,000	$1,000	$1,000	$1,000	$1,000	$1,000	……………….	up to infinity
	Your cash flow	$1,000	$1,000	$1,000	$1,000	$1,000	$1,000	$1,000	………………..	up to infinity
	(Your cash flow)minus (Cash flow of roommate)=	$1,000
	At 10% discount:
	Present value of cash flow of room mate	$        10,000	(1000/0.1)
	Present value of your cash flow	$        11,000	(1000+(1000/0.1))
	Your Cash flow is (11000-10000)=$1,000 more than cash flow of roommate
	Answer:
	A. Your perpetuity is worth $1,000 more than the roommate's
2
	$2,000 invested in quarterly interest of(9.5/4)%=2.375%=	0.02375
	$2,500 invested in quarterly interest of(8/4)%=2 %=	0.02
	Number of years =N
	Future Value of $2000=2000*(1.02375^N)			1.00367647
	Future Value of $2500=2500*(1.02^N)
	2000*(1.02375^N)=2500*(1.02^N)
	(1.02375/0.02)^N=2500/2000=1.25
	1.00367647^N=1.25
	N* Log 1.00367647=Log1.25	0.096910013
		0.001593743
	N=0.09691001/0.00159374=	60.8065592
	Future Value of $2000=2000*(1.02375^60.8065592)	8334.640605
	Future Value of $2500=2500*(1.02^60.8065592)	8334.640308
	The two accounts will have equal value after 60.81 quarters=	15.2025	Years	(60.81/4)
3	5.5% interest annually
	Quarterly interest =(5.5/4)%=1.375%=	0.01375			1.3750
	Effective six monthly interest=(1.01375^2)-1=	0.027689062
	Number of six monthly period in 5 years=5*2	10
	Amount deposited at the end of each six monthly period	$2,500
	Future value of annuity	$28,356.55	(Using FV function of excel with Rate=0.02768906, Nper=10,Pmt=-2500)
	Amount in the account at the end of 5 years	$28,356.55
4	Amount of monthly withdrawal	$6,000
	Number of months of withdrawal	480	(40*12)
	Monthly interest=(9/12)%=0.75%
	Present value of future cash flows	$777,845.41	(Using PV function of excel with Rate=0.75%, Nper=480,Pmt=-6000)
	Amount required to be deposited to meet the goal	$777,845.41