Question

On 1/1/21, you purchase a used car for $5,000 with a 12% loan (note) payable monthly. Payments of
principal and interest are made at the end of each month for the next three years.
Required
a. Compute the amount of your monthly payment.
b. Create an effective interest amortization table in Excel for the entire life of the car loan
payable using the following columns: payment number, payment date, beginning carrying
value, effective interest rate, interest expense, cash paid (principal plus interest), reduction of
principal), and ending carrying value.
Note: your time value table may not include the factor you need; you have to determine
which of the following you should use:
PV single sum, 3 periods at 12% 0.71178
PV single sum, 36 periods at 1% 0.69892
PV ordinary annuity, 3 periods at 12% 2.40183
PV ordinary annuity, 36 periods at 1% 30.10751
PV annuity due, 3 periods at 12% 2.69005
PV annuity due, 36 periods at 1% 30.40858
The goal is to prove that, at end of the car loan, the entire principal of the loan has been
repaid. As with bond amortization schedules, these spreadsheets are important in practice
because they serve as the basis for journal entries for every payment date and adjusting
journal entries at year-end.
NOTE: You should use normal Excel calculations and formulas for the completion of this
assignment. However, you are NOT allowed to use a pre-built Excel template
c. Once your table is complete, tell me:
 What is the journal entry on 1/1/21 to purchase the car?
 What is the journal entry on 1/31/21, the first payment date?
 What is the journal entry on 2/28/21, the second payment date?
 What would happen if you paid an additional $50 in principal each month? When
would the loan be paid off? How much interest would you save over the life of the
loan?

Answer 1

a. The EMI Calculator:

Loan Amount	$ 5,000.00
Interest Rate	12.00%
Year of Loan	3
Repayment Schedule	12
	EMI = $166.07 {P*(r(1+r)^n / ((1+r)^n)-1)} Where P= Principal Amount r = interest per month ( 12%/12 months)=1% n = no of insatlmments= 3 years*12 months=36 months {5000*{((0.01*(1+0.01)^36)) / (((1+0.01)^36)-1)

or The Calculations from the problem data:

EMI	166.0715	($5,000/30.10751)
		( LOAN/ annuity, 36 periods at 1%)

b.Effective interest amortization table

Scheduled Payment (EMI) Principal Interest Amount During Extra out of the the Payment EMI month Payment Date Total Interest Effective till that Interest month Rate Principal Ending Balance PmtNo. Principal Opening Balance - 1 31-01-2021 $ 2 28-02-2021 $ 3 31-03-2021 $ 4 30-04-2021 $ 5 31-05-2021 $ 6 30-06-2021 $ 7 31-07-2021 $ 8 31-08-2021 $ 9 30-09-2021 $ 10 31-10-2021 $ 11 30-11-2021 $ 12 31-12-2021 $ 13 31-01-2022 $ 14 28-02-2022 $ 15 31-03-2022 $ 16 30-04-2022 $ 17 31-05-2022 $ 18 30-06-2022 $ 19 31-07-2022 $ 20 31-08-2022 $ 21 30-09-2022 $ 22 31-10-2022 $ 23 30-11-2022 $ 24 31-12-2022 $ 25 31-01-2023 $ 26 28-02-2023 $ 27 31-03-2023 $ 28 30-04-2023 $ 29 31-05-2023 $ 30 30-06-2023 $ 31 31-07-2023 $ 32 31-08-2023 $ 33 30-09-2023 $ 34 31-10-2023 $ 35 30-11-2023 $ 36 31-12-2023 $ 5,000.00 $ 4,883.93 $ 4,766.70 $ 4,648.29 $ 4,528.70 $ 4,407.92 $ 4,285.93 $ 4,162.71 $ 4,038.27 $ 3,912.58 $ 3,785.63 $ 3,657.42 $ 3,527.92 $ 3,397.13 $ 3,265.03 $ 3,131.61 $ 2,996.85 $ 2,860.75 $ 2,723.29 $ 2,584.45 $ 2,444.22 $ 2,302.59 $ 2,159.55 $ 2,015.07 $ 1,869.15 $ 1,721.77 $ 1,572.91 $ 1,422.57 $ 1,270.73 $ 1,117.36 $ 962.46 $ 806.02 $ 648.01 $ 488.41 $ 327.23 $ 164.43 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ 166.07 $ $ 116.07 $ 50.00 $4,883.93 $ 50.00 0.01 $ 117.23 $ 48.84 $4,766.70 $ 98.84 0.009768 $ 118.40 $ 47.67 $4,648.29 $ 146.51 0.009533 $ 119.59 $ 46.48 $4,528.70 $ 192.99 0.009297 $ 120.78 $ 45.29 $4,407.92 $ 238.28 0.009057 $ 121.99 $ 44.08 $4,285.93 $ 282.36 0.008816 123.21 $ 42.86 $4,162.71 $ 325.21 0.00857 $ 124.44 $ 41.63 $4,038.27 $ 366.84 0.008325 $ 125.69 $ 40.38 $3,912.58 $ 407.22 0.008077 $ 126.95 $39.13 $3,785.63 $ 446.35 0.007825 $ 128.22 $ 37.86 $3,657.42 $ 484.21 0.007571 $ 129.50 $ 36.57 $3,527.92 $ 520.78 0.007315 $ 130.79 $ 35.28 $3,397.13 $ 556.06 0.007056 $ 132.10 $ 33.97 $3,265.03 $ 590.03 0.006794 $ 133.42 $ 32.65 $3,131.61 $ 622.68 0.00653 $ 134.76 $ 31.32 $2,996.85 $ 654.00 0.006263 $ 136.10 $ 29.97 $2,860.75 $ 683.97 0.005994 $ 137.46 $ 28.61 $2,723.29 $ 712.57 0.005721 $ 138.84 $ 27.23 $2,584.45 $ 739.81 0.005447 $ 140.23 $ 25.84 $2,444.22 $ 765.65 0.005169 $ 141.63 $ 24.44 $2,302.59 $ 790.09 0.004888 $ 143.05 $ 23.03 $2,159.55 $ 813.12 0.004605 $ 144.48 $ 21.60 $2,015.07 $ 834.71 0.004319 $ 145.92 $ 20.15 $1,869.15 S 854.87 0.00403 $147.38 $ 18.69 $1,721.77 $ 873.56 0.003738 $ 148.85 $ 17.22 $1,572.91 $ 890.77 0.003444 $ 150.34 $15.73 $1,422.57 $ 906.50 0.003146 $ 151.85 $ 14.23 $1,270.73 $ 920.73 0.002845 $ 153.36 $ 12.71 $1,117.36 $ 933.44 0.002541 $ 154.90 $ 11.17 $ 962.46 $ 944.61 0.002235 $ 156.45 $ 9.62 $ 806.02 $ 954.23 0.001925 $ 158.01 $ 8.06 $ 648.01 $ 962.30 0.001612 $ 159.59 $ 6.48 $ 488.41 $ 968.78 0.001296 $ 161.19 $ 4.88 $ 327.23 $ 973.66 0.000977 $ 162.80 $ 3.27 $ 164.43 $ 976.93 0.000654 $ 162.78 $ 1.64 $ $ 978.58 0.000329

c.

journal entry

Journal Entries
01-01-2021	Car A/c	Dr	$ 5,000.00
	To Loan A/c			$ 5,000.00
	( Being car purchased in Loan & loan is recorded)
31-01-2021	Loan A/c	Dr	$     116.07
	Interest Paid A/c	Dr	$       50.00
	TO Cash A/c			$     166.07
	( Being Amount paid as 1st Installment)
28-02-2021	Loan A/c	Dr	$     117.23
	Interest Paid A/c	Dr	$       48.84
	TO Cash A/c			$     166.07
	( Being Amount paid as 2nd Installment)