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 is recomputed to fully pay the loan balance in the initially agreed period. The borrower enhances his monthly repayments by Shs 10,000/= per month from the end of month 74. The borrower is advanced an additional Shs 500,000/= at the end of month 80 and the monthly repayment is recomputed to fully pay the loan balance in 9 months less than had initially been agreed.

Expert Solution

Concept :

We compute Equated Monthly Installment figures by excel formula "PMT" function

For interest computations, we can use normal interest formula rather than excel's INT formula.

Solution :

Please Note (For Student): I have no option here to upload my excel file where all detailed calculations are done for each computation & the full schedule of 111 months. If you require the excel file, please ask for it. I would be glad to provide the same.

jeff jeffy answered 1 year ago

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

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

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

Question