An amortization table has been created for a business with the following information.

Use this file Module 9-Assignment File Fall 2022 .xlsxDownload Module 9-Assignment File Fall 2022 .xlsx

Loan: $4,000,000

Annual interest rate: 4.9%

Loan period: 10 years

Payments: monthly, end of each month

Loan will be fully paid in 10 years.

In column A, create a sequence of numbers to match your loan periods.

A) In the Amortization sheet, show the Interest, Principal, and Balance of the loan for all periods.

Create a table to show cumulative interest payments and cumulative principal payments of this loan for each year.

B) In the Cashflow sheet, complete the cashflow for a service business using the loan information from parts A and B. Calculate net cash flow for the duration of the projects. Additional information is as follows:

Annual revenue: $850,000 fixed for all years Annual operating costs include: Wages: $240,000 Insurance: $14,000 Maintenance and utility: $20,000 Income tax rate: 15%. This business does not pay any tax when there is a loss. (If taxable income is negative, tax will show zero).

Loan 4,000,000.00 APR 4.90% Payment Periods within the year 12 Payments (total years of loan) 10 Amortization table: Period Interest Principal Balance

Cumulative principal & interest payments: Payments in year: 1 2 3 4 5 6 7 8 9 10 Interest Principal

Fixed Income 850,000 Life 10 Income tax Rate 0.15 Wages 240,000 Property Value (cost) 4,000,000 Insurance 14,000 Maintenance and utility 20,000 Salvage 1,000,000 Year 1 2 3 4 5 6 7 8 9 10 Revenue Operating costs Operating income Interest Payments Depreciation Taxable income Tax After Tax income Add depreciation Principal Payments Net Cash Flow