loan calculator with residual balance

[brocklandsnursery]

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

 

[Fred Smith]

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.