Question

Your company is planning to borrow $0.75 million on a 5-year, 14%, annual payment, fully amortized term loan.
The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the question below.
What fraction of the payment made at the end of the second year will represent repayment of principal? Do not round intermediate calculations. Round your answer to two decimal places.

__________%

a A A A $ % Formatting as Table Undo Clipboard Font Alignment Number Tables F1 fx 1 Loan Amortization $750,000 Loan amount Term in years Annual coupon rate 14.00% 7 8 Calculation of Loan Payment Loan payment = $218,462.66 Formula #NA 10 Loan Amortization Schedule Year Beginning Balance Payment Interest Principal Ending Balance - WN Sheet1 e a Z | x] piwie 92

the answer is not .5 92.
as someone previously answered.

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

A В D E F 2 $750,000 Loan amount 4 Term in years 5 5 Annual Coupon rate 14% 6 Formula 7 8 Loan Payment $218,462.66 -РMT(85,B4,-Вз,0) 9 10 Beginning Balance $750,000.00 $636,537.34 $507,189.91 $359,733.84 $191,633.91 Principal $113,462.66 $129,347.43 Ending Balance $636,537.34 $507,189.91 $359,733.84 $191,633.91 11 Year Payment Interest $105,000 $218,462.66 12 1 $218,462.66 $218,462.66 $218,462.66 $218,462.66 $89,115 $71,007 $50,363 $26,829 13 2 $147,456.07 $168,099.92 $191,633.91 14 3 15 4 $0.00 16 5 17 18 19 Formula 20 Year Beginning Balance Principal Ending Balance Payment Interest =SB$8 B12*$B$5 21 1 ЕВЗ =C12- D12 =B12-E12 -B13* $B$5 =F12 =SB$8 22 C13-D13 2 B13-E13 =F13 =SB$8 =B14* $B$5 B15*$B$5 23 C14-D14 -B14-E14 -C15-D15 =$B$8 24 4 =F14 -B15-E15 =F15 25 - S B$8 = B16 * $B$5 5 C16-D16 B16-E16 26 59.21%E13/c13 27 Percentage of 2nd payment represent principal repayment 28 29

Cell reference -

В C D E F 1 2 Loan amount 750000 4 Term in years Annual Coupon rate 0.14 5 5 6 7 Formula —РM (85, B4,-Вз,0) 8 Loan Payment =FORMULATEXT( B8) 9 10 11 Interest Principal Ending Balance Year Beginning Balance Payment =SB$8 =B12*$B$5 =C12-D12 12 1 ЕВЗ -B12-E12 B13*SB$5 =B14*$B$5 B15*$B$5 -SB$8 13 2 =F12 -C13 - D13 -B13-E13 -$B$8 =$B$8 =$B$8 =F13 14 3 -C14-D14 =B14-E14 15 4 -F14 -C15-D15 -B15-E15 B16* $B$5 16 5 -C16-D16 =F15 -B16-E16 17 18 19 Formula Principal =FORMULATEXT(E12) -FORMULATEXT(E13) =FORMULATEXT (E14) =FORMULATEXT(E15) 20 Year Ending Balance Рayment =FORMULATEXT(B12) FORMULATEXT(C12)FORMULATEXT(D12) =FORMULATEXT(B13) FORMULATEXT(C13) -FORMULATEXT (D13) =FORMULATEXT(B14) FORMULATEXT(C14)FORMULATEXT (D14) =FORMULATEXT(B15) FORMULATEXT(C15) FORMULATEXT(D15) =FORMULATEXT(B16) FORMULATEXT(C16)=FORMULATEXT(D16) Beginning Balance Interest =FORMULATEXT(F12) =FORMULATEXT(F13) =FORMULATEXT(F14) =FORMULATEXT(F15) =FORMULATEXT(F16) 21 1 22 2 23 3 24 4 25 5 =FORMULATEXT(E16) 26 -Е13/C13 27 Percentage of 2nd payment represent principal repayment =FORMULATEXT(D27) 28 29

Please note: As per data provided in question, % of second payment represent principal repayment is 59.21% correct. If it require number format enter 0.59.

Hope this will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.