Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

(I would appreciate the effort in solving the entire problem. Thank you in advance) Part 2: Mortgage Decision To expand, El Cap Climbing Company (ECCC)

(I would appreciate the effort in solving the entire problem. Thank you in advance)

Part 2: Mortgage Decision

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, youll create a chart that represents the portion of each payment that goes toward principal and interest.

A. Prepare the following:

  • A loan amortization schedule
  • A chart showing the percentage of the payment applied to the principal and interest

Loan Amortization Schedule

First, youll 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.11.

Payment 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 youll create in Excel. Note that the table shows only the figures for the first and the last year of payments; youll need to calculate the amounts for the remaining payments and fill them in.

Once youve determined how each amount in the table is obtained, you can use relative and absolute cell references to fill in the full 360 payments.

The following is an explanation of the columns in the table:

  • Payment numberThe first column in the table shows the 360 payments required to pay off the mortgage loan (30 years, with 12 monthly payments per year).
  • Payment amountThe second column shows the monthly payment amount.
  • InterestThe third column shows the portion of the monthly payment that goes to interest.
  • PrincipalThe fourth column shows the portion paid toward the principal.
  • BalanceThe fifth column shows the starting balance of $500,000, and the remaining balance each month after the principal is subtracted.
  • Annual interest expenseThe last column provides a running total of the interest expense on the mortgage for the entire 12-month period. Its the amount that would be reported on the financial statements.
  • TotalsThe 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, youll 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. Dont 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 dont need to be the same).image text in transcribed

B. Answer the following:

  1. How can you describe the relationship between time and the amount paid towards principal and interest?
  2. Knowing what you know about ECCCs cash flow from Part 1, is it reasonable to believe that ECCC can take on this new debt?

Answers to part 1 are in the following link (Mind the years mentioned):

https://www.chegg.com/homework-help/questions-and-answers/el-cap-climbing-company-eccc-small-startup-manufactures-sells-high-quality-climbing-gear-f-q43762218?trackid=d57ca4cf063f&strackid=b5ca56655753

Mortgage Principal and Interest 3,000.00 2.500.00 2,000.00 Dollars 1,500.00 Principal Payment Interest Expense 1,000.00 500.00 76 16 922 241 256 271 286 DOE 316 331 346 TSE Payment Month Mortgage Principal and Interest 3,000.00 2.500.00 2,000.00 Dollars 1,500.00 Principal Payment Interest Expense 1,000.00 500.00 76 16 922 241 256 271 286 DOE 316 331 346 TSE Payment Month

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Financial Statements A Step By Step Guide To Understanding And Creating Financial Reports

Authors: Thomas Ittelson

1st Edition

1632652072, 978-1632652072

More Books

Students also viewed these Finance questions

Question

Explain why households do not hold diversified portfolios.

Answered: 1 week ago