already solved i) need help with ii)

please use excel and show formulas

please fill in table and show formulas and all work

Jim wants to start a new company. (He needs an initial investment of $9,500,000 to purchase and outfit his company appropriately. Of this investment, he already has $1,000,000 saved up, so he'll need a loan of $8,500,000 financed with constant payments). He has yet to choose the bank that will lend him the money and must explore his options. The period of the loan is over 10 years. All the projects will have a transaction fee to a broker of the sale that is equal to 3% of the assets value. The inflation rate is 5% per year on all revenues regardless of which project is chosen. The CCA (Class 8, 20% rate) on these assets is split with the ratio of 80% intangible and 20% tangible and the company can only claim CCA on the tangible portion of the assets at 20% of the assets total value. The company also has to get periodic turnaround inspections of tanks, fire tubes and other battery maintenance (a battery is a combination of surface vessels (tanks) and processing equipment to get the product into a sellable state) which occurs every 5 years at an expense of $300,000. The advertising cost for the first year will be $20,000 and each of the years following it will be $40,000. The company will have an operating cost of 400,000 in year 2 with it increasing at 5% per year in years 3-10 as operating and maintenance costs. The asset is expected to have a decrease in production from year 4 forward at a rate of 10% per year. Financing After contacting different banks, Jim has the following three offers: Bank A-4% with annual compounding Bank B-6% with semi-annual compounding Bank C-5% with monthly compounding Bank D - 4% with bi-weekly compounding i) Create a table with the effective interest rate expressed by the Excel formula and by the numerical formula ii) Determine the best bank for John and create the loan scheme complete with principal, repayment, interest and cash flow for each of the ten years. DATA BANK LOAN $9,500,000.00 TIME PRINCIPAL INTEREST NET PAYMENT CASH FLOW INVESTMENT INITIAL REVENUE TIME (YEARS) $3,000,000.00 101 0 $9,500,000.00 1 DISCOUNT RATE INTEREST RATE 15.00% 4.0096 21 3 26 4 COMPOUNDING BANK LOAN RATE 4.078% 5 BANK PAYMENT 6 7 8 9 10 TOTAL