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?