Question

You plan to purchase a $200,000 house using a 30-year mortgage obtained from your local credit union. The mortgage rate offered to you is 6.50 percent. You will make a down payment of 20 percent of the purchase price. (LG 7-4) a. Calculate your monthly payments on this mortgage. b. Construct the amortization schedule for the first six payments.

please show in excel

Answer 1

The Given Information is as below:-

1. Cost of the house:- $200,000 , 2. Mortgage Tenrure - 30 Years, 3. Mortagage Rate:- 6.50 Percent. 4. Down Payment for the House :- 20% of $200,000 = $40,000.

Therefore Interest Rate to be paid per Month (R) = 6.5%/12 =  0.54%

Mortrage Amount (P) = Cost of the house - Down Payment = $200,000 - $40,000 = $160,000

Number of Monthly Installments(N)= Mortage Tenrure Period X 12 = 30 * 12 = 360

A. Calcualte the Monthly Payments on this mortgage.

To calculate the Monthly Payment of the mortgage we require the Mortgage amount, Number of Monthly Payments & Interest rate to be paid monthly.

Formula for the Mortgage Monthly Payments=

R = Interest Rate to be paid Per Month

P = Mortrage Amount

N = Number of Monthly Installments

Therefor Monthly Payments = (0.54%x160,000)/ (1-((1+0.54%)^-360)

= $1011.3

Hence the Montly Payment amount of the Mortgage = $1011.3

B. Construct the amortization schedule for the First Six Months.

For the given statement we require three inputs. which are as follows

1. Montly Payment amount of the Mortgage = $1011.3

2. Interest paid per Month = Mortgage Amount x Interest rate per month

3. Principal amount paid per Month = Monthly Payment - Interest paid of the month

The Amortization schedule for the first six payments in the as below

Period	Payment	Interest	Principal	Residual Debt
1	1,011.3	866.7	144.6	1,59,855.4
2	1,011.3	865.9	145.4	1,59,709.9
3	1,011.3	865.1	146.2	1,59,563.7
4	1,011.3	864.3	147.0	1,59,416.7
5	1,011.3	863.5	147.8	1,59,268.9
6	1,011.3	862.7	148.6	1,59,120.3