Question

In: Finance

Use Excel.             A business has a mortgage of £200,000 for a term of 25 years,...

Use Excel.

           

A business has a mortgage of £200,000 for a term of 25 years, to be paid in equal monthly instalments. Assume an interest rate of 4.8% p.a. (calculated monthly) throughout the period.

  1. Determine the regular monthly payment.

  1. Construct a table showing the outstanding balance for each month for the full term of the mortgage.

  1. Determine the amount outstanding when the business is exactly half way through the term of the mortgage.

  1. Determine the time remaining when the outstanding balance is half of the original amount.

  1. Explore how the solutions to Q1-4 would change with different interest rates. Consider rates of 3.0% and 6%. In each case assume the rate remains the same for the full term of the mortgage.

Solutions

Expert Solution

As per rules I am answering the first 4 subparts of the question

1: Monthly payment = $1145.99

2: Due to character limitation of the portal, The first 10 and last 10 months are

Month StartingBalance Interest Principal EndingBalance TotalInterest
1 200000.00 800.00 345.99 199654.01 800.00
2 199654.01 798.62 347.38 199306.63 1598.62
3 199306.63 797.23 348.77 198957.86 2395.84
4 198957.86 795.83 350.16 198607.70 3191.67
5 198607.70 794.43 351.56 198256.14 3986.10
6 198256.14 793.02 352.97 197903.17 4779.13
7 197903.17 791.61 354.38 197548.78 5570.74
8 197548.78 790.20 355.80 197192.99 6360.94
9 197192.99 788.77 357.22 196835.76 7149.71
10 196835.76 787.34 358.65 196477.11 7937.05
291 11211.80 44.85 1101.15 10110.66 143594.89
292 10110.66 40.44 1105.55 9005.10 143635.33
293 9005.10 36.02 1109.97 7895.13 143671.35
294 7895.13 31.58 1114.41 6780.72 143702.93
295 6780.72 27.12 1118.87 5661.85 143730.05
296 5661.85 22.65 1123.35 4538.50 143752.70
297 4538.50 18.15 1127.84 3410.66 143770.86
298 3410.66 13.64 1132.35 2278.31 143784.50
299 2278.31 9.11 1136.88 1141.43 143793.61
300 1141.43 4.57 1141.43 0.00 143798.18

3: Amount outstanding at term 150 =

129076.49

4: Periods remaining when the mortgage is half paid = 300-192 = 108 months

WORKINGS

part 1 and 2

PART 3:

part 4

jeff jeffy answered 1 year ago
Next > < Previous

Related Solutions

The term t (in years) of a $200,000 home mortgage at 7.5% interest can be approximated...
The term t (in years) of a $200,000 home mortgage at 7.5% interest can be approximated by the function below, where x is the monthly payment in dollars. t = 13.375 ln( x/ x-1250) , x > 1250 (a) Use a graphing utility to graph the model.,     (b) Use the model to approximate the term of a home mortgage for which the monthly payment is $1390. (Round your answer to the nearest year.) ________ yr What is the total amount...
Suppose you currently have 25 years remaining on a mortgage that started as a $200,000, 30-year...
Suppose you currently have 25 years remaining on a mortgage that started as a $200,000, 30-year 6% mortgage. Your current balance is $186,108.71. Your current payment (including both principal and interest) is $1,199.10. Ignoring closing costs, evaluate whether you should refinance into a 30-year 5%mortgage or a 15-year 4% mortgage. Determine the following for both alternatives: a. What would be the new monthly payment assuming you refinance the existing balance of $186,108.71? b. What would be the total accumulated interest...
Brian mortgaged $200,000 on his house 10 years ago. The mortgage was 25-year fixed rate at...
Brian mortgaged $200,000 on his house 10 years ago. The mortgage was 25-year fixed rate at 8%. He realizes today that a refinance opportunity is available at 5% for the rest of his mortgage. Assume there are no additional fees. For simplicity, assume annual mortgage payment. Please show all your work. a.) What was Brian’s annual mortgage payment? b.) If he took the refinance opportunity, what is his new annual mortgage payment? Do not use Excel, please. Need to make...
A $425,000 mortgage with a 3-year term is amortized over 25 years at an interest rate...
A $425,000 mortgage with a 3-year term is amortized over 25 years at an interest rate of 8.2% compounded semi-annually. If payments are made at the end of each month, determine the mortgage balance at the end of the 3-year term.
Calculate the mortgage balance 25 days after the 45th payment on a mortgage loan of $200,000,...
Calculate the mortgage balance 25 days after the 45th payment on a mortgage loan of $200,000, amortised over 15 years at an interest rate of 5.75%.
The Switzers are nearing the end of the first five-year term of a $200,000 mortgage loan...
The Switzers are nearing the end of the first five-year term of a $200,000 mortgage loan with a 25-year amortization. The interest rate has been 6.5% compounded semiannually for the initial term. How much will their monthly payments increase if the interest rate upon renewal is 7.5% compounded semiannually and the original amortization period is continued? (Do not round the intermediate calculations.
The typical term of a fixed rate mortgage for a house is 30 years. The mortgage...
The typical term of a fixed rate mortgage for a house is 30 years. The mortgage has a nominal annual interest rate of 4.75 percent compounded monthly, and payments are due at the end of each month. (a). What is the monthly payment ($/month) if the borrowed principal is $100,000? (b). Suppose that the mortgage has the same term but that instead of quoting an interest rate, you are simply quoted a monthly payment of $599.55 per month. Based on...
You take out a mortgage in the amount of $200,000 for 30 years at a rate...
You take out a mortgage in the amount of $200,000 for 30 years at a rate of 5.75%: a. What is the monthly payment amount? b. How much interest will you pay over 30 years? c. What will be the payoff at the end of the 5th year? d. How much interest will you pay during the 5th year? Please show your work and explain your answers.
You take out a mortgage in the amount of $200,000 for 30 years at a rate...
You take out a mortgage in the amount of $200,000 for 30 years at a rate of 5.75%: a. What is the monthly payment amount? b. How much interest will you pay over 30 years? c. What will be the payoff at the end of the 5th year? d. How much interest will you pay during the 5th year? Please show your work and explain your answers. No answers from excel spread sheets
Define the term ‘business’. How has the concept of ‘business’ over the years? What is the...
Define the term ‘business’. How has the concept of ‘business’ over the years? What is the difference between the act of ‘doing business’ and ‘a business’? What will the concept of ‘business’ look like in the future? Why? Please discuss.
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT