Question

Suppose you are buying a house that cost $300,000. You make a 10% down payment and are also going to make semiannual payments for next 10 years on the balance of the loan which you are financing at 5% APR. Also, the IRS allows the tax exemption for the mortgage interest payment at the end of each year and your tax rate is 30% (i.e. Tax saving = annual interest * tax rate). Using the given information, construct the amortization table and answer the following questions;

a) What is the semiannual payment of your mortgage loan?

b) How much interest do you pay at the first year?

c) What is the tax saving at the first year?

d) What is after-tax semiannual payment at the second half of the fifth year?

e) Counting the tax savings, what is the net present value of the house you will buy? f) At what tax rate the net present value of the house becomes $250,000?

Answer 1

a]

Semiannual loan payment is calculated using PMT function in Excel :

rate = 5% / 2   (converting annual rate into semiannual rate)

nper = 10 * 2 (10 year loan with 2 semiannual payments each year)

pv = 270000 (loan amount = cost of house * (1 - down payment %) = $300,000 * (1 -10%) = $270,000)

PMT is calculated to be $17,319.72

: B fac C =PMT(5%/2,10*2,270000) D E F A 1 ($17,319.72)!

b]

Interest paid in 1st year is calculated using CUMIPMT function in Excel :

rate = 5% / 2   (converting annual rate into semiannual rate)

nper = 10 * 2 (10 year loan with 2 semiannual payments each year)

pv = 270000 (loan amount = cost of house * (1 - down payment %) = $300,000 * (1 -10%) = $270,000)

start period = 1 (We are calculating total interest paid between 1st and 2nd semiannual payment)

end period = 2 (We are calculating total interest paid between 1st and 2nd semiannual payment)

type = 0 (each payment is made at the end of month)

CUMIPMT is calculated to be $13,235.76

| A2 - X for ECUMIPMT(5%/2,10*2,270000,1,2,0) B C D E F G 2 $(13,235.76)!

c]

Tax saving = Interest paid in 1st year * tax rate

Tax saving = $13,235.76 * 30% = $3,970.73

d]

after-tax semiannual payment = semiannual payment - (interest paid in second half of the fifth year * tax rate)

Interest paid in second half of the fifth year is calculated using CUMIPMT function in Excel :

rate = 5% / 2   (converting annual rate into semiannual rate)

nper = 10 * 2 (10 year loan with 2 semiannual payments each year)

pv = 270000 (loan amount = cost of house * (1 - down payment %) = $300,000 * (1 -10%) = $270,000)

start period = 10 (We are calculating total interest paid between 10th and 10th semiannual payment)

end period = 10 (We are calculating total interest paid between 10th and 10th semiannual payment)

type = 0 (each payment is made at the end of month)

CUMIPMT is calculated to be $4,119.59

X fac =CUMIPMT(5%/2,10*2,270000 10,10,0) D E F G B C ДА 4 $ (4,119.59)!

after-tax semiannual payment = semiannual payment - (interest paid in second half of the fifth year * tax rate)

after-tax semiannual payment = $17,319.72 - ($4,119.59 * 30%)

after-tax semiannual payment = $16,083.85