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

Formula to perform an operation in a particular order


I need to create a set of formula which allocates a payment made for tax in a particular order. The scenario is as follows: If income tax in not paid within 30 days of it being due, the tax attracts a late penalty fee, and then interest at one-half of one percent per month. When a payment is made, the payment is allocated to penalties first, then interest and the remaining amount to the outstanding tax.

See the spreadsheet attached. Columns C and D show the total tax incurred. Columns I and J should the penalties and interest incurred as in the January case, the tax was 2 months in arrears. The payment was then made April 11, 2016. That payment has now to be allocated to the penalty first, then interest and the remainder to the tax in arrears. Can someone please help me with this problem?



The months late part of the Amended sheet doesn't make sense to me. I am not certain that this particular setup using this template is a good way to do what you want to do. If this template is for a school project then just do it they way that they want; otherwise, if this is actually for something that matters in the real world, explain that in more detail and we can help you create a useful template to use or at least point you in the right direction.
don (rep: 1989) Jun 4, '17 at 1:32 am
Thank you very much for your response.The months late is only there to calculate how many months overdue the payment of the tax is from the report date. It is on the number of months overdue that one is able to calculate the interest outstanding as well as to trigger a late payment fee of 10% of the tax owed if not paid after 1 month. This is for a real world situation so a more useful template would be appreciated if available.
Malcolm Jun 6, '17 at 1:11 pm
Add to Discussion



Hello Malcolm,

Here is a possible solution for what you are looking for. I only made changes to row 8 (new headers) and row 9 (highlighted cells - new formulas)

Cheers   ;-}


Answer the Question

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