December 8, 2016

I have a situation where if I settle a supplier not later than the 22 nd of a particular month , I will obtain a settlement discount based on the following formula supplied by the supplier

payment amount x number of days paid early x 8% divided by 365 days

To qualify for the settlement discount, the payment must be made on the 22nd of a particular month or earlier

I have set up a spreadsheet to compute the settlement discount and compared this to what I can earn if I invested the funds

Kindly check my calculations and amend if necessary

Trusted Members

Moderators

January 31, 2022

Your current formula calculates the discount based on the number of early days, wether or not early payment occurred on or before the 22nd of the month. That's seems to be incorrect based on the rule to qualify for a discount. The following formula would fix that:

=IF(DAY($D$3) <= 22, (B2 * $G$2 * $F$2) / 365, 0)

Trusted Members

Moderators

January 31, 2022

December 8, 2016

From an investment point of view, I think that one would have to compute the int to the last working day to see what Int one could have earned in order to compare to the early settlement discount/rebate so one can make an informed decision whether it is better to take the settle discount or rather investment the funds up to the last working day and earn interest

Trusted Members

Moderators

January 31, 2022

Can't tell I really understand your schedule then. The interest earned for the month at 6% for the number days you held on to an investment would be:

=B15*DAY($D$3)/365*$H$2

Compare that to what you would have earned if you had kept the investment the entire month and see if that 'loss' outweighs the 'early settlement bonus'. That seems to always be the case when the early settlement is calculated at a higher % than interest earned.

Seems like an odd calculation, tough. But perhaps I'm still just not understanding your intentions.

Trusted Members

Moderators

January 31, 2022

That i understand, but not the calculations you made initially. If you would do the analysis manually (with pen on paper) what would be the outcome you expect for let's say the first investment only? If you can explain that, it would be easier to come-up with a formula.

Trusted Members

Moderators

January 31, 2022

I'll do my best but can't guarantee I fully understand.

The early settlement amount should be calculated only if you repay no later than the 22nd day of the month. So you need to use the formula I gave you in post#2. Otherwise you will even get a rebate when you repay after the 22nd.

In the next step you calculate 'interest saving by paying early' as the principle amount X 22 days X 8%.

Shouldn't that be the principle amount X 8 days X 8%? Because you **save** 8 days paying interest by paying early, not 22!

Your formula for 'interest earned by investing' takes only the full 30 days interest income. But when you repay 8 days early you will have earned less.

I'm just not following you here. Sorry!

Perhaps I can try from scratch and describe in words, let's say for BR1 only, what I believe is happening from start to finish. You get the supplier invoice for 222K and it's due the 30th. You pay the full amount either on the due date or 8 days early. When you pay early, you get a discount amounting to 389? When you hang on to your money until the due date you can earn 1094 interest. When you pay early you only earn 802 interest. In other words you give up 292 interest earnings to get a discount of 398. So you win 98. Great!

But as said earlier, that's an odd situation as the discount is calculated at 8% where investment income only at 6%. The best would always be to pay directly upon the invoice date and get a full rebate at 8% for 1410 which is 316 better than what you could ever earn by investing at 6%.

December 8, 2016

Thanks for the reply Riny.

The early settlement amount should be calculated only if we repay no later than the 22nd day of the month. If paid after the 22nd, then there is no rebate/settlement discount which in this case is the same

I agree with you that it is best to pay the last day of the month as one can earn more int that what the settlement discount is worth

Trusted Members

Moderators

January 31, 2022

I'm OK on your first comment. That's what I've been trying to explain all the time. So, just use the formula I suggested.

Your second comment, however, is completely opposite of what I mentioned. When the settlement discount gives you 8% and investing only 6%, you should pay the bill immediately, i.e. 30 days early.

But then, this is not an Excel issue. The formulas aren't that difficult and I think you can do them yourself. As long as you have the logic right!

1 Guest(s)