Question

Only Part a and b (Plan 1 & Plan 2) Using Excel only

J&J Cattle has purchased a quarter section of land for $160,000. They make a down payment of $20,000, and the remainder of the purchase price ($140,000) is financed at 11 percent compounded quarterly with quarterly payments over 2 years. Develop an Excel® table to illustrate the payment amounts and schedule for the loan, assuming payback follows

Plan 1: Pay the accumulated interest at the end of each interest period and repay the principal at the end of the loan period.

Plan 2: Make equal principal payments, plus interest on the unpaid balance at the end of the period.

Plan 3: Make equal end-of-period payments.

Plan 4: Make a single payment of principal and interest at the end of the loan period. A different plan: Pay off the principal per the table below. In addition, pay the accumulated interest at the end of each interest period.

Quarter	1	2	3	4	5	6	7	8
Principal	$X	$ 2X	$ 5X	$ 4X	$3X	$3X	$2X	$X

Answer 1

		PLAN1
		Quarterly interestRate =(11/4)%=2.75%=0.0275
		Quarterly interest Payment	$3,850	(140000*0.0275)
		Quarter	1	2	3	4	5	6	7	8
		Interest Payment	$3,850	$3,850	$3,850	$3,850	$3,850	$3,850	$3,850	$3,850
		Principal Payment								$140,000
		Total Quarterly Payment	$3,850	$3,850	$3,850	$3,850	$3,850	$3,850	$3,850	$143,850
		PLAN 2
		Quarter	1	2	3	4	5	6	7	8
	A	Beginning Principal Balance	$140,000	$122,500	$105,000	$87,500	$70,000	$52,500	$35,000	$17,500
	B=A*0.0275	Interest payment	$3,850	$3,369	$2,888	$2,406	$1,925	$1,444	$963	$481
	C=140000/8	Principal Payment	$17,500	$17,500	$17,500	$17,500	$17,500	$17,500	$17,500	$17,500
	D=A-C	Ending Principal Balance	$122,500	$105,000	$87,500	$70,000	$52,500	$35,000	$17,500	$0
	E=B+C	Total Quarterly Payment	$21,350	$20,869	$20,388	$19,906	$19,425	$18,944	$18,463	$17,981