South Africa Formula to Compute Early Settlement Discount/Rebate

[Flupsie1]

I have an Excel spreadsheet and the payment amount in cell A1, the end of month date in C1 , the early settlement date in C2 (this must be the 22nd or earlier) and the int rate in D1

The manual formula to compute the settlement discount/rebate is the payment amount x number of days paid early x 9.75% divided by 365 days


I have attached my spreadsheet showing a formula in B1. I need someone to check if it is 100% correct. It appears to be correct

It would be appreciated if someone could check my calc and if not correct, provide a solution

 

[Harryflang]

Hi.
You say it must be 22nd or earlier. Does that mean for example settlement on 25 September attracts no discount?

 

[Harryflang]

It would also help to know the terms of the discount

 

[Flupsie1]

Hi.
You say it must be 22nd or earlier. Does that mean for example settlement on 25 September attracts no discount?

Thank for the reply. If paid later than the 22 nd , then there will be no early settlement discount/rebate

 

[Harryflang]

Then you need to make at least one adjustment to your spreadsheet - see the attached.
I don't think the discount calculation is correct as far as the interest part goes, but will need to understand the discount policy to assist.

 

[Flupsie1]

The settlement discount is only applicable if payment is made on the 22nd or prior to this date

I only need a formula to determine whether it is better to investment the funds at 7.5% than settle earlier