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
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.