Forum

Notifications
Clear all

Compare Settlement Interest VS Int Earned

14 Posts
2 Users
0 Reactions
104 Views
(@howardc)
Posts: 54
Trusted Member
Topic starter
 

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

 
Posted : 23/09/2023 11:37 pm
Riny van Eekelen
(@riny)
Posts: 1195
Member Moderator
 

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)

 
Posted : 24/09/2023 1:04 am
(@howardc)
Posts: 54
Trusted Member
Topic starter
 

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

 
Posted : 24/09/2023 1:28 am
Riny van Eekelen
(@riny)
Posts: 1195
Member Moderator
 

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.

 
Posted : 24/09/2023 1:36 am
(@howardc)
Posts: 54
Trusted Member
Topic starter
 

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

 
Posted : 24/09/2023 2:04 am
Riny van Eekelen
(@riny)
Posts: 1195
Member Moderator
 

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.

 
Posted : 24/09/2023 2:58 am
(@howardc)
Posts: 54
Trusted Member
Topic starter
 

I am trying to determine whether it is better to pay early and take the early settlement discount or rather invest the funds

 
Posted : 24/09/2023 6:41 am
Riny van Eekelen
(@riny)
Posts: 1195
Member Moderator
 

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.

 
Posted : 24/09/2023 7:41 am
(@howardc)
Posts: 54
Trusted Member
Topic starter
 

If I invested the funds , the int would be for the entire month eg 100,000*(6%/12) = 500

 
Posted : 24/09/2023 8:01 am
(@howardc)
Posts: 54
Trusted Member
Topic starter
 

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

 
Posted : 24/09/2023 9:30 am
Riny van Eekelen
(@riny)
Posts: 1195
Member Moderator
 

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%.

 
Posted : 25/09/2023 1:38 am
(@howardc)
Posts: 54
Trusted Member
Topic starter
 

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

 
Posted : 25/09/2023 4:08 am
Riny van Eekelen
(@riny)
Posts: 1195
Member Moderator
 

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!

 
Posted : 25/09/2023 4:30 am
(@howardc)
Posts: 54
Trusted Member
Topic starter
 

Thanks for your valuable Input

 
Posted : 25/09/2023 9:08 am
Share: