Interest claculator that allows payments and advances

[Greg]

Dear all,

I have been using the attached document to calculate interest on judgment debts that have been reduced by occasional payments. The document was constructed with the help of a member whose name I cannot remember.

However, I now wish to modify the document so that "Advances" can be made as well as payments. In that regard I have added a button "Make New Advance" but I haven't done anything else.

I want the button to bring up a form to enter the value of the "New Advance" as well as the date that the New Advance is made. If possible, the entered information will appear on a row of the document and be taken into account and recalculate the interest payable from the date of entry to the current date, or the date that a new entry is made, whichever comes first.

I could mess around with this for ages and still not get it right. With luck a member will know exactly what to do.

Thanks in advance.

Greg.

[SamT]

What is the difference between an Advance and a Payment?

Looking at the formulas in Daily Rate, I notice that they reference Dates starting in Cell F3 and down.

There are no dates in Column F until Row 20! The Date with a value of 0 is 1/1/1904 so all the Daily Rate values for the first 17 payments will be based on 1904, then they will be based on the date of the payment 17 rows above.

After correcting? the formula to use the Payment Date, it shows an increase in the final balance of $83.49.

I also notice that you are adding one day to each period between payments. IOW, the first payment occurred 386 days after the judgment, but the calculator is using a 387 day period to calculate the balance. Is this deliberate?

Changing this formula to use actual days between payments and the date of payment as above reduces the final balance by $391.83.

[Greg]

Hi Sam,

The document should probably refer to debits and credits. Payments being credits and Advances being debits.

On my document the start date is at J8 and you are correct in saying that there are no other dates until F20. Please note that I am using dates based on dd/mm/yyyy.

I now see what you mean about the extra day. It was probably deliberate but is clearly wrong.

Aside from those issues do you have any comment on how best to apply Advances (i.e. Debits)?

[Greg]

This is a better way to calculate the date difference.

=DATEDIF(F20, H20, "D")

[SamT]

I don't really understand the purpose of the Day Function in the Daily Rate column.

IF the year of the date in the Period End Column (F) is 2015, Then, regardless of the actual date in column F

DATE(YEAR(F20),1,367) = 1/3/2016
and DAY(1/3/2016) = 3

The Day will equal 3 for every date in 2015. Since 2016 is a leap year, Day will be 2 for every date in 2016.

I would assume that you would want the Daily interest, @ 36% APR, on the previous balance. There are 3 common ways to determine interest rate; Based on a 360 day year; Based on a 365 day year; And based on the Calendar, (365 days, except after Feb 29, then 366.) The Calendar year is very rarely used due to the complicated computations that must be done based on the actual date.

Usually monthly payment Interest rates are based on the 360 Day year. With an actual APR of 36%, the monthly rate on the unpaid balance is 36/(360/30) or a fixed 3% per month. Basing the rate on a 365 day year with monthly payments would mean that the monthly rate would vary from 36/(365/32) to 36/(365/28) depending on the month and year. It is just not done.

More frequent payment schedules will be based on a 52 week year or 365 day year. The weekly rate would be .6932% even though the actual year is 1 or 2 days longer than 52x7. The daily rate is a fixed 36/365, or 0.09863% even in leap years.

These rules are acceptable in GAAP.

You seem to be using a Daily payment schedule with no penalties. I recommend that you base the Year on 365 Days. Actually, I won't do a calendar based rate, so. . .



I just spent the last several hours working on the UserForm. Then had a Catastrophic Failure, whatever that is and could not save it.