In: Finance
Solve using excel:
A. Suppose a potential home buyer is interested in taking a $500,000 mortgage loan that has a term of 30 years and a fixed mortgage rate of 5.25%. What is the monthly mortgage payment that the homeowner would need to make if this loan is fully amortizing?
B. You have taken out a $350,000, 3/1 ARM. The initial rate of 6.0% (annual) is locked in for 3 years. Calculate the outstanding balance on the loan after 3 years. The interest rate after the initial lock period is 6.5%. (Note: the term on this 3/1 ARM is 30 years)
C. You have taken out a $300,000, 5/1 ARM. The initial rate of 5.4% (annual) is locked in for 5 years. Calculate the payment after recasting the loan (i.e., after the reset) assuming the interest rate after the initial lock period is 8.0%. (Note: the term on this 5/1 ARM is 30 years)
A.Monthly mortgage payment that the homeowner would need to make if this loan is fully amortizing |
can be calculated by using the formula to find |
present value of ordinary annuity, ie. |
PV of mortgage=Pmt.*(1-(1+r)^-n)/r |
where, PV of mortgage is given as $ 500000 |
Pmt.=the monthly payment to be found out----?? |
r= the monthly interest rate, ie. 5.25%/12=0.4375% p.m. or 0.004375 p.m. |
n= no.of months, ie. 30*12= 360 months |
So, using the values , in the above formula, |
500000=Pmt.*(1-(1+0.004375)^-360)/0.004375 |
& solving for pmt., we get the monthly pmt. On the mortgage as |
2761.02 |
(ANSWER) |
B.Outstanding balance on the loan after 3 years is |
First, we will find the monthly payment on the mortgage |
using the formula, as in A, ie. |
PV of mortgage=Pmt.*(1-(1+r)^-n)/r |
where, PV of mortgage is given as $ 350000 |
Pmt.=the monthly payment to be found out----?? |
r= the monthly interest rate, ie. 6%/12=0.5% p.m. or 0.005 p.m. |
n= no.of months, ie. 30*12= 360 months |
So, using the values , in the above formula, |
350000=Pmt.*(1-(1+0.005)^-360)/0.005 |
2098.43 |
Now, with this monthly payment on the 30-yr. mortgage, |
we will find the Outstanding balance on the loan after 3 years |
FV(Rem. Bal.)=Future value of the original loan at end of 36 months-FV of annuity at end of 36 months |
ie. FV=(PV*(1+r)^n)-(Pmt.*((1+r)^n-1)/r) |
where, FV= Future value of remaning loan balance--- to be found out---?? |
PV= Present value-ie.Original loan/mortgage balance--- $ 350000 |
r= rate of interest, ie. 6% p.a. or 0.5% or 0.005 p.m. |
n= no.of months, ie. 3yrs.*12 mths.= 36 mths. |
Pmt.=the monthly payment to be found out, ie.2098.43 |
So, using the values , in the above formula, |
ie. FV=(350000*(1+0.005)^36)-(2098.43*((1+0.005)^36-1)/0.005) |
336294.12 |
(ANSWER) |
C.First, we will find the monthly payment on the mortgage |
using the formula, as in A, ie. |
PV of mortgage=Pmt.*(1-(1+r)^-n)/r |
where, PV of mortgage is given as $ 300000 |
Pmt.=the monthly payment to be found out----?? |
r= the monthly interest rate, ie. 5.4%/12=0.45% p.m. or 0.0045 p.m. |
n= no.of months, ie. 30*12= 360 months |
So, using the values , in the above formula, |
300000=Pmt.*(1-(1+0.0045)^-360)/0.0045 |
1684.59 |
Now, with this monthly payment on the 30-yr. mortgage, |
we will find the Outstanding balance on the loan after 5 years |
FV(Rem. Bal.)=Future value of the original loan at end of 60 months-FV of annuity at end of 60 months |
ie. FV=(PV*(1+r)^n)-(Pmt.*((1+r)^n-1)/r) |
where, FV= Future value of remaning loan balance--- to be found out---?? |
PV= Present value-ie.Original loan/mortgage balance--ie.300000 |
r= rate of interest, ie. 5.4% p.a. or 0.45% or 0.0045 p.m. |
n= no.of months, ie. 5yrs.*12 mths.= 60 mths. |
Pmt.=the monthly payment to be found out, ie.1684.59 |
So, using the values , in the above formula, |
ie. FV=(300000*(1+0.0045)^60)-(1684.59*((1+0.0045)^60-1)/0.0045) |
277012.09 |
Now, we will find the monthly pmt. On this remaining 25yrs. Motgage |
with the above rem. Loan bal. as principal of the mortgage |
using the formula, as in A, ie. |
PV of mortgage=Pmt.*(1-(1+r)^-n)/r |
where, PV of mortgage is the remaining balance at end of 5yrs.or 60 months, ie.277012.09 |
Pmt.=the monthly payment to be found out----?? |
r= the monthly interest rate, ie. 8%/12=0.6667% p.m. or 0.006667 p.m. |
n= no.of months, ie. 25*12= 300 months |
So, using the values , in the above formula, |
277012.09=Pmt.*(1-(1+0.006667)^-300)/0.006667 |
2138.10 |
(Answer) |