zation schedule The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Open spreadsheet a. Complete an amortization schedule for a $42,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 9% compounded annually. Round all answers to the Ending Balance Year 1 $ 2 $ 3 $ Year 1: Year 2: Year Beginning Balance Activity % Interest % % $ % $ $ Payment % Principal % b. What percentage of the payment represents interest and what percentage represents principal for each of the three years? Round all answers to two decimal places. % % $ $ $ c. Why do these percentages change over time? Interest $ $ $ Repayment. of Principal $ $ $
zation schedule The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Open spreadsheet a. Complete an amortization schedule for a $42,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 9% compounded annually. Round all answers to the Ending Balance Year 1 $ 2 $ 3 $ Year 1: Year 2: Year Beginning Balance Activity % Interest % % $ % $ $ Payment % Principal % b. What percentage of the payment represents interest and what percentage represents principal for each of the three years? Round all answers to two decimal places. % % $ $ $ c. Why do these percentages change over time? Interest $ $ $ Repayment. of Principal $ $ $
Essentials Of Investments
11th Edition
ISBN:9781260013924
Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Chapter1: Investments: Background And Issues
Section: Chapter Questions
Problem 1PS
Related questions
Question
Transcribed Image Text:12345
3 Loan amount to be repaid (PV)
Interest rate (r)
Length of loan (in years)
6
789
10 112 13 14 15
16
17
18
19
20
21
22
23
24
25
A
Amortization schedule
28
29
30
31
32
33
24
a. Setting up amortization table
Calculation of loan payment
Year
1
2
3
Formulas
Year
1
2
3
Year
1
23
3
b. Calculating % of Payment Representing Interest and Principal for Each Year
Year
1
2
B
WN
$42,000.00
9.00%
3
3
Beginning Balance
Payment %
Representing
Interest
Beginning Balance
#N/A
#N/A
#N/A
с
26
27 b. Calculating % of Payment Representing Interest and Principal for Each Year
Formula
#N/A
Payment %
Representing
Interest
#N/A
#N/A
#N/A
Payment
Payment %
Representing
Principal
Payment
#N/A
#N/A
#N/A
Payment %
Representing
Principal
#N/A
#N/A
#N/A
D
Interest
Check: Total =
100%
Interest
#N/A
#N/A
#N/A
Check: Total =
100%
#N/A
#N/A
#N/A
E
Repayment of
Principal
Repayment of
Principal
#N/A
#N/A
#N/A
F
Remaining Balance
Remaining Balance
#N/A
#N/A
#N/A
G
Transcribed Image Text:Excel Online Structured Activity: Amortization schedule
The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below.
X
Open spreadsheet
a. Complete an amortization schedule for a $42,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 9% compounded annually. Round all answers to the nearest cent.
Year
1
2
3
Year 1:
Year 2:
Year 3:
I
$
$
$
Beginning
Balance
|||
IV
V
% Interest
%
heck My Work
$
$
%
Payment
b. What percentage of the payment represents interest and what percentage represents principal for each of the three years? Round all answers to two decimal places.
% Principal
%
Reset Problem
%
$
$
$
%
Interest
$
$
$
Repayment
of Principal
$
c. Why do these percentages change over time?
I. These percentages change over time because even though the total payment is constant the amount of interest paid each year is declining as the remaining or outstanding balance declines.
II. These percentages change over time because even though the total payment is constant the amount of interest paid each year is increasing as the remaining or outstanding balance declines.
III. These percentages change over time because even though the total payment is constant the amount of interest paid each year is declining as the remaining or outstanding balance increases.
IV. These percentages change over time because even though the total payment is constant the amount of interest paid each year is increasing as the remaining or outstanding balance increases.
V. These percentages do not change over time; interest and principal are each a constant percentage of the total payment.
$
$
Ending
Balance
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 5 steps with 3 images
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, finance and related others by exploring similar questions and additional content below.Recommended textbooks for you
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
Foundations Of Finance
Finance
ISBN:
9780134897264
Author:
KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:
Pearson,
Fundamentals of Financial Management (MindTap Cou…
Finance
ISBN:
9781337395250
Author:
Eugene F. Brigham, Joel F. Houston
Publisher:
Cengage Learning
Corporate Finance (The Mcgraw-hill/Irwin Series i…
Finance
ISBN:
9780077861759
Author:
Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor
Publisher:
McGraw-Hill Education