Forum Discussion
Perplexing problem, looking for a possible solution.
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.
Riny,
Thank you again for your help, it is sincerely appreciated. Please look at the schedule that I've attached. The first payment due would not always start on August 1st. The first payment could start on the first day of any month. This spreadsheet will create the repayment schedule based off the date entered in cell G4 and the number of payments entered in G12.
I need to create a formula for cells D8 through D19 that would charge variable interest like this...
0% interest for payments in Aug, Sept, Oct, Nov, and Dec.
2% interest on the previous months principal balance in January.
0.75% for the months of Feb, Mar, Apr and May.
If a formula could be created to calc the interest, the cells G8 through G19 would be removed, since they would be unnecessary.
Any suggestions?
