How to produce daily loan amortisation schedule

Question

I've seen some examples of how to calculate an amortisation schedule either monthly or yearly. My bank charges interest on a daily basis so I'd like to create a spreadsheet detailing my current balance down to the day, including future dates. I'm struggling to find how to create this and would appreciate some help.

I know the following

Initial Principal
Monthly payment ( although I'd like to know how this was calculated )
Term, yrs
Interest Rate, per annum

I'd like to be able to have an entry in my ammortisation schedule for every day of my loan so I can see how much I owe daily.

I'd greatly appreciate some help with this.

Kind regards Mark.

Answer 1

You typically don't need a daily am schedule because your loan balance does not change daily. Interest accrues daily but the principal does not change until you make a payment.

But, to answer the question, you would obviously have a row for each date. Start with the initial balance, and multiply that by the interest rate / 12, then divide by the number of days in that month to get the daily interest accrued. Copy that down until you get to the first payment, then add in the amount of interest paid in that payment, which should be total interest accrued in the previous rows. Then subtract that from the total payment to get the amount of principal paid. Subtract that from the total principal owed on the next line.

Then, take the formulas for daily interest, copy them down until you get to the _next payment, and repeat for 360 months.

That will illustrate that the principal balance is always the same between payments and only the interest changes.

If you want to calculate how much principal and interest you owe on any given date, take the principal owed from the payment immediately before, and pro-rate the interest owed in the next payment by dividing by the number of days in that payment period and multiplying by the number of days since the last payment. That will roughly be the total amount owed, not accounting for different day count bases and a few days for settlement that the bank usually charges.

Answer 2

Below is the standard loan formula, where the initial loan principal is set equal to the sum of the payments discounted to present value, i.e. divided by (1 + r)^k where k is the month number. From the summation a closed-form expression can be obtained by induction, which can then be expressed in terms of d, the regular payment amount.

s = principal
r = periodic rate
n = number of payments
d = payment amount

∴ d = r s (1 + 1/((1 + r)^n - 1))

In order to have a equal payment every month a simplification is made: a year is assumed to be made up of 12 equal months, otherwise the payments would be different each month.

An expedient way to square the circle and find a daily balance is to accumulate smoothly from payment to payment, whether the month has 28 days, 30 or 31.

Taking a simple example: a 10k loan at 5% nominal annual interest compounded monthly, over 5 years

s = 10000
r = 0.05/12
n = 60

d = r s (1 + 1/((1 + r)^n - 1)) = 188.71

The balance b after each payment in month x is given by the expression

(d + (1 + r)^x (r s - d))/r

so the balances at the end of month 1 & 2 are

x = 1
b1 = (d + (1 + r)^x (r s - d))/r = 9852.95

x = 2
b2 = (d + (1 + r)^x (r s - d))/r = 9705.30

To calculate the daily rate i for a month with z days use

i = (1 + r)^(1/z) - 1

If month 2 is February the daily rate i is

z = 28
i = (1 + r)^(1/z) - 1 = 0.000148511

So the balance at the end of, say, February 15th would be

b1 (1 + i)^15 = b1 (1 + 0.000148511)^15 = 9874.93

If an unscheduled repayment of 1000 was made at the end of February 15th, the new balance at the end of February 28th would be

(9874.93 - 1000) (1 + 0.000148511)^(28 - 15) - d = 8703.36

and monthly amortisation could continue from there.

In all there would be 3 daily rates, depending on the number of days in the month (neglecting leap years).

z = 28   ∴ i = (1 + r)^(1/z) - 1 = 0.000148511
z = 30   ∴ i = (1 + r)^(1/z) - 1 = 0.000138610
z = 31   ∴ i = (1 + r)^(1/z) - 1 = 0.000134138

Below is some demonstration Mathematica code that calculates and plots the daily values over the first quarter (without any unscheduled repayments). A simple pattern of 5 lines is repeated to calculate the daily values for each month.

x = 0;
b0 = (d + (1 + r)^x (r s - d))/r;       (*  = 10000 obviously *)
z = 31;
i = (1 + r)^(1/z) - 1;
s0 = Table[b0 (1 + i)^k, {k, 1, z - 1}];

x = 1;
b1 = (d + (1 + r)^x (r s - d))/r;       (*  = 9852.95 as previously *)
z = 28;
i = (1 + r)^(1/z) - 1;
s1 = Table[b1 (1 + i)^k, {k, 1, z - 1}];

x = 2;
b2 = (d + (1 + r)^x (r s - d))/r;       (*  = 9705.30 as previously *)
z = 31;
i = (1 + r)^(1/z) - 1;
s2 = Table[b2 (1 + i)^k, {k, 1, z - 1}];

x = 3;
b3 = (d + (1 + r)^x (r s - d))/r;

DateListPlot[Transpose[{
   Table[DateList[{2022, 1, k}], {k, 0, 90}],
   Flatten[{b0, s0, b1, s1, b2, s2, b3}]}],
 PlotLabel -> "Q1 Amortization"]

One can jump straight to any month and calculate the balance on a specific day. For example, month 48, December 8th

x = 48 - 1
b47 = (d + (1 + r)^x (r s - d))/r = 2383.17   (Nov 30th balance)
z = 31
i = (1 + r)^(1/z) - 1

Balance on December 8th = b47 (1 + i)^8 = 2385.73

Furthermore, the balance on December 31st after the repayment is

x = 48
b48 = (d + (1 + r)^x (r s - d))/r = 2204.39

and the balance at the end of month 60 is, as expected

x = 60
b60 = (d + (1 + r)^x (r s - d))/r = 0