Question

In: Finance

SHOW CLEAR WORK ON EXCEL A 30-year fully amortizing mortgage loan was made 10 years ago...

SHOW CLEAR WORK ON EXCEL

A 30-year fully amortizing mortgage loan was made 10 years ago for $75,000 at 6 percent interest. The borrower would like to prepay the mortgage balance by $10,000.

a. Assuminghecanreducehismonthlymortgagepayments,whatisthenewmortgagepayment?

b. Assuming the loan maturity is shortened and using the original monthly payments, what is the new loan maturity?

Solutions

Expert Solution

Loan Amount = $75000

Interest Rate = 6% i.e. 0.5% for monthly payments

Loan Term = 30 years i.e. 360 months

Monthly payment amount can be calculated as below:

P = 75000*0.5%/[1-(1+.5%)^(-360)] = $449.67 (refer cells G2, H2 & I2)

Hence After making the payment for 10 years, outstanding loan amount can be calculated as below:

Loan amount = PV of future payments = 449.67 * [(1-(1+.5%)^(-240))/.5%] = $62765.29

(same could be calculated using excel formula for present value as shown in the attached image, refer cells H6, I6)

After making the prepayment of $10000 at the end of year 10,

Loan balance outstanding = $62765.29 - $10000 = $52765.29

Assuming he can reduce his monthly mortgage payments, the new mortgage payment is

P = 52765.29*0.5%/[1-(1+.5%)^(-240)] = $378.03 (refer cells L11 & M11)

Assuming the loan maturity is shortened and using the original monthly payments, new loan maturity is = 177.16 months (refer cells L12 &M12).


Related Solutions

SHOW CLEAR WORK ON EXCEL A fully amortizing mortgage loan is made for $100,000 at 6...
SHOW CLEAR WORK ON EXCEL A fully amortizing mortgage loan is made for $100,000 at 6 percent interest for 30 years. Determine payments for each of the periods a–d below if interest is accured: a. Monthly. b. Quarterly. c. Annually. d. Weekly.
A fully amortizing mortgage loan is made for $100,000 at 4.5% for 30 years. Payments will...
A fully amortizing mortgage loan is made for $100,000 at 4.5% for 30 years. Payments will be made monthly. Calculate the following: a. Monthly payments b. Interest and principal payments during month 1. c. Total interest and principal payments made over the life of the loan (30 years). d. If the property were sold at the end of year 15, how much is still owed on the mortgage? e. At the end of year 15, how much has been paid...
A fully amortizing mortgage loan is made for $200,000 at 6 percent interest for 30 years....
A fully amortizing mortgage loan is made for $200,000 at 6 percent interest for 30 years. Payments are to be made monthly. (Ignore origination fee and other fees). a.      Using Excel, construct fully amortizing mortgage loan table including beginning balance, payment, interest, principal, and ending balance. (Please check your ending balance by using PV function in Excel) b.      Interest and principal payments during month 1. c.       Total Principal and total interest paid over 30 years. d.      The outstanding loan balance...
A fully amortizing mortgage loan is made for $90,000 for 15 years. The interest rate is...
A fully amortizing mortgage loan is made for $90,000 for 15 years. The interest rate is 6 percent per year compounding monthly. Payments are to be made monthly. What is the principal payment in the first monthly payment?
An investor obtained a fully amortizing mortgage five years ago for $175,000 at 11.5% for 30...
An investor obtained a fully amortizing mortgage five years ago for $175,000 at 11.5% for 30 years. Mortgage rates have dropped so that a fully amortizing 20-year loan can be obtained at 10%. There is no prepayment penalty on the mortgage balance of the original loan, but 3 points will be charged on the new loan and other closing costs will be $3000. All payments are monthly. What is the effective "return" earned by the investor if he refinances?
A. An investor obtained a fully amortizing mortgage five years ago for $95,000 at 11% for...
A. An investor obtained a fully amortizing mortgage five years ago for $95,000 at 11% for 30 years. Mortgage rates have dropped so that a fully amortizing 25-year loan can be obtained at 10%. There is no prepayment penalty on the mortgage balance of the original loan, but three points will be charged on the new loan and other closing costs will be $2,000. All payments are monthly. (annualize rates) What rate of return must the investor be able to...
10. Ann is looking for a fully amortizing 30 year Fixed Rate Mortgage with monthly payments...
10. Ann is looking for a fully amortizing 30 year Fixed Rate Mortgage with monthly payments for $3,200,000. Mortgage A has a 4.38% interest rate and requires Ann to pay 1.5 points upfront. Mortgage B has a 6% interest rate and requires Ann to pay zero fees upfront. Assuming Ann makes payments for 30 years, what is Ann’s annualized IRR from mortgage A?
Consider the following mortgage: Loan amount $376,038 30 year maturity with monthly payments Fully amortizing Fixed...
Consider the following mortgage: Loan amount $376,038 30 year maturity with monthly payments Fully amortizing Fixed interest rate at 5% Suppose the investors use 50% PSA model to predict prepayments. What would be their prediction for the dollar amount of prepayments in the first month? Round your answer to the nearest cent (e.g. if you answer is $1,400.677777, write 1400.68).
Five years ago you took out a 10-year amortizing loan to purchase an apartment. The loan...
Five years ago you took out a 10-year amortizing loan to purchase an apartment. The loan has 4.0% APR with monthly payments of $1,800. How much do you owe on the loan today? The remaining loan balance is $________. (round to the nearest dollar) How much interest did you pay on the loan in the past year? The interest paid in year five was $______. (round to the nearest dollar) Over the entire period of 10 years, how much interest...
SHOW YOUR WORK not only the solutions, on worksheet tab in Excel file A fully amortizing...
SHOW YOUR WORK not only the solutions, on worksheet tab in Excel file A fully amortizing mortgage loan is made for $80,000 at 6 percent interest for 25 years. Payments are to be made monthly. Calculate: a. Monthly payments. b. Interest and principal payments during month 1. c. Total principal and total interest paid over 25 years. d. The outstanding loan balance if the loan is repaid at the end of year 10. e. Total monthly interest and principal payments...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT