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.

Order No.	Customer Name	Purchase Date	Purchase Price	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)	Amount Outstanding (as at 31 Jan 2015) = Purchase Price - Amounts Due to Date + Amount Due This Month
00111	ABC Insurance Co.	10-Nov-2013	$ 2400	$ 0.00	$ 0.00
00112	XYZ Attorneys	05-Jan-2014	$ 2600	$ 121.33​	$ 121.33
00113	ABC Insurance Co.	12-Apr-2014	$ 2800	$ 130.67	$ 368.67
00114	ABC Insurance Co.	28-Jun-2014	$ 2200	$ 102.67	$ 495.00
00115	XYZ Attorneys	16-Sep-2014	$ 2400	$ 112.00	$ 876.00
00116	ABC Insurance Co.	22-Nov-2014	$ 3200	$ 149.33	$ 1466.67
00117	XYZ Attorneys	07-Dec-2014	$ 2600	$ 121.33	$ 1313.00
00118	ABC Insurance Co.	29 Jan-2015	$ 2400​	$ 1200.00	$ 2412.00

<tbody>
</tbody>

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

 

[RonB1111]

Enter these formulas and copy down:
In E2: =IF(DATEDIF(C2,$G$1,"m")>12,0,IF(DATEDIF(C2,$G$1,"m")=0,D2/2,D2/2/12+D2/2*0.01))
In F2: =IF(DATEDIF(C2,$G$1,"m")>12,0,(12-DATEDIF(C2,$G$1,"m"))*D2/2/12+E2)

The formulas are based upon your verbal description because I can't figure out how you calculated the Amount Outstanding for Orders #: 00113 thru 00116. They are inconsistent with 113, 117 and 118. Let me know whether the formulas are giving you the correct amounts, otherwise, please explain how the Amounts Outstanding are to be calculated.

 

[Bjorn75]

Thanks for your very useful input. I was completely unaware of the DATEDIF function and the formulas you provided do help me along 95% of the way. Two issues, though:

1. Amount Outstanding for Order 00113 should be $368.67 - I have calculated this as $2800 - $1400 (due on 30th April 2014) - 8 x $130.67 (due May - December 2014) + $14 (1% of $1400) (3 cents rounding difference)

2. The first payment of 50% is due on the last day of the month, therefore we do not calculate from the actual date of purchase but from the last day of the month on which the purchase was made. Is there a way to tweak the formula to take this into consideration?

 

[RonB1111]

Glad you're almost there. Just need some additional clarification:

1. For Order 00113, you're showing "8 x $130.67 + $14". The $130.67 already includes the $14 (eg: 1400 / 12 = $116.67 +$14 = $130.67. This explains why I was confused because in Order 00116 you show $1313 outstanding at 1/31/15 which is half of $2600 + only 1 months fee of $13. So I assumed that you didn't recognize the monthly fee until the month the fee was incurred, otherwise Order 116's outstanding amount would have been $1300 + 12*$13 = $1456. I was also confused because on Order 00117 you're showing a total amount of $2412 which appears to be the total order $2400 + $12 for 1 months fee, but then there would be a total of 13 months fees paid instead of 12 which you verbally describe in your example. Also, I'm still not sure how the outstanding amounts for Orders 00114 and 00115 are calculated, and Order 00116 seems to be $1600/12*11 without any monthly fees. So I'm sure I can modify the formulas if you could confirm or clarify when these monthly fees are to be added to the outstanding amount.

2. I don't understand what change you're requesting here. Would you show an example showing what my formula calculates vs the amount it should be.

 

[Bjorn75]

Hi again and thanks again for your time and effort spent on my problem!

1. You assumed correctly that I do not calculate the monthly admin fee until it is incurred and I realise now that I had a mental lapse in the way I calculated the "Amount Outstanding" and that your formula does indeed calculate it correctly. The correct approach is as follows: "50% balance amount divided by 12 multiplied by the number of remaining months (including the current month) plus one month's admin fee". The only discrepancy which remains lies in order 00114, and I assume that this is connected to the problem described in point 2 below.

2. The first of the 13 months (at the end of which the 50% downpayment becomes due) will always be a partial month, i.e. even if a purchase was made on the 1st of a month, it will not be a 100% full month until the 50% downpayment becomes due on the 31st as it is only 30 days later and not 31. However, the formula always calculates a full 30 or 31 days ("m") from the purchase date, even if the purchase was made on the 29th and the downpayment becomes due 2 days later already.
In other words, what the function should do, is regard the first month as a partial month which ends on the 30th/31st (or on the 28th/29th in the case of February) and regard each of the subsequent 12 months as full months beginning on the 1st. The reason for this being that the monthly admin fee for the subsequent 12 months applies fully as of the 1st day of the month and is not charged pro rata.

(It is difficult to explain without visual aids, but I hope I have described the problem a bit clearer now.)

 

[RonB1111]

1. I'm sorry but it isn't clear what the problem is. So let's start with my calculation of the outstanding amount for Order #00114. My calculation is:
Monthly Payment is: $2200 / 2 = $1100 / 12 = $91.67/mo + $11 fee = $102.67/mo incl fee (I think we agree on this).
Outstanding: $91.67 x 6 months = $550.02 + $11 fee = $561.02 (My formula doesn't round the 91.67 so it gives $561.00) The 6 months are Jan thru June. You're amount is $495 but I can't figure out how that's calculated. What should the outstanding amount for Order 00114 be?

2. I'm believe my formula does what you're saying. The amount due on the last day of the Purchase month is the half the purchase price. The outstanding amount on the last day of the Purchase month is the Purchase Price + the admin fee for 1 month. It doesn't matter whether the Purchase Date was the 1st or 31st or any day in between. For the next 12 months, the "amount due this month" is half the Purchase Price divided by 12 + 1 month's admin fee. The Outstanding amount is half the Purchase Price divided by 12 x the number of months remaining + 1 month's admin fee. If you could provide an example showing where my formula is not giving the result you expect then I'll be able to modify the formula.

 

[Bjorn75]

The outstanding amount of $561 for order 00114 is correct, no disagreement on this. However, it only shows the correct amount if cell G1 = 2015/01/28 or higher. The reason for this is that the purchase date is the 28th and the formula is counting full months starting from the 28th, i.e. each order's cycle begins on the day of purchase, but the cycle should begin on the first day of the month following the month of purchase. (You must please ignore the classic way of calculating interest, whereby each individual day is counted. In my case, the full 1% monthly admin fee applies regardless of whether the customer settles their account on the 1st or the 31st of a month.)

The value displayed in cell G1 is the current date, i.e. =today(). Therefore, if I print an account statement today, i.e. on 23rd January, the outstanding amount for 00114 will show as $652.67; if I print the account statement on 28th January, the outstanding amount for 00114 will show as $561.00. But I need the account statement to show $561 throughout the entire calendar month from the 1st to the 31st. This applies to all orders, i.e. the first 50% becomes due on the 30th/30st of the month of purchase, regardless if the purchase was made on the 2nd or on the 29th of the month. And then the admin fee cycle starts on the 1st of the subsequent month for all orders.

I suppose the easy way out would be to insert a column next to the purchase date in which the due date for the 50% downpayment is entered manually, i.e. the 30th/31st (or 28th/29th for February), and then the formula will inevitably always start calculating from the 1st of the subsequent month. But my aim is to minimise the amount of human input so as to avoid error sources.

Other than the above, I cannot think of a better way to explain my problem. Sorry!

 

[RonB1111]

Now I understand. Looking back at my original response, I forgot to state that G1 would always contain the last day of the month. This could be either manually entered in case you wanted to know the balances for months other than the current month, or if that wasn't important, then you could use a formula in G1 like: =EOMONTH(TODAY(),0)

 

[Bjorn75]

The EOMONTH function definitely is the key to the solution here... thank you!!! If I incorporate this function into the formulas, they would look as below and should produce the correct values. But, for some reason they don't for orders 00117 and 00118.

Amount Due This Month: =IF(DATEDIF(EOMONTH(C2,0),EOMONTH($G$1,0),"m")>12,0,IF(DATEDIF(EOMONTH(C2,0),$G$1,"m")=0,D2/2,D2/2/12+D2/2*0.01))
Amount Outstanding: =IF(DATEDIF(C2,$G$1,"m")>12,0,(12-DATEDIF(C2,$G$1,"m"))*D2/2/12+H2)

Based on today's date, the worksheet now looks like this:

	A	B	C	D	H	F	G
1	Order No.	Customer Name	Purchase Date	Purchase Price	Amount Due This Month	Amount Outstanding	2015/01/28
2	00111	ABC Insurance Co.	2013/11/10	$ 2 400.00	$ -	$ -
3	00112	XYZ Attorneys	2013/12/23	$ 2 600.00	$ -	$ -
4	00113	ABC Insurance Co.	2014/04/12	$ 2 800.00	$ 130.67	$ 480.67
5	00114	ABC Insurance Co.	2014/06/28	$ 2 200.00	$ 102.67	$ 561.00
6	00115	XYZ Attorneys	2014/09/16	$ 2 400.00	$ 112.00	$ 912.00
7	00116	ABC Insurance Co.	2014/11/22	$ 3 200.00	$ 149.33	$ 1 482.67
8	00117	XYZ Attorneys	2014/12/07	$ 2 600.00	$ 1 300.00	$ 2 491.67
9	00118	ABC Insurance Co.	2015/01/20	$ 2 400.00	#NUM!	#NUM!

<colgroup><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

The correct results for orders 00117 and 00118 respectively should be $ 121.33 / $ 1 313.00 and $ 1 200.00 / $ 2 400.00
I have been sitting over this for 2 hours now and just cannot figure out the problem.

 

[RonB1111]

I would have put the formula =EOMONTH(TODAY(),0) in cell G1 and kept the original formulas. If it was important to have Today's date displayed on the sheet, I'd have put that in another cell. However these modified formulas will work:
In E2 and copy down:

=IF(DATEDIF(C2,EOMONTH($G$1,0),"m")>12,0,IF(DATEDIF(C2,EOMONTH($G$1,0),"m")=0,D2/2,D2/2/12+D2/2*0.01))

In F2 and copy down:

=IF(DATEDIF(C2,EOMONTH($G$1,0),"m")>12,0,(12-DATEDIF(C2,EOMONTH($G$1,0),"m"))*D2/2/12+E2)

The EOMONTH goes only in front of G1's in the formulas, not the C2's.
Also, I'm not sure why in the 2nd formula you changed the ending from E2 to H2. In your sample data, you are showing column A B C D H F G - I assume the H was a typo.