Hello,

I found the HW assignment on course hero but when I met my tutor tonight she indicated the numbers were off. I must be able to show my work to answer the questions so I made separate spreadsheet to show year by year and how I came up with the numbers. Is that right? I'm really confused. I just don't know which numbers are right.

Question 1 Amount invested by Yvonne, when she was 12 = Number of years for which amount was invested = Interest rate = Worth of CD at maturity = $ 5,000.00 10 7.25% ? n i pv pmt fv 10 7.25% $ (5,000.00) na $10,068.00 Therefore, CD is worth $10,068.00 when it is transferred into investment account. Question 2 Amount transferred to investment account = Annual investment made in investment account = Interest rate = Number of years since initial investment = Worth of investment account now = $10,068.00 $ 1,500.00 6.75% 23 ? n i pv pmt fv 23 6.75% $10,068.00 $ 1,500.00 $128,070.98 Therefore, worth of investment account now is $ 128,070.98 Question 3 Annual investment made in IRA account = Interest rate = Number of years since initial investment = Worth of IRA account now = $ 2,000.00 7.00% 23 ? n i pv pmt fv 23 7.00% na $ 2,000.00 $114,353.34 Therefore, worth of IRA account now is $ 114,353.34 Question 4 Total investment now = Number of years till retirement = Interest rate = $242,424.32 10 8.00% Annual contributions to investment and IRA account Amount accumulated at the age of retirement = $ 3,500.00 ? n i pv pmt fv 10 8.00% $242,424.32 $ 3,500.00 $578,135.13 Amount accumulated at the time of early retirement is $ 580,435.22 Question 5 Amount available at the age of early retirement Number of years she needs to withdraw money = Interest rate = Amount that can be withdrawn yearly = $578,135.13 10 8% ? pv i n pmt $578,135.13 8.00% 10 ($79,777.02) Amount that can be withdrawn annually if early retirement taken is $ 80,094.41 TVM with Excel Assignment When Yvonne was twelve, her grandfather gave her $5,000 to be used for college. Yvonne was a good student and dreamed of going to a good school so she invested the gift in a 10-year bank CD earning 7%. She received a generous scholarship and never needed to use the money from her grandfather. After graduating from college at age 22, she began working at a great job. Knowing the value of early investing, she began saving for retirement right away. When the CD matured she put it into an investment account earning 6 % and made an additional annual investment of $1,500 every January 1st. She also opened an IRA account and put $2,000 into it every year on January 1 st and over the years it earned a steady 7%. Now Yvonne is 45 and thinking she'd like to retire early at age 55. At age 65 she'll get an extremely generous corporate pension, which along with Social Security payments will allow her to live very well indeed. She plans to continue making the same annual contributions to her accounts until retirement. She believes that with clever investing strategies both of her accounts will be able to earn 8% from this point on. What Yvonne wants to know is whether her retirement savings will be able to provide enough income each year to allow her to live comfortably until age 65 when the pension kicks in. 1. 2. 3. 4. 5. How much was the CD worth when she transferred it into that investment account? How much is the investment account worth now? How much is the IRA account worth now? How much will she have accumulated when she takes early retirement? How much will she be able to withdraw each January 1st if she takes early retirement? Guidelines: Number and briefly explain each of your steps Show your inputs for each step using the TVM functions of Excel, not the financial calculator Laying out your solution using a timeline may help you visualize the cash flows more accurately. List any additional assumptions you make I must be able to replicate your work - points off if I have to struggle with it. Complete the solution using Excel (.xls or .xlsx). No other software/file format is acceptable. Category Points Nicely formatted with inputs shown, work explained, easy to follow 5 Solution reflects the facts of the problem, 1 pt/sub-question 5 Correct application of TVM, 3 pts/sub-question 15 25 CD and Investment Account Information Age Age 12 Age 13 Age 14 Age 15 Age 16 Age 17 Age 18 Age 19 Age 20 Age 21 Age 22 Age 23 Age 24 Age 25 Age 26 Age 27 Age 28 Age 29 Age 30 Age 31 Age 32 Age 33 Age 34 Age 35 Age 36 Age 37 Age 38 Age 39 Age 40 Age 41 Age 42 Age 43 Age 44 Age 45 Age 46 Age 47 Age 48 Age 49 Age 50 Age 51 Age 52 Age 53 Age 54 Age 55 Age 56 Beginning Amount Adding Money Interest Rate Interest Amount $5,000 7.25% $362.50 $5,362.50 7.25% $388.78 $5,751.28 7.25% $416.97 $6,168.25 7.25% $447.20 $6,615.45 7.25% $479.62 $7,095.07 7.25% $514.39 $7,609.46 7.25% $551.69 $8,161.15 7.25% $591.68 $8,752.83 7.25% $634.58 $9,387.41 7.25% $680.59 $10,068.00 7.25% $729.93 $10,797.93 $1,500.00 6.75% $830.11 $13,128.04 $1,500.00 6.75% $987.39 $15,615.43 $1,500.00 6.75% $1,155.29 $18,270.72 $1,500.00 6.75% $1,334.52 $21,105.24 $1,500.00 6.75% $1,525.85 $24,131.10 $1,500.00 6.75% $1,730.10 $27,361.20 $1,500.00 6.75% $1,948.13 $30,809.33 $1,500.00 6.75% $2,180.88 $34,490.21 $1,500.00 6.75% $2,429.34 $38,419.54 $1,500.00 6.75% $2,694.57 $42,614.11 $1,500.00 6.75% $2,977.70 $47,091.82 $1,500.00 6.75% $3,279.95 $51,871.76 $1,500.00 6.75% $3,602.59 $56,974.36 $1,500.00 6.75% $3,947.02 $62,421.38 $1,500.00 6.75% $4,314.69 $68,236.07 $1,500.00 6.75% $4,707.18 $74,443.25 $1,500.00 6.75% $5,126.17 $81,069.42 $1,500.00 6.75% $5,573.44 $88,142.86 $1,500.00 6.75% $6,050.89 $95,693.75 $1,500.00 6.75% $6,560.58 $103,754.33 $1,500.00 6.75% $7,104.67 $112,359.00 $1,500.00 6.75% $7,685.48 $121,544.48 $1,500.00 6.75% $8,305.50 $131,349.98 $1,500.00 6.75% $8,967.37 $141,817.36 $1,500.00 6.75% $9,673.92 $152,991.28 $1,500.00 6.75% $10,428.16 $164,919.44 $1,500.00 6.75% $11,233.31 $177,652.75 $1,500.00 6.75% $12,092.81 $191,245.56 $1,500.00 6.75% $13,010.33 $205,755.89 $1,500.00 6.75% $13,989.77 $221,245.66 $1,500.00 6.75% $15,035.33 $237,780.99 $1,500.00 6.75% $16,151.47 $255,432.46 $1,500.00 6.75% $17,342.94 $274,275.40 $1,500.00 6.75% $18,614.84 Age 57 Age 58 Age 59 Age 60 Age 61 Age 62 Age 63 Age 64 Age 65 Age 66 $294,390.24 $319,561.46 $346,746.38 $376,106.09 $407,814.58 $442,059.74 $479,044.52 $518,988.08 $562,127.13 $608,717.30 $1,500.00 $1,500.00 $1,500.00 $1,500.00 $1,500.00 $1,500.00 $1,500.00 $1,500.00 $1,500.00 $1,500.00 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% $23,671.22 $25,684.92 $27,859.71 $30,208.49 $32,745.17 $35,484.78 $38,443.56 $41,639.05 $45,090.17 $48,817.38 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% $140.00 $289.80 $450.09 $621.59 $805.10 $1,001.46 $1,211.56 $1,436.37 $1,676.92 $1,934.30 $2,209.70 $2,504.38 $2,819.69 $3,157.07 $3,518.06 $3,904.33 $4,317.63 $4,759.86 $5,233.06 $5,739.37 $6,281.12 $6,860.80 $7,481.06 $8,144.73 $8,854.87 $9,614.71 IRA Account Age 23 Age 24 Age 25 Age 26 Age 27 Age 28 Age 29 Age 30 Age 31 Age 32 Age 33 Age 34 Age 35 Age 36 Age 37 Age 38 Age 39 Age 40 Age 41 Age 42 Age 43 Age 44 Age 45 Age 46 Age 47 Age 48 $2,000.00 $2,140.00 $4,429.80 $6,879.89 $9,501.48 $12,306.58 $15,308.04 $18,519.61 $21,955.98 $25,632.90 $29,567.20 $33,776.90 $38,281.29 $43,100.98 $48,258.04 $53,776.11 $59,680.43 $65,998.07 $72,757.93 $79,990.98 $87,730.35 $96,011.48 $104,872.28 $114,353.34 $124,498.08 $135,352.94 Age 49 Age 50 Age 51 Age 52 Age 53 Age 54 Age 55 Age 56 Age 57 Age 58 Age 59 Age 60 Age 61 Age 62 Age 63 Age 64 Age 65 Age 66 $146,967.65 $159,395.38 $172,693.06 $186,921.57 $202,146.08 $218,436.31 $235,866.85 $254,517.53 $274,473.76 $298,591.66 $324,638.99 $352,770.11 $383,151.72 $415,963.86 $451,400.96 $489,673.04 $531,006.88 $575,647.43 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 $2,000.00 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 7.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% $10,427.74 $11,297.68 $12,228.51 $13,224.51 $14,290.23 $15,430.54 $16,650.68 $17,956.23 $22,117.90 $24,047.33 $26,131.12 $28,381.61 $30,812.14 $33,437.11 $36,272.08 $39,333.84 $42,640.55 $46,211.79 Year-end Balance $5,362.50 $5,751.28 $6,168.25 $6,615.45 $7,095.07 $7,609.46 $8,161.15 $8,752.83 $9,387.41 $10,068.00 $10,797.93 $13,128.04 $15,615.43 $18,270.72 $21,105.24 $24,131.10 $27,361.20 $30,809.33 $34,490.21 $38,419.54 $42,614.11 $47,091.82 $51,871.76 $56,974.36 $62,421.38 $68,236.07 $74,443.25 $81,069.42 $88,142.86 $95,693.75 $103,754.33 $112,359.00 $121,544.48 $131,349.98 $141,817.36 $152,991.28 $164,919.44 $177,652.75 $191,245.56 $205,755.89 $221,245.66 $237,780.99 $255,432.46 $274,275.40 $294,390.24 $319,561.46 $346,746.38 $376,106.09 $407,814.58 $442,059.74 $479,044.52 $518,988.08 $562,127.13 $608,717.30 $659,034.69 $2,140.00 $4,429.80 $6,879.89 $9,501.48 $12,306.58 $15,308.04 $18,519.61 $21,955.98 $25,632.90 $29,567.20 $33,776.90 $38,281.29 $43,100.98 $48,258.04 $53,776.11 $59,680.43 $65,998.07 $72,757.93 $79,990.98 $87,730.35 $96,011.48 $104,872.28 $114,353.34 $124,498.08 $135,352.94 $146,967.65 $159,395.38 $172,693.06 $186,921.57 $202,146.08 $218,436.31 $235,866.85 $254,517.53 $274,473.76 $298,591.66 $324,638.99 $352,770.11 $383,151.72 $415,963.86 $451,400.96 $489,673.04 $531,006.88 $575,647.43 $623,859.23 $3,500.00 8.00% $280.00 $3,780.00