Question

You obtain a loan of $150,000 at 5.875% amortized over thirty years with monthly payments. You are required to pay closing costs and fees of 2.0% of the loan amount to the lender. What is the yield of the loan if paid off at the end of 5 years? 

Answer 1

Loan amount = $150,000

Rate of interest per annum = 5.875%

Rate of interest per month = 5.875%/12

Loan term in number months = 30 *12 = 360

To get monthly payments, we use PMT function of excel

PMT(rate,nper,pv, fv,type)

= PMT (5.875%/12, 360, -150,000, 0,0)

=$887.31

Now we need to calculate outstanding loan balance at the end of 5 years (60 months)

i = Loan interest rate per period = 5.875%/12

n = Number of loan payments remaining = 360-60 = 300
Outstanding loan balance = PMT x (1 - 1 / (1 + i)ⁿ) / i

=$887.31 * (1- 1/(1 + (5.875%/12))³⁰⁰) / (5.875%/12)

=$139,364.16

Hence,

Closing costs and fees = 2.0% * $139,364.16 = $2,787.28

Hence total payment at end of 60 months (excluding EMI) = $139,364.16 + $2,787.28 = $142,151.44

Now, we apply excel function RATE to get yield:

RATE (nper,pmt, pv,fv,type)

RATE (60,$887.31,-150000,142,151.44,0,0)

=0.5169341%

Monthly yield = 0.5169341%

Yearly yield = 0.5169341% *12 = 6.20%