Question

Solve only a) part

Please note - Different question.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

a) For each of the five payment schedules, determine the present worth of the loan payments made by the borrower. Use an Excel® spreadsheet and program it such that you can enter different interest rates for the borrower's TVOM. Use TVOM rates of 8 percent, 11 percent, and 14 percent compounded quarterly. (Show your analysis and justification by drawing a graph)

Answer 1

Financed Amount	$        1,40,000.00
Rate of Interest	11%
Loan Amortization Schedule						TVOM Calculation
Period	Opening balance	Interest	Principal Repayment	Total payment per quarter	Closing balance	DCF@8%	PV	DCF@11%	PV	DCF@14%	PV
Q1	140000.00	3850.00	17500	21350.00	122500.00	0.980392	20931.37	0.973236	20778.59	0.966184	20628.02
Q2	122500.00	3368.75	17500	20868.75	105000.00	0.961169	20058.39	0.947188	19766.64	0.933511	19481.2
Q3	105000.00	2887.50	17500	20387.50	87500.00	0.942322	19211.6	0.921838	18793.97	0.901943	18388.36
Q4	87500.00	2406.25	17500	19906.25	70000.00	0.923845	18390.3	0.897166	17859.21	0.871442	17347.15
Q5	70000.00	1925.00	17500	19425.00	52500.00	0.905731	17593.82	0.873154	16961.02	0.841973	16355.33
Q6	52500.00	1443.75	17500	18943.75	35000.00	0.887971	16821.51	0.849785	16098.11	0.813501	15410.75
Q7	35000.00	962.50	17500	18462.50	17500.00	0.87056	16072.72	0.827041	15269.25	0.785991	14511.36
Q8	17500.00	481.25	17500	17981.25	0.00	0.85349	15346.82	0.804906	14473.22	0.759412	13655.17

Notes:
1) 8% is annual discounting rate which is taken as 2% per quarter. Similarly, 2.75% per quarter for 11% discounting rate and 3.5% per quarter for 14% discounting rate.
2) It is assumed that quarterly payment are made at the end of quarter.