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.
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.