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.
| A
| B
| C
| D
| E
| F
|
|---|
1
| annl rate | 8.20% | | | | |
2 | pmt | $12,564.59 | | | | |
3 | | | | | | |
4
| Pmt# | | Days | Pmt | Int | Bal |
5 | | 8/30/2018 | | | | $800,000.00
|
6 | 1 | 10/5/2018
| 36 | $12,564.59 | $6,470.14 | $793,905.55 |
7 | 2 | 11/5/2018 | 31 | $12,564.59 | $5,529.06 | $786,870.02
|
8 | 3 | 12/5/2018 | 30 | $12,564.59 | $5,303.29 | $779,608.72 |
|
|
|
|
|
|
|
87
| 82 | 7/5/2025 | 30 | $12,564.59 | $250.60
| $24,869.08 |
88 | 83 | 8/5/2025 | 31 | $12,564.59 | $173.20 | $12,477.69 |
89
| 84 | 9/5/2025 | 31 | $12,564.59 | $86.90 | -4.43E-06 |
<tbody>
</tbody>
Rich (BB code):
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.
