Question
(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.
- Start by selecting the Interest Expense and Principal columns. Make sure to select the column headers and values. Dont select the Totals row.
- 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).
- 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).
B. Answer the following:
- How can you describe the relationship between time and the amount paid towards principal and interest?
- 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 MonthStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started