Log In
Register
Home
December 8, 2016
Offline
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
Moderators
January 31, 2022
Offline
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)
December 8, 2016
Offline
Thanks Riny. Thanks for the updated formula
Please check my formula for my second requirement where I need to compute the int earned if those funds were invested for the number of days paid early and then compare to the settle discount for early settlement
Moderators
January 31, 2022
Offline
Well that formula seems to calculate an amount relative to the actual number of 'early days' at 6%, irrespective of the timing of the early payment. If that's not what you intend you need to clarify your intentions.
December 8, 2016
Offline
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
Moderators
January 31, 2022
Offline
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.
December 8, 2016
Offline
I am trying to determine whether it is better to pay early and take the early settlement discount or rather invest the funds
Moderators
January 31, 2022
Offline
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.
December 8, 2016
Offline
If I invested the funds , the int would be for the entire month eg 100,000*(6%/12) = 500
December 8, 2016
Offline
Thanks for all your input
I have amended my spreadsheet showing the rebate, the int saving by paying early, the int earned if invested
Kindly check my logic and amend my formulas where necessary
Moderators
January 31, 2022
Offline
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
Offline
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
Moderators
January 31, 2022
Offline
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)
