In: Accounting
On 1 April 2019, Exotica Ltd borrowed $56,870 from the bank at 10% per annum interest. This loan was a secured loan and is was for one year. This loan is repayable in amounts of $5,000 at the end of each month. Required 1. In an excel spreadsheet prepare a journal entry to record the initial mortgage (1 mark). 2. Within the Excel spreadsheet prepare a mortgage schedule for this loan. Round your calculations to two decimal places and the nearest dollar 3. In the Excel spreadsheet, use the mortgage schedule to prepare the journal entries for loan repayments for April and May 2019 .
Loan amortization schedule [ in $]
Months | Opening balance | payment | principal | Interest | Balance |
1 | 56,870.00 | 5,000.00 | 4,526.08 | 473.92 | 52,343.92 |
2 | 52,343.92 | 5,000.00 | 4,563.80 | 436.20 | 47,780.12 |
3 | 47,780.12 | 5,000.00 | 4,601.83 | 398.17 | 43,178.28 |
4 | 43,178.28 | 5,000.00 | 4,640.18 | 359.82 | 38,538.10 |
5 | 38,538.10 | 5,000.00 | 4,678.85 | 321.15 | 33,859.25 |
6 | 33,859.25 | 5,000.00 | 4,717.84 | 282.16 | 29,141.41 |
7 | 29,141.41 | 5,000.00 | 4,757.15 | 242.85 | 24,384.26 |
8 | 24,384.26 | 5,000.00 | 4,796.80 | 203.20 | 19,587.46 |
9 | 19,587.46 | 5,000.00 | 4,836.77 | 163.23 | 14,750.69 |
10 | 14,750.69 | 5,000.00 | 4,877.08 | 122.92 | 9,873.61 |
11 | 9,873.61 | 5,000.00 | 4,917.72 | 82.28 | 4,955.89 |
12 | 4,955.89 | 5,000.00 | 4,955.89 | 44.11 | 0.00 |
Total | 60,000.00 | 56,870.00 | 3,130.00 | 0 |
For the April month calculation is made as follows.
Interest = Principal * Interest
= $ 56870*0.10/12
= $473.92
Principal = $5000- $473.92
=$4526.08
Ending principal = $56870-$4526.08
=$52343.92
In the same way calculation is made for remaining months.
Journal entries are as follows.
JOURNAL ENTRIES IN THE BOOKS OF EXOTICA LTD | |||
Date | particulars | DR | CR |
01.01.2020 | Bank A/c…………………………………………………………..DR | $56870 | |
To Secured loan A/c | $56870 | ||
[ Being loan amount Disbursed ] | |||
30.04.2020 | Interest on loan A/c………………………………………….DR | 359.82 | |
Secured Loan A/c……………………………………………….DR | 4,640.18 | ||
To Bank A/c | 5,000.00 | ||
[ Being payment of loan is made along with interest for the month of April] | |||
30.05.2020 | Interest on loan A/c………………………………………….DR | 321.15 | |
Secured Loan A/c……………………………………………….DR | 4,678.85 | ||
To Bank A/c | 5,000.00 | ||
[ Being payment of loan is made along with interest for the month of May] | |||
Dear students kindly like my answer if you are satisfied with the answer and if you are disliking it let me know the reason in the comment section so that i will be able to provide you the information as you expect in the future.