Lindsay is 25 years old and has a new job in web development. She wants to make
ID: 3182950 • Letter: L
Question
Lindsay is 25 years old and has a new job in web development. She wants to make sure that she is financially sound in 30 years, so she plans to invest the same amount into a retirement account at the end of every year for the next 30 years.
(a) Construct a data table in Excel that will show Lindsay the balance of her retirement account for various levels of annual investment and return. If Lindsay invests $15,000 at return of 6%, what would be the balance at the end of 20th year in the account? If required, round your answers to two decimal places. $ (b) Develop the two-way table in Excel for the balance at the end of 30th year in the account. Consider annual investment amounts of $5000 to $20,000 in increments of $1000, and returns of 0% to 12% in increments of 1%. Note that because Lindsay invests at the end of the year, there is no interest earned on the contribution for the year in which she contributes. Complete the below table. If required, round your answers to two decimal places. 10% 11% $5,000 $ $ $6,000 $ $ $7,000 $ $ $8,000 $ $ $9,000 $ $ $10,000 $ $ $11,000 $ $ $12,000 $ $ $13,000 $ $ $14,000 $ $ $15,000 $ $ $16,000 $ $ $17,000 $ $ $18,000 $ $ $19,000 $ $ $20,000 $ $Explanation / Answer
a)
At the end of 20th year, total principal amount paid = 20*15000 = $300,000
interest for the principal paid in nth year will be equal to PTR where T=20-n years.
P= Principal= $15000 and R = rate of interest = 0.06
Total interest = PR(20-1) + PR(20-2) + ........ + PR(20-20)
= PR(19+18+17+ ...... +1 + 0)
=(15000)(0.06)(190)
=171000
the balance at the end of 20th year in the account = 300000 + 171000 = $471,000
B)Similarly we can calculate amount at different interest rates,
The cell [Amount in $] being B2,
use the formula { =$B3*30+435*$B3*C$2 } to calculate amount at 0% and principal 5000.
Amount for all other cells is simply obtained by dragging the right hand bottom corner of the cell over the whole area.
Amount in $ 0% 1% 2% 3% 4% 5% 6% 7% 8% 9% 10% 11% 12% 5000 150000 171750 193500 215250 237000 258750 280500 302250 324000 345750 367500 389250 411000 6000 180000 206100 232200 258300 284400 310500 336600 362700 388800 414900 441000 467100 493200 7000 210000 240450 270900 301350 331800 362250 392700 423150 453600 484050 514500 544950 575400 8000 240000 274800 309600 344400 379200 414000 448800 483600 518400 553200 588000 622800 657600 9000 270000 309150 348300 387450 426600 465750 504900 544050 583200 622350 661500 700650 739800 10000 300000 343500 387000 430500 474000 517500 561000 604500 648000 691500 735000 778500 822000 11000 330000 377850 425700 473550 521400 569250 617100 664950 712800 760650 808500 856350 904200 12000 360000 412200 464400 516600 568800 621000 673200 725400 777600 829800 882000 934200 986400 13000 390000 446550 503100 559650 616200 672750 729300 785850 842400 898950 955500 1012050 1068600 14000 420000 480900 541800 602700 663600 724500 785400 846300 907200 968100 1029000 1089900 1150800 15000 450000 515250 580500 645750 711000 776250 841500 906750 972000 1037250 1102500 1167750 1233000 16000 480000 549600 619200 688800 758400 828000 897600 967200 1036800 1106400 1176000 1245600 1315200 17000 510000 583950 657900 731850 805800 879750 953700 1027650 1101600 1175550 1249500 1323450 1397400 18000 540000 618300 696600 774900 853200 931500 1009800 1088100 1166400 1244700 1323000 1401300 1479600 19000 570000 652650 735300 817950 900600 983250 1065900 1148550 1231200 1313850 1396500 1479150 1561800 20000 600000 687000 774000 861000 948000 1035000 1122000 1209000 1296000 1383000 1470000 1557000 1644000Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.