Calculate Loan Amoritization Payment Schedule from data and add Result to Table

I've combined many different table views of MySQL database information resulting in a Public 'search' all locations application for site visitors and a Private 'manage my location data' for the owner.

Each property has potentially two parties making lease/loan payments on the properties, of which I have the following variables [with sample data for reference]:

Commencement date 1/1/08
Initial Term  5
Renewals  5
Renewal term  5
Escalator  10%
Escalator term  Term
Annual amount - $6000
Payment Schedule  Monthly

The Calculation needs to work as such:
Take the commencement date through the number of years (initial term) for first term.  That gives you the first date range, annual amount is the rent for that first term.

Next line will be the end of the first term through the number of years for renewal term, 2nd date range, annual amount for the 2nd date range is the annual amount (or last terms if you are talking about term3 and on) X 1 +(escalator in decimal form) - continuing for the number of renewals.

So this example would result in:
Payment schedule
1/1/08  12/31/13           $6,000
1/1/14  12/31/19           $6,600
1/1/20  12/31/25           $7,260
1/1/26  12/31/31           $7,986
1/1/31 - 12/31/36            $8784.60

Then I need to add that Payment Schedule to the existing 'private management view for the owner' or perhaps link to an expanded schedule.

How can I accomplish this?  What's the best route considering future variable updates, etc.

Start Free Trial