Compute Ann annualized IRR from the mortgage

Assignment Help Financial Management

Reference no: EM131845266

Ann would like to buy a house. It costs $1,000,000. She will make an $80,000 down payment.

She will take out a mortgage for $920,000. It will be a 30 year, fully amortizing, fixed rate mortgage (FRM), with monthly payments and monthly compounding. The annual interest rate is 3.41%. She must pay 2% in fees at the time of the loan.

Note: the home is bought and the loan is taken in month 0, the first payment is due in month 1.

Ann’s net cash flow each month is equal to the total cash inflows minus total cash outflows that month.

She forecasts three possible scenarios for house price appreciation (HPA):

A. Optimistic Case: 9% annual HPA, hence 9/12% monthly HPA

B. Base Case: 4.5% annual HPA, hence 4.5/12% monthly HPA

C. Pessimistic Case: 0% annual HPA, hence 0/12% monthly HPA

1. Fill in the Excel spreadsheet for Ann (the amortization schedule).

2. Compute Ann’s annualized IRR from the mortgage in the spreadsheet (use the net cash flow).

3. What is the IRR? ___________________

a. Is it higher or lower than the mortgage contract rate?

b. Why?

4. Plot Ann’s mortgage balance in one graph. Place the graph here or on a separate tab on the Excel spreadsheet.

5. Plot Ann’s mortgage payment, interest payment and principal payment in one graph.Place the graph here or on a separate tab on the Excel spreadsheet.

6. Plot Ann’s home equity every month under each of the three HPA scenarios in one graph.Place the graph here or on a separate tab on the Excel spreadsheet.

7. Assume Ann will make the required monthly payment every month for 30 years. How much home equity will Ann have after 15 years (180 months) of payments under each of the three HPA scenarios?

Reference no: EM131845266

Questions Cloud

Define the problem in the given scenario : The Centers for Disease Control (CDC), a federal agency, conducted a study of childhood obesity across America. The researchers found that obesity rates.

Write the best version possible to the content : Write the best version possible, but try and avoid any substantial changes to the content.Keep the essential facts.

Development goals concerning environmental sustainability : In the 2015 Millennial Development Goals concerning Environmental Sustainability.. goal #7, NOT The latest goals, who were the actors involved

What are the advantages and limitations of nuclear medicine : What are the advantages and limitations of nuclear medicine? What ailments are typically diagnosed and treated via nuclear medicine procedures?

Compute Ann annualized IRR from the mortgage : Compute Ann’s annualized IRR from the mortgage in the spreadsheet. Plot Ann’s mortgage balance in one graph. Place the graph here or on a separate tab.

What is international economic cooperation among nations : What is international economic cooperation among nations? And what are some of the potential benefits or harms if the different countries engaged

What is the primary benefit of the ventilation system : A certain ventilation system would cost a firm approximately $60,000, which can be amoritized over its useful life at a cost of $15,000/year.

Describe the sampling strategy and sampling technique : Describe the sampling strategy and sampling technique. Evaluate the validity and reliability of this experiment. Explain your answer.

Leadership varies widely by culture and personality : Leadership varies widely by culture and personality. An international organization with locations in several countries must balance the local custom

User Account

All Pages