Question

This problem has three questions. Make sure to answer all three questions. For the following problem, assume that all debts and payments are incurred at the end of the month and that interest is compounded monthly. Assume that retirement savings are invested in the stock market and earn an annual interest rate of 11%, which is compounded monthly. . Big Spender James gets a credit card at the beginning of his freshman year. • He adds $693 of credit card debt every month for 48 months. During college he doesn't make any payments. • His credit card interest rate is 21% per year, compounded monthly. • After he graduates (48 months) he starts to pay off his debt. He pays $1,240 per month toward his credit card bill • His credit card accrues interest while he is in college and it continues to accrue interest until it is completely paid off. Question 1: How many monthly payments does James have to make to pay off his credit card? Round your answer up to the nearest whole month. . Retirement Planning • The month after James finishes paying off his credit card debt he starts saving for retirement (i.e. ignore any leftover money in the month he finishes paying off his credit card). He saves $1,240 per month (i.e. the same amount that he used to pay towards his credit card debt). Timothy and Adelaide both start college the same year as James. Neither one of them incur any debt while they are in college. • Timothy starts saving $840 per month as soon as he graduates from college. • Adelaide doesn't start saving until 10 years after she graduates, at which point she saves $1,240 per month. • All three of them work for 35 years and then retire (i.e. they retire 39 years after they started college). Question 2: How much money does each person end up putting into their savings account? James's Total Payments Timothy's Total Payments Adelaide's Total Payments

Answer 1

Answer to Question 1 $ Annuity amount Interest rate Term (in months) FV of ordinary annuity (i=1.75%, n=48) Total debt due at the end of 48 months (693*74.26278) Formula used in column D $ 693.00 1.75% =(21%/12) 48 74.26278 =ROUND(FV(D5,D6,-1,0,0),5) $ 51,464.11 =ROUND(D4*D7,2) $ $ Present value of future payments Annuity amount Interest rate Term (in months) (21%/12) 51,464.11 1,240.00 1.75% =(21%/12) 75 =ROUND(NPER(D13,-D12,D11,0,0),0) Conclusion James has to make 75 monthly payments of $1,240 in order to pay off his debt completely. Answer to Question 2 and 3 Formula used in column D For James Savings per month Total no. of Months saved James's total payments Interest rate FV of ordinary annuity James's savings at retirement (1240*345) $ 1,240 345 = (35*12)-75 $ 427,800 =D22*D23 0.92% =(11%/12) 2431.81193 =ROUND(FV(D25,023,-1,0,0),5) $ 3,015,447 =ROUND(D22*D26,0) (i=0.92%, n=345) (1240*2431.81193) For Timothy Savings per month Total no. of Months saved Timothy's total payments (840*420) Interest rate FV of ordinary annuity (i=0.92%, n=420) Timothy's savings at retirement (840*4928.29637) $ 840 420 =(35*12) $ 352,800 =D30*D31 0.92% =(11%/12) 4928.29637 =ROUND(FV(D33,D31,-1,0,0),5) $ 4,139,769 =ROUND(D30*D34,0)

$ For Timothy Savings per month Total no. of Months saved Timothy's total payments (840*420) Interest rate FV of ordinary annuity (i=0.92%, n=420) Timothy's savings at retirement (840*4928.29637) 840 420 =(35*12) $ 352,800 =D30*D31 0.92% =(11%/12) 4928.29637 =ROUND(FV(D33,D31,-1,0,0),5) $ 4,139,769 =ROUND(D30*D34,0) For Adelaide Savings per month Total no. of Months saved Adelaide's total payments (1240*300) Interest rate FV of ordinary annuity (i=0.92%, n=300) Timothy's savings at retirement (1240*1576.13330) $ 1,240 300 =(35-10)*12 $ 372,000 =D38*D39 0.92% =(11%/12) 1576.13330 =ROUND(FV(D41,D39,-1,0,0),5) $ 1,954,405 =ROUND(D38*D42,0) Conclusion James's total payments Timothy's total payments Adelaide's total payments James's savings at retirement Timothy's savings at retirement Timothy's savings at retirement $ $ $ $ $ $ 427,800 352,800 372,000 3,015,447 4,139,769 1,954,405