Listed below are the case problem, excel sheet, and check worksheet. Even though the check worksheet shows what the final answer should look like, we have to get the answer by using the proper functions/equations.

Level 1 Question Evaluating Loan Options for Flowers By Diana Diana Bullard currently rents space for her small florist business. As her business continues to grow, Diana has decided to purchase her own building. She has selected a site and now requires financing. After meeting with several banks to discuss financing options for a mortgage, Diana has the data she needs to analyze her options. She lists the purchase price of the building and the different values for each of the loan variables together with the other data inputs in an Excel workbook named Loan.xlsx. Her analysis must also take into account the following: Down paymentThe amount of money Diana will pay at the time she purchases the building. Provided is the percent of the building purchase price that will be required for a down payment on each corresponding loan. The difference between the sale price and the down payment is the loan valuethe face value of the loan. PointsThe additional charges banks sometimes require when lending a mortgage. Banks usually offer mortgage loans in a variety of interest rate and point combinations. Frequently, loans with higher points have lower interest rates. One point equals 1% of the loan value, so one point on a $7,500 loan is $75. FeesThe additional amounts banks sometimes charge when lending a mortgage. These amounts vary by bank and loan type. Typical charges include application fees, appraisal fees, credit report fees, and so on. Your task is to complete the Loan worksheet for Diana, using cell references whenever possible. Write the formulas in cells G8 through K8 so that they can be copied down the column to calculate values for each option. Write the formulas so they will automatically update if the mortgage value changes. Loan options 17 are all compounded monthly. Complete the following:

1. Open the workbook named Loan.xlsx in the Chapter 6 folder, and then save the file as Loan Analysis.xlsx.

2. In the Loan Value column, calculate the face value of this mortgage. The purchase price of the building is in cell E3.

3. In the Monthly Payment column, calculate the monthly mortgage payment for this loan amount based on the loan value you just calculated. Use the corresponding loan duration, and nominal interest rate indicated. Assume that the loan is completely paid off at the end of this duration. The number of compounding periods per year is in cell E4.

4. In the Actual Amount Borrowed column, calculate the actual amount Diana will borrow by subtracting the points and fees from the loan value.

5. To take these fees into account, the lender is required by law to disclose the APR (the annual percentage rate of interest) of the loan being charged. However, banks can calculate APR in different ways, including or excluding different fees. To calculate an actual annual interest rate being charged on this loan (APR), use the actual amount borrowed (Step 4) as the present value of the loan, the monthly payment (Step 3), and the corresponding loan duration.

6. In the Payment with Balloon column, use the nominal interest rate and loan value (from column G) to determine the monthly loan payment if you altered the loan to include a $20,000 balloon payment at the end of the loan.

7. The building seller has also offered Diana a private loan for 85% of the value of the building. In return, Diana must pay $4,500 per month for the next 10 years. Determine the annual interest rate being charged (cell E17). Inputs do not have to be explicitly listed elsewhere.

8. Diana is negotiating with the seller and is willing to pay $10,000 per quarter at 6% interest per year compounded quarterly. She will borrow everything but a 10% down payment. Determine how many years it will take to pay off the loan (cell E18). Inputs do not have to be explicitly listed elsewhere.

9. Ten years ago, Diana invested $75,000 in a bank CD. The CD has earned 3.25% annual interest compounded yearly. Determine (TRUE/FALSE) if Diana has sufficient funds from this CD for the down payment for Option #1 (cell E19).

10. Diana has decided that she prefers a bank loan and, given cash flow issues, wants the loan with the smallest payment. Highlight in light blue the cell in column H containing the payment of the loan Diana should select. Optional Challenge: Using Conditional Formatting, highlight the cell containing the minimum payment value, so that if any of the values on any of the bank loans are later modified, the correct value will be automatically highlighted.

11. Save and close the Loan Analysis.xlsx workbook.

Loan Analysis Worksheet $500,000 $20,000 Purchase Price Number of Compounding Periodslyear Optional Balloon Payment Actual Nominal Interest Duration Payment Loan Quarterly Monthly Amount Down nt Borrowed APR with Balloon Points Fees Value 3375 3375 3 375 (yrs) Payment P Option#| Payment | Rate/yr 4.90% 525% 5.75% 6.25% 5.50% 6.00% 6.50% 30 20% 10% 5% 0% 20% 10% 2 30 30 30 15 15 10 0 0 12 5 0350 0350 13 15 17 7 Private loan (1) annual rate Private loan (2) duration CD value enough for down 20 13 In the APR column, formulas using he RATE function entered that alculate the annual percentage rate of interest charged 3 Purchase Price 4 Number of Compounding Periods/year In the Loan Value column, formulas entered that calculate the face value of the mortgage $500,000 5 Optional Balloon Payment $ 20,000 Actual LoanQuarterly MonthlyAmount Down Inter rest Duration Payment Payment Ratey (yrS) Points Fees Value Payment Payment Borrowed APR with Balloon 3| 375| 400,000 | ($6.380. 11)|($2.126.70)| 387,625| 5.18%| ($6,306.10) 3| 375 450,000 | ($7468.13)| ($2.489.3 3| 375 475,000| ($8.330. 79)|($2,776.93)| 46 375| 6,04%| ($8,267.52) ;s 0D5anDOD,,000BID ($9.252.07) ($3,084.02) 43 .tuiS9 GaiW , ($9,194.49) 1 000,0SEO ($6.825.69) ($2.275.23): 6,001969 tiise, ($6,759.41) 0 350 450,000 ($11,427.04) ($3,809.01) 449,650 6.01% ($11,219.17 0| 4.90% 5.25% 5.75% 6.25% In the Payment with Balloon column, formulas using the PMT function entered that determine the monthly loan payment if the loan is altered to include a S20,000 balloon payment at the end of the loan 10% 5%| 436,125| 5.53% | ($7,398.71) 10 13 14 6 10%| 6.00% 6.50% 350| 475,000 |($12.452.81)| ($4,150.94)| 474,650| 6.51% 253.48 Recommended monthly payment highlighted in light blue 17 Private loan (1) annual rate: 18 Pivate loan (2) duration: 19 CD value enough for down payment: In the Quarterly Payment column, formulas using the PMT function entered that calculate the monthly mortgage payments 5.096 20.38 TRUE In the Actual Amount Borrowed column, formula entered to calculate the actual amount borrowed 21 In Cell E17, formula using the RATE function entered to determine the interest 23 24 ate being charged for the private loan 25 (S4500 paid monthly for 10 years) 26 27 28 In Cell E18, formula using the NPER function entered to calculate the number of years it would take to pay off the loan at a rate of 619% In Cell E19, formula using the F entered to determine if there will be sufficient funds available generated from the $75,000 CD investment t function