Iteration and Looping Code

[gator02]

i think this is a tough one (at least it is to me), here is my situation:

I work in the bond industry (primary markets), we bring new issues to market and are responsible for "sizing" and structuring the deal. By structuring I mean structuring the amount of principal that needs to be amortized (can be issued) each year under the given constraints (revenues) of the issuer. Below is an example of a principal amortization schedule.

Principal

3,575,000.00
3,720,000.00
3,870,000.00
4,020,000.00
7,295,000.00
7,585,000.00
7,890,000.00
26,835,000.00
71,930,000.00

Notice that these are rounded into $5,000 blocks (that's how bonds are generally sold to institutional investors). This becomes important after viewing the code below. The aforementioned code listed below "fills" these principal amounts without exceeding the given constraints. Drum roll...here comes the code:

Calculate
Sheets("Debt Service").Select
Beep
Application.StatusBar = "Interpolate Level Debt Service"
Range("watch_1").Select
If Range("Rounding").Value < 0 Or Range("Rounding").Value >= 5000 Then
guess1 = 0
guess2 = 1000000
Range("Target").Value = guess1
Calculate
result1 = Range("Rounding").Value
Range("Target").Value = guess2
Calculate
result2 = Range("Rounding").Value
Do While Range("Rounding").Value < 0 Or Range("Rounding").Value >= 5000
guess3 = guess2 - (result2 * (guess2 - guess1) / ((result2 - result1 + 0.000001)))
Range("Target").Value = guess3
Calculate
result3 = Range("Rounding").Value
guess1 = guess2
guess2 = guess3
result1 = result2
result2 = result3
Loop
Calculate
End If
Beep
Application.StatusBar = False
End Sub

The debt service on this issue is a function of the principal amounts and the coupon (interest rate) on the individual principals. The code above works beautifully, however I need to add another condition to this macro and do not know how. I need all of the above to happen AND I need it to continue to happen until I have iterated to solve for the minimum TOTAL debt service (within the constraints obviously). Remember debt service is principal times the coupon. Any suggestions. Though this is long I still feel that it might need further explanation. I would gladly send a spreadsheet to anyone who is willing to help. Thanks in advance.

 

[Fryer Tuck]

Looks to me like you are in an endless loop already as your "Do while" relies on the value in Range("Rounding"), and that value never gets changed in the loop.

Am I missing soemthing?

 

[gator02]

Yeah but probably b/c of my poor explanation. If you are a borrower and you need to borrow $21,000 you can't because the principals are sold in blocks of $5,000, but you can borrow $25,000. That leaves you with $4,000 of rounding. Every bond issue has a range ("rounding") somewhere between 0 and 5000. That part of the code works but there are different ways to "size" the principal amounts under given revenue constraints to produce $25,000 worth of principal. Given a generous revenue stream there may even be an infinite # of ways to size/structure the deal, but i need to include some code that sizes the deal to produce minimum total debt service.

Principal * Coupon = Debt Service

sum of the principals must equal 25,000 but do so in a way that minimizes total debt service. The rounding cell is the sum of the principals (25,000) less the amount needed (21,000). Therefore, rounding = 4,000. Hope this clarifies, if you are sincerely interested in knowing more about how this works and the formula's behind the principal, etc i will send you a spreadsheet.