Question

In: Finance

Create an amortization table for the following mortgage. a. Mortgage amount is $300,000. b. The mortgage...

Create an amortization table for the following mortgage.

a. Mortgage amount is $300,000.

b. The mortgage is a 30 year fixed at 4% (paid monthly).

c. You pay an additional $100 per month until the mortgage is paid.

When will the mortgage be paid off?

Please show in Excel along with formulas.

Expert Solution

Compute the monthly rate using the equation as shown below:

Monthly rate = Annual rate / Number of periods in a year

= 4% / 12

= 0.3333%

Hence, there monthly rate is 0.3333%.

Compute the period using the equation as shown below:

Period = Number of years * Number of payments per year

= 30 * 12

= 360

Hence, the period is 360.

Compute the PVIFA at .3333% and 360 period, using the equation as shown below:

PVIFA = {1 – (1 + Rate)-Number of periods}/ Rate

= {1 – (1 + 0.3333%)-360}/ .3333%

= 209.4713584

Hence, the PVIFA at .3333% and 360 period is 209.4713584.

Compute the monthly payment using the equation as shown below:

Monthly payment = Mortgage amount / PVIFA ( 0.3333%, 360)

= $300,000 / 209.4713584

= $1,432.176706

Hence, the monthly payment is $1,432.176706.

Prepare the amortization schedule using MS-Excel as follows:

The result of the above table is as follows:

Hence, the loan is fully repaid after 318 months or 26.5 years.

jeff jeffy answered 1 year ago

In Excel, create the amortization table for a 15-year mortgage for $780,000 at 6.6% interest. Individual...

In Excel, create the amortization table for a 15-year mortgage for $780,000 at 6.6% interest. Individual taxpayers may be able to deduct the amount of interest that they paid on a mortgage for a personal residence. If the mortgage was issued on July 1, how much interest will be earned in the first year? How much interest will be earned in the second year?

In Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual...

In Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual taxpayers may be able to deduct the amount of interest that they paid on a mortgage for a personal residence. If the mortgage was issued on May 1, how much interest will be earned in the first year? How much interest will be earned in the second year? Upload your Excel file below.

Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual taxpayers...

Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual taxpayers may be able to deduct the amount of interest that they paid on a mortgage for a personal residence. If the mortgage was issued on May 1, how much interest will be earned in the first year? How much interest will be earned in the second year? Upload your Excel file below. I dont know how to create an ammortization in excel. It is...

You are considering an adjustable rate mortgage loan with the following characteristics: • Loan amount: $300,000...

You are considering an adjustable rate mortgage loan with the following characteristics: • Loan amount: $300,000 • Term: 20 years • Index: one year T-Bill • Margin: 2.5% • Periodic cap: 2% • Lifetime cap: 5% • Negative amortization: not allowed • Financing costs: $3,500 in origination fees and 1 discount point Suppose the Treasury bill yield is 3.5% at the outset and is then moves to 4.5% at the beginning of the second year and to 8.5% at the...

A property is encumbered as follows: First mortgage, A: $300,000 Second mortgage, B: $40,000 Third mortgage,...

A property is encumbered as follows: First mortgage, A: $300,000 Second mortgage, B: $40,000 Third mortgage, C: $10,000 What is the minimum that mortgagee B has to pay to acquire the property at a foreclosure sale?

Constant Amortizing Mortgage Create an amortization schedule and answer the following questions for a $600,000 30-year...

Constant Amortizing Mortgage Create an amortization schedule and answer the following questions for a $600,000 30-year fully-amortizing CAM loan with monthly payments. The contract interest rate on the loan is 3.25% and there are no points associated with the loan. a.) Assuming no upfront fees/points or prepayment penalty, what is the EIR if the mortgage is prepaid in full at the end of year 7? b.) Assuming 1 point in origination fees but no prepayment penalty, what is the EIR...

Create a loan amortization schedule in Excell for a $275,000 mortgage that will be repaid over...

Create a loan amortization schedule in Excell for a $275,000 mortgage that will be repaid over 20 years with monthlypayments. The annual interest rate is 5.5 %. What is your monthly payment? $ What is the total dollar amount of payments made over the life of this loan? $__ What is the total dollar amount of interest paid over the life of this loan? $_ How many months will it take to pay off the loan if you pay an extra $100...

Bond Amortization and adjusting entry. Use excel to create an amortization table for 10 years and...

Bond Amortization and adjusting entry. Use excel to create an amortization table for 10 years and show the adjusting entry for the bond payable for the June payment. Show calculations. Had issued $1,500,000 of 4%, 10-year bond, dated 1/1/18 for $1,383,079 when the market rate was 5%. Interest is paid on June 30 and January 1 using the effective interest rate method. The June payment is included in the Dec. 1 TB. Trial Balance December 1, 2021 Description Debit Credit...

Assume a mortgage loan amount of $ 300,000, with annual interest rate 4% and 5 years...

Assume a mortgage loan amount of $ 300,000, with annual interest rate 4% and 5 years term 1. Calculate annual payment amount 2. Calculate monthly payment amount 3. Do you pay more annually or monthly? Why? 4. Prepare a 60 month loan amortization schedule. How much is your total interest payment?

For this quiz, create an amortization table spreadsheet for a loan with initial balance of $50,000...

For this quiz, create an amortization table spreadsheet for a loan with initial balance of $50,000 at 6.43% with a monthly payment of $1200. Answer the following questions based on that spreadsheet. What will the loan balance be after the first payment is made? After which monthly payment does the loan become fully paid off? Using the $1200 regular monthly payment in that last month will overpay the loan. How much should that last monthly payment be reduced to so...