Question
In: Accounting
You are ready to buy a house and you have $50,000 for a down payment and...
- 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.
- How much money will the bank loan you?
- How much can you offer for the house?
- Create a loan amortization table in Excel and submit the spreadsheet. This should be done with monthly payments.
- show all work in excel
Solutions
Expert Solution
| Given: | |
| Amount available for down payment & closing cost (a) | $50,000 |
| Closing cost (% of loan value) (b) | 2.50% |
| Annual salary (c) | $200,000 |
| Housing cost allowed by Bank (% of monthly income) (d) | 28% |
| Interest rate on loan (annual compunded monthly) (e) | 3.40% |
| Tenure of loan (in years) (f) | 30 |
| Workings: | |
| Monthly housing cost allowed by Bank (g)=(c)/12*(d) | $4,667 |
|
PV of ordinary annuity (h) [Excel formula] PV(rate,nper,pmt,type)*-1 = PV(3.40%/12,12*30,4667,0)*-1 |
$1,052,281 |
| Closing cost (i)=(b)*(h) | $26,307 |
| Amount that can be offered for the house (a)+(h)-(i) | $1,075,974 |
| Bank will loan a maximum of $1,052,281 | |
| Amount that can be offered for the house is $1,075,974 | |
| Note: All amounts rounded off |
As the loan amortisation scedule is having 360 rows not able to submit in the window. The formulas are provided to prepare the table and table for first 20 and last 20 periods are provided for reference.
| Period | Payment (a) | Principal (b) | Interest (c) | Balance |
| 1 | Monthly repayment in negartive | Total amount of loan | Principal * rate/12 | (a)+(b)+(c) |
| 1 | ($4,667) | $1,052,281 | =$1,052,281*3.4%/12 | $1,050,595 |
| 2 | Monthly repayment in negartive | Balance in last period | Principal * rate/12 | (a)+(b)+(c) |
| 2 | ($4,667) | $1,050,595 | =$1,050,595*3.4%/12 | $1,048,905 |
Loan amortisation schedule:
| Period | Payment | Principal | Interest | Balance |
| 1 | ($4,667) | $1,052,281 | $2,981 | $1,050,595 |
| 2 | ($4,667) | $1,050,595 | $2,977 | $1,048,905 |
| 3 | ($4,667) | $1,048,905 | $2,972 | $1,047,211 |
| 4 | ($4,667) | $1,047,211 | $2,967 | $1,045,511 |
| 5 | ($4,667) | $1,045,511 | $2,962 | $1,043,807 |
| 6 | ($4,667) | $1,043,807 | $2,957 | $1,042,097 |
| 7 | ($4,667) | $1,042,097 | $2,953 | $1,040,383 |
| 8 | ($4,667) | $1,040,383 | $2,948 | $1,038,664 |
| 9 | ($4,667) | $1,038,664 | $2,943 | $1,036,941 |
| 10 | ($4,667) | $1,036,941 | $2,938 | $1,035,212 |
| 11 | ($4,667) | $1,035,212 | $2,933 | $1,033,478 |
| 12 | ($4,667) | $1,033,478 | $2,928 | $1,031,740 |
| 13 | ($4,667) | $1,031,740 | $2,923 | $1,029,997 |
| 14 | ($4,667) | $1,029,997 | $2,918 | $1,028,248 |
| 15 | ($4,667) | $1,028,248 | $2,913 | $1,026,495 |
| 16 | ($4,667) | $1,026,495 | $2,908 | $1,024,737 |
| 17 | ($4,667) | $1,024,737 | $2,903 | $1,022,973 |
| 18 | ($4,667) | $1,022,973 | $2,898 | $1,021,205 |
| 19 | ($4,667) | $1,021,205 | $2,893 | $1,019,432 |
| 20 | ($4,667) | $1,019,432 | $2,888 | $1,017,654 |
:
:
| Period | Payment | Principal | Interest | Balance |
| 341 | ($4,667) | $90,613 | $257 | $86,204 |
| 342 | ($4,667) | $86,204 | $244 | $81,781 |
| 343 | ($4,667) | $81,781 | $232 | $77,346 |
| 344 | ($4,667) | $77,346 | $219 | $72,899 |
| 345 | ($4,667) | $72,899 | $207 | $68,438 |
| 346 | ($4,667) | $68,438 | $194 | $63,966 |
| 347 | ($4,667) | $63,966 | $181 | $59,480 |
| 348 | ($4,667) | $59,480 | $169 | $54,982 |
| 349 | ($4,667) | $54,982 | $156 | $50,471 |
| 350 | ($4,667) | $50,471 | $143 | $45,948 |
| 351 | ($4,667) | $45,948 | $130 | $41,411 |
| 352 | ($4,667) | $41,411 | $117 | $36,862 |
| 353 | ($4,667) | $36,862 | $104 | $32,300 |
| 354 | ($4,667) | $32,300 | $92 | $27,724 |
| 355 | ($4,667) | $27,724 | $79 | $23,136 |
| 356 | ($4,667) | $23,136 | $66 | $18,535 |
| 357 | ($4,667) | $18,535 | $53 | $13,921 |
| 358 | ($4,667) | $13,921 | $39 | $9,294 |
| 359 | ($4,667) | $9,294 | $26 | $4,653 |
ekkarill92 answered 2 years ago