Question

In: Finance

In order to expand, El Cap Climbing Company (ECCC) is considering taking out a mortgage for...

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

Solutions

Expert Solution

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.


Related Solutions

You are considering taking out an adjustable rate mortgage (ARM) to finance your new home and...
You are considering taking out an adjustable rate mortgage (ARM) to finance your new home and have been given the following information by the bank: $250,000 house price 80% Loan to Value 200 bps margin 2.5% LIBOR – Index for Year 1 Teaser Rate in Year 1 – 2% Periodic Cap – 1.5% Life Cap – 5% You also went on Bloomberg and found the following LIBOR Index projections for the next few years: 3% - Index for Year 2...
Suppose that you are considering taking out an adjustable-rate mortgage with the following terms: Amount borrowed:                       &n
Suppose that you are considering taking out an adjustable-rate mortgage with the following terms: Amount borrowed:                         $475,000 Index rate:                                          Prime Rate (Current value is 1.55%) Margin:                                                235 basis points. Periodic cap:                                      1.5 percentage points Lifetime cap:                                      5 percentage points Amortization:                                    25 years What will the initial monthly payment be for this loan? If the loan’s interest rate adjusts every year and the prime rate increases to 2.75% by the end of the first year, what will your payment be in...
A company is considering purchasing a new second machine in order to expand their business. The...
A company is considering purchasing a new second machine in order to expand their business. The information for the new machine is: Cost= $100,000 Increase in contribution margin= $25,000 Life of the machine= 5 years Required rate of return = 10% Calculate the following: a. Net present value (NPV) (Use factors to three decimal places, X.XXX, and use a minus sign or parentheses for a negative net present value. Enter the net present value of the investment rounded to the...
In order to expand its bottled water business, a local company is considering the acquisition of...
In order to expand its bottled water business, a local company is considering the acquisition of additional equipment. It plans to buy the equipment. The total cost of the equipment (as one initial payment at the beginning of the project) is $200,500. During the time span of its use, the equipment is expected to produce net cash inflows of $67,000 each year. The equipment is expected to be used for 4 years, then re-sold in the used-equipment market for $25,000....
Candy Company is considering purchasing a second chocolate dipping machine in order to expand their business....
Candy Company is considering purchasing a second chocolate dipping machine in order to expand their business. The information StupendousStupendous has accumulated regarding the new machine​ is: Cost of the machine $110,000 Increased contribution margin $17,000 Life of the machine 10 years Required rate of return 6% StupendousStupendous estimates they will be able to produce more candy using the second machine and thus increase their annual contribution margin. They also estimate there will be a small disposal value of the machine...
Jayce is borrowing $148,000 to buy a house. He is taking out a 30 year mortgage...
Jayce is borrowing $148,000 to buy a house. He is taking out a 30 year mortgage with a 5.35% fixed interest rate. Property taxes are $4,975 and homeowners insurance is $1125 per year. PMI is $75 per month. Find his total monthly (PITI) payment. Show your work.
Using Java: When buying a house or condo (using a mortgage), or when taking out a...
Using Java: When buying a house or condo (using a mortgage), or when taking out a loan, you’ll usually wind up with some form of a fixed monthly payment. Mortgages and loans work this way: You take out a loan from the bank or lender for a specified amount of money at a specified annual interest rate with a specified monthly payment amount At the beginning of every month, interest is added to the amount you owe the bank or...
you are taking out a $100,000 mortgage loan to be repaid over 25 years in 300...
you are taking out a $100,000 mortgage loan to be repaid over 25 years in 300 monthly payments. a. if the interest rate is 16% per year, what is the amount of the monthly payment? b. if you can only afford to pay $1000 per month, how large a loan can you take? c. if you can afford to pay $1500 per month and need to borrow $100,000, how many months would it take to pay the mortgage? d. if...
Bob Heavy Tools is considering the acquisition of a light truck, in order to expand the...
Bob Heavy Tools is considering the acquisition of a light truck, in order to expand the business to other geographical areas. If he can expand to other areas, additional business will represents an increase in Earnings before depreciation and taxes of $60,000 annually during the first 3 years and $25,000 annually in the last 3 years. Bob’s cost of capital is 12% and is in the 32% marginal tax rate. If the light truck cost is $160,000, What is the...
The Rialto family bought a house 10 years ago, taking out a mortgage loan for $249,500...
The Rialto family bought a house 10 years ago, taking out a mortgage loan for $249,500 at 7% interest for 30 years. They've made all of their monthly payments on time for the past 10 years. With 20 years left on the loan, how much does the Rialto family still owe on the mortgage? (Hint: first you will need to figure out the monthly payment on the 30 year loan.)
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT