In order to expand, El Cap Climbing Company (ECCC) isconsidering taking out a mortgage for a new store location, anonresidential real property that includes land and a building.Leah is unsure if she has the cash flow to take on any more debt.She asked you to create a loan amortization schedule for theproposed mortgage loan. Then, you’ll create a chart that representsthe portion of each payment that goes toward principal andinterest. A. Prepare the following: n A loan amortization schedulen A chart showing the percentage of the payment applied toward theprincipal and interest
Loan Amortization Schedule
First, you’ll need to create a loan amortization schedule in thedownloaded Excel spreadsheet. Create the table on the tab named“Part 2 Loan Amortization Sched.” The following table illustratesthe payments and interest amounts for a fixed-rate, 30-year,$500,000 mortgage, at a five-percent interest rate. The monthlypayment will be 2,684.11Payment Number
Payment Amount
5% Interest Expense
Principal Balance Annual Interest Expense 0 500,000.00 1 2,684.112,083.33 600.78 499,399.22 2 2,684.11 2,080.83 603.28 498,795.94…break in the sequence… Totals 466,278.03 500,000.00 359 2,684.1122.22 2,661.89 2,671.41 360 2,682.54 11.13 2,671.41 - 855.56
The table serves as an example of what you’ll create in Excel. Notethat the table shows only the figures for the first and the lastyear of payments; you’ll need to calculate the amounts for theremaining payments, and fill them in. Once you’ve determined howeach of the amounts in the table is obtained, you can use relativeand absolute cell references to fill in the full 360 paymentsThefollowing is an explanation of the columns in the table: n Paymentnumber—The first column in the table shows the 360 paymentsrequired to pay off the mortgage loan (30 years, with 12 monthlypayments per year). n Payment amount—The second column shows themonthly payment amount. n Interest—The third column shows theportion of the monthly payment that goes to interest. nPrincipal—The fourth column shows the portion paid toward theprincipal. n Balance—The fifth column shows the starting balance of$500,000, and the remaining balance each month after the principalis subtracted. n Annual interest expense—The last column provides arunning total of the interest expense on the mortgage for theentire 12-month period. It’s the amount that would be reported onthe financial statements. n Totals—The “Totals” under the “5%Interest Expense” and “Principal” columns show the final totals forthe 30-year life of the mortgage.
Mortgage Principal and Interest Chart Next, you’ll create a chartfollowing these steps. Create the table on the tab named “Part 2Chart.” 1. Start by selecting the Interest Expense and Principalcolumns. Make sure to select the column headers and values. Don’tselect the Totals row. 2. Click on the Insert tab and select a“Stacked Column.” Make sure to label the x-axis (payment month) andy-axis (dollars), and include a legend for the two values (interestand principal). 3. Your final chart should be set up similar to thechart below, with the data populating the chart. (The incrementsdon’t need to be the same). B. Answer the following: 1. How can youdescribe the relationship between time and the amount paid towardsprincipal and interest? 2. Knowing what we know about ECCC’s cashflow from Part 1, is it reasonable to believe that ECCC can take onthis new debt