Question

In: Accounting

Prepare (using a spreadsheet package) an entire duration amortization schedule as follows: Initial amount: Shs 6,000,000/=;...

Prepare (using a spreadsheet package) an entire duration amortization schedule as follows:

Initial amount: Shs 6,000,000/=; rate of interest: 12% per annum; period: 10 years payable monthly in arrears.

An additional amount of Shs 500,000/= is repaid with the 20^th installment, and the monthly installment recomputed to fully pay the loan balance in the initially agreed period.

The rate of interest is adjusted to 13% per annum with effect from the end of the fourth year. The monthly repayment is recomputed to fully pay the loan balance in twelve months less than the initially agreed period.

The borrower enhances his monthly repayments by Shs 15,000/= per month from the end of month 64.

The borrower is advanced an additional Shs 700,000/= at the end of month 70 and the monthly repayment is recomputed to fully pay the loan balance in the period that had initially been agreed upon.

Expert Solution

Month Opening O/s Prinicpal Interest Repayment closing o/s

0 - 60,00,000 - - 60,00,000

1 60,00,000 - 60,000 86,083 59,73,917

2 59,73,917 - 59,739 86,083 59,47,574

3 59,47,574 - 59,476 86,083 59,20,967

4 59,20,967 - 59,210 86,083 58,94,094

5 58,94,094 - 58,941 86,083 58,66,953

6 58,66,953 - 58,670 86,083 58,39,540

7 58,39,540 - 58,395 86,083 58,11,852

8 58,11,852 - 58,119 86,083 57,83,888

9 57,83,888 - 57,839 86,083 57,55,645

10 57,55,645 - 57,556 86,083 57,27,119

11 57,27,119 - 57,271 86,083 56,98,307

12 56,98,307 - 56,983 86,083 56,69,208

13 56,69,208 - 56,692 86,083 56,39,817

14 56,39,817 - 56,398 86,083 56,10,133

15 56,10,133 - 56,101 86,083 55,80,152

16 55,80,152 - 55,802 86,083 55,49,871

17 55,49,871 - 55,499 86,083 55,19,287

18 55,19,287 - 55,193 86,083 54,88,397

19 54,88,397 - 54,884 86,083 54,57,198

20 54,57,198 - 54,572 5,86,083 49,25,687

21 49,25,687 - 49,257 78,150 48,96,795

22 48,96,795 - 48,968 78,150 48,67,613

23 48,67,613 - 48,676 78,150 48,38,139

24 48,38,139 - 48,381 78,150 48,08,371

25 48,08,371 - 48,084 78,150 47,78,305

26 47,78,305 - 47,783 78,150 47,47,938

27 47,47,938 - 47,479 78,150 47,17,268

28 47,17,268 - 47,173 78,150 46,86,291

29 46,86,291 - 46,863 78,150 46,55,004

30 46,55,004 - 46,550 78,150 46,23,405

31 46,23,405 - 46,234 78,150 45,91,489

32 45,91,489 - 45,915 78,150 45,59,254

33 45,59,254 - 45,593 78,150 45,26,697

34 45,26,697 - 45,267 78,150 44,93,814

35 44,93,814 - 44,938 78,150 44,60,603

36 44,60,603 - 44,606 78,150 44,27,059

37 44,27,059 - 44,271 78,150 43,93,180

38 43,93,180 - 43,932 78,150 43,58,962

39 43,58,962 - 43,590 78,150 43,24,402

40 43,24,402 - 43,244 78,150 42,89,496

41 42,89,496 - 42,895 78,150 42,54,242

42 42,54,242 - 42,542 78,150 42,18,634

43 42,18,634 - 42,186 78,150 41,82,671

44 41,82,671 - 41,827 78,150 41,46,348

45 41,46,348 - 41,463 78,150 41,09,662

46 41,09,662 - 41,097 78,150 40,72,609

47 40,72,609 - 40,726 78,150 40,35,185

48 40,35,185 - 40,352 78,150 39,97,387

49 39,97,387 - 43,305 90,953 39,49,740

50 39,49,740 - 42,789 90,953 39,01,576

51 39,01,576 - 42,267 90,953 38,52,890

52 38,52,890 - 41,740 90,953 38,03,677

53 38,03,677 - 41,206 90,953 37,53,930

54 37,53,930 - 40,668 90,953 37,03,645

55 37,03,645 - 40,123 90,953 36,52,815

56 36,52,815 - 39,572 90,953 36,01,434

57 36,01,434 - 39,016 90,953 35,49,497

58 35,49,497 - 38,453 90,953 34,96,997

59 34,96,997 - 37,884 90,953 34,43,928

60 34,43,928 - 37,309 90,953 33,90,285

61 33,90,285 - 36,728 90,953 33,36,060

62 33,36,060 - 36,141 90,953 32,81,248

63 32,81,248 - 35,547 90,953 32,25,842

64 32,25,842 - 34,947 90,953 31,69,836

65 31,69,836 - 34,340 1,05,953 30,98,223

66 30,98,223 - 33,564 1,05,953 30,25,834

67 30,25,834 - 32,780 1,05,953 29,52,661

68 29,52,661 - 31,987 1,05,953 28,78,695

69 28,78,695 - 31,186 1,05,953 28,03,928

70 28,03,928 7,00,000 30,376 1,05,953 34,28,351

71 34,28,351 - 37,140 89,167 33,76,324

72 33,76,324 - 36,577 89,167 33,23,734

73 33,23,734 - 36,007 89,167 32,70,573

74 32,70,573 - 35,431 89,167 32,16,837

75 32,16,837 - 34,849 89,167 31,62,519

76 31,62,519 - 34,261 89,167 31,07,612

77 31,07,612 - 33,666 89,167 30,52,110

78 30,52,110 - 33,065 89,167 29,96,007

79 29,96,007 - 32,457 89,167 29,39,297

80 29,39,297 - 31,842 89,167 28,81,972

81 28,81,972 - 31,221 89,167 28,24,026

82 28,24,026 - 30,594 89,167 27,65,452

83 27,65,452 - 29,959 89,167 27,06,243

84 27,06,243 - 29,318 89,167 26,46,394

85 26,46,394 - 28,669 89,167 25,85,895

86 25,85,895 - 28,014 89,167 25,24,742

87 25,24,742 - 27,351 89,167 24,62,926

88 24,62,926 - 26,682 89,167 24,00,440

89 24,00,440 - 26,005 89,167 23,37,277

90 23,37,277 - 25,321 89,167 22,73,430

91 22,73,430 - 24,629 89,167 22,08,892

92 22,08,892 - 23,930 89,167 21,43,654

93 21,43,654 - 23,223 89,167 20,77,709

94 20,77,709 - 22,509 89,167 20,11,051

95 20,11,051 - 21,786 89,167 19,43,670

96 19,43,670 - 21,056 89,167 18,75,558

97 18,75,558 - 20,319 89,167 18,06,710

98 18,06,710 - 19,573 89,167 17,37,115

99 17,37,115 - 18,819 89,167 16,66,766

100 16,66,766 - 18,057 89,167 15,95,655

101 15,95,655 - 17,286 89,167 15,23,774

102 15,23,774 - 16,508 89,167 14,51,114

103 14,51,114 - 15,720 89,167 13,77,667

104 13,77,667 - 14,925 89,167 13,03,424

105 13,03,424 - 14,120 89,167 12,28,377

106 12,28,377 - 13,307 89,167 11,52,517

107 11,52,517 - 12,486 89,167 10,75,836

108 10,75,836 - 11,655 89,167 9,98,323

109 9,98,323 - 10,815 89,167 9,19,971

110 9,19,971 - 9,966 89,167 8,40,770

111 8,40,770 - 9,108 89,167 7,60,711

112 7,60,711 - 8,241 89,167 6,79,784

113 6,79,784 - 7,364 89,167 5,97,981

114 5,97,981 - 6,478 89,167 5,15,292

115 5,15,292 - 5,582 89,167 4,31,707

116 4,31,707 - 4,677 89,167 3,47,216

117 3,47,216 - 3,762 89,167 2,61,810

118 2,61,810 - 2,836 89,167 1,75,479

119 1,75,479 - 1,901 89,167 88,212

120 88,212 - 956 89,167 1

ekkarill92 answered 1 year ago

1. Prepare (using a spreadsheet package) an entire duration amortization schedule as follows: Initial amount: Shs...

1. Prepare (using a spreadsheet package) an entire duration amortization schedule as follows: Initial amount: Shs 4,000,000/=; rate of interest: 14% per annum; period: 10 years payable monthly in arrears. An additional amount of Shs 400,000/= is repaid with the 30th installment, and the monthly installment recomputed to fully pay the loan balance in the initially agreed period. The rate of interest is adjusted to 16% per annum with effect from the end of the fourth year. The monthly repayment...

Prepare an amortization schedule for the 1st and 2nd years (effective interest method) using the following...

Prepare an amortization schedule for the 1st and 2nd years (effective interest method) using the following data: 1. On January 1, 2010 XYZ Co. issued $3,000,000, 6%, 10 year bonds, interest payable on June 30th and December 31st to yield 5%. Use the following format and round to the nearest dollar (may have small rounding error). The bonds were issued for $3,233,834. Date Cash paid Interest expense Amortization Bond Carry Value 2. Show how this bond would be reported on...

Prepare an amortization schedule for a three-year loan of $24,000.

Prepare an amortization schedule for a three-year loan of $24,000. The interest rate is 16 percent per year, and the loan calls for equal principal payments.

Amortization with Equal Payments. Prepare an amortization schedule for a three-year loan of $57,000. The interest...

Amortization with Equal Payments. Prepare an amortization schedule for a three-year loan of $57,000. The interest rate is 8 percent per year, and the loan calls for equal annual payments. How much interest is paid in the third year? How much total interest is paid over the life of the loan?

For this quiz, create an amortization table spreadsheet for a loan with initial balance of $50,000...

For this quiz, create an amortization table spreadsheet for a loan with initial balance of $50,000 at 6.43% with a monthly payment of $1200. Answer the following questions based on that spreadsheet. What will the loan balance be after the first payment is made? After which monthly payment does the loan become fully paid off? Using the $1200 regular monthly payment in that last month will overpay the loan. How much should that last monthly payment be reduced to so...

What is the amortization schedule for a mortgage of $1,600,000 in with 1). an initial ten...

What is the amortization schedule for a mortgage of $1,600,000 in with 1). an initial ten year fixed rate of 6.5%; twenty year amortization (monthly); ballooning at the end of the tenth year; and 2) Second ten year rate "capped" at 7.5%; monthly amortization over the remaining ten years on the balance rolled-over from #1.

Prepare an amortization spreadsheet in Excel. The sheet should be labeled, and I should be able to...

Prepare an amortization spreadsheet in Excel. The sheet should be labeled, and I should be able to change purchase price, interest rate, or the other relevant factors and the spreadsheet should automatically update. As we discussed in class, spend some time labeling the spreadsheet and using proper cell references. This is the first but not the last spreadsheet of this type, it is highly likely that elements of this spreadsheet will be helpful in subsequent assignments, so time spent here may mean...

a. Prepare the amortization schedule for a thirty-year loan of $100,000. The APR is 3% and...

a. Prepare the amortization schedule for a thirty-year loan of $100,000. The APR is 3% and the loan calls for equal monthly payments. The following table shows how you should prepare the amortization schedule for the loan. Month Beginning Balance Total Payment Interest Payment Principal Payment Ending Balance 1 $100,000.00 b. Use the annuity formula to find how much principal you still owe to the bank at the end of the third year. Check that this value is the same...

Problem 6-55 Amortization with Equal Payments [LO3] Prepare an amortization schedule for a five-year loan of...

Problem 6-55 Amortization with Equal Payments [LO3] Prepare an amortization schedule for a five-year loan of $61,000. The interest rate is 8 percent per year, and the loan calls for equal annual payments. (Do not round intermediate calculations and round your answers to 2 decimal places, e.g., 32.16. Leave no cells blank - be certain to enter "0" wherever required.) Year Beginning Balance Total Payment Interest Payment Principal Payment Ending Balance 1 $ $ $ $ $ 2 ...

Question One [10 marks] Prepare a spreadsheet to show a repayment schedule for a loan of...

Question One [10 marks] Prepare a spreadsheet to show a repayment schedule for a loan of $100,000, [6 marks] repayable quarterly over 10 years calculated at an effective interest rate of 9.68% (a) Calculate the amount of the quarterly repayment. [1 mark] (b) Calculate the loan outstanding right after the 10th payment. [1 mark] (c) Calculate the interest component of the 11th payment. [1 mark] (d ) Calculate the principal(capital) repaid in the 11th payment. [1 mark] interest rate 20.00%...