Help with a Debt / Variable Payment Question

[Marl]

A part of a larger financial model I have the following problem.

The debt period is 15 years with the cash available for debt service in a row with amounts for each annual period (15 cells). The cash available for debt service is consistent for the first 10 years and then declines significantly to a consistent value for the remaining 5 years. Using PPMT and IPMT functions I calculate the periodic principal, interest, declining balance, etc.

I want to optomize the debt load to achieve maximum debt without exceeding the value of cash available for debt service in either the first ten years or the remaining 5 years. How can I alter payment structure bring about a payment reduction in year 11 that is proportional to the reduction in cash available for debt service?

Any suggestions would be appreciated.

 

[plettieri]

Hi Marl:


Have you looked at the 'goal seek' function found under the tools menu bar?

plettieri