Question

You are considering purchasing a small commercial property for $1,000,000. A bank has offered you two different loan options, the first would require a down payment of 30% and would have an interest rate of 5.25%. The second option would require a downpayment of only 20%, but the interest rate would rise to 6%. Both loans would have 25 year terms. After determining your monthly payment for each, calculate how much you are effectively paying (solving for RATE) for the additional $100,000 being borrowed under the 20% downpayment option.

a. 0.89%

b. 11.25%

c. 9.66%

d. 10.72%

Answer 1

Option 1: Property Price = 1,000,000 | Downpayment = 30% | Loan amount = 1,000,000 * 70% = $700,000

Interest rate = 5.25% | Time = 25 years

Let's first calculate the monthly payment for Option 1 using the Present Value of Annuity formula.

PV of Annuity formula = (PMT / R)*(1 - (1+R)^-T)

Reversing the formula for PMT

PMT = (PV of Loan * R) / (1 - (1+R)^-T)

R = 5.25% / 12 | T = 25 * 12 = 300 (For monthly payments) | Present Value of Loan = 700,000

Putting respective values in the PMT formula.

PMT = (700,000 * 5.25% / 12) / (1 - (1+5.25% / 12)^-300)

PMT = 3,062.50 / (1 - 0.26992)

PMT = 3,062.50 / 0.73008

Monthly Payment for Option 1 = $ 4,194.73

Option 2: Property Price = 1,000,000 | Downpayment = 20% | Loan amount = 1,000,000 * 70% = $800,000

Interest rate = 6% | Time = 25 years

Let's calculate the monthly payment for Option 2 using the Present Value of Annuity formula.

PV of Annuity formula = (PMT / R)*(1 - (1+R)^-T)

Reversing the formula for PMT

PMT = (PV of Loan * R) / (1 - (1+R)^-T)

R = 6% / 12 | T = 25 * 12 = 300 (For monthly payments) | Present Value of Loan = 800,000

Putting respective values in the PMT formula.

PMT = (800,000 * 6% / 12) / (1 - (1+6% / 12)^-300)

PMT = 4,000 / (1 - 0.22397)

PMT = 4,000 / 0.77603

Monthly Payment for Option 2 = $ 5,154.41

Extra Paid monthly for Borrowing 100,000 using Option 2 = Monthly Payment in Option 2 - Monthly Payment in Option 1

Extra Monthly Payment for Option 2 for borrowing extra 100,000 = 5,154.41 - 4,194.73 = $959.68

Using the Rate function in Excel and inputting the Time period, Extra payment and Extra Principal, I was able to get the monthly rate that you would be paying for borrowing extra 100,000.

For Rate function in excel, we need to enter 3 inputs. NPER which 25*12 = 300, Extra Payment of $959.68 and Extra principal of 100,000.

Note: Any one of the input among Extra payment or Extra Principal need to be negative value otherwise, the function will return error.

Below is the screenshot of how Rate function would look like and its result:

Rate = 0.89303% is the monthly rate that you would be paying for borrowing extra 100,000.

To calculate Effective Annual rate, we will convert the monthly rate into Effective Annual rate using below formula.

EAR = (1+Monthly rate)¹² - 1

EAR = (1+0.89303%)¹² - 1

EAR = 1.1125 - 1

Effective Annual Rate for borrowing extra 100,000 = 11.25%

Hence, You would be effectively paying 11.25% each year for borrowing 100,000 which is Option (b) among the given choices.