Question: PLEASE show what is put into the cell. Ann would like to buy a house. It costs $2,500,000. Her down payment will be $50,000. She

PLEASE show what is put into the cell. Ann would like to buy a house.

It costs $2,500,000.

Her down payment will be $50,000.

She will take out a mortgage for the remainder.

It will be a 30 year, fully amortizing, FRM, with constant monthly payments and monthly compounding.

The annual interest rate is 4.00%.

She will pay $5,000 in closing costs at origination.

She will also pay 1.75% of the balance in buy-down points at origination.

2. Fill in the spreadsheet (sheet FA AMORTIZATION SCHEDULE) for Ann.

(It is called an amortization schedule or amortization calendar.)

3. Compute Anns annualized IRR for the mortgage in the spreadsheet. (Use the net cash flow.)

(3.a) What is the annualized IRR for the mortgage?

(3.b) Is it higher or lower than the mortgage contract rate?

(3.c) Why?

PLEASE show what is put into the cell. Ann would like to

House Price Down Payment Mortgage Mortgage Mortgage Total Mortgage Costs Annual Contract Rate Term (years) Payments per year Total Number of Payments Balloon Month $2,500,000.00 Green text represent inputs $50,000.00 $2,450,000.00 $5,000.00 Purple text represent outputs 1.75% $47,875.00 4.00% 30 12 360 $0.00 Loan Balance (after pmt) Mortgage Payment Interest Principal Payment $2,450,000.00 1 $2,446,469.99 $11,696.67 $8,166.67 $3,530.01 2 3. 4 5 Closing Costs Buydown Points 6 7 8 9 10 Mortgage cashflow IRR Monthly IRR Annualized IRR Cash inflow to borrower Cash outflow from borrower Net Cash Flow to Borrower $2,450,000.00 -$47,875.00 -11,696.67 $2,402,125.00 -$11,696.67 Scenario: HPA Optimistic Base Pessimistic Very Bad Optimistic #### #### Base Home Equity Home Equity in 10 years in 30 years Very Bad 4.50% 2.50% 0.00% -6.00% House Price Pessimistic #### Home Equity Optimistic Base Pessimistic Very Bad $50,000.00 $50,000.00 $50,000.00 $50,000.00 $62,905.01 $58,738.34 $53,530.01 $41,030.01 House Price Down Payment Mortgage Mortgage Mortgage Total Mortgage Costs Annual Contract Rate Term (years) Payments per year Total Number of Payments Balloon Month $2,500,000.00 Green text represent inputs $50,000.00 $2,450,000.00 $5,000.00 Purple text represent outputs 1.75% $47,875.00 4.00% 30 12 360 $0.00 Loan Balance (after pmt) Mortgage Payment Interest Principal Payment $2,450,000.00 1 $2,446,469.99 $11,696.67 $8,166.67 $3,530.01 2 3. 4 5 Closing Costs Buydown Points 6 7 8 9 10 Mortgage cashflow IRR Monthly IRR Annualized IRR Cash inflow to borrower Cash outflow from borrower Net Cash Flow to Borrower $2,450,000.00 -$47,875.00 -11,696.67 $2,402,125.00 -$11,696.67 Scenario: HPA Optimistic Base Pessimistic Very Bad Optimistic #### #### Base Home Equity Home Equity in 10 years in 30 years Very Bad 4.50% 2.50% 0.00% -6.00% House Price Pessimistic #### Home Equity Optimistic Base Pessimistic Very Bad $50,000.00 $50,000.00 $50,000.00 $50,000.00 $62,905.01 $58,738.34 $53,530.01 $41,030.01

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!

pls answer ques. 3 & 4...work is required to be shown on excel if possible. Thank you Project Assignment No. 1 PROBLEM 2- Maria is interested in financing the purchase of a property for sale for...

PLEASE ENTER ANSWERS IN BLUE BOLD INK! Use the following information for all the questions. Ann would like to buy a house. It costs $2,500,000. Her down payment will be $50,000. She will take out a...

Ann would like to buy a house. It costs $2,500,000. Her down payment will be $50,000. She will take out a mortgage for the remainder. It will be a 30 year, fully amortizing, FRM, with constant...

Use the following information for the remaining questions. Ann would like to buy a house. It costs $2,500,000. Her down payment will be $50,000. She will take out a mortgage for the remainder. It...

Can you help me with the IRR and the excel? TI 2 200.000 59000 Peo.c00.00 11 000 Green represent inputs Purple represent outputs Marger MEE JOS al ser 11 547700 90 WS 5 + dan Balance Mermerest...

{ 6 points} Leverage and limited liability 1 . {1 point} You pay $1000 for a property with 9% cap rate, keep it for 5 years, and sell at the end of year 5 at the same cap rate. The selling costs are...

Please help me answer to Question 2. Thanks ! Ann would like to buy a house. It costs $2,500,000. Her down payment will be $50,000. She will take out a mortgage for the remainder. It will be a 30...

CAN SOMEONE PLEASE PLEASE HELP ME WITH THIS Ann would like to buy a house. It costs $800,000. Her down payment will be $40,000. She will take out a mortgage for $760,000. It will be a 30 year, fully...

Write an essay on the article The Future of Audit

Why do economists complicate the equation for elasticity by averaging prices and quantities?

Your company is planning to invest in a project that will generate revenue for the next 1 0 years. In Year 1 , you expect the revenue to be $ 1 0 , 0 0 0 , and you anticipate that it will increase by...

A company is preparing completing their Cash Budget. The following data has been prepared for cash receipts and payments. January February March Cash receipts $1,061,200 $1,182,400 $1,091,700 Cash...