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. AnnualPeriodicРayment(PMT)Duration inOPTIONPeriodsInterestPresentFutureAnnualyears(NPER)per yearRateValue (PV)Value (FV)Payments(RATE)24(1) What will my payments be?6.0% 5500,000406,0204%2424(2) How much will my savings beworth?2$(50,000)24(30,000)24(20,000)2$(35,000)5.3%20300,000%242.(3) How much do I need to start?5.8% 62,000,0002$12%$42$24(240,000)2$(140,000)(4) What is the interest rate?12101,000,0002$(5) How long will it take to pay off?46.5%1,000,000%24%24SUBMIT COMPLED2.on In cell C

An amortization schedule (also known as an amortisation table) is a table that outlines each…

Answered: B11 through E30. Column B contains the…

FINANCIAL ACCOUNTING

10th Edition

ISBN:9781259964947

Author:Libby

Publisher:Libby

Chapter1: Financial Statements And Business Decisions

Section: Chapter Questions

Problem 1Q

See similar textbooks

Related questions

Concept explainers

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.

Annual
Periodic
Рayment
(PMT)
Duration in
OPTION
Periods
Interest
Present
Future
Annual
years
(NPER)
per year
Rate
Value (PV)
Value (FV)
Payments
(RATE)
24
(1) What will my payments be?
6.0% 5
500,000
406,020
4
%24
24
(2) How much will my savings be
worth?
2$
(50,000)
24
(30,000)
24
(20,000)
2$
(35,000)
5.3%
20
300,000
%24
2.
(3) How much do I need to start?
5.8% 6
2,000,000
2$
12
%$4
2$
24
(240,000)
2$
(140,000)
(4) What is the interest rate?
12
10
1,000,000
2$
(5) How long will it take to pay off?
4
6.5%
1,000,000
%24
%24
SUBMIT COMPLED
2.
on In cell C

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.

Similar questions