Question

Kindly do on excel and screenshot with formulas.

1. You are ready to buy a house and you have $50,000 for a down payment and closing costs. Closing costs are estimated to be 3.5% of the loan value. You have an annual salary of $200,000. The bank is willing to allow your housing costs – mortgage, property tax, and homeowners insurance to be equal to 28% of your monthly income. You have estimated that property tax will be $1,000/month and homeowner’s insurance will be $100/month. The interest rate on the loan is 3.9% per year with monthly compounding for a 30-year fixed-rate loan.
a. How much money will the bank loan you?
b. How much can you offer for the house?
c. Create a loan amortization table in Excel and submit the spreadsheet. This should be done with monthly payments.

2. Canine, Inc., has identified an investment project with the following cash flows. If the discount rate is 8 percent for the first 2 years and 6% for the years after, beginning in year 3, what is the future value of these cash flows in Year 5? What is the future value of the cash flows in year 10? What is the present value of the cash flows?

Year   Cash Flow
1   $1,075
2   $1,235
3   $1,510
4   $1,965
5   0
6   0
7   $10,000

Answer 1

first we need to compute the maximum amount of money that the bank will be willing to lend

for that we need to find out the maximum monthly mortgage payment allowed by bank based on annual income.

the monthly mortgage payment       = $3,566.67

rate of interest    r    =   3.9% p.a or 0.325% per month   or 0.00325

period of repayment n =   360 months ( 30 yearsX12)

Let P be the loan amount,

We know,

equated monthly payments    

substituting,

                               

                                                

                               

                                           

The maximum amount bank would lend is $ 756,181.28

b)

now lets find out the amount of Closing Cost and down payment available

        

c) amortization schedule

the excel formula used

.