Question

I have to do this in Excel. Please Provide the formulas step by step and a screenshoot of output how it looks in the end.
Question is about the MARKET vs the CONTRACTUAL value of a loan, BUT taking it in monthly terms.

You borrow $35,000 in order to purchase a car with an annual rate of 3.75% for four years monthly. Calculate your payment and the amortization table for the loan. At the 19th month you want to buy back the future payments of the car. Explain how you will evaluate how much you would have to pay for the remaining of your loan, evaluate it, and indicate your benefit of this transaction.

Answer 1

Given that

Original Principal	35000
Loan term (Years)	4
Payments Per Year	12
Total number of Payments	48
Annual Interest Rate	3.75%
Monthly Payment	786.36

Month	Payment	Interest	Principal	Balance
0				35000
1	786.36	109.38	676.98	34323.02
2	786.36	107.26	679.10	33643.92
3	786.36	105.14	681.22	32962.70
4	786.36	103.01	683.35	32279.35
5	786.36	100.87	685.48	31593.87
6	786.36	98.73	687.63	30906.24
7	786.36	96.58	689.78	30216.46
8	786.36	94.43	691.93	29524.53
9	786.36	92.26	694.09	28830.44
10	786.36	90.10	696.26	28134.18
11	786.36	87.92	698.44	27435.74
12	786.36	85.74	700.62	26735.12
13	786.36	83.55	702.81	26032.31
14	786.36	81.35	705.01	25327.30
15	786.36	79.15	707.21	24620.09
16	786.36	76.94	709.42	23910.67
17	786.36	74.72	711.64	23199.04
18	786.36	72.50	713.86	22485.18
19	786.36	70.27	716.09	21769.08
20	786.36	68.03	718.33	21050.75
21	786.36	65.78	720.57	20330.18
22	786.36	63.53	722.83	19607.36
23	786.36	61.27	725.08	18882.27
24	786.36	59.01	727.35	18154.92
25	786.36	56.73	729.62	17425.30
26	786.36	54.45	731.90	16693.39
27	786.36	52.17	734.19	15959.20
28	786.36	49.87	736.48	15222.72
29	786.36	47.57	738.79	14483.93
30	786.36	45.26	741.10	13742.84
31	786.36	42.95	743.41	12999.43
32	786.36	40.62	745.73	12253.69
33	786.36	38.29	748.06	11505.63
34	786.36	35.96	750.40	10755.22
35	786.36	33.61	752.75	10002.48
36	786.36	31.26	755.10	9247.38
37	786.36	28.90	757.46	8489.92
38	786.36	26.53	759.83	7730.09
39	786.36	24.16	762.20	6967.89
40	786.36	21.77	764.58	6203.31
41	786.36	19.39	766.97	5436.34
42	786.36	16.99	769.37	4666.97
43	786.36	14.58	771.77	3895.19
44	786.36	12.17	774.18	3121.01
45	786.36	9.75	776.60	2344.40
46	786.36	7.33	779.03	1565.37
47	786.36	4.89	781.47	783.91
48	786.36	2.45	783.91	0.00

Here is the screenshot of my workings in excel.

For Calculating the monthly loan payment amount, use the formula

=PMT(B6/B4,B5,-B2,0)

B6/B4 is the monthly interest rate ; B5 is the number of payments to be made as per the loan contract; -B2 is the loan amount. As we do not want negative figures and the PMT formula calculates 'the payments', the figures will show in negative if we don't use the 'minus'.

For Calculating Interest payments,

=IPMT($B$6/$B$4,B12,$B$5,-$F$11)

This formula is used for interest payment calculations, where $B$6/$B$4 is the monthly interest rate (as we are copying the formula down, we need to freeze the constant cells, hence $ is used) ; B12 is the nth number of interest payment for which we calculate the interest payment (so, for first month, its B12, second month - B13, and so on. $B$5 is the total number of payments to be made as per loan contract. -$F$11 is again the loan amount. Please note that we are freezing this amount as the IPMT gives us the interest amount of the nth month. It doesn't want to know the last loan balance outstanding.

Principal amount can be calculated simply by applying (Monthly payment - Interest) because whatever is remaining after the interest component is the principal component of the monthly payment.

So, Principal for first month = C12-D12

Or you can calculate Principal amount paid each month using the PPMT formula, similar to the IPMT formula as demonstrated above.

=PPMT($B$6/$B$4,B12,$B$5,-$F$11)

For the balance, the formula is again very simple. It is equal to Previous Balance minus Monthly Payment. So, for the first month, it is =F11-E12

After applying the formula for first month, you can safely copy it till the 48th month to get the loan repayment schedule.

______________________________________________________________________________

If you want to repurchase the loan at the 19th Month, you will have to make the payment of Balance outstanding at the 18th month plus the prepayment penalties, if the bank charges such penalties as per the loan contract. (ie. $22,485.18 + prepayment penalties)