1. Ms. Cobain, a 25-year old professional, plans to retire 35 years from today a
ID: 2621605 • Letter: 1
Question
1. Ms. Cobain, a 25-year old professional, plans to retire 35 years from today and is analyzing her retirement options with an advisor. She is certain that $60,000 per year can cover her annual expenses during the 40 years following retirement. Show your work to get credit for your answers. (4 points)
a) If she can earn a 7% return, how much does she needs to have in savings at the time she retires to cover the annual expenses during retirement ($60,000)?
b) Rework problem a) but assuming Monthly withdrawals this time ($5,000 per month, which is equivalent to $60,000 annually). Use the same interest rate of 7% per year but compounded monthly this time.
c) How much does she need to deposit at the end of each year (from now until retirement) to accomplish the retirement target estimated in section b), with monthly withdrawals?
2. You just got your first job as a Finance Professional and plan to start in your new job soon after graduation. You have decided to purchase a new car worth $28,000 to reward yourself for the accomplishment. However, you only have $3,000 available right now to spend. The bank offers you a six-year car loan @ 7.250% interest rate (with monthly payments). Show your work to get credit for your answers. (4 points)
a) Prepare an amortization table for the full life of the loan (72 months). Use the spreadsheet TVM functions.
b) How much will you be paying in interests during the life of the loan? How much will you pay in interest in the first year of the loan?
c) How much would you save in interest expenses if you could get a 5% interest rate (versus 7.250%)?
Explanation / Answer
1.a. Value of savings at time of retirement can be calculated in Excel as =PV(7%,40,-60000). This is equal to $ 799,902.53
1.b. Value of savings at time of retirement can be calculated in Excel as =PV(7%/12,40*12,-5000). This is equal to $ 804,594.19
1.c. She needs to save enough every month to accumulate future value of 804,594.19 at the end of 35 years from now.
Monthly interest rate = 7%/12
The annual interest rate = (1+monthly rate)^12-1 = (1+7%/12)^12-1 = 7.229%
So annual savings required can be calculated in Excel as =PMT(7.229%,35,,-804594.19). This is equal to $ 5,536.21
2. As we have $ 3,000 already, then the loan amount = car value - amount in hand = 28,000-3,000 = $ 25,000
Monthly payment required can be calculated in Excel as =PMT(7.25%/12,72,-25000). This is equal to $ 429.23
Amortization table is as below:
This table is calculated as:
Month 1
Outstanding principal = loan value = 25000
Interest charged = 25000*7.25%/12 = 151.04
Principal repaid = 429.23-interest charged = 429.23-151.04 = 278.19
Month 2
Outstanding principal = previous principal outstanding - principal repaid = 25000 - 278.19 = 24,721.81
Interest charged = 24,721.81*7.25%/12 = 149.36
Principal repaid = 429.23-interest charged = 429.23-149.36 = 279.87
And so on..
2.b. Total interest paid throughtout the loan can be calculated by summing the 3rd column (interest charged). This is equal to 5905. Alternatively, this can also be calculated by monthly payment*72-loan taken = 429.23*72-25,000 = 5905.
Interest paid in first year can be calculated by adding the first 12 values in the 3rd column (interest charged), i.e. 151.04+149.36+147.67+ ... +131.98. This is equal to 1,699.
2.c. If we get a 5% interest rate, then monthly payment can be calculated in Excel as =PMT(5%/12,72,-25000). This is equal to $402.62.
So total interest paid = monthly payment*72-loan taken = 402.62*72-25,000 = 3989
So interest saved = interest paid in first case - interest paid in second case = 5905 - 3989 = $1916
Hope this helped ! Let me know in case of any queries.
Month Outstanding principal Interest charged Principal repaid 1 25,000.00 151.04 278.19 2 24,721.81 149.36 279.87 3 24,441.94 147.67 281.56 4 24,160.38 145.97 283.26 5 23,877.12 144.26 284.97 6 23,592.15 142.54 286.69 7 23,305.46 140.80 288.43 8 23,017.03 139.06 290.17 9 22,726.86 137.31 291.92 10 22,434.94 135.54 293.69 11 22,141.25 133.77 295.46 12 21,845.79 131.98 297.25 13 21,548.55 130.19 299.04 14 21,249.51 128.38 300.85 15 20,948.66 126.56 302.67 16 20,645.99 124.74 304.49 17 20,341.50 122.90 306.33 18 20,035.17 121.05 308.18 19 19,726.98 119.18 310.05 20 19,416.94 117.31 311.92 21 19,105.02 115.43 313.80 22 18,791.21 113.53 315.70 23 18,475.51 111.62 317.61 24 18,157.91 109.70 319.53 25 17,838.38 107.77 321.46 26 17,516.92 105.83 323.40 27 17,193.53 103.88 325.35 28 16,868.17 101.91 327.32 29 16,540.85 99.93 329.30 30 16,211.56 97.94 331.29 31 15,880.27 95.94 333.29 32 15,546.99 93.93 335.30 33 15,211.69 91.90 337.33 34 14,874.36 89.87 339.36 35 14,535.00 87.82 341.41 36 14,193.58 85.75 343.48 37 13,850.11 83.68 345.55 38 13,504.55 81.59 347.64 39 13,156.91 79.49 349.74 40 12,807.17 77.38 351.85 41 12,455.32 75.25 353.98 42 12,101.34 73.11 356.12 43 11,745.22 70.96 358.27 44 11,386.95 68.80 360.43 45 11,026.52 66.62 362.61 46 10,663.91 64.43 364.80 47 10,299.11 62.22 367.01 48 9,932.10 60.01 369.22 49 9,562.88 57.78 371.45 50 9,191.42 55.53 373.70 51 8,817.72 53.27 375.96 52 8,441.77 51.00 378.23 53 8,063.54 48.72 380.51 54 7,683.03 46.42 382.81 55 7,300.22 44.11 385.12 56 6,915.09 41.78 387.45 57 6,527.64 39.44 389.79 58 6,137.85 37.08 392.15 59 5,745.70 34.71 394.52 60 5,351.18 32.33 396.90 61 4,954.28 29.93 399.30 62 4,554.99 27.52 401.71 63 4,153.28 25.09 404.14 64 3,749.14 22.65 406.58 65 3,342.56 20.19 409.04 66 2,933.52 17.72 411.51 67 2,522.02 15.24 413.99 68 2,108.02 12.74 416.49 69 1,691.53 10.22 419.01 70 1,272.52 7.69 421.54 71 850.98 5.14 424.09 72 426.89 2.58 426.65Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.