Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculate Compound interest

0

I am working on a project at work and was asked to calculate compound interest on delinquent employee contribution. I've attached the workbook with some examples. The interest is 12% per annum, which I understand to be 1% per month- column H. The employer interested stops being charged as soon as a postmark date is entered in the contribution payments section.

Please let me know if my calculation/formula for calculating the compound interest is accurate? Should it be calculated differently?

Answer
Discuss

Answers

0

I have seen this exact same workbook with the exact same question before. If it's for work I'm not surprised they didn't find a solution. The task isn't described in sufficient detail and the workbook doesn't support a solution. If it's a task from school the job is to calculate "compound interest". Find it explained here.

In order to calculate compound interest you need the following parameters.

  1. An amount
    The "Balance due" in column J might be the amount or perhaps "Total contributions due" in column C, but these aren't suitable because the amount on which interest is charged changes whenever a partial payment is made. In fact, a new calculation starts on the day on or after each partial payment.
  2. An interest rate
    You say it's 12% annually. Fair enough.
    Column H ("Interest % assessed") converts this to 1% monthly. Possible.
    To hide this vital parameter in a formula is unprofessional in the highest degree. For you to guess at it like a highschool student is unacceptable in business.
  3. A compounding period.
    The task doesn't say. It seems that the workbook's intention is to compound interest monthly. I point to the legal implications. Most people don't understand compound interest. Therefore, banks etc. are required to show the true cost. Here I meet a task where the business leaves the decision of how to charge interest to an employee who asks for help on the internet. My verdict: unethical, in many jurisdictions illegal. My recommendation: Let the worksheet show the interest rate and compound period openly, as opposed to hiding both in some formula somewhere.
  4. The period for which interest is charged.
    Again, the worksheet is designed to hide facts. The required fact is that "compounded interest is charged for 231 days at the rate of 1% per month". The "231" days should be shown. Ostensibly, the number is hidden between the "Due date" and "Postmark date", which the worksheet immediately repudiates however by having several postmark dates.
    Of course, the worksheet also doesn't calculate the period like that at all. It uses a different rule: interest is charged at 1% per month for every completed month (formula in column E). The unfinished month during which payment was made is free, and if payment was made on the exact date on which the month ends, that last month is completely free, too.

In short, the workbook fails to provide precise data for all the four parameters a calculation of compound interest invariably requires. It also fails to disclose its methods of calculation which is the reason why I wouldn't sign off on it if I were the boss who must decide how much interest my company must charge a client. Bear in mind, this is a calculation of overdue payments, very likely to go to court, where this amateurish calculation would be my pleasure to tear to pieces.

So, if this is a task from school after all, I suggest you become very familiar with the method of calculating compound interest (see the link above) and then argue that calculation based on this workbook is impossible because it provides no precise parameters for its calculation. On the other hand, if this is a commercial undertaking a completely different approach must be taken. Your worksheet can't be made to work.

Discuss

Discussion

I thought the same as your guess where interest should only be charged until the postmarked date. But was asked to calculate compound interest. I’ve attached the workbook in hopes that you can help!

The employer will pay $1,000 monthly until all delinquent contributions are paid. The payment will be applied to The oldest delinquency first. I appreciate your time.
PLN_learning (rep: 2) Nov 29, '18 at 7:38 pm
Is this a task you brought home from school?
Variatus (rep: 4889) Nov 30, '18 at 2:10 am
No. This is for work. We need to charge compound interest on the delinquent employee contributions. Since there is no specific time frame (1year,5 yes, etc) I am not sure how to use a formula to calculate the compound interest. Interest will stop being charged as soon as a payment is received for each month. However the payments are to be applied to the principal of the oldest delinquency and interest stops being charged as soon as the payment for each month is received.
PLN_learning (rep: 2) Dec 1, '18 at 9:54 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login