# Looking for Excel formula to calculate the benefit of making additional payments on a loan with interest daily compounded monthly

I'm looking to make extra payments to my student loans. I currently owe \$30,000 at 5.25%. I pay \$250 on the 15th of every month. My loans accrue interest daily that are then compounded monthly. I want a formula that will show the effect of paying my loan down daily (\$5), weekly (\$35) or bi-weekly (\$75).

• \$75 biweekly won't pay the \$250/mo you pay now. Will the lender accept such partial payments? – JTP - Apologise to Monica Jul 24 '17 at 3:16
• @JoeTaxpayer I think, per title, these would be in addition to the \$250/month, but I wondered the same. – Hart CO Jul 24 '17 at 3:16
• @HartCO - good point. I see that now, but the latter question still applies. The bank may not accept payments when they aren't due, the extra might only be accepted when accompanied by the regular payment. – JTP - Apologise to Monica Jul 24 '17 at 3:21
• As already stated, keep in mind that while you can ask to have extra payments applied to principle (for any type of loan) the bank is under no obligation to do that and can (and often do) apply it towards the interest first, and only when that has been exhausted do they apply it to the principle. So you can ask and see if they do it or just keep paying extra until you pass the point where payments exceed interest and have it come off that way, which is horrible to be honest. Our mortgage company accepts them, nice of them, and so did Ford on the car but I believe that's the exception, not rule – GµårÐïåñ Jul 24 '17 at 3:39
• – Chris W. Rea Dec 9 '17 at 2:01

There is no single formula that will do this comparison for you. There are formulas that will help you get there, but there's no =comparepaymentstrategy(strategyA, strategyB).

Separately, paying X per month vs X divided by 2 twice per month is an extremely incremental difference.

Lets say you have your \$30,000 loan at 5.25%. Fully amortized over 15 years that's a payment of \$245.44, almost the \$250 you're paying now. The daily interest on \$30,000 is about \$4.31, the daily interest on \$29,925 (an extra \$75 payment that is applied directly to principle) is \$4.30. So you'll save something around \$0.01 per day between payments when paying multiple times in a month versus simply loading all your expected payments into your monthly amount. Generously assuming \$0.01 of savings every single day over 15 years is a total of about \$657 or about \$44 per year. This is a generous assumption because the regular principle will exist every month until you make your mid month payment(s), you can never have a full month of savings. All of this assumes your lender will take multiple payments each month and apply them directly to the principle, which is unlikely.

There isn't much compounded benefit, because the incremental savings is so low. \$30,000 (regular principle) vs \$29,925 (principle less mid month partial payment) vs \$29,924.85 (less the interest saved from the prior month's mid month payment). You're removing \$0.15 or so from the principle each month because either way the same total amount is being paid each month. It would be several months before that savings grows to a point that it becomes more than a rounding error at 5.25% annual interest which would generate a compounded savings..

look at function cumipmt (I think that's the right name) First, get the function to duplicate your existing loan and make sure you get it to match reality. Then you can add extra payments in

CUMIPMT(rate, nper, pv, start_period, end_period, type)

I'm not sure how to adjust for periods different than your regular payment period.

• Why is this downvoted? When added to some compare and contrast scenarios this is the answer. – Joe S Jul 24 '17 at 14:37
• @JoeS This function is not useful because there is no way to incorporate payment amounts let alone irregular extra payments, it's just calculating interest over a given period based on present value and rate. – Hart CO Jul 25 '17 at 4:07

I'm sure there is no built-in Excel formula for the OP's calculation, but one can be constructed like so: Considering case for \$250 monthly plus \$5 daily:

Taking into account monthly compounding of the nominal rate,

the monthly rate `r = 5.25/100/12`

the effective annual rate `yr = (1 + r)^12 - 1`

Now calculating everything on a daily interest basis with months of average length:

average number of days per year `dy = 365.2425`

average days per month `av = dy/12`

daily rate `dr = (1 + yr)^(1/dy) - 1`

principal `s = 30000`

The payments:

``````d1 = 5
d2 = 250
``````

The program, or formula:

``````p = s
n    = 0

While[p[n] > 0,
p[n + 1] = p[n]*(1 + dr) - d1 - If[Floor[Mod[n, av] + 1.063] == 30, d2, 0];
n++]
``````

With \$5 paid daily the loan is paid down in 2,770 days, or `2770/av = 91 months`.

With `d1 = 0` the loan is paid down in 5,205 days, or `5205/av = 171 months`.

Check: using the standard formula for time to repayment with \$250 monthly:

months `= -(Log[1 - (r s)/d2]/Log[1 + r]) = 170.53`, so 171 months to complete.

Explanatory note:

`Floor[Mod[n, av] + 1.063] == 30` is true once at the end of every average month, i.e. at `n = 30, 61, 91, 122 etc.` so it applies the \$250 repayment at the end of each average month.

This could be quite readily transcribed to an Excel formula. I will see about adding variations for weekly and bi-weekly.

Adding extended formula for daily, weekly, bi-weekly and monthly

``````(* Setup rate constants *)
r = 5.25/100/12;
yr = (1 + r)^12 - 1;
dy = 365.2425;
av = dy/12;
dr = (1 + yr)^(1/dy) - 1;
s = 30000;
p = s;
n = 0;

(* Set repayment amounts *)
d1 = 5;
d2 = 35;
d3 = 75;
d4 = 250;

(* Run formula *)
While[p[n] > 0, p[n + 1] =
p[n]*(1 + dr)
- d1
- If[Mod[n + 1, 7] == 0, d2, 0]
- If[Mod[n + 1, 14] == 0, d3, 0]
- If[Floor[Mod[n, av] + 1.063] == 30, d4, 0];
n++]
``````

Results

``````daily  weekly  bi-weekly   monthly    days to pay-down   months
d1      d2       d3         d4             n             n/av
0       0        0        250           5205            171
5       0        0        250           2770             91
0      35        0        250           2770             91
0       0       75        250           2678             88
5      35       75        250           1414            46.5
``````

Adding \$5 daily or \$35 weekly result in the same loan period reduction.

The case will all repayments is added for interest. This is how it looks graphically: For an Excel implementation this is the first idea for transcription:

https://stackoverflow.com/questions/4939537/how-to-loop-in-excel-without-vba-or-macros