Question

In: Finance

A loan of 941,000 is to be repaid in 20 years by month-end repayments starting in...

A loan of 941,000 is to be repaid in 20 years by month-end repayments starting in one month. The interest rate is 8.4% p.a. compounded monthly. Calculate the principal paid in Year 6. (between the end of month 60 and the end of month 72). Correct your answer to the nearest cent without any units. (Do not use "$" or "," in your answer. e.g. 12345.67) (Hint: you can use Excel to find the answer.)

Solutions

Expert Solution

The PMT or the payment per month is calculated using the PMT function in excel

=PMT(Rate,nper,pv,fv)= PMT(8.4%/12,240,-941000,0)= 8107

The loan amortisation table is shown below in excel :

Opening Bal PMT Interest = Opening Balance *8.4%/12 Principal = PMT - Interest Closing Bal= Opening Balance - Principal
1 941000 8,107 6587 1520 939480
2 939480 8,107 6576 1530 937950
3 937950 8,107 6566 1541 936409
4 936409 8,107 6555 1552 934857
5 934857 8,107 6544 1563 933294
6 933294 8,107 6533 1574 931720
7 931720 8,107 6522 1585 930136
8 930136 8,107 6511 1596 928540
9 928540 8,107 6500 1607 926933
10 926933 8,107 6489 1618 925315
11 925315 8,107 6477 1630 923685
12 923685 8,107 6466 1641 922044
13 922044 8,107 6454 1652 920392
14 920392 8,107 6443 1664 918728
15 918728 8,107 6431 1676 917052
16 917052 8,107 6419 1687 915365
17 915365 8,107 6408 1699 913665
18 913665 8,107 6396 1711 911954
19 911954 8,107 6384 1723 910231
20 910231 8,107 6372 1735 908496
21 908496 8,107 6359 1747 906749
22 906749 8,107 6347 1760 904989
23 904989 8,107 6335 1772 903217
24 903217 8,107 6323 1784 901433
25 901433 8,107 6310 1797 899637
26 899637 8,107 6297 1809 897827
27 897827 8,107 6285 1822 896005
28 896005 8,107 6272 1835 894171
29 894171 8,107 6259 1848 892323
30 892323 8,107 6246 1860 890462
31 890462 8,107 6233 1874 888589
32 888589 8,107 6220 1887 886702
33 886702 8,107 6207 1900 884802
34 884802 8,107 6194 1913 882889
35 882889 8,107 6180 1927 880963
36 880963 8,107 6167 1940 879023
37 879023 8,107 6153 1954 877069
38 877069 8,107 6139 1967 875102
39 875102 8,107 6126 1981 873121
40 873121 8,107 6112 1995 871126
41 871126 8,107 6098 2009 869117
42 869117 8,107 6084 2023 867094
43 867094 8,107 6070 2037 865057
44 865057 8,107 6055 2051 863006
45 863006 8,107 6041 2066 860940
46 860940 8,107 6027 2080 858860
47 858860 8,107 6012 2095 856765
48 856765 8,107 5997 2109 854656
49 854656 8,107 5983 2124 852531
50 852531 8,107 5968 2139 850392
51 850392 8,107 5953 2154 848238
52 848238 8,107 5938 2169 846069
53 846069 8,107 5922 2184 843885
54 843885 8,107 5907 2200 841686
55 841686 8,107 5892 2215 839471
56 839471 8,107 5876 2230 837240
57 837240 8,107 5861 2246 834994
58 834994 8,107 5845 2262 832732
59 832732 8,107 5829 2278 830455
60 830455 8,107 5813 2294 828161
61 828161 8,107 5797 2309.63 825851
62 825851 8,107 5781 2325.80 823526
63 823526 8,107 5765 2342.08 821183
64 821183 8,107 5748 2358.47 818825
65 818825 8,107 5732 2374.98 816450
66 816450 8,107 5715 2391.61 814058
67 814058 8,107 5698 2408.35 811650
68 811650 8,107 5682 2425.21 809225
69 809225 8,107 5665 2442.18 806783
70 806783 8,107 5647 2459.28 804323
71 804323 8,107 5630 2476.49 801847
72 801847 8,107 5613 2493.83 799353

Adding the principal repaid from month 61 to 72 = 28807.91 (Answer)

jeff jeffy answered 1 year ago
Next > < Previous

Related Solutions

A loan of 673,000 is to be repaid in 30 years by month-end repayments starting in...
A loan of 673,000 is to be repaid in 30 years by month-end repayments starting in one month. The interest rate is 6.3% p.a. compounded monthly. Calculate the interest paid in Year 5. (between the end of month 48 and the end of month 60). Correct your answer to the nearest cent without any units. (Do not use "$" or "," in your answer. e.g. 12345.67) (Hint: you can use Excel to find the answer.) Answer:
A loan is to be repaid over 30 years, with month-end repayments of 3,000. If the...
A loan is to be repaid over 30 years, with month-end repayments of 3,000. If the interest rate is 6.5% p.a. compounded monthly. Calculate the principal paid for year 10. Correct your answer to the nearest cent without any units. (Do not use "$" or "," in your answer. e.g. 12345.67)  
A loan is to be repaid over 30 years, with month-end repayments of 9,000. If the...
A loan is to be repaid over 30 years, with month-end repayments of 9,000. If the interest rate is 7.0% p.a. compounded monthly. Calculate the interest paid for year 10. Correct your answer to the nearest cent without any units. (Do not use "$" or "," in your answer. e.g. 12345.67)  
A loan is to be repaid over 30 years, with month-end repayments of 8,000. If the...
A loan is to be repaid over 30 years, with month-end repayments of 8,000. If the interest rate is 3.8% p.a. compounded monthly. Calculate the interest paid for year 10. Correct your answer to the nearest cent without any units. (Do not use "$" or "," in your answer. e.g. 12345.67)
A loan is to be repaid over 30 years, with month-end repayments of 8,000. If the...
A loan is to be repaid over 30 years, with month-end repayments of 8,000. If the interest rate is 4.9% p.a. compounded monthly. Calculate the loan outstanding balance at the end of 10 years. Correct your answer to the nearest cent without any units. (Do not use "$" or "," in your answer. e.g. 12345.67)
A loan is to be repaid over 30 years, with month-end repayments of 4,000. If the...
A loan is to be repaid over 30 years, with month-end repayments of 4,000. If the interest rate is 5.3% p.a. compounded monthly. Calculate the principal paid for year 10. Correct your answer to the nearest cent without any units. (Do not use "$" or "," in your answer. e.g. 12345.67)
A loan is to be repaid over 30 years, with month-end repayments of 6,000. If the...
A loan is to be repaid over 30 years, with month-end repayments of 6,000. If the interest rate is 4.9% p.a. compounded monthly. Calculate the interest paid for year 10. Correct your answer to the nearest cent without any units. (Do not use "$" or "," in your answer. e.g. 12345.67).
A loan of $100,000 is made today. This loan will be repaid by 10 level repayments,...
A loan of $100,000 is made today. This loan will be repaid by 10 level repayments, followed by a final smaller repayment, i.e., there are 11 repayments in total. The first of the level repayments will occur exactly 2 years from today, and each subsequent repayment (including the final smaller repayment) will occur exactly 1 year after the previous repayment. Explicitly, the final repayment will occur exactly 12 years from today. If the interest being charged on this loan is...
A loan is being repaid with 20 payments of $ 1,000 at the end of each...
A loan is being repaid with 20 payments of $ 1,000 at the end of each quarter. Given that the nominal rate of interest is 8% per year compounded quarterly, find the outstanding balance of the loan immediately after 10 payments have been made (a) by the prospective method, (b) by the retrospective method.
A loan of $100,000 is to be repaid by two equal repayments of X. One repayment...
A loan of $100,000 is to be repaid by two equal repayments of X. One repayment is due at the end of 2 years, the second repayment is due at the end of 6 years. The interest rate is at 4% p.a. compounded quarterly for the first 3 years and then 4.4% p.a. compounded quarterly thereafter. What is the size of each repayment? Select one: a. $58,762.97 b. $58,222.14 c. $57,989.46 d. $56,779.19
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT