Question

You obtain a loan of $150,000 at 5.875% amortized over 30 years with monthly payments. You are required to pay closing costs and fees of 2% 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

Step 1: Calculate Monthly Payment

The amount of monthly payment can be calculated with the use of PMT (Payment) function/formula of EXCEL/Financial Calculator. The function/formula for PMT is PMT(Rate,Nper,PV,FV) where Rate = Interest Rate, Nper = Period, PV = Present Value and FV = Future Value (if any).

Here, Rate = 5.875%/12, Nper = 30*12 = 360, PV = $150,000 + 2%*150,000 = $153,000 and FV = 0

Using these values in the above function/formula for PMT, we get,

Monthly Payment = PMT(5.875%/12,360,153000,0) = $905.05

_____

Step 2: Calculate Amount Outstanding after 5 Years

The value of loan outstanding after 5 years can be calculated with the use of FV (Future Value) function/formula of EXCEL/Financial Calculator. The function/formula for FV is FV(Rate,Nper,-PMT,PV) where Rate = Interest Rate, Nper = Period, PMT = Payment and PV = Present Value.

Here, Rate = 5.875%/12, Nper = 5*12 = 60, PMT = $905.05 and PV = $153,000

Using these values in the above function/formula for FV, we get,

Loan Amount Outstanding after 5 Years = FV(5.875%/12,60,-905.05,153000) = $142,151.64

_____

Step 3: Calculate Yield of the Loan if Paid off at the End of 5 Years

The yield on the loan can be calculated with the use of Rate function/formula of EXCEL/Financial Calculator. The function/formula for Rate is Rate(Nper,PMT,-PV,FV) where Nper = Period, PMT = Payment, PV = Present Value and FV = Future Value.

Here, Nper = 5*12 = 60, PMT = $905.05, PV = $150,000 and FV = $142,151.64

Using these values in the above function/formula for Rate, we get,

Yield = Rate(60,905.05,-150000,142151.64)*12 = 6.35%

_____

Notes:

1) Closing fees is assumed to be paid from out of pocket.

2) There can be a slight difference in final answer on account of rounding off values.