In: Finance
An investor has $60,000 to invest in a $280,000 property. He can obtain either a $220,000 loan at 9.5 percent for 20 years or a $180,000 loan at 9 percent for 20 years and a second mortgage for $40,000 at 13 percent for 20 years. All loans require monthly payments and are fully amortizing.
a. Which alternative should the borrower choose, assuming he will own the property for the full loan term?
b. Would your answer change if the borrower plans to own the property only five years?
c. Would your answers to (a) and (c) change if the second mortgage had a 10 year term?
Needs to be answered in Microsoft Excel with calculations
Ans a. | |||
Finding the monthly installments for the two options | |||
for 20 years loan Term | Option 1 | Option 2. | |
Installment calculation formula: | 1st Mortgage | 2nd Mortgage | |
A= [i*P*(1+i)^n]/[(1+i)^n-1] | |||
A = periodical installment=?? | |||
P=Loan amount = | $ 220,000 | $ 180,000 | $ 40,000 |
i= interest rate per period | 9.5% pa=0.7917% per month | 9% pa=0.75% per month | 13% pa=1.0833% per month |
n=total no of payments | 240 months=20yrs | 240 months=20yrs | 240 months=20yrs |
Monthly Installments = | [0.7917%*220000*1.007917^240]/(1.007917^240-1] | [0.75%*180000*1.0075^240]/(1.0075^240-1] | [1.0833%*40000*1.010833^240]/(1.010833^240-1) |
Monthly Installments Amt = | $ 2,050.75 | $ 1,619.51 | $ 468.62 |
Monthly Installment Option 1 | $ 2,050.75 |
Total Monthly Installment in option 2 = | $ 2,088.13 |
So the borrower should choose $220,000@ 9.5% for 20 years. |
Ans b. | |||
When the loan period is made 5 years | |||
5 years loan | Option 1 | Option 2. | |
Installment calculation formula: | 1st Mortgage | 2nd Mortgage | |
A= [i*P*(1+i)^n]/[(1+i)^n-1] | |||
A = periodical installment=?? | |||
P=Loan amount = | $ 220,000 | $ 180,000 | $ 40,000 |
i= interest rate per period | 9.5% pa=0.7917% per month | 9% pa=0.75% per month | 13% pa=1.0833% per month |
n=total no of payments | 60 months | 60 months | 60 months |
Monthly Installments = | [0.7917%*220000*1.007917^60]/(1.007917^60-1] | [0.75%*180000*1.0075^60]/(1.0075^60-1] | [1.0833%*40000*1.010833^60]/(1.010833^60-1) |
Monthly Installments Amt = | $ 4,620.45 | $ 3,736.50 | $ 910.11 |
Monthly Installment Option 1 | $ 4,620.45 |
Total Monthly Installment in option 2 = | $ 4,646.62 |
So the borrower should choose $220,000@ 9.5% for 20 years. |
Ans c. | |||
Assume second mortgage in Second option is for 10 years | Option 1 | Option 2. | |
Installment calculation formula: | 1st Mortgage | 2nd Mortgage | |
A= [i*P*(1+i)^n]/[(1+i)^n-1] | |||
A = periodical installment=?? | |||
P=Loan amount = | $ 220,000 | $ 180,000 | $ 40,000 |
i= interest rate per period | 9.5% pa=0.7917% per month | 9% pa=0.75% per month | 13% pa=1.0833% per month |
n=total no of payments | 240 months=20yrs | 240 months=20yrs | 120 months =10 years |
Monthly Installments = | [0.7917%*220000*1.007917^240]/(1.007917^240-1] | [0.75%*180000*1.0075^240]/(1.0075^240-1] | [1.0833%*40000*1.010833^120]/(1.010833^120-1) |
Monthly Installments Amt = | $ 2,050.75 | $ 1,619.51 | $ 597.23 |
Monthly Installment Option 1 | $ 2,050.75 |
Total Monthly Installment in option 2 = | $ 2,216.74 |
So the borrower should choose $220,000@ 9.5% for 20 years. | |
So there is no change in option in all the three scenario. |