Homework Help is Here – Start Your Trial Now!
Business
Accounting
To pay for remodeling, the company will take out a $500,000 five-year loan at 9.5% interest, compounded quarterly. The terms of the loan have been entered in the Loan Analysis worksheet. In cell B8, calculate the quarterly payment on the loan based on the loan conditions already entered. Complete the amortization schedule in cells B11 through E30. Column B contains the interest payment for each quarter, and column C contains the principal payment. Column D contains the remaining principal at the start of each month. The initial principal remaining is $500,000. The subsequent remaining principal values are reduced by the principal payment made in the previous quarter. Calculate the ending balance in cell D31. Use the IPMT function in cell B11 to calculate the interest amount paid per period. Copy this formula to cell B30. Use the PPMT function in cell C11 to calculate the principal amount paid per period. Copy this formula to cell C30. Write a formula in cell D11 to indicate the beginning balance of the loan. Write a formula in cell D12 to calculate the remaining balance after each payment (only adjust for the principal). Copy this formula down the column to cell D31.

To pay for remodeling, the company will take out a $500,000 five-year loan at 9.5% interest, compounded quarterly. The terms of the loan have been entered in the Loan Analysis worksheet. In cell B8, calculate the quarterly payment on the loan based on the loan conditions already entered. Complete the amortization schedule in cells B11 through E30. Column B contains the interest payment for each quarter, and column C contains the principal payment. Column D contains the remaining principal at the start of each month. The initial principal remaining is $500,000. The subsequent remaining principal values are reduced by the principal payment made in the previous quarter. Calculate the ending balance in cell D31. Use the IPMT function in cell B11 to calculate the interest amount paid per period. Copy this formula to cell B30. Use the PPMT function in cell C11 to calculate the principal amount paid per period. Copy this formula to cell C30. Write a formula in cell D11 to indicate the beginning balance of the loan. Write a formula in cell D12 to calculate the remaining balance after each payment (only adjust for the principal). Copy this formula down the column to cell D31.

Pfin (with Mindtap, 1 Term Printed Access Card) (mindtap Course List)
Pfin (with Mindtap, 1 Term Printed Access Card) (mindtap Course List)
7th Edition
ISBN:9780357033609
Author:Randall Billingsley, Lawrence J. Gitman, Michael D. Joehnk
Publisher:Randall Billingsley, Lawrence J. Gitman, Michael D. Joehnk
Chapter7: Using Consumer Loans
Section: Chapter Questions
Problem 5FPE
See similar textbooks
icon
Related questions
Question

To pay for remodeling, the company will take out a $500,000 five-year loan at 9.5% interest, compounded quarterly. The terms of the loan have been entered in the Loan Analysis worksheet.

  1. In cell B8, calculate the quarterly payment on the loan based on the loan conditions already entered.
  2. Complete the amortization schedule in cells B11 through E30. Column B contains the interest payment for each quarter, and column C contains the principal payment. Column D contains the remaining principal at the start of each month. The initial principal remaining is $500,000. The subsequent remaining principal values are reduced by the principal payment made in the previous quarter. Calculate the ending balance in cell D31.
    1. Use the IPMT function in cell B11 to calculate the interest amount paid per period. Copy this formula to cell B30.
    2. Use the PPMT function in cell C11 to calculate the principal amount paid per period. Copy this formula to cell C30.
    3. Write a formula in cell D11 to indicate the beginning balance of the loan.
    4. Write a formula in cell D12 to calculate the remaining balance after each payment (only adjust for the principal). Copy this formula down the column to cell D31.
Amortization Table Original Loan $500,000 Annual Interest Rate 9.5% Loan duration (yrs) 5. Number of periods 4 per year Ending Value of Loan Quarterly Payment Principal Payment Remaining Principal Period Interest Payment 1. 3 4 5. 9. 8 11 12 13 and 14 15 17 18 20 Ending Balance
Transcribed Image Text:Amortization Table Original Loan $500,000 Annual Interest Rate 9.5% Loan duration (yrs) 5. Number of periods 4 per year Ending Value of Loan Quarterly Payment Principal Payment Remaining Principal Period Interest Payment 1. 3 4 5. 9. 8 11 12 13 and 14 15 17 18 20 Ending Balance
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps with 2 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Pfin (with Mindtap, 1 Term Printed Access Card) (…
Pfin (with Mindtap, 1 Term Printed Access Card) (…
Finance
ISBN:
9780357033609
Author:
Randall Billingsley, Lawrence J. Gitman, Michael D. Joehnk
Publisher:
Cengage Learning
Fundamentals of Financial Management (MindTap Cou…
Fundamentals of Financial Management (MindTap Cou…
Finance
ISBN:
9781337395250
Author:
Eugene F. Brigham, Joel F. Houston
Publisher:
Cengage Learning
SEE MORE TEXTBOOKS