Question

In: Finance

Solve using excel: A. Suppose a potential home buyer is interested in taking a $500,000 mortgage...

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)

Solutions

Expert Solution

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)

Related Solutions

Solve Using excel spreadsheet: A young couple take out a 30-year home mortgage of $134,000.00 at...
Solve Using excel spreadsheet: A young couple take out a 30-year home mortgage of $134,000.00 at 8.7% compounded monthly. They make their regular monthly payment for 5 years, then decide to up their monthly payment to $1,250.00. A. What is the regular monthly payment? Correct answer: $1049.40 B. What is the unpaid balance when they begin paying the accelerated monthly payment of $1,250.00? C. How many monthly payment of $1,250.00 will it take to pay off the loan? D. How...
Solve using excel: A. Suppose you have taken out a $125,000 fully-amortizing fixed rate mortgage loan...
Solve using excel: A. Suppose you have taken out a $125,000 fully-amortizing fixed rate mortgage loan that has a term of 15 years and an interest rate of 6%. After your first mortgage payment, how much of the original loan balance is remaining? 
 B. Assume you have taken out a partially amortizing loan for $325,000 that has a term of 7 years, but amortizes over 30 years. Calculate the balloon payment at maturity (Year 7) if the interest rate on...
Suppose you are interested in taking a mortgage loan for $250,000 in order to purchase your principal residence.
Suppose you are interested in taking a mortgage loan for $250,000 in order to purchase your principal residence. Your lender has suggested that you might be interested in taking an FHA loan. In order to do so, you must pay an additional up-front mortgage insurance premium (UFMIP) of 1.0% of the mortgage balance. If the interest rate on the fully-amortizing mortgage loan is 5% and the term is 30 years, what is your monthly mortgage payment assuming the UFMIP is...
A potential buyer of a sportscar is interested in the costs of a vehicle per mile...
A potential buyer of a sportscar is interested in the costs of a vehicle per mile travelled at net present value. The buyer is planning to sell the car after three years of operation at residual value. The car costs £35000.00 to purchase and the buyer incurs costs for insurance, fuel and mantenance of £1400.00 per year. The car is expected to depreciate in value by £4000.00 per year (straight line depreciation). The buyer is planning to travel 17000 miles...
PLEASE SOLVE IN EXCEL WITH CALCULATIONS AND WORK. A mortgage for $100,000 is made with initial...
PLEASE SOLVE IN EXCEL WITH CALCULATIONS AND WORK. A mortgage for $100,000 is made with initial payments of $500 per month for the first year. The interest rate is 9 percent. After the first year, payments will increase to an amount that makes the loan fully amortizable over the remaining 24 years with constant monthly payments. a. Calculate the interest deductions for the loan for the first year. b. How much, if any, interest must be deferred until the second...
Suppose you bought a new home for $210,000 using a 33-year mortgage with semiannual payments of...
Suppose you bought a new home for $210,000 using a 33-year mortgage with semiannual payments of $6,609.301. The annual interest rate of the mortgage is 5.1%. Right after the first 7 years (26 years left), approximately how much money have you paid in interest and how much in principal?
Suppose you bought a new home for $230,000 using a 22-year mortgage with semiannual payments of...
Suppose you bought a new home for $230,000 using a 22-year mortgage with semiannual payments of $10,236.491. The annual interest rate of the mortgage is 6.9%. Right after the first 4 years (18 years left), approximately how much money have you paid in interest and how much in principal? Group of answer choices Interest: $81,891.93; Principal: $209,204.62 Interest: $435,917.86; Principal: $55,433.72 Interest: $61,096.55; Principal: $20,795.38 Interest: $20,795.38; Principal: $10,236.49
Suppose you bought a new home for $240,000 using a 30-year mortgage with monthly payments of...
Suppose you bought a new home for $240,000 using a 30-year mortgage with monthly payments of $1,548.667. The annual interest rate of the mortgage is 6.7%. After the first 3 years (36 monthly payments), approximately how much money have you paid in interest and how much in principal? Group of answer choices Interest: $55,752.02; Principal: $8,294.67 Interest: $47,457.35; Principal: $8,294.67 Interest: $1,548.67; Principal: $231,705.33 Interest: $47,457.35; Principal $1,548.67
Please solve by hand, not using excel. No discount rate provided. Suppose Blue Thumb Tools is...
Please solve by hand, not using excel. No discount rate provided. Suppose Blue Thumb Tools is considering the introduction of a new, heavier hammer to be used for driving spikes. The new hammer will cost $490,000. The cost will be depreciated straight-line to zero over the project’s five-year life, at the end of which the new hammer can be scrapped for $40,000. The new hammer will save the firm $146,000 per year in pretax operating costs, and it required an...
*****SOLVE USING EXCEL AND PLEASE SHOW THE EXCEL COMMANDS THAT ARE USED******** A company is considering...
*****SOLVE USING EXCEL AND PLEASE SHOW THE EXCEL COMMANDS THAT ARE USED******** A company is considering the purchase of a new machine that will enable it to increase its expected sales. The machine will have a price of $100,000.  In addition, the machine must be installed and tested.  The costs of installation and testing will amount to $10,000.  The machine will be depreciated using 3-years MACRS. (Use MACRS table from class excel exercise by copying the table and pasting it)          The equipment will...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT