Question

You are planning to purchase a new house or condominium to use as your primary residence. This assignment will analyze some of the financial aspects of doing so. The final purchase price is $420,000 and, if you need a mortgage from the bank, your down payment will have to be 20% of the purchase price. The mortgage is a 30-year fixed rate loan with an Annual Percentage Rate (APR) of 6.00%. You will incur a one-time closing cost of $6,500 and you will add this closing cost to the loan borrowed; if no loan, you will still pay this $6,500 with cash. The mortgage will be amortized over the 30-year period with equal monthly payments Exactly right after you pay the 60th monthly payment (5 years after the loan starts), you can sell the house for a net proceeds of $625,000 (this is the money you actually received after deducting all sale expenses but BEFORE you pay off your remaining loan principal). Ignore the capital gain tax or property tax in this case. Compute both the annual and the monthly effective interest rates of this loan. Compute the monthly payment amount of this loan. 1. 2. 3. Compute the remaining principal that will be due on the mortgage when you sell the house. Your last payment is made in month 60, so the remaining principal is the required payment immediately after that monthly payment 4. Calculate the annual Internal Rate of Return (IRR) for this investment if the above mortgage is involved, assuming all 12 monthly payments paid together at the end of each year for this calculation 5. Calculate the annual Internal Rate of Return (IRR) if an all-cash (no finance) investment is involved; i.e. you pay the fulll purchase price plus the closing cost at the beginning and collect all net sale proceeds at the end. 6. Assuming you can either finance the purchase as (4) or purchase with all-cash as (5) and there are no other differences between the two strategies, conduct an incremental IRR analysis (must show the procedure) to determine which strategy you should select and provide calculation and explanation for your decision. (Your MARR is at 7.00%)

Answer 1

You have asked 6 questions in the same post. I have addressed the first four sub parts. Please post the balance sub parts separately.

Total loan amount = 420,000 x (1 - 20%) + 6,500 = $  342,500

APR = 6%; Interest rate per month = 6% / 12 = 0.5%; Nper = 30 years = 12 x 30 = 360

I have answered part (2) first and then part (1)

Part (2)

Monthly payment = PMT (Rate, Nper, PV, FV) = PMT (0.5%, 360, -342500, 0) =  2,053

Part (1)

Net disbursement = Total amount - fees = 342,500 - 6,500 = 336,000

The monthly effective interest rate = RATE (Nper, PMT, PV, FV) = RATE (360, 2053, -336000, 0) = 0.5148%

The annual effective interest rate = (1 + monthly rate)¹² - 1 = (1 + 0.5148%)¹² - 1 = 6.36%

Part (3)

Nper left after five years = 12 x (30 - 5) = 300

Loan outstanding = -PV (Rate, Nper, PMT, FV) = -PV (0.5%, 300, 2053, 0) = $  318,711

Part (4)

Year 0 cash outflow = own contribution = 20% x 420,000 = $ 84,000

Annual outflow towards debt servicing = 12 x PMT = 12 x 2,053 =  24,642

Please see the table below:

T V W 224 Year 225 Cash out flows 226 Net proceeds from sale 227 Net cash flows 1 2. 3 4 5 (84,000) (24,642) (24,642) (24,642) (24,642) (24,642) 625,000 (84,000) (24,642) (24,642) 35.16%=IRR(T227:Y227) (24,642) (24,642) 600,358 228 IRR