Question: You are an assistant to John Moore, a mortgage broker at the Northeast Mortgage Company. John spends his days reviewing mortgage rates and trends, meeting

You are an assistant to John Moore, a mortgage broker at the Northeast Mortgage Company. John spends his days reviewing mortgage rates and trends, meeting with clients, and preparing paperwork. He relies on your expertise in using Excel to help analyze mortgage data. Today, John provided you with sample mortgage data: loan number, house cost, down payment, mortgage rate, and the length of the loan in years. He has asked you to perform some basic calculations so that he can check the output provided by his system to verify that it is calculating results correctly.

(a) In cell B5, input the current date and time, which should be the date and time whenever John examines the spreadsheet. (b) Calculate the amount financed in cell E11:E15. (c) The APRs (annual percentage rates) vary based on the number of years to pay off the loan. Table 2 shows the details of APRs when the numbers of years to pay off the loan are 15, 25, and 30. Fill in the cells F11:F15 using any look-up functions.

(please provide excel code )

1 23456 A Table 1. Basic Information Input Area B C Northeast Mortgage Company Date and Time # Pmts Per Year: 7 PMI Rate 8 Down Pmt Rate: 9 Loan # 10 11 12 798504 502321 891546 13 14 655369 504017 15 16 17 18 Loan # 19 891546 20 504017 21 22 23 12 0.38% 20.00% House Cost Down Payment $ 425,750.00 $ 86,000.00 $ 335,900.00 $ 75,500.00 $ 205,500.00 $ 30,000.00 285,900.00 $ 50,000.00 329,900.00 $ 70,000.00 $ PMI Status D E Table 2. Annual Percentage Rates (APR) Years APR 15 3.25% 25 3.50% 30 3.75% Years Amount Financed 25 30 25 15 30 F LL APR G Monthly Payment H Total Interests %Down I J Monthly PMI K L 1 23456 A Table 1. Basic Information Input Area B C Northeast Mortgage Company Date and Time # Pmts Per Year: 7 PMI Rate 8 Down Pmt Rate: 9 Loan # 10 11 12 798504 502321 891546 13 14 655369 504017 15 16 17 18 Loan # 19 891546 20 504017 21 22 23 12 0.38% 20.00% House Cost Down Payment $ 425,750.00 $ 86,000.00 $ 335,900.00 $ 75,500.00 $ 205,500.00 $ 30,000.00 285,900.00 $ 50,000.00 329,900.00 $ 70,000.00 $ PMI Status D E Table 2. Annual Percentage Rates (APR) Years APR 15 3.25% 25 3.50% 30 3.75% Years Amount Financed 25 30 25 15 30 F LL APR G Monthly Payment H Total Interests %Down I J Monthly PMI K L

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!

Exp 2 2 _ Excel _ Ch 0 2 _ HOE - Townsend Mortgage Company Project Description: You are an assistant to Yui Matheson, a mortgage broker at the Townsend Mortgage Company. Yui spends days reviewing...

CERTIFICATE IV IN FINANCE AND MORTGAGE BROKING - FN540820 Page 1 UNIT 9 MANAGE PERSONAL AND PROFESSIONAL DEVELOPMENT Unit Code: BSBPEF501 This unit describes the skills and knowledge required to...

I am doing tax return project. attachments are the materials professor provide. He wants me to do a current year engagement file (Similar with prior year engagement file). AC 371 Tax Return Project...

hi. I'm stuck with a report whose Chic Paints Ltd. and I am finding difficulty to answer some questions such as the organisations accounting system requirements including hardware and software...

The AMB Consolidation On August 13, 1997, Anne Shea of the Curators' Fund (the Fund) sat in her office overlooking downtown Kansas City, knowing she had to decide her position on the proposed...

Hello, I need help with with Return of the Tallahassee Beancounters Case (the 2010 version). The solution and teachers handouts for the 2003 version have already been made available, however it's...

Your end-of-semester assignment is as follows: (a) (i) read the two Economist articles entitled ?Debt is Good for You? (dated 01/25/2001) and ?Debtors? Prison? (dated 02/09/2009), which discuss the...

How are the changes that Taco Bell implemented, such as K-minus, SOS, etc., similar to, or different from, the various concepts/techniques that were presented in weeks 1-8 in this module. Provide a...

Question 1 1 pts A broker brings his seller an offer from a potential buyer. If the seller asks about the buyer's race, the broker: Group of answer choices must disclose this information to the...

List the four parts of the Code of Professional Conduct, and state the purpose of each.

Find the equation of the circle with (2, 7) and (-6, 13) as the extremeties of its diameter. O (x + 8)2.+ (y 10)2 = 10 O (x + 2)2 + (y 6)? = 64 %D O (x + 2)2 + (y 10)2 = 25 O (x - 2)2 + (y + 10)2...

According to Benford's law, numbers with the first digit " 4 " are more lkely than those wth first digt " 3 " , True or Folse

10) For a bond that pays cash interest (i.e., not a zero coupon bond), YTM is (a) always equal to the coupon rate (b) always equal to the current yield (c) is always less than the coupon rate (d) can...

Although benefits account for a large share of employee compensation, many feel there is little evidence on whether an employer receives an adequate return on the benefits investment. One suggestion...

Your company, like many others, is experiencing doubledigit percentage increases in health care costs. What suggestions can you offer that may reduce the rate of cost increases?