Sherry and Sam want to purchase a condo at the coast. They will spend $650,000 on...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Sherry and Sam want to purchase a condo at the coast. They will spend $650,000 on the condo and are taking out a $650,000 loan for the condo for twenty years at 7.0% interest. What is the annual payment on the mortgage? Construct the amortization schedule of the loan for the twenty years in a spreadsheet to show the annual interest costs, the principal reduction, and the ending balance each year. Then change the amortization schedule to reflect that after ten years Sherry and Sam will increase their annual payment to $80,000 per year. When will they fully repay the mortgage with this increased payment if they apply all the extra dollars above the original payment to the principal? Given Data Annual Interest rate 7.0% Years for Loan 20 New Annual Payment $80,000.00 Solution Beginning Balance S650,000.00 Ending Balance Annual Interest Principal Reduction Extra Year Payment Expense Principal 2 3 6. 7 9. 10 11 12 13 14 15 16 17 18 19 20 if Sherry and Sam increase the annual payment to $80,000 per year, they can pay off the loan lyears carly. Requirements Points 1. Start Excel. In cell E15, by using absolute cell references, calculate the annual payment of the loan. Use the Excel PMT function. Assume that all payments are made at the end of the year. Use the loan amount in cell D15 and the relevant cells from the 2. Given Data section. 1 In cell F15, by using ell references, calculate the annual interest expenses on the loan for the first year. Use the loan amount in cell D15 and an absolute reference to the relevant cell from the Given Data section. 3. 1 Note: The output of the expression you typed in this cell is expected as a positive number. In cell G15, by using cell references, calculate the annual principal repayment on the loan for the first year. 4. 1 Note: The output of the expression you typed in this cell is expected as a positive number. In cell H15, enter the amount of extra annual payment toward the principal repayment for year 1. Do not use equal sign 5. when entering a numeric value. In cell I15, by using cell references, calculate the loan account balance at the end of the first year. Remember to include the 1 6. extra annual payments toward the principal repayments in H15 to calculate the ending account balance. 1 7. In cell D16, by using a cell reference, enter the loan account balance at the beginning of the second year. 8. In cell range D17:D34, copy the expression from cell D16 down the column to row 34. 1 1 9. In cell range E16:134, copy the contents from cell range E15:I15 down the columns to row 34. 1 In cell H25, by using cell references, calculate the extra annual payment toward the principal repayment for year 11. Use cell E25 and an absolute reference to the relevant cell from the Given Data section. 10. 1 Note: The output of the expression you typed in this cell is expected as a negative number. 11. In cell range H26:H34, copy the expression from cell H25 down the column to row 34. In cell H36, enter the number of years early Sherry and Sam can pay off the loan. Do not use the equal sign when entering 1 12. a numeric value. 1 13. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Sherry and Sam want to purchase a condo at the coast. They will spend $650,000 on the condo and are taking out a $650,000 loan for the condo for twenty years at 7.0% interest. What is the annual payment on the mortgage? Construct the amortization schedule of the loan for the twenty years in a spreadsheet to show the annual interest costs, the principal reduction, and the ending balance each year. Then change the amortization schedule to reflect that after ten years Sherry and Sam will increase their annual payment to $80,000 per year. When will they fully repay the mortgage with this increased payment if they apply all the extra dollars above the original payment to the principal? Given Data Annual Interest rate 7.0% Years for Loan 20 New Annual Payment $80,000.00 Solution Beginning Balance S650,000.00 Ending Balance Annual Interest Principal Reduction Extra Year Payment Expense Principal 2 3 6. 7 9. 10 11 12 13 14 15 16 17 18 19 20 if Sherry and Sam increase the annual payment to $80,000 per year, they can pay off the loan lyears carly. Requirements Points 1. Start Excel. In cell E15, by using absolute cell references, calculate the annual payment of the loan. Use the Excel PMT function. Assume that all payments are made at the end of the year. Use the loan amount in cell D15 and the relevant cells from the 2. Given Data section. 1 In cell F15, by using ell references, calculate the annual interest expenses on the loan for the first year. Use the loan amount in cell D15 and an absolute reference to the relevant cell from the Given Data section. 3. 1 Note: The output of the expression you typed in this cell is expected as a positive number. In cell G15, by using cell references, calculate the annual principal repayment on the loan for the first year. 4. 1 Note: The output of the expression you typed in this cell is expected as a positive number. In cell H15, enter the amount of extra annual payment toward the principal repayment for year 1. Do not use equal sign 5. when entering a numeric value. In cell I15, by using cell references, calculate the loan account balance at the end of the first year. Remember to include the 1 6. extra annual payments toward the principal repayments in H15 to calculate the ending account balance. 1 7. In cell D16, by using a cell reference, enter the loan account balance at the beginning of the second year. 8. In cell range D17:D34, copy the expression from cell D16 down the column to row 34. 1 1 9. In cell range E16:134, copy the contents from cell range E15:I15 down the columns to row 34. 1 In cell H25, by using cell references, calculate the extra annual payment toward the principal repayment for year 11. Use cell E25 and an absolute reference to the relevant cell from the Given Data section. 10. 1 Note: The output of the expression you typed in this cell is expected as a negative number. 11. In cell range H26:H34, copy the expression from cell H25 down the column to row 34. In cell H36, enter the number of years early Sherry and Sam can pay off the loan. Do not use the equal sign when entering 1 12. a numeric value. 1 13. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
Expert Answer:
Related Book For
Vector Mechanics for Engineers Statics and Dynamics
ISBN: 978-0073212227
8th Edition
Authors: Ferdinand Beer, E. Russell Johnston, Jr., Elliot Eisenberg, William Clausen, David Mazurek, Phillip Cornwell
Posted Date:
Students also viewed these finance questions
-
Question V Consider a forward contract for shares of company X. The current stock price of the share is $100. The share will pay quarterly dividends of $1/share, beginning in 3 months. The interest...
-
Payments of $51.00 are made at the end of each month for six years at 8% compounded annually. Classify the annuity by (a) term; (b) date of payment; and (c) conversion period.
-
Payments of $3500 will be made at the end of every year for 17 years. Using a nominal rate of 5.25% compounded monthly, calculate the annuitys: a. Present value. b. Future value.
-
The following information was drawn from the 2016 accounting records of Ozark Merchandisers: 1. Inventory that had cost $21,200 was sold for $39,900 under terms 2/20, net/30. 2. Customers returned...
-
Use the information in Exhibit 4.4 (see page 92) to determine the amount of federal tax payable for each of the following individuals. Non-refundable tax credits are not applicable. a. Brenda, who...
-
An engineer is designing a subsonic wind tunnel. The test section is to have a cross-sectional area of 4 m 2 and an airspeed of 60 m/s. Th e air density is 1.2 kg/m 3 . The area of the tunnel exit is...
-
An AICPA Fraud Task Force suggests that if a forensic accountant is brought into an audit to conduct a separate investigation (and not an adjunct to an audit), what standards apply?
-
The manager of Able Car Inspection reviewed his monthly operating costs for the past year. His costs ranged from $4,000 for 1,000 inspections to $3,600 for 600 inspections. Requirements 1. Calculate...
-
75. Bill Somers, an equity analyst, is evaluating the stock of Westmore Associates. Somers has collected the following data with respect to the Westmore stock and equity market: Exhibit: Data...
-
The S&H Construction Company expects to have total sales next year totaling $15,000,000. In addition, the firm pays taxes at 35 percent and will owe $300,000 in interest expense. Based on last years...
-
South, Inc., earns book net income before tax of $400,000 in year 1. It acquires a depreciable asset in year 1, and its first-year tax depreciation exceeds book depreciation by $50,000. In year 2,...
-
Write a Java program that checks if a string is a palindrome using a Deque. Your program will output "Yes" if it is a palindrome and "No" if it is not. You must solve this using a Deque class which...
-
State Motor Vehicles and Transportation Agency (SMVTA) conducts a study and concludes that most accidents involving pass3enger buses are caused by bus drivers who are under the influence of alcohol...
-
ces Coastal's balance sheet for a recent year revealed the following information: Current assets Noncurrent assets Noncurrent liabilities Stockholders' equity Required: $ 890,000 470,000 400,000...
-
Rearrange this equation to isolate c. a = b -(-) C =
-
Given the following data, what is the value of gross profit as determined by the average cost method? Sales revenue 300 units at $15 per unit Purchases 240 units at $10 per unit Beginning inventory...
-
Case 1; for the following Independent situation, assume that you are the audit partner on the engagement: 1 During your audit of SMADIL BATRRIES, you conclude that there is a possibility that...
-
Write a function that reads a Float24_t value: Float24_t float24_read(void) A legitimate float24 value string is of the form: "mantissabexponent" where the mantissa (m) and the exponent (e) may have...
-
A 4-lb collar is attached to a spring of constant 6 lb/in. and can slide without friction on a horizontal rod. The collar is at rest when it is struck with a mallet and given an initial velocity of...
-
The spacer shown was cut from a thin, uniform plate. Denoting the mass of the component by m, determine its mass moment of inertia with respect to (a) The axis AA′, (b) The centroidal axis...
-
For the beam shown, draw the shear and bending-moment diagrams, and determine the location and magnitude of the maximum absolute value of the bending moment knowing that (a) M = 0, (b) M =12kN m. 20...
-
To estimate the optimal mix of debt and equity for your firm and to evaluate the effect on firm value of moving to that mix. Key Questions Based on the cost of capital approach, what is the optimal...
-
To determine whether your firm should move to its optimal mix (and if so, how) and to analyze the right type of debt for your firm. Key Questions If your firms actual debt ratio is different from...
-
HG Lang Designs operates an exclusive bridal boutique in Manhattan. All gowns are designed by Lang but are manufactured by various small firms in France and Italy. The following financial information...
Study smarter with the SolutionInn App