Forum Discussion
Perplexing problem, looking for a possible solution.
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?
Hi, I think your schedule is doing exactly what it is supposed to do (see attached). Fill in the correct %-ages in column G and the interest will be calculated automatically. Why would you want to calculate the interest in one (potentially complex) formula rather that doing it on a monthly basis with a simple ones with a SUM at the bottom?
By the way, I simplified your calculation for the next payment date, using the EOMONTH function. It does all the counting of days, months and years for you.
Love the EOMONTH function, thank you.
If you look at the spreadsheet you attached, it makes the assumption that all payment arrangements begin on 08/01 (Cell G4). That would allow the interest rates to be static in cells G8:G19.
If you change G4 to a different start date: 12/01/2019 that would throw the interest calculation off.
Is there still a simple solution (hopefully) or does this now require a semi-complex formula? Would the formula be an IF / THEN statement (if cell B8 is "specific month" (then multiple by x percentage).