Payment Plan with Unequal Payment Annuities

[Bjorn75]

Hi,

I require some help on the following problem, please:

Problem:
We have granted some customers special payment terms whereby they are allowed to pay off their debt in 13 unequal instalments on the last day of every month. I need Excel to show to me on any given day the total payment due per customer on the last day of that month for all the transactions made by that customer, as well as the balance amount payable for the remaining months.

Parameters:
1. The first payment of 50% of the purchase price is due on the last day of the same month in which the purchase was made.
2. The balance amount is payable in 12 equal instalments on the last day of the 12 consecutive months with a 1% administration fee added per month on the 50% balance.
3. The actual last day of the month needs to be considered, i.e. the 30th/31st for March - January and the 28th for February (29th in a leap year).

Example:
A customer makes a purchase for $2400 on 29th January 2015 (see last item in table below).
The first payment of $1200 (=50%) will be due on 31st January 2015.
The consecutive annuity payments of $112 per month ($100 + 1% of $1200) will be due on 28th February, 31st March, 30th April and so on, until the final payment has been reached on 31st January 2016.
The total fee paid at the end of the annuity period therefore amounts to $2544.

[TABLE="class: grid, width: 1150, align: left"]
<tbody>[TR]
[TD="align: center"]Order No.[/TD]
[TD="align: center"]Customer Name[/TD]
[TD="align: center"]Purchase Date[/TD]
[TD="align: center"]Purchase Price[/TD]
[TD="align: center"]Amount Due This Month(on 31 Jan 2015)
= 50% balance (if still in same month as purchase)
or
= (50% balance divided by 12) + (1% of 50% balance)[/TD]
[TD="align: center"]Amount Outstanding (as at 31 Jan 2015)
= Purchase Price - Amounts Due to Date + Amount Due This Month[/TD]
[/TR]
[TR]
[TD="align: center"]00111[/TD]
[TD]ABC Insurance Co.[/TD]
[TD="align: center"]10-Nov-2013[/TD]
[TD="align: center"]$ 2400[/TD]
[TD="align: center"]$ 0.00[/TD]
[TD="align: center"]$ 0.00[/TD]
[/TR]
[TR]
[TD="align: center"]00112[/TD]
[TD]XYZ Attorneys[/TD]
[TD="align: center"]05-Jan-2014[/TD]
[TD="align: center"]$ 2600[/TD]
[TD="align: right"]

$ 121.33​

[/TD]
[TD="align: center"]$ 121.33[/TD]
[/TR]
[TR]
[TD="align: center"]00113[/TD]
[TD]ABC Insurance Co.[/TD]
[TD="align: center"]12-Apr-2014[/TD]
[TD="align: center"]$ 2800[/TD]
[TD="align: center"]$ 130.67[/TD]
[TD="align: center"]$ 368.67[/TD]
[/TR]
[TR]
[TD="align: center"]00114[/TD]
[TD]ABC Insurance Co.[/TD]
[TD="align: center"]28-Jun-2014[/TD]
[TD="align: center"]$ 2200[/TD]
[TD="align: center"]$ 102.67[/TD]
[TD="align: center"]$ 495.00[/TD]
[/TR]
[TR]
[TD="align: center"]00115[/TD]
[TD]XYZ Attorneys[/TD]
[TD="align: center"]16-Sep-2014[/TD]
[TD="align: center"]$ 2400[/TD]
[TD="align: center"]$ 112.00[/TD]
[TD="align: center"]$ 876.00[/TD]
[/TR]
[TR]
[TD="align: center"]00116[/TD]
[TD]ABC Insurance Co.[/TD]
[TD="align: center"]22-Nov-2014[/TD]
[TD="align: center"]$ 3200[/TD]
[TD="align: center"]$ 149.33[/TD]
[TD="align: center"]$ 1466.67[/TD]
[/TR]
[TR]
[TD="align: center"]00117[/TD]
[TD]XYZ Attorneys[/TD]
[TD="align: center"]07-Dec-2014[/TD]
[TD="align: center"]$ 2600[/TD]
[TD="align: center"]$ 121.33[/TD]
[TD="align: center"]$ 1313.00[/TD]
[/TR]
[TR]
[TD="align: center"]00118[/TD]
[TD]ABC Insurance Co.[/TD]
[TD="align: center"]29 Jan-2015[/TD]
[TD="align: right"]

$ 2400​

[/TD]
[TD="align: center"]$ 1200.00[/TD]
[TD="align: center"]$ 2412.00[/TD]
[/TR]
</tbody>[/TABLE]


The real challenge lies in the fact that there are several customers each making several such transactions on different dates throughout the year and that the formula needs to differentiate whether it is the first payment or one of the consecutive instalments and if the transaction has been fully paid.

Thanks for your help.

Rgds
Bjorn75

 

[Bjorn75]

Yes, the H was a typo (instead of E).
I pasted your formula into column E and column F automatically showed all the desired results, too.
I then pasted the second formula into column F, just to see what would happen, and all the results for 00113 - 00118 showed up incorrect (all of them 4 digits).

But nevermind, I have what I needed and I am eternally grateful for you helping out this stranger in Germany as it saves us a couple of hours of administration every month.
And thanks for replying so quickly every time, your dedication is admirable! (This was my first time seeking help in an online forum, by the way!)

 

[RonB1111]

You're welcome! Thank you for providing feedback.