Your client is XYZ company. After discussions it is identified they require a sp
ID: 2531658 • Letter: Y
Question
Your client is XYZ company. After discussions it is identified they require a spreadsheet summarising the repayment amounts and current balance for a long-term loan of $500,000 to be repaid over eight years at a rate of 10% per annum. They are interested in business information that will show the amount they need to repay each year.
Show the calculations required for the loan.
Prepare the spreadsheet for the loan. Include a summary section to comment on the table. Round the initial opening balance of the loan up to the nearest dollar and round up to present values in whole dollars.
Explanation / Answer
Answer
Amount to be repaid over 10 years
$ 500,000
Interest rate
10%
Annual Installment function(formula)
= pmt(10%,10,500000)
Annual repayment (using above MS Excel function) will be
$ 81,372.7
Beginning Principal Balance
Interest at 10%
Instalment amount
Reduction in Liability
Ending Principal balance
[A= ‘E’ of Last Year]
[B = A x 10%]
[C= Calculated above]
[D = C – B]
[E = A – D]
Year 1
$ 5,00,000.00
$ 50,000.00
$ 81,372.70
$ 31,372.70
$ 4,68,627.30
Year 2
$ 4,68,627.30
$ 46,862.73
$ 81,372.70
$ 34,509.97
$ 4,34,117.33
Year 3
$ 4,34,117.33
$ 43,411.73
$ 81,372.70
$ 37,960.97
$ 3,96,156.36
Year 4
$ 3,96,156.36
$ 39,615.64
$ 81,372.70
$ 41,757.06
$ 3,54,399.30
Year 5
$ 3,54,399.30
$ 35,439.93
$ 81,372.70
$ 45,932.77
$ 3,08,466.53
Year 6
$ 3,08,466.53
$ 30,846.65
$ 81,372.70
$ 50,526.05
$ 2,57,940.48
Year 7
$ 2,57,940.48
$ 25,794.05
$ 81,372.70
$ 55,578.65
$ 2,02,361.83
Year 8
$ 2,02,361.83
$ 20,236.18
$ 81,372.70
$ 61,136.52
$ 1,41,225.31
Year 9
$ 1,41,225.31
$ 14,122.53
$ 81,372.70
$ 67,250.17
$ 73,975.14
Year 10
$ 73,975.14
$ 7,397.51
$ 81,372.70
$ 73,975.19
$ (0.04)
Amount to be repaid over 10 years
$ 500,000
Interest rate
10%
Annual Installment function(formula)
= pmt(10%,10,500000)
Annual repayment (using above MS Excel function) will be
$ 81,372.7
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.