Parts H, I, and J

1. (75 points total) A half section of irrigated farmland (i.e. 320 acres) has just become available and you approach your Uncle about buying the land. The asking price is S6,500/acre but you figure it can be had for $6,200/acre. You have $12,000 to contribute and your Uncle agrees to put $300,000 toward the purchase with the remaining amount being financed with Farm Credit Services (FCS) of America. Answer the following questions: (5 points) How much do you need to finance with the bank? (5 points) The bank will loan the amount above at 6% annual interest amortized over 25 years with semi-annual payments. How much are payments twice per year? (10 points) Use Excel to put together an amortization schedule using the following headings a. b. c. PeriodBeginning Balance PrincipaInterestTotal PMT Ending Balance Make sure to extend your schedule the full 25 years (i.e. 50 payments) and make sure your ending balance is zero (at least within rounding). Print out your schedule to turn in using Fit Sheet on One Page". (2 points) According to your amortization schedule, how much do you still owe after making the 27th payment? (10 points) Use the PVa and FVA annuity formulas and your calculator (i.e. not Excel) to corroborate this outstanding loan balance (i.e. the amount still owed after the 27th payment). Recall, an outstanding loan balance (OLB) is always the PV of the remaining payments as well as the FV of the payments already made. (20 points) Construct two graphs in Excel. The first graph should show the principal and interest paid in each period as a "Stacked Column" chart. The second graph should show the OLB in each period as a "Scatter with Smooth Lines" chart. Make sure to label your axes and title your graphs for full credit. (5 points) How much total interest will you pay on the loan if it goes to maturity? d. e. f. g. For 1h and li, use Excel functions or your financial calculator (or TM formulas) to calculate exact answers. You may corroborate your answer with your amortization schedule from 1c, but do not approrimate your answers using the schedule. h. (5 points) At what point in tine do you still owe exactly of the original amount you borrowed? (5 points) At what point in time is half your semi-annual payment interest and the other half principal? (8 points) How big a balloon payment (i.e $60,000? i. to get the semi-annual payment down to