In: Finance
In order to expand, El Cap Climbing Company (ECCC) is
considering taking out a mortgage for a new store location, a
nonresidential 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 the
proposed mortgage loan. Then, you’ll create a chart that represents
the portion of each payment that goes toward principal and
interest. A. Prepare the following: n A loan amortization schedule
n A chart showing the percentage of the payment applied toward the
principal and interest
Loan Amortization Schedule
First, you’ll need to create a loan amortization schedule in the
downloaded Excel spreadsheet. Create the table on the tab named
“Part 2 Loan Amortization Sched.” The following table illustrates
the payments and interest amounts for a fixed-rate, 30-year,
$500,000 mortgage, at a five-percent interest rate. The monthly
payment will be 2,684.11Payment Number
Payment Amount
5% Interest Expense
Principal Balance Annual Interest Expense 0 500,000.00 1 2,684.11
2,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.11
22.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. Note
that the table shows only the figures for the first and the last
year of payments; you’ll need to calculate the amounts for the
remaining payments, and fill them in. Once you’ve determined how
each of the amounts in the table is obtained, you can use relative
and absolute cell references to fill in the full 360 paymentsThe
following is an explanation of the columns in the table: n Payment
number—The first column in the table shows the 360 payments
required to pay off the mortgage loan (30 years, with 12 monthly
payments per year). n Payment amount—The second column shows the
monthly payment amount. n Interest—The third column shows the
portion of the monthly payment that goes to interest. n
Principal—The fourth column shows the portion paid toward the
principal. n Balance—The fifth column shows the starting balance of
$500,000, and the remaining balance each month after the principal
is subtracted. n Annual interest expense—The last column provides a
running total of the interest expense on the mortgage for the
entire 12-month period. It’s the amount that would be reported on
the financial statements. n Totals—The “Totals” under the “5%
Interest Expense” and “Principal” columns show the final totals for
the 30-year life of the mortgage.
Mortgage Principal and Interest Chart Next, you’ll create a chart
following these steps. Create the table on the tab named “Part 2
Chart.” 1. Start by selecting the Interest Expense and Principal
columns. Make sure to select the column headers and values. Don’t
select the Totals row. 2. Click on the Insert tab and select a
“Stacked Column.” Make sure to label the x-axis (payment month) and
y-axis (dollars), and include a legend for the two values (interest
and principal). 3. Your final chart should be set up similar to the
chart below, with the data populating the chart. (The increments
don’t need to be the same). B. Answer the following: 1. How can you
describe the relationship between time and the amount paid towards
principal and interest? 2. Knowing what we know about ECCC’s cash
flow from Part 1, is it reasonable to believe that ECCC can take on
this new debt
Amortization Schedule
Please find below the amortization schedule for the given loan of $ 500,000 for 30 years at the rate of interest of 5% p.a.:
Mortgage Interest and Principal Chart
Please find below the Mortgage interest and principal chart:
Q 1- How can you describe the relationship between time and the amount paid towards principal and interest?
Ans- By looking at the chart above, we can see that as time passes, the amount of principal increases and the amount of interest decreses. This is due to the fact that with time the loan balance reduces and interest is calculated on the reduced balance.
Q 2: Belongs to different part.