A residential home worth $ 1,249,000 in a town. Create an amortization schedule with 2 different...

A residential home worth $ 1,249,000 in a town. Create an amortization schedule with 2 different financing options with Excel. Complete the following steps:

I. Determine a down payment. (a standard down payment is 20%)

II. Research 2 different financing options (describe)

III. Use Excel to create a complete amortization schedule for the lif of both financing options.

IV. Write up an analysis that compares and contrasts the two financing options in detail. Please be specific. Include justifications for selecting an option.

Expert Solution

Answer (I) :- The Value of Down payment is 1249000*20% = 249800

Answer (II) :- Different Financing Options :-

Fixed Rate Loan :- It is the most common used financing option in which the rate of interest on loan is fixed over the period of loan. It is generally of 15 to 20 years . In this the yearly or monthly payment of loan can be done over the period of loan

FHA Loan :- FHA Loan stands for federal housing administrative Loan . In this type of loan generally of stand alone downpayment near about 20% is made and the remaining amount is paid over the life of loan. The Downpayment is not down as 3.5%

Answer (III) :- Amortisation schedule

As per Fixed Rate loan

	Amortisation Schedule

Assume Interest rate is 10%p.a				And loan repaid in 15 installments
	Amount of loan =		1249000

FV of loan	=	1249000*FVF(10%,15)		=	1249000*4.18		=	5220820
Amount of each installment.			5220820/PVAF(10%,15)		=	1249000/7.6060 =		164212.46



Year	Opening Bal.	Installment	Interest	Principal	Closing Bal.
1	12,49,000.00	1,64,212.46	1,24,900.00	39,312.46	12,09,687.54
2	12,09,687.54	1,64,212.46	1,20,968.75	43,243.71	11,66,443.83
3	11,66,443.83	1,64,212.46	1,16,644.38	47,568.08	11,18,875.76
4	11,18,875.76	1,64,212.46	1,11,887.58	52,324.88	10,66,550.87
5	10,66,550.87	1,64,212.46	1,06,655.09	57,557.37	10,08,993.50
6	10,08,993.50	1,64,212.46	1,00,899.35	63,313.11	9,45,680.39
7	9,45,680.39	1,64,212.46	94,568.04	69,644.42	8,76,035.97
8	8,76,035.97	1,64,212.46	87,603.60	76,608.86	7,99,427.11
9	7,99,427.11	1,64,212.46	79,942.71	84,269.75	7,15,157.36
10	7,15,157.36	1,64,212.46	71,515.74	92,696.72	6,22,460.63
11	6,22,460.63	1,64,212.46	62,246.06	1,01,966.40	5,20,494.24
12	5,20,494.24	1,64,212.46	52,049.42	1,12,163.04	4,08,331.20
13	4,08,331.20	1,64,212.46	40,833.12	1,23,379.34	2,84,951.86
14	2,84,951.86	1,64,212.46	28,495.19	1,35,717.27	1,49,234.59
15	1,49,234.59	1,64,212.46	14,923.46	1,49,289.00	-

As per FHA Loan


	Amortisation Schedule

Assume Interest rate is 10%p.a				And loan repaid in 15 installments
	Amount of loan =		1249000
	Less :- Down Payment (20%)		-249800
	Remaining		999200

FV of loan	=	999200*FVF(10%,15)		=	999200*4.18		=	4176656
Amount of each installment.			999200/PVAF(10%,15)		=	999200/7.6060 =		131369.97



Year	Opening Bal.	Installment	Interest	Principal	Closing Bal.
1	9,99,200.00	1,31,369.97	99,920.00	31,449.97	9,67,750.03
2	9,67,750.03	1,31,369.97	96,775.00	34,594.97	9,33,155.06
3	9,33,155.06	1,31,369.97	93,315.51	38,054.46	8,95,100.60
4	8,95,100.60	1,31,369.97	89,510.06	41,859.91	8,53,240.69
5	8,53,240.69	1,31,369.97	85,324.07	46,045.90	8,07,194.79
6	8,07,194.79	1,31,369.97	80,719.48	50,650.49	7,56,544.30
7	7,56,544.30	1,31,369.97	75,654.43	55,715.54	7,00,828.76
8	7,00,828.76	1,31,369.97	70,082.88	61,287.09	6,39,541.66
9	6,39,541.66	1,31,369.97	63,954.17	67,415.80	5,72,125.86
10	5,72,125.86	1,31,369.97	57,212.59	74,157.38	4,97,968.47
11	4,97,968.47	1,31,369.97	49,796.85	81,573.12	4,16,395.35
12	4,16,395.35	1,31,369.97	41,639.54	89,730.43	3,26,664.92
13	3,26,664.92	1,31,369.97	32,666.49	98,703.48	2,27,961.44
14	2,27,961.44	1,31,369.97	22,796.14	1,08,573.83	1,19,387.61
15	1,19,387.61	1,31,369.97	11,938.76	1,19,431.21	-


Answer (IV)	The comparison in these two are not possible the plan is to be choosen as per the cash arrangement

jeff jeffy answered 2 years ago

Create the amortization schedule of a four-year ordinary annuity worth $ 15,000 and an effective annual...

Create the amortization schedule of a four-year ordinary annuity worth $ 15,000 and an effective annual return on 6 percent with monthly instalments, and calculate how much principal is expected to be paid in the first month of the second year. If it possible, could u do it in excel table. Thank you in advance.

Create an Amortization Schedule for a home that cost $259,000 with 20% down for a down-payment....

Create an Amortization Schedule for a home that cost $259,000 with 20% down for a down-payment. Do not use an online amortization schedule. Note the textbook amortization schedule is using an annual payment. Please complete a schedule for both 180 months (15 years) and 360 months (30 years). The interest rate for the year is 2.75% according to Bankrate for the area the home is in. Remember to divide your interest by 12 to get a monthly rate. How much...

An amortization schedule is a common concept within a banking enterprise. Create your own amortization schedule...

An amortization schedule is a common concept within a banking enterprise. Create your own amortization schedule for a $10,000 loan with monthly payments paid over two years. Assume the annual percentage rate (APR) is 4%. Create the amortization schedule within Excel or another spreadsheet application. Use page 184 as a template. Keep in mind that the example on page 184 uses annual payments, while your schedule will use monthly payments. You will need to adjust the number of payment periods...

Finance- Amortization Schedule Create the amortization schedule for a loan of $5,000, paid monthly over two...

Finance- Amortization Schedule Create the amortization schedule for a loan of $5,000, paid monthly over two years using an 8 percent APR.

Problem 5-50 Amortization Schedule (LG9) Create the amortization schedule for a loan of $4,300, paid monthly...

Problem 5-50 Amortization Schedule (LG9) Create the amortization schedule for a loan of $4,300, paid monthly over two years using an 9 percent APR. (Round your answers to 2 decimal places.) Month Beginning Balance Total Payment Interest Paid Principal Paid Ending Balance 1 2 3 4 5 6 7 ...

Problem 5-49 Amortization Schedule (LG9) Create the amortization schedule for a loan of $14,000, paid monthly...

Problem 5-49 Amortization Schedule (LG9) Create the amortization schedule for a loan of $14,000, paid monthly over three years using a 9 percent APR. (Round your answers to 2 decimal places.) Month Beginning Balance Total Payment Interest Paid Principal Paid Ending Balance 1 2 3 4 5 6 7...

Amortization Schedules This project requires you to create an amortization schedule for two types of loans,...

Amortization Schedules This project requires you to create an amortization schedule for two types of loans, a fully amortizing constant payment mortgage (CPM) loan and a constant amortizing (CAM) loan. In your report, compare the amortization schedule of the CPM and CAM loans (How are they similar? How are they different? Which would you prefer and why?) Part 1: Monthly Payment Consider a $10,000 loan made at a 12 percent annual (nominal) rate of interest for 3 years. A) Calculate...

Amortization Schedule You will be creating an amortization schedule for a house on the market. To...

Amortization Schedule You will be creating an amortization schedule for a house on the market. To do so, you will need the principal amount, the interest rate, and the amount of years you will borrow the loan. The amount you put as a down payment is up to you, but it can range from a minimum of 3.5% for an FHA loan or 5% for a traditional loan to a maximum of whatever. Traditionally you only need to go as...

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...

Create a loan amortization schedule in excell for a $27,000 car loan that will be repaid...

Create a loan amortization schedule in excell for a $27,000 car loan that will be repaid over 48 months at an annual interest rate of 6%. 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...

Question