I am often faced with paying late alimony, and I want to be sure that the interest calculations they are hitting me with are correct. Can Margill Law Edition help me out?

/in , , /by

Question:

I am often faced with paying late alimony, and I want to be sure that the interest calculations they are hitting me with are correct. I pay 4% simple interest only on late or fractional payments. I am testing your software right now, and if I am say $500 short but pay it within 21 days the Recurring payments tell me that there is over $60 in interest, rather than the correct $1.15 ($500 late for 21 days at 4% simple annual interest). Is there another module I should be using for my application?

Answer:

You should be using the “Arrears” calculation (not rRecurring payments) and then you’ll get the $1.15…
With your example, 21 days late, you must enter the 500 alimony due on the 1st for example and then when you pay it, enter -500 on the 21st. So it keeps a running tab. You only enter the alimony missed or late, not if paid.
I don’t have your number of missed arrears but here’s an example. No missed arrears until July 1, 2020. Then Compute:
Results – nothing really intersting so far since I only have an amount due on July 1:
Then I add lines and a running balance continues.
In this example the July 1 amount is never paid but the September 1 amount is not paid on Sept 1 but on Sept 21.
I then started paying all my alimony and want to know the total amount owed today (Oct 20, 2021 at end of day). I added a line with a 0.00 payment:
If interest rates change, you can also change the rates…
You can even add irregular arrears before crating the scheule via Excel (if you have many).
Hope this helps
Marc Gelinas, Attorney, MBA

How can I mass import “Unpaid” payments with an Excel sheet in Margill Loan Manager? I need to obtain the Outstanding payment amounts.

/in , , , , /by

Question: How can I mass import “Unpaid” payments with an Excel sheet in Margill Loan Manager? I need to obtain the Outstanding payment amount too.

Answer: Usually, when payments are NOT made (so were skipped or the payments returned for non sufficient funds (NSF), on a historical basis, these would simply be ignored and only the Paid payments entered (even partial and late payments)

However, in order to count the number of Unpaid payments and to obtain the Outstanding amounts, it may be a good idea to enter payments lines of 0.00 and include the payment that SHOULD have been paid, thus allowing Margill to calculate the Outstanding payment amounts.

One would go through the “Post payment” tool under “Tools”. On the far right is the “Bulk Payment Import” button. You need “Import new payments”.

This mass (or bulk) import tool allows you to import payments (Paid pmt, partial pmt, late pmt, etc.) (as well as additional principal – a negative amount – and column fees and other information in the Results or payment table) but does not allow the import of Unpaid payments of 0.00. So we must be a little creative…

The tool does allow the import of what are called “Other” Line statuses. “Other” Line statuses never pay interest or principal – they are made to manage special scenarios and allow you to add more data in bulk such as Column Fees or other information in columns to the right. If the Outstanding amount was not important you could rename, for example, “Other 3” to “Unpaid” and mass import these. However, when “Other” is added, since this is not a real “payment”, no matter how it is renamed, an amount in the “Expected Pmt”  column will not affect the Outstanding as an Unpaid Pmt does (see example below where Other 3 does not increase the Outstanding to 1000):

In the question at hand, the Outstanding amount is required, so we cannot use an “Other” Line status with a payment of 0.00.

What can be done however, and this will be our solution, is to use a “Paid Pmt (x)” Line status, rename it to “Unpaid…” (renamed to “Unpaid Special” below) and mass import this Line status with a payment of 0.00 and an “Expected Pmt” for the amount that was supposed to be paid.

Margill allows “Paid” type Line statuses with a payment of 0.00. A little odd I agree, but this allows for greater flexibility. Even with the name “Unpaid”, the payment must not necessarily be 0.00 as in a real “Unpaid” Line status (line 6 below “Unpaid Visa” where must =0)

Once this Line status is created, in Bulk Payment Import > Import new payments, find the appropriate number for “Unpaid Special” (6 in this case – this is not the Line status order as in Line status Settings that vary depending on the order you desire). The Excel sheet must contain data and a header in columns A, B, C, D and L.

Here is the Excel sheet with only 2 loans. Notice I also added fees (column T for my Admin Fees)

Bulk import window:

Final result in Record 10003 after pressing on “Insert lines” with an Outstanding of 1300:

You can even get the number of each and every Line status through “Personalized Reports” > “Record List” (“Tally” theme):

Is Margill Interest calculator or Margill Law calculator able to create a compound interest calculation with irregular payments and interest rates that change over time?

/in , , /by

Q: Is Margill Interest calculator or Margill Law calculator able to create a compound interest calculation with irregular payments and interest rates that change over time?

So is my understanding correct that your software will be able to work out the balance owing in terms of an overdue loan with compounded interest based on the following example:

Initial amount o/s            R 250 000             As at 1/01/2020                 Interest rate 7%

Interest rate changes as follows:

Format is DD/MM/YYYY
01/07/2020                         7.5%
01/09/2020                         8.25%
07/11/2020                         8.75%
05/02/2021                         9%

Payments received (R = Rands – South African currency):

5/02/2020            R 2 000
7/03/2020            R 5 000
1/04/2020            R 1 000
30/6/2020            R 500
5/08/2020            R 500
3/09/2020            R 500
15/01/2021            R 5 000
7/02/2021            R 2 000

What is the amount o/s as at today, with interest compounded monthly? Today we are April 30, 2021

A: Yes, very easily. Such calculations are the purpose of the software. Margill can do thus much more easily than with a spreadsheet and a lot let risk of error.

This was not really necessary but I first constructed an interest table (you could have changed the rates manually in the resulting payment schedule).
Go to Rate Tables and click on New Table. You must use this icon to add the dates and interest rates: .
Save the table. I saved it under the name “Tanya”…
Then go to the very powerfal calculation called “Recurring Payments”. Here is the information to first plug in (never mind my the $ currency –  my Windows currency is the $ – yours would be R).
Notice my Payment frequency is Irregular.
Notice also that I used an Interest Table instead of a fixed interest rate – we see the Tanya table.

Now to enter the irregular payments, click on . This window shows up.

You can either entre the payments manually in here (even add negative amounts that are principal increases):

Even if there weren’t many payments, I created a small Excel sheet for the import – very useful when there are dozens, hundreds or thousands of payments:

Then press on the Excel icon and select the Excel file:

Now Save and you get back to this window. Notice the check  that shows there’s data in the Irregular payments. We are now ready to Compute the Results table.

You will see this window appearing that asks until when do you wish to compute interest. I entered April 30. Interest will be computed until April 30 in the morning, not end of day.

Press on OK and we have the results.

Notice I added a line at the end just in case I wanted the interest until April 30 end of day (midnight) – so added a line with May 1. Industry standard says first day included, last day excluded. Use the icons on the far right to add, insert and delete lines. There’s also an Undo button.

You can save and update this table as required – you can add lines, change payments, insert payments if by error you had forgotten one, add or change interest rates, etc. You can do just about anything with this calculation.

The right mouse click offers many options:

 

Hope this answers your questions…

 

 

How can I create a schedule where I can see the interest that accrues on a daily basis, every day?

/in , , , , /by

Q: My law firm must calculate the interest from June 30, 2019 to May 28, 2020 on an amount due by an insurance company. I must be able to see the interest that accrues every day.

Interest rate is 4% annually and amount is 150,250.33.

A: In Margill Law Edition, you would usually use the “Interest on one amount between two dates” calculation:

Data entry:

This would give you the amount as two lines with a split on December 31 at midnight:

I know, you want detail, lots of it, on a daily basis so instead of using “Interest on one amount between two dates”, use the very powerful “Recurring Payments (Amortization)” calculation that can do just about anything, not only loans or mortgages.

Here is how I would enter the data to see the payments every single day. Notice:

  • “First Payment Date” is one date after my “Origination Date” or start date
  • “Payment Method” = “Payments set to 0.00”.
  • For “Number of Payments”, I right clicked with the mouse to enter 05-28-2020 and Margill calculates a cool 333 payments (of 0.00)

We get a 333 line schedule with the daily interest for each day.

We get almost the same amount as in the calculation done with “Interest on one amount between two dates”. We are higher by 0.66 since the calculations below are done line by line and the 2 decimal point pennies leads to this slight difference.

—————————————

In Simple interest, using the Actual/Actual Day count, the interest in 2019 is slightly higher than in leap year 2020. This also could have be done in Compound interest where the daily interest would change almost every day.

Remember the interest for the end date is excluded. So interest does not include the interest for May 28, 2020.

Margill Law/Standard Edition – Regular and Irregular payment schedule

/in /by

Question:

I was wondering who I could contact at Margill for help on calculating a rather complex amortization table.

Beginning on 6/25/17 – the principal loan amount was $640,000 at an interest rate of 3% – payments of $3,000 to begin on 1/1/2018.

  • However, a payment of $350,000 was made on 7/12/2017
  • Additional loan amount of $500,000 was made on 9/25/2017
  • Additional loan amount of $150,000 was made on 12/8/2017

Answer:

Actually quite simple:

Go to “Recurring Payments” calculation. We will suppose this is compound interest, compounded monthly and monthly payments since it is a loan.

Let’s suppose 20 payments of $3000 each (if payments are missing or if there are too many, you can add or delete after).

Enter the data below:

Then Compute or F5.

This is our preliminary schedule:

 

You can now edit the schedule by adding the lump sum payment and extra principal.

The payment and extra principal were made in 2017 so I should add three lines above my payment date of 2018-01-01

I can do this with this icon or the right mouse click:

Then change the dates – start with Line 3 since you must follow the chronological order:

And finally change the amounts (1 payment and 2 loans). Notice lines 2 and 3 are negative since we are adding principal.

Now of course, with a balance of 934,064.93 after 20 payments, we would need to add a bunch of payments to fully pay back. You could add these lines with this icon (we would need to add hundreds of payment since payments are quite low):

My guess is you are looking for a balance at a specific date (let’s say today, start of day). Insert a line with a 0.00 payment:

And there you have it.

How to do erratic payments in Margill Standard/Law Edition

/in /by

Question:

How to do erratic payments in Margill Standard/Law Edition

Answer:

Pretty simple. Once you entered all your loan data. Click Compute, and you will get to the payment schedule:

From there, you can change the Pmt date, the Payment amount, and the Rate.

You can even use the right click button to get many more options!

It is also possible to add or remove lines.

The totals will then be recalculated with the new schedule.

The Margill Team

Margill Standard/Law versions: How to do an irregular payments schedule

/in /by

Question:

I have a principal of $200,000 starting 03/01/2017 (over 24 months) and the last payment to be made on March 1, 2019.

First payment is on 4/1/2017 at unknown amount.

Two $50,000 lump sum payments to be made on 05/01/2017 and 05/01/2018.

I must also compute the payments in between the $50,000 payments.

Interest at 5 percent.

Can did this be calculated in one calculation?

Answer:

Yes. Pretty easy to do in fact.

Go to “Recurring Payments” caclulation.

I will suppose compounding is Monthy.

Enter this preliminary data

Compute or F5 to get these preliminary results:

You can totally adapt this to your needs.

We know payments of 50,000 are to be paid on 05/01/2017 and 05/01/2018. Change these directly in the schedule.

As for the payments in between, they must be recomputed so as to reach a balance of 0.00 at the end.

Select all lines (Ctrl A) then exclude the 50,000 lines (Ctrl click on lines 2 and 14) and right click with the mouse. I want my balance to be 0.00.

And here you have it. My last payment if off by a few cents so I checked “Balance = 0.00” on the bottom right.

Save that calculation and you can then adapt to what happens for real over time.

Took less than a minute….

Client comment after post:

Marc, I just checked it. You made four people very happy today. One client, two attorneys, and me.

 I really appreciate your availability, patience, and instruction.