I have an excel project I'm having trouble with. I need to use financial formulas but I'm not getting the appropriate results. I have completed questions a-d. When using the Rate formula, to achieve $50,000 in a matter of 2 years saving $1,000 a month, i get a rate of -5.55%. When testing this out using the FV formula, the total does not come out to $50,000 but instead I get $47,871.55. I need help correcting this formula. I'm including screen shots of the entire instruction packet as well as the excel screen shots. The first excel sreen shots show my results and the second one shows the formulas used. Purchasing a HomeYou want to purchase a home in the future. You have decided to begin setting money aside each monthtoward your down payment. Your goal is to have 20% down when you are ready to purchase your home. Tohelp determine how much you should put away and how long it will take, you have decided to create an Excelworkbook to track your progress and help you visualize your success.a. Open the Excel file, e06ch11Home. Save your file as e06ch11Home_LastFirst using your last and first name.b. Create a blank worksheet, and rename the worksheet Home.c. Enter the following data, and label appropriately.• Enter the amount you are planning to be able to pay for the home you want to purchase, $250,000.• Enter a formula to calculate the amount of down payment you will need, 20% of home purchase value.• Enter the amount of interest you will earn, 0.01%.• Enter the amount of money you are setting aside, $1,000/month.d. Enter formulas to answer the questions below, labeled appropriately.• How long would you need to put money aside to arrive at your 20% down payment?• You want to buy a home within 2 years. In a blank cell, using the information above, calculate the amountyou would need to set aside each month to accomplish this goal.• You do not have enough money coming in to set this amount aside each month. Using the originalinformation, what rate of interest would you need to earn to keep the monthly payments at $1,000?• If you could set aside $1,500 at 2.45% interest, how much would you have set aside after 2 years?e. You have decided that you will not be able to buy the home after 2 years unless you invest in low-risk bonds.You were able to find a short-term bond in which to invest. The par value is $10,000, the coupon rate is 5%annually, the maturity is 2 years, and the yield to maturity is 8%.• Enter the data above in separate cells labeled appropriately.• Create a formula to calculate the coupon payment and label.• Determine the present value of the bond, and label appropriately.f. You are not sure it's worth it to save the money. You decide to see what difference saving the money willmean as you make your future payments. Click on the Amortization worksheet. Complete the twoamortization schedules using IPMT and PPMT functions.g. To the right of your amortization schedules, calculate the total amount of savings due to the down payment.• Below your amortization schedules, calculate the total payments made for each loan.• Calculate the difference in the total payments made over the two schedules.From the result, subtract the original S50,000 deposit you made.• Confirm the result of your calculation by calculating the total cumulative interest payments made for the lifeof the loan for each loan. Subtract the two calculated cumulative interest payments. The result should be thesame as your calculation above. These calculations show the amount you have saved by having a downpayment on your loan.h. Format both worksheets to provide a professional appearance.į. Save the workbook, exit Excel, and then submit your file as directed by your instructor. BDFHK1 Loan Option 1Down payment #2Down payment 32 Amount of Loan3 Down Payment Rate4 Down Payment5 Interest Rate Earn6 Monthly Deposit7 Number of months8 Desired months9 payment needed$250,000$50,000.00$1,000.00$50,000.00$1,500.00Down PaymentDown Payment20%Monthly DepositMonth Deposit$50,000Desired years24Desired Months240.01%Rate needed-5.55%Rate2.45%$1,000Amount Earned$47,871.55Amount Earned$48,224.6350.1279244624$2,085.9410 rate needed-5.55%111213AFHK1 Loan Option 12 Amount of Loan3 Down Payment Rate4 Down Payment5 Interest Rate Earn6 Monthly Deposit7 Number of months8 Desired months9 payment needed10 rate neededDown payment #2Down PaymentDown payment 3Down PaymentMonth Deposit250000S0000500000.2Monthly Deposit10001500SUM(C2*C3)Desired years24Desired Months240.0001Rate needed=RATE(G4,G3,-G2,1)Rate0.02451000Amount Earned-FV[-G5,G4,-G3)Amount Earned-FV(K5,K4,-K3)=NPER(CS,C6,-C4)24=PMT(C5,C8,-C4)RATE(C8,C6,-C4,1)1112

The appropriate excel formulas are used to compute the time period, rate, and monthly payments on…

Answered: d. Enter formulas to answer the…

11th Edition

ISBN:9781260013924

Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Chapter1: Investments: Background And Issues

Section: Chapter Questions

Problem 1PS