7. In order to purchase a house, you have taken out a 30 year mortgage of...

7. In order to purchase a house, you have taken out a 30 year mortgage of $200,000 at 4.29% interest per year. You make payments at the end of every month. What is the amount of each monthly payment?Refer to question 7. Using Excel, prepare an amortization table for the mortgage. Obtain the results for the first fifteen periods and include the results with your completed assignment. On the same page, show the overall sums for the relevant columns over the full period of the mortgage.

Solutions

Expert Solution

Solution:

7.	Monthly payments on this mortgage = $988.57

Working Notes:
	The total number of payments
	In this case, make one payment per month for 30 years. This means you will make 360 payments over the course of the mortgage 12 x 30 = 360



	Calculation a monthly payment
	The 4.29% is an annual interest rate. Since all the following calculations are based on a monthly payment schedule, the annual rate needs to be converted to a monthly rate. The monthly interest rate would be =4.29%/12 =0.3575% [7% / 12 = 0.583%].




	Determining the monthly payment to account for interest requires a complicated formula shown below: using formula of present value of annuity as present value of annuity is equals to total mortgage value.



	present value of annuity = Px[ 1-1 /(1 + i)^n)]/ i

	P=monthly payment
	i= interest rate per period = 4.29%/12
	n= no. Of period = 12 x 30 =360

	PV of annuity= Mortgage loan $200,000

	present value of annuity = Px[ 1-1 /(1 + i)^n)]/ i
	200,000 = P x (1-1/(1+(4.29%/12))^360)/(4.29%/12)
	200,000 = P x 202.3126657778
	P= $200,000/202.3126657778
	P= 988.5688532
	P= $988.57

	Monthly payment = $988.57

Now 2nd Part

	For 1st payment, we already know the total amount is $988.57 monthly payments. To determine how much of that goes toward interest, we multiply the remaining balance $200,000 mortgage value by the monthly interest rate: 200,000 x 0.3575% = $715 . The rest goes toward the mortgage balance ($988.57 - $715 = $273.57). So after the first payment, the remaining amount on the mortgage is $ ($200,000 - $273.57 = $199,726.43).






Here	Total interest paid during life mortgage is 155,884.79
	Total principle paid during life mortgage is 200,000 the mortgage amount .
	Total amount paid is 355,884.79

Computation table is given below of excel

Notes:	Here monthly payments are taken 5 decimals means 988.56885 in our computation in excel as for convenient it is shown in two decimal.

Payment	Amount	Interest @ 0.3575%	Principal	Balance
				200,000
1	988.57	715.00	273.57	199726.43
2	988.57	714.02	274.55	199451.88
3	988.57	713.04	275.53	199176.36
4	988.57	712.06	276.51	198899.84
5	988.57	711.07	277.50	198622.34
6	988.57	710.07	278.49	198343.85
7	988.57	709.08	279.49	198064.36
8	988.57	708.08	280.49	197783.87
9	988.57	707.08	281.49	197502.38
10	988.57	706.07	282.50	197219.88
11	988.57	705.06	283.51	196936.37
12	988.57	704.05	284.52	196651.85
13	988.57	703.03	285.54	196366.31
14	988.57	702.01	286.56	196079.75
15	988.57	700.99	287.58	195792.17

356.00	988.57	17.48	971.09	3919.19
357.00	988.57	14.01	974.56	2944.63
358.00	988.57	10.53	978.04	1966.59
359.00	988.57	7.03	981.54	985.05
360.00	988.57	3.52	985.05	0.00
Total	355884.79	155884.79	200000.00


Working notes :


Payment	Amount	Interest @ 0.3575%	Principal	Balance
				200,000
1	988.57	715.00	273.57	199726.43
		[200,000 x .3575%]	[988.57-715]	[200,000-273.57]

Please feel free to ask if anything about above solution in comment section of the question.

jeff jeffy answered 2 years ago

Next > < Previous

Related Solutions

Suppose you are buying a house and have taken out a mortgage for $250,000. The mortgage...

Suppose you are buying a house and have taken out a mortgage for $250,000. The mortgage is a 30 year fixed rate mortgage with an APR of 5.25%. What is your monthly mortgage payment? Construct a loan amortization table in Excel for mortgage loan in the previous problem. You should do the problem in Excel using monthly payments and you must submit the spreadsheet with formulas.

You have just taken out a 30‑year mortgage on your new home for$103,104. This mortgage...

You have just taken out a 30‑year mortgage on your new home for $103,104. This mortgage is to be repaid in 360 equal monthly installments. If the stated (nominal) annual interest rate is 13.35 percent, what is the amount of each of the monthly installments? (Note: The convention when periodic payments are involved is to assume that the compounding frequency is the same as the payment frequency, unless stated otherwise. Thus this implies 13.35 % APR, compounded monthly for this...

You have just bought a house and have taken out a mortgage (an installment) loan for...

You have just bought a house and have taken out a mortgage (an installment) loan for $500,000. This is a 30-year loan that requires monthly payments and the first payment is due one month from today. The APR for the loan is 24%. You are interested to know how much of your 210th monthly payment will go toward the repayment of principal? That amount is _______________ Question 10 options: $762.65 $9,586.98 $625.64 $9,494.78 $513.24 $9,382.38 $421.04 $9,245.37

You purchase a house that costs $625,000 with an 8%, 30-year mortgage. You make a 20%...

You purchase a house that costs $625,000 with an 8%, 30-year mortgage. You make a 20% down payment to avoid PMI insurance. 1. What is your monthly payment? 2. Amortize the first and second payments. 3. What is the mortgage balance after 5 years? 4. What percentage of the principal is paid off after 5 years? 5. Suppose after 5 years you refinance at 6% the remaining balance at a cost of $10,000, for 30 years. What is your new...

A homeowner took out a 30-year, fixed-rate mortgage of $145,000. The mortgage was taken out 6...

A homeowner took out a 30-year, fixed-rate mortgage of $145,000. The mortgage was taken out 6 years ago at a rate of 8.4 percent. If the homeowner refinances, the charges will be $2,550. What is the highest interest rate at which it would be beneficial to refinance the mortgage? (Do not round intermediate calculations. Enter your answer as a percent rounded to 2 decimal places.)

You take out a 30-year mortgage to buy a house worth $449,000. The down payment is...

You take out a 30-year mortgage to buy a house worth $449,000. The down payment is 7% and the annual interest rate is 4.9%. What are the monthly payments? Round to the nearest cent.

You take out a 30-year mortgage to buy a house worth $312,000. The down payment is...

You take out a 30-year mortgage to buy a house worth $312,000. The down payment is 21% and the annual interest rate is 4.4%. What are the monthly payments? Round to the nearest cent.

You take out a 30-year mortgage to buy a house worth $396,000. The down payment is...

You take out a 30-year mortgage to buy a house worth $396,000. The down payment is 19% and the annual interest rate is 4.9%. What are the monthly payments? Round to the nearest cent.

Suppose you take out a 30-year mortgage for a house that costs $496,845. Assume the following:...

Suppose you take out a 30-year mortgage for a house that costs $496,845. Assume the following: The annual interest rate on the mortgage is 3.9%. The bank requires a minimum down payment of 16% at the time of the loan. The annual property tax is 2.1% of the cost of the house. The annual homeowner's insurance is 0.6% of the cost of the house. There is no PMI If you make the minimum down payment, what will your monthly PITI...

You plan to purchase a house for $115,000 using a 30-year mortgage obtained from your local...

You plan to purchase a house for $115,000 using a 30-year mortgage obtained from your local bank. You will make a down payment of 20 percent of the purchase price. You will not pay off the mortgage early. (LG 7-3) a. Your bank offers you the following two options for payment: Option 1: Mortgage rate of 9 percent and zero points. Option 2: Mortgage rate of 8.85 percent and 2 points. Which option should you choose? b. Your bank offers...

Subjects