Question

Only Part c and d (Plan 3 & Plan 4) 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

Purchase value	160,000	$
Down payment	20,000	$
Loan	140,000	$
Interest rate	11%	compounded quarterly
Payment term	2	years
	8	quarters
Plan 3: Make equal end of period payments
This plan would require loan repayment to be made in equated quarterly payments
at the end of the period
using Excel's inbuilt PMT function, the periodic payment is calculated as follows:
PV		140000
Rate		2.75%	per quarter
N		8	quarters
Quarterly payments		-19,734
The loan repayment schedule is as follows for plan 3
Quarter	1	2	3	4	5	6	7	8
Payment	19,734	19,734	19,734	19,734	19,734	19,734	19,734	19,734
Plan 4:
Principal repayment as per following table and accumulated interest payment at the end of every quarter
Quarter	1	2	3	4	5	6	7	8
Principal	$X	$ 2X	$ 5X	$ 4X	$3X	$3X	$2X	$X
Total principal divisions		21
X	=	6,667
Quarter	0	1	2	3	4	5	6	7	8
Beginning loan	140000	140,000	133,333	120,000	86,667	60,000	40,000	20,000	6,667
Principal pay		1	2	5	4	3	3	2	1
Principal pay		6,667	13,333	33,333	26,667	20,000	20,000	13,333	6,667
Interest pay		3,850	3,667	3,300	2,383	1,650	1,100	550	183
Ending loan	140,000	133,333	120,000	86,667	60,000	40,000	20,000	6,667	0
		1	2	3	4	5	6	7	8
Total payment		10,517	17,000	36,633	29,050	21,650	21,100	13,883	6,850