Diana Bullard currently rents space for her small florist business. As her business continues to
\ngrow, Diana has decided to purchase her own building. She has selected a site and now requires
\nfinancing. After meeting with several banks to discuss financing options for a mortgage, Diana
\nhas the data she needs to analyze her options. She lists the purchase price of the building and the
\ndifferent values for each of the loan variables together with the other data inputs in an Excel
\nworkbook named Loan.xlsx. Her analysis must also take into account the following:
\n\u2022 Down payment\u2014The amount of money Diana will pay at the time she purchases the
\nbuilding. Provided is the percent of the building purchase price that will be required for a
\ndown payment on each corresponding loan. The difference between the sale price and the
\ndown payment is the loan value\u2014the face value of the loan.
\n\u2022 Points\u2014The additional charges banks sometimes require when lending a mortgage. Banks
\nusually offer mortgage loans in a variety of interest rate and point combinations. Frequently,
\nloans with higher points have lower interest rates. One point equals 1% of the loan value, so
\none point on a $7,500 loan is $75.
\n\u2022 Fees\u2014The additional amounts banks sometimes charge when lending a mortgage. These
\namounts vary by bank and loan type. Typical charges include application fees, appraisal fees,
\ncredit report fees, and so on.
\nYour task is to complete the Loan worksheet for Diana, using cell references whenever possible.
\nWrite the formulas in cells G8 through K8 so that they can be copied down the column to
\ncalculate values for each option. Write the formulas so they will automatically update if the
\nmortgage value changes. Loan options 1\u20137 are all compounded monthly.
\nComplete the following:
\n1. Open the workbook named Loan.xlsx. The file is attached to the RWP2 assignment link in
\nthis learning module. Save the file name as FirstName_LastName_LoanAnalysis.xlsx.
\n2. In the Loan Value column, calculate the face value of this mortgage. The purchase price of
\nthe building is in cell E3.
\n3. In the Monthly Payment column, calculate the monthly mortgage payment for this loan
\namount based on the loan value you just calculated. Use the corresponding loan duration, and
\nnominal interest rate indicated. Assume that the loan is completely paid off at the end of this
\nduration. The number of compounding periods per year is in cell E4.
\n4. In the Actual Amount Borrowed column, calculate the actual amount Diana will borrow by
\nsubtracting the points and fees from the loan value.
\n5. To take these fees into account, the lender is required by law to disclose the APR (the annual
\npercentage rate of interest) of the loan being charged. However, banks can calculate APR in
\n2
\ndifferent ways, including or excluding different fees. To calculate an actual annual interest
\nrate being charged on this loan (APR), use the actual amount borrowed (Step 4) as the
\npresent value of the loan, the monthly payment (Step 3), and the corresponding loan duration.
\n6. In the Payment with Balloon column, use the nominal interest rate and loan value (from
\ncolumn G) to determine the monthly loan payment if you altered the loan to include a
\n$20,000 balloon payment at the end of the loan.
\n7. The building seller has also offered Diana a private loan for 85% of the value of the building.
\nIn return, Diana must pay $4,500 per month for the next 10 years. Determine the annual
\ninterest rate being charged (cell E17). Inputs do not have to be explicitly listed elsewhere.
\n8. Diana is negotiating with the seller and is willing to pay $10,000 per quarter at 6\u00bd % interest
\nper year compounded quarterly. She will borrow everything but a 10% down payment.
\nDetermine how many years it will take to pay off the loan (cell E18). Inputs do not have to be
\nexplicitly listed elsewhere.
\n9. Ten years ago, Diana invested $75,000 in a bank CD. The CD has earned 3.25% annual
\ninterest compounded yearly. Determine (TRUE\/FALSE) if Diana has sufficient funds from
\nthis CD for the down payment for Option #1 (cell E19).
\n10. Diana has decided that she prefers a bank loan and, given cash flow issues, wants the loan
\nwith the smallest payment. Highlight in light blue the cell in column H containing the
\npayment of the loan Diana should select.
\n11. Save and close the FirstName_LastName_LoanAnalysis.xlsx workbook.<\/p>\n","protected":false},"excerpt":{"rendered":"
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. Read More …<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-425676","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/425676","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/comments?post=425676"}],"version-history":[{"count":0,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/425676\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/media?parent=425676"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/categories?post=425676"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/tags?post=425676"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}