OK, the simplistic answer is "Using Excel's Annuity Functions".
Get into the Excel Help and read the topic "PV", particularly, the formula
at the bottom. If you are comfortable with that formula, then we can talk
you through this.
Otherwise, ask your accountant!
Doing what you need in Excel is not difficult. But knowing what you need to
do, can be very complex; and the consequences of getting it wrong are
unpleasant
Generically, you need to create four calculations:
1) The amount by which the principle and interest were reduced when he was
making the full payment.
2) The amount by which the interest was reduced while he was paying
interest only.
3) The net present value of the loan.
4) A payment schedule covering principle, interest, and any balloon payment
in the future.
Effectively, you have three "phases":
Phase one: in which principle and all interest were being paid.
If you have a conventional loan agreement, each payment is applied first to
the interest (compounded on the outstanding value at the end of each payment
period), and the balance of the payment reduces the principle. So each
payment, the interest is fully covered, and the principle gets a little
smaller, so the interest is less next period.
All being normal, this is a Net Present Value" annuity calculations using
the PV function.
Check the wording of your agreement carefully: there are various other ways
of writing loan agreements, including the very nasty "Rule of 78" loans
which have some really weird schedules.
Phase two: If the interest is fully covered, there is no compounding, the
principle remains unchanged. You simply need to check that the interest WAS
fully covered.
Phase three: For a conventional loan agreement, you can treat this as if
the loan of the remaining outstanding principle (calculated in Phase 1)
began on the day that full payments resumed (calculated in Phase 2).
Hope this helps
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I
have a loan with a fixed interest rate. The person I lent money was unable to
pay the full amount for about 11 months, so only paid interest. Now he's
paying interest plus a little toward principal.
How can I track the amount of the principal that's due after each of the
varied payments?
This email is my business email -- Please do not email me about forum
matters unless you intend to pay!
