Question

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 2.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.4% per year with monthly compounding for a 30-year fixed rate loan.
   1. How much money will the bank loan you?
   2. How much can you offer for the house?
   3. Create a loan amortization table in Excel and submit the spreadsheet. This should be done with monthly payments.

Answer 1

Bank Budget for Housing Cost:
Annual Salary	$ 200000
Monthy Salary	$ 16666
Housing Cost Budget/Month (28% of $ 16666)	$ 4667
Estimated Mortgage Cost Cmputation
Housing Cost	4667
Less: Property Tax	1000
Less: Home Owners Insurance	100
Mortgage Cost/Month - Estimated	3567
Interest On Home Loan	3.4% p.a.
Interest Rate per month	0.28%
Tenure	30 Years
EMI Months	360	Instalments
The home loan EMI calculation formula is EMI = [P x R x (1+R)^N]/[(1+R)^N-1] where, P: Principal amount R: Interest rate (per month) N: Number of monthly instalments/loan tenure in months. Alternatively, you can use MS Excel to know your home loan EMI. The formula to calculate home loan EMI in MS Excel is PMT (rate, nper, pv) where,
Here the equation , 3567 = [P* 0.28%* (1+28%)^360]/[(1+.28%)^360-1]
So from above euation you will cometo know about P which is the principal repayment amount, the loan of which can be given by bank to you.
Total involvement of Fund= P+$50000