Forum Discussion
Perplexing problem, looking for a possible solution.
A friend of mine asked me to create a solution to this problem. I thought this would be a simple spreadsheet but the solution has been eluding me.
Issue:
A client owes an outstanding balance and would like to setup a payment arrangement. The client determines the number of payments.
Variable:
- The bill is sent to the client on August 1st.
- The bill must be paid in full by May 15th.
- There is no interest charged for the months of Aug, Sept, Oct, Nov and Dec.
- On January 6th there is 2% interest charged on the outstanding principal balance.
- On the first of each subsequent month the outstanding principal balance is charged 0.75% interest. (Feb, Mar, Apr, May).
*My friend would like for all of her employees to enter a principal amount, a start date and the number of payments. The spreadsheet would create the repayment schedule.
Has anyone every seen a repayment spreadsheet that calculates variable interest on a monthly basis? The January 6th and May 15th dates are throwing me off.
9 Replies
Started looking at this but realised that there is a need for some more clarification in order to be able to set up the "rules" for such a payment schedule.
1) What is the logic of the January 6 date? Is it always the 6th day after the beginning of the 6th month after the start date of the loan? E.g. if the loan starts, February 1, would the first interest date then be July 6? But, asking the question makes me wonder why not just use the first of that month as the interest date?
2) What if the repayment schedule is shorter than 5 months? No interest then?
3) What is the relevance of the 15th a month as the end date? Can it also be e.g. the 1st, the 10th or the 30th?
Can you upload an example of what results you expect to see in the example you gave.
Good morning and I'm extremely interested in how someone would tackle this problem.
I agree that some clarification is needed.
I'm assuming this is a tax bill being mailed to clients on August 1st and must be paid by May 15. I'm fairly certain that May 15 is an arbitrary date and could be extended to May 30, to simply this problem.
The monthly payment arrangement would not incur interest from Aug - Jan 5th. On January 6 the outstanding principal balance would incur a 2% penalty. Each subsequent month (on the 1st) would incur a 0.75% penalty on the outstanding balance (Feb, March, April, May).
No payment arrangements would be setup or have payments scheduled through the months of June and July.
Payment Arrangements By Month in Which They Occur:
August, September, October, November and December = no penalty
January 6th = 2% penalty on outstanding principal (not sure if Jan 6 is arbitrary date but maybe to simplify the formula, this could be calculated using a January 1 date)
Feb, March, April and May = 0.75% penalty charged on outstanding principal balance as of the first of that month.
Any thoughts on how you would tackle this issue would be appreciated. I have numerous spreadsheets created using different methodologies but I can get any of them to work correctly. The variable penalty rates is throwing me off.
Still not sure what you/your friend needs, but have a look at the attached schedule. If it is a tax payment schedule (as you suspect), enter the monthly payments made (or to be made) and see what happens. In my example, I assumed that the "customer" pays 1/10th of the original balance plus interest accrued for the previous month.
