ZuoTi.Pro
Question

Generate Annuity in Excel

Sally needs to buy a new car and has it narrowed down to three choice with price being the deciding factor. She is putting $5,000 down on the vehicle. What is the best option for her? 3 4 1) Determine PMT, Interest Payment, Cumulative interest paid and the total amount paid for each car (B28:D31)? 82) Complete a full amortization table that can be updated based on the car that she will buy (F19:K80) 93) What is her best option and why? Answer in the box below 10 11 Requirement: Use financial functions where applicable 12 13 14 3) 15 16 17 18 Car 2 $ 42,900$40,000 38,500 $ (5,000) $ (5,000)$ (5,000) $ 37,900 $ 35,000$ 33,500 Car 1 Car 3 Car Being purchased Payment Principle Payment Interest Principle Ending Balance 19 20 Purchase Price 21 Down Payment 22 Amount Financed 23 Rate per Annum 24 Length 1.99% 60 4.00% 60 6.00% 60 25 Begin Period 26 End Period 60 60 60 27 28 Payment 29 Cumulative Interest 10 30 Cumulative Principle 31 Total Paid for Car 32 12

business   finance   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

3) She has to purchase car2 since the less amount she will pay on this only
2)Full amortization table for car2 will be provided below
Car 1 Car 2 Car 3 38500 5000 2 Purchase price 42900 3 Down payment 5000 4 Amount financed B2+B3 5 Rate per annum 0.0199 6 Length 7 Begin period 8 End periof 9 10 Pavment 11 Cumm interest CUMIPMT(B5/12,B8, B4,B7,B8,0)CUMIPMT(C5/12,C8,C4,C7,C8,0) CUMIPMT(D5/12,D8,D4,D7,D8,0) 12 Cumm principle |--CUMPRINC(B5/12,88,84. B7,88,0)--CUMPRINC(C5/12.C8,C4.c7,C8,0) |--CUMPRINClD5/12.D8.D4. D7, D8,0 13 Total paid for car B11+B12 14 5000 C2+C3 D2+D3 0.06 0.04 PMT(B5/12,B6,-B4,0,0 PMT(C5/12, C6,-C4,0,0 PMT(D5/12,D6,-D4,0,0 -C11+C12 D11+D12

1) payment payment Interest Principle End bal 527.91 34,472.09 529.67 33,942.42 531.44 33,410.98 533.21 32877.77 35,000.00 644.5 116.67 34 472.09 644.58114.91 33,942.42 644.58 113.14 33.410.98 644.58 111.37 32,877.77 644.58 109.59 32.342.79 644.5107.81 31,806.02 644.58106.02 31,267.46 644.58 104.22 30,727.11 644.58 102.42 30,184.95 644.58 100.62 29,640.99 644.58 29,095.21 644.58 96.98 28.547.62 644.58 95.16 27,998.20 644.58 93.33 27446.95 644.58 91.49 26,893.86 644.58 26,338.93 644.58 87.80 25,782.15 644.58 25,223.51 644.58 84.08 24,663.01 644.58 24,100.64644.5880.34 23,536.40 644.58 22,970.27 644.58 22,402.26 644.58 74.67 21,832.36 644.5872.77 21,260.56 644.58 70.87 20,686.85 644.58 20,111.22 644.5867.04 19.533.6 644.5 65.11 18,954.22 644.58 18,372.82 644.58 61.24 17,789.48 644.58 17,204.20 644.5857.35 16,616.97 644.58 nt Amount financed 35,000 534.99 32,342.79 536.77 31,806.02 538.56 31,267.16 540.35 30,727.11 542.15 30,184.95 543.96 29,640.99 545.77 29,095.21 60 gin perio 647.65 5,358.93 1,948.18 3,674.70 39,848.18 38,674.70 547.59 28,547.62 549.42 27,998.20 551.25 27,446.95 553.09 26,893.86 554.93 26,338.93 556.78 25,782.15 558.64 25,223.51 560.50 24,663.01 562.37 24,100.64 Total paid for car 38,858.93 89.65 566.12 22970.27 568.01 22,402.26 569.90 21,832.36 571.80 21,260.56 573.71 20,686.85 575.62 20,111.22 577.54 19,533.68 5/9.47 18,954.22 581.40 18,372.82 583.34 17,789.48 585.28 17,204.20 587.23 16,616.97 589.19 16,027.79 15.436.63 76.57 593.12 14,8 14843.51 595.10 14,248.41 597.08 13,651.33 599.07 13,052.25 601.07 12451.18 603.07 11848.11 605.08 11,243.02 607.10 10,635.92 609.13 10,026.80 15436.63 644.5 51.46 14,248.41 644.58 47.49 13,651.33 644.58 13,052.25 644.58 43.51 12451.18 644.58 45.50 8.11644.58 39.49 11,243.02 644.58 10,635.92 644.58 10,026.80 644.58 33.42 611.16 9415 613.19 8,802.45 615.24 8,187.21 617.29 7.569.92 619.356,950.58 621.41 6,329.17 623.18 5,705.69 625.56 5,080.13 627.64 4.452.48 629.74 3,822.75 631.84 3,190.91 2,556.97 636.06 1,920.91 46 9.415.64 644.58 8,802.45 644.58 8,187.21 644.58 7,569.92 644.58 25.23 6.950.58 644.58 6,329.17 644.58 5,705.69 644.5819 5,080.13 644.58 4,452.48 644.58 14.8 3,822.75 644.58 3,190.91 5810.64 2,556.97 644.58 1,920.91 644.58 1,282.74 644.58 54 56 642.44 644.58

payment Principle payment Interest Principle End bal -C4 F2* ($C$5/12) G2-H2 C$10 F3*(SC$5/12) G3-H3 3 -E2+1 E3+1 E4+1 F4*(SC$5/12) -G4-H F5* ($C$5/12)G5-H5 F6* (SC$5/12) G6-H6 F7 (SC$5/12) G7-H7 F8*($C$5/12) G8-H8 F9* ($C$5/12) G9-H9 F10* (SC$5/12) G10-H10 F11*(SC$5/12) G11-H11 F12*(SC$5/12) -G12-H12 F13 (SC$5/12) G13-H13 F14*(SC$5/12) G14-H14 F15($C$5/12)-G15-H15 F16 (SC$5/12) G16-H16 F17* ($C$5/12) G17-H17 F18* (SC$5/12) G18-H18 -F19* (SCS5/12 G19-H19 F20* ($C$5/12) -G20-H20 F21* ($C$5/12) G21-H21 F22* (SC$5/12) G22-H22 F23* ($C$5/12) -G23-H23 F24*(SC$5/12) G24-H24 F25*($C$5/12) G25-H25 F26*(SCS5/12) G26-H26 F27* (ŞC$5/12) -G27-H27 F28*(SC$5/12) -G28-H28 J3 SC$10 $C$10 F5-15 F6-16 -F7-17 U5 J8 10 E9+1 11E10+1 J10 121=E11+1 |=11 13 E12+1 J12 14 E13+1 J13 15-E14+1 14 16 E15+1 J15 F10-110 F11-111 F12-112 -F13-113 F 14-ו14 F15-115 F16-116 F17-117 -F18-118 F19-119 F20-120 -$C$10 -$C$10 181-E17+1 ー17 19 E18+1 J18 20 E19+1 J19 21 E20+1 J20 22 E21+1 J21 23 E22+1 J22 24 E23+1 J23 25 E24+1 J24 26 E25+1 J25 27|=E26+1 -J26 281=E27+1 |s)27 29E28+1 J28 30 -E29+1 -J29 31 =E30+1 |=130 32 E31+1 J31 33 E32+1 J32 34 E33+1 J33 35 -E34+1 J34 F22-122 -F23-123 F24-124 F25-125 F26-126 F27-127 -F28-128 F29-129 F30-130 F30*(SCS5/12) G30-H30 F32*($C$5/12) G32-H32 F33* (SC$5/12) -G33-H33 F34* (SC$5/12) -G34-H34 F35 (SC$5/12) G35-H35 F32-132 -F33-133 F34-134 F35-135

-F35* (SC$5/12) -G35-H35 -F36 (SC$5/12) -G36-H36 -F37*(SCS5/12) -G37-H37 -F38* (SC$5/12) -G38-H38 -F39* ($C$5/12) G39-H39 -F4

0 0
Add a comment
Know the answer?
Add Answer to:
Generate Annuity in Excel Sally needs to buy a new car and has it narrowed down...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • Paula is considering the purchase of a new car. She has narrowed her search to two...

    Paula is considering the purchase of a new car. She has narrowed her search to two cars that are equally appealing to her. Car A costs $28,000, and Car B costs $28,500. The manufacturer of Car A is offering 0% financing for 48 months with zero down, while the manufacturer of Car B is offering a rebate of $2000 at the time of purchase plus financing at the rate of 3%/year compounded monthly over 48 months with zero down. If...

  • A friend of yours bought a new sports car with a $5,500 down payment plus a...

    A friend of yours bought a new sports car with a $5,500 down payment plus a $32,000 car loan that is financed at an interest rate of 0.75% per month for 60 months. After 2 years, the "Blue Book' value of her vehicle in the used-car marketplace is $19,000 a. Calculate the required monthly loan payment on the car b. How much does your friend still owe on the car loan immediately after she makes her 24th payment? d. If...

  • A friend of yours bought a new sports car with a ​$4,500 down payment plus a...

    A friend of yours bought a new sports car with a ​$4,500 down payment plus a ​$25,000 car loan that is financed at an interest rate of 0.25​% per month for 60 months. After 2 ​years, the​ "Blue Book" value of her vehicle in the​ used-car marketplace is ​$14,000. a. Calculate the required monthly loan payment on the car. b. How much does your friend still owe on the car loan immediately after she makes her 24th ​payment? c. Compare...

  • A friend of yours bought a new sports car with a ​$4,500 down payment plus a...

    A friend of yours bought a new sports car with a ​$4,500 down payment plus a ​$25,000 car loan that is financed at an interest rate of 0.25​% per month for 60 months. After 2 ​years, the​ "Blue Book" value of her vehicle in the​ used-car marketplace is ​$14,000. a. Calculate the required monthly loan payment on the car. b. How much does your friend still owe on the car loan immediately after she makes her 24th ​payment? c. Compare...

  • A friend of yours bought a new sports car with a $5,500 down payment plus a...

    A friend of yours bought a new sports car with a $5,500 down payment plus a $29,000 car loan that is financed at an interest rate of 0.50% per month for 60 months. After 2 years, the "Blue Book" value of her vehicle in the used-car marketplace is $17,000. a. Calculate the required monthly loan payment on the car. b. How much does your friend still owe on the car loan immediately after she makes her 24th payment? c. Compare...

  • A friend of yours bought a new sports car with a $4,500 down payment plus a...

    A friend of yours bought a new sports car with a $4,500 down payment plus a $29,000 car loan that is financed at an interest rate of 0.25% per month for 60 months. After 2 years, the "Blue Book" value of her vehicle in the used-car marketplace is $16,000. a. Calculate the required monthly loan payment on the car. b. How much does your friend still owe on the car loan immediately after she makes her 24th payment? c. Compare...

  • Jinhee Ju. 27. has an annual salary of $37,000. Jinhee wants to buy a new car in 3 years, and she wants to save e...

    Jinhee Ju. 27. has an annual salary of $37,000. Jinhee wants to buy a new car in 3 years, and she wants to save enough money to make a $7,000 down payment on the car and finance the balance. Also, in her plan is a wedding. Jinhee and her boyfriend, Paul, have set a wedding date 2 vears in the future, after he finishes medical school. Paul will have a $100,000 student loan to repay after graduation. But both Jinhee...

  • Mrs. Landingham recently purchased a new car. In addition to her down payment she will borrow...

    Mrs. Landingham recently purchased a new car. In addition to her down payment she will borrow $10,000 to pay for the car, which she will pay back with 60 equal monthly payments over the next five years. The stated annual interest rate is 12%, compounded monthly. If she receives the loan today and makes her first payment one month from today, what will be the amount of her first payment? I've tried so many times and I don't have any...

  • Carla decides to buy a new car with a commercial value of $ 180 000 with...

    Carla decides to buy a new car with a commercial value of $ 180 000 with her savings she will make a down payment of 20% of the car's value and the rest in 36 fixed monthly payments payable at the end of each month. If the finance company that will grant you the vehicle loan will charge you an annual interest rate of 11%, compounded monthly, what is the amount of the fixed monthly payment that you must pay?

  • Sally is a 36 year-old, divorced, mother of two who lives in West Chester, PA. Her...

    Sally is a 36 year-old, divorced, mother of two who lives in West Chester, PA. Her two boys, Alex (age 5) and Andrew (age 8) live with her full-time and attend Chesterbrook Academy, a nearby private elementary school. Sally works as a Software engineer for Cerner Corporation in Malvern, PA. Sally’s aging mother also lives with them, and Sally provides full financial support for her. The following transactions occurred over 2018. 1) Sally received $125,000 salary in 2018 from her...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT