Help in Loan Amortization for 365 days calculation

[vikas_newports]

I took a vehicle car loan for $777,700 at 8.85% for 7 years. I used PMT and got a monthly payment of $12,453. The bank has a payment of $12,473

I inquired the bank why the difference and they said it was the difference between 360 days vs 365. I don't know what is this.

How do I calculate it to get their monthly payment and make an amortization schedule with interest and principal?

I have attached my calculation in excel file and a calculation in pdf file from Bank end in a google drive link. link below

https://drive.google.com/drive/folders/1ES3sUR_4fWgpaRr82L3TcGuQ-pwXF5ie?usp=sharing

 

[StephenCrump]

C5: =i*(A6-A5)/365*B5
B6: =SUM(B5:C5)-Repayment
Repayment = 12,472.7377 ... using Goal Seek to get B89 = 0
The Bank has then rounded all amounts to the dollar.

Book1
	A	B	C
1	Interest	8.85%	p.a.
2	Repayment	12,472.7377
3
4	Date	Balance	Interest
5	30. Aug 18	777,700.00	6,788.36
6	5. Oct 18	772,015.62	5,802.81
7	5. Nov 18	765,345.69	5,567.10
8	5. Dec 18	758,440.06	5,700.77
9	5. Jan 19	751,668.09	5,649.87
10	5. Feb 19	744,845.22	5,056.78
11	5. Mar 19	737,429.27	5,542.84
12	5. Apr 19	730,499.37	5,313.63
13	5. May 19	723,340.27	5,436.94
14	5. Jun 19	716,304.47	5,210.38
15	5. Jul 19	709,042.11	5,329.47
16	5. Aug 19	701,898.85	5,275.78
17	5. Sep 19	694,701.89	5,053.24
18	5. Oct 19	687,282.39	5,165.92
19	5. Nov 19	679,975.57	4,946.12
20	5. Dec 19	672,448.96	5,054.42
21	5. Jan 20	665,030.64	4,998.66
22	5. Feb 20	657,556.56	4,623.61
23	5. Mar 20	649,707.44	4,883.49
24	5. Apr 20	642,118.19	4,670.75
25	5. May 20	634,316.20	4,767.80
26	5. Jun 20	626,611.26	4,557.95
27	5. Jul 20	618,696.48	4,650.39
28	5. Aug 20	610,874.13	4,591.60
29	5. Sep 20	602,992.99	4,386.15
30	5. Oct 20	594,906.41	4,471.58
31	5. Nov 20	586,905.25	4,269.13
32	5. Dec 20	578,701.64	4,349.78
33	5. Jan 21	570,578.68	4,288.72
34	5. Feb 21	562,394.66	3,818.12
35	5. Mar 21	553,740.05	4,162.15
36	5. Apr 21	545,429.46	3,967.44
37	5. May 21	536,924.16	4,035.76
38	5. Jun 21	528,487.18	3,844.20
39	5. Jul 21	519,858.65	3,907.49
40	5. Aug 21	511,293.39	3,843.11
41	5. Sep 21	502,663.76	3,656.36
42	5. Oct 21	493,847.39	3,711.97
43	5. Nov 21	485,086.62	3,528.51
44	5. Dec 21	476,142.39	3,578.89
45	5. Jan 22	467,248.55	3,512.04
46	5. Feb 22	458,287.86	3,111.34
47	5. Mar 22	448,926.45	3,374.33
48	5. Apr 22	439,828.04	3,199.30
49	5. May 22	430,554.60	3,236.24
50	5. Jun 22	421,318.10	3,064.66
51	5. Jul 22	411,910.02	3,096.10
52	5. Aug 22	402,533.38	3,025.62
53	5. Sep 22	393,086.26	2,859.30
54	5. Oct 22	383,472.82	2,882.35
55	5. Nov 22	373,882.43	2,719.61
56	5. Dec 22	364,129.30	2,736.96
57	5. Jan 23	354,393.52	2,663.78
58	5. Feb 23	344,584.56	2,339.40
59	5. Mar 23	334,451.22	2,513.88
60	5. Apr 23	324,492.37	2,360.35
61	5. May 23	314,379.98	2,363.02
62	5. Jun 23	304,270.26	2,213.25
63	5. Jul 23	294,010.77	2,209.91
64	5. Aug 23	283,747.95	2,132.77
65	5. Sep 23	273,407.99	1,988.76
66	5. Oct 23	262,924.01	1,976.25
67	5. Nov 23	252,427.53	1,836.15
68	5. Dec 23	241,790.94	1,817.41
69	5. Jan 24	231,135.61	1,737.32
70	5. Feb 24	220,400.19	1,549.75
71	5. Mar 24	209,477.19	1,574.52
72	5. Apr 24	198,578.98	1,444.46
73	5. May 24	187,550.70	1,409.71
74	5. Jun 24	176,487.68	1,283.77
75	5. Jul 24	165,298.70	1,242.46
76	5. Aug 24	154,068.42	1,158.05
77	5. Sep 24	142,753.73	1,038.39
78	5. Oct 24	131,319.38	987.05
79	5. Nov 24	119,833.70	871.67
80	5. Dec 24	108,232.63	813.52
81	5. Jan 25	96,573.41	725.89
82	5. Feb 25	84,826.56	575.89
83	5. Mar 25	72,929.72	548.17
84	5. Apr 25	61,005.15	443.75
85	5. May 25	48,976.16	368.13
86	5. Jun 25	36,871.55	268.20
87	5. Jul 25	24,667.02	185.41
88	5. Aug 25	12,379.69	93.05
89	5. Sep 25	0.00
Sheet1

 

[vikas_newports]

Thank you @StephenCrump
I got you calculations except for per month Repayment I did it just by inputting the 12,473 instead of goal seek
First I tried to used PMT function(B2) then I applied goal seek by select as B89,0,B2 and getting error Cell must contain a value

 

[Andrew Fergus]

That error is referring to the cell B2 - that cell must contain a value (and not a formula) when using goal seek.

If I use goal seek on the PMT function by changing the rate, I get a rate of 8.90% over 84 months for a repayment value of $12,473, which is slightly higher than the quoted rate of 8.85%.

As to why this would be 360 days instead of 365 is a mystery - it might be worth asking how this works with the bank.

On what date was the loan drawn down?

 

[vikas_newports]

Thank you @Andrew Fergus for your valuable input. The main point is that Loan rate is fixed. in this situation I am aware that repayment/installment is 12,473 but what if I do not know the rental amount then How can I get the monthly installment match with this schedule. Loan draw down date I assume 30-Aug-2018 as the system generated schedule is based on it.

That error is referring to the cell B2 - that cell must contain a value (and not a formula) when using goal seek.

If I use goal seek on the PMT function by changing the rate, I get a rate of 8.90% over 84 months for a repayment value of $12,473, which is slightly higher than the quoted rate of 8.85%.

As to why this would be 360 days instead of 365 is a mystery - it might be worth asking how this works with the bank.

On what date was the loan drawn down?

 

[joeu2004]

@Vikas.... I think you missed the point of Stephen's posting (#2), namely: We cannot use Excel financial functions like PMT.

Excel financial functions expect the period to be equal. But the lender charges simple daily interest for the exact number of days between payment due dates, which usually varies between 28 and 31 days. And in your case, the first period is 36 days, namely: Aug 30 to Oct 5.

So, we must use Goal Seek or Solver to derive B2. In order to do that, B2 must be empty (or a constant, not a formula) to begin with.

Moreover, the result will not be 12473 exactly. Instead, it will be 12472.7376948927 (using Solver).

In the PDF, the lender rounded all values for presentation purposes. Presumably, the lender uses exact calculations for the amortization schedule, then rounds each value when presenting the results.

-----

If you format B89 as General or Scientific, you might notice that it is not exactly zero. Nevertheless, it should be "close to zero"; close enough so that the rounded result is less than 0.01.

Such small "errors" arise because of the approximate nature of the iterative Goal Seek and Solver algorithms, as well as because of anomalies due to internal binary arithmetic (e.g. 10.01 - 10 <> 0.01 !).

Also, since the real-life payment must be rounded at least to 12472.74, the final balance might differ from zero significantly (e.g. -0.27 or -30.38). Lendors handle this is in different ways.

 

[vikas_newports]

@Joeu2006, thank you I got your point , Let's take an example for this calculation if I am the bank and I need to make a repayment plan for the loan amount $800,000 for the 7 year with fixed 8.2% for all years. Loan draw down date / loan start date 30-Aug-2018 and First installment date 05-Oct-2018. for the first installment we have 36 days for profit and in other months 30,31 and 28 or 29 days . How can the installment will be made.

 

[joeu2004]

Each lendor uses their own program for calculating the terms and amortization schedule of a loan. It is not likely that they use Excel. Moreover, each lendor has their own rules for rounding both payments and amortization calculations, and for handling end-of-month due dates.

Using Excel, we can only estimate the amortization schedule of a loan.

For a loan of $800,000 over 7 years (84 months) at 8.2% annually with exact daily interest, refer to the following design. See the important notes below.

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

A
​

[/TH]
[TH]

B
​

[/TH]
[TH]

C
​

[/TH]
[TH]

D
​

[/TH]
[TH]

E
​

[/TH]
[TH]

F
​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

1
​

[/TD]
[TD]annl rate[/TD]
[TD]

8.20%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

2​

[/TD]
[TD]pmt[/TD]
[TD]

$12,564.59​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

4
​

[/TD]
[TD]

Pmt#​

[/TD]
[TD][/TD]
[TD]

Days​

[/TD]
[TD]

Pmt​

[/TD]
[TD]

Int​

[/TD]
[TD]

Bal​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

5​

[/TD]
[TD][/TD]
[TD]

8/30/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

$800,000.00
​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

6​

[/TD]
[TD]

1​

[/TD]
[TD]

10/5/2018
​

[/TD]
[TD]

36​

[/TD]
[TD]

$12,564.59​

[/TD]
[TD]

$6,470.14​

[/TD]
[TD]

$793,905.55​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

7​

[/TD]
[TD]

2​

[/TD]
[TD]

11/5/2018​

[/TD]
[TD]

31​

[/TD]
[TD]

$12,564.59​

[/TD]
[TD]

$5,529.06​

[/TD]
[TD]

$786,870.02
​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

8​

[/TD]
[TD]

3​

[/TD]
[TD]

12/5/2018​

[/TD]
[TD]

30​

[/TD]
[TD]

$12,564.59​

[/TD]
[TD]

$5,303.29​

[/TD]
[TD]

$779,608.72​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

---

​

[/TD]
[TD]

---

​

[/TD]
[TD]

---

​

[/TD]
[TD]

---

​

[/TD]
[TD]

---

​

[/TD]
[TD]

---

​

[/TD]
[TD]

---

​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

87
​

[/TD]
[TD]

82​

[/TD]
[TD]

7/5/2025​

[/TD]
[TD]

30​

[/TD]
[TD]

$12,564.59​

[/TD]
[TD]

$250.60
​

[/TD]
[TD]

$24,869.08​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

88​

[/TD]
[TD]

83​

[/TD]
[TD]

8/5/2025​

[/TD]
[TD]

31​

[/TD]
[TD]

$12,564.59​

[/TD]
[TD]

$173.20​

[/TD]
[TD]

$12,477.69​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

89
​

[/TD]
[TD]

84​

[/TD]
[TD]

9/5/2025​

[/TD]
[TD]

31​

[/TD]
[TD]

$12,564.59​

[/TD]
[TD]

$86.90​

[/TD]
[TD]

-4.43E-06​

[/TD]
[/TR]
</tbody>[/TABLE]

B2: empty initially
C6: =B6-B5
D6: =B2
E6: =F5*C6*$B$1/(DATE(1+YEAR(B6),1,1)-DATE(YEAR(B6),1,1))
F6: =F5+E6-D6
A7: =A6+1
B7: =EDATE($B$6,A7-1)
D7: =$D$6
Copy C6:F6 into C7:F7
Copy A7:F7 into A8:F89
Format F89 as Scientific for demonstration purposes only.

In the formula for the daily interest rate in E6, the denominator calculates 366 in leap years and 365 in normal years. Some lendors always use 365.

The formula for the date of the next payment in B7 ensures that the payment is the same day of each month, regardless of the days between months. It avoids "date drift" when the previous month has 28, 29 or 30 days. Some lendors ensure that if the first payment is at the end of the month, all subsequent payments are at the end of the month.

B2 should be empty initially. Use Goal Seek or Solver to set F89 to zero by changing B2.

As the example demonstrates, the final balance in F89 is often infinitesimally different from zero.

For this example, initially, B2 is 12564.5893990008 (using Solver). But in the real world, the payment must be rounded to some degree.

A lender will choose the degree of rounding (precision and multiple). And they might choose to always round down, usually resulting in a small "balloon payment" (extra principal) with the last payment. Or they might choose to always round up, usually resulting in a small "finance charge" (extra interest) with the last payment.

It is tempting to use a formula of the form =ROUND(C2,2) in B2, and let Goal Seek or Solver change C2 iteratively. However, we should not explicitly round in the system of formulas that Goal Seek or Solver recalculates iteratively. Often, that causes the value of the target cell (F89) to oscillate, resulting in endless iterations and eventually an error ("failure to find a solution").

So after finding a solution with Goal Seek or Solver, I might manually round the derived value. I like to use a formula of the form
=ROUNDDOWN(12564.5893990008,2), which preserves the more precise value that was derived.

 

[joeu2004]

Errata (too late to edit)....

D6: =B2
[....]
D7: =$D$6
[....]
Copy C6:F6 into C7:F7 (!)

Remove the special formula in D7. And change D6 to =$B$2.

 

[Andrew Fergus]

You raise some good points and I always use the method you have tabulated, never the PMT function (although it gives an ok approximation). Regarding this part:

For this example, initially, B2 is 12564.5893990008 (using Solver). But in the real world, the payment must be rounded to some degree.

A lender will choose the degree of rounding (precision and multiple). And they might choose to always round down, usually resulting in a small "balloon payment" (extra principal) with the last payment. Or they might choose to always round up, usually resulting in a small "finance charge" (extra interest) with the last payment.

It is tempting to use a formula of the form =ROUND(C2,2) in B2, and let Goal Seek or Solver change C2 iteratively. However, we should not explicitly round in the system of formulas that Goal Seek or Solver recalculates iteratively. Often, that causes the value of the target cell (F89) to oscillate, resulting in endless iterations and eventually an error ("failure to find a solution").

So after finding a solution with Goal Seek or Solver, I might manually round the derived value. I like to use a formula of the form
=ROUNDDOWN(12564.5893990008,2), which preserves the more precise value that was derived.

I force the interest calculation and repayment value to 2 decimal places (given those are the values actually paid) and can get the goal seek to work by referencing the terminal balance cell (F89) with another formula that rounds to say 1dp or 0dp and forcing the goal seek to solve for that cell to avoid the "no solution" error. It ends up being accurate to within a few cents with a slightly different balloon payment. I think the outcomes will be very similar, just a slightly different method.