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.