Question

Please do it in MS Excel

Required: Solve these question in Microsoft Excel

Question 01: You want to retire on the day you have $1,000,000 is your savings account. You expect to earn 4 percent compounded monthly, on your money during your retirement. Your plan is to withdraw $4500 a month as retirement income from this account. How many years can you be retired until you run out of money?

.

Question 02: you are able to pay mortgage payments of $675 a month for thirty years. the interest rate is 9.5 percent, compounded monthly, What price can you afford to buy if you have $5000 cash available as a down payment.

Note: Solve these question in MS Excel

Answer 1

Question is asking for time in which the 1000000 money is finished so,
PV	1000000
Rate	0.003333333	(4%/12)
N	?
PMT	4500	monthly withdrawal
Time =	405.65	Months
	NPER(4%/12,4500,-1000000)
Converting months to years gives us , (405.65/12)= 33.80 Years
Note: PV value is normally used with - sign in formulas
In this part of question we have to calculate, PV of EMIs we can afford for 30 years
PV	?
Rate	0.007916667	(9.5%/12)
N	360	(30 x 12)
PMT	675
PV =	($80,275.51)
	PV(9.5%/12,360,675)
So we can afford upto (80275.51 + 5000) = $85,275.51