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. Amortization TableOriginal Loan$500,000Annual Interest Rate9.5%Loan duration (yrs)5.Number of periods4per yearEnding Value of LoanQuarterly PaymentPrincipalPaymentRemainingPrincipalPeriodInterest Payment1.345.9.8111213and1415171820Ending Balance

Given data: Original Loan=$500,000 Annual Interest Rate=9.5% Loan duration (yrs)=5 Number of periods…

Answered: To pay for remodeling, the company will…

EBK CONTEMPORARY FINANCIAL MANAGEMENT

14th Edition

ISBN:9781337514835

Author:MOYER

Publisher:MOYER

Chapter16: Working Capital Policy And Short-term Financing

Section: Chapter Questions

Problem 18P

See similar textbooks

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.

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.
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.