Question

Ann is looking for a fully amortizing 30 year Fixed Rate Mortgage with monthly payments for $3,200,000. Mortgage A has a 4.38% interest rate and requires Ann to pay 1.5 points upfront. Mortgage B has a 6% interest rate and requires Ann to pay zero fees upfront.

(A) Assuming Ann makes payments for 30 years, what is Ann’s annualized IRR from mortgage A?

(B) Assuming Ann makes payments for 30 years, what is Ann’s annualized IRR from mortgage B?

(C) Assuming Ann makes payments for 2 years before she sells the house and pays the bank the balance, what is Ann’s annualized IRR from mortgage A?

(D) Assuming Ann makes payments for 2 years before she sells the house and pays the bank the balance, what is Ann’s annualized IRR from mortgage B?

Answer 1

ANALYSIS OF MORTGAGE A

Rate

Monthly interest rate=(4.38/12)%

0.365%

Nper

Number of months =30*12

360

Pmt

Monthly mortgage payment

$3,200,000

PV

Amount of loan

$640,537,778

(Using PV function of excel with Rate=0.365%, Nper=360, Pmt=-3200000)

A=PV*1.5%

Amount paid upfron (1.5 points)

$9,608,067

B=PV-A

Initial Cash flow

$630,929,712

Pv1

Initial Cash flow

$630,929,712

Nper

Number of months

360

Pmt

Monthly Payments

$3,200,000

RATE

Monthly Internal Rate of Return

0.37570%

(Using RATE function of excel with Nper=360, Pmt=3200000, Pv=-630929712)

(A)

Annualized IRR=RATE*12

4.5085%

.(C)

Future Value of payment for 2 years:

Pmt

Monthly Payments

$3,200,000

Nper

Number of months

24

(2*12)

Rate

Monthly interest rate=(4.38/12)%

0.365%

FV

Future Value of payment for 2 years:

$80,111,645

(Using FV function of excel with Rate=0.365%, Nper=24, Pmt=-3200000,)

Future Value of Loan after 2 years

PV

Amount of Loan

$640,537,778

Nper

Number of months

24

Rate

Monthly interest rate=(4.38/12)%

0.365%

FV1

Future Value of Loan after 2 years

$699,068,420

(Using FV function of excel with Rate=0.365%, Nper=24, Pv=-64053778)

B=FV1-FV

Loan Balance after 2 years

$618,956,775

IRR for 2 years

Pv

Initial Cash Flow

$630,929,712

Nper

Number of months

24

Pmt

Monthly Payments

$3,200,000

Fv

Terminal Payment

$618,956,775

RATE

Monthly Internal Rate of Return

0.4320%

(Using RATE function of excel with Nper=360, Pmt=3200000, Pv=-630929712,Fv=630929712)

.(C)

Annualized IRR=RATE*12

5.18%