I have been working with the Excel template from microsoft that
calculates my loan repayments. This has been OK if the loan is paid
out in full by the end of the contract.

I need a template that has a balance owing (residual or balloon),
even better, one that I can enter the % residual and compare loans if
need be.

This is needed for two reasons.
1. to work out exactly what my principal and interest is each month
2. to compare different residual options for loans

There are lots of loan templates, so it's difficult to comment without
knowing exactly which one you are using.

You could look where the spreadsheets calculates your payment (it will be a
PMT function), then modify it to suit your situation (by entering a value
for the FV parameter).

You could also roll your own, as amortization tables are not hard to do. You
need five columns:

A. Opening Balance = previous period's opening balance
B. Payment (calculate using PMT function)
C. Interest (=A*IntRate/NumPeriodsPerYear)
D. Principal reduction (=B-C)
E. Closing Balance (=A-D)

Typically your PMT function would reference a table of values where you
enter the data you want. Once you have your basic table, you can embellish
it to suit your preferences.

Regards,
Fred.

wrote in message
...
I have been working with the Excel template from microsoft that
calculates my loan repayments. This has been OK if the loan is paid
out in full by the end of the contract.

I need a template that has a balance owing (residual or balloon),
even better, one that I can enter the % residual and compare loans if
need be.

This is needed for two reasons.
1. to work out exactly what my principal and interest is each month
2. to compare different residual options for loans

Generally, there are available different software which helps to count the calculate of the interest. As per the principal and duration terms. Another thing is that there is required to get the remaining amount of the principal to pay.